Does AlwaysOn setup adds to row versioning on Primary replica

Udham Singh 41

Hi,

We have 4 replica set of an AOAG in SQL server 2016 Enterprise edition, 2 sync and 2 async. We have notice that on primary replica we are getting "NonSnapshot Version Transactions" counter value in sys.dm_os_performance_counters. And hence sometime it is triggering the "Longest Transaction Running Time" alert threshold > 50.

Nowhere we are using\changing the isolation level to SNAPSHOT or READ_COMMITED_ISOLATION level, it is the default one i.e. READ COMMITTED for every transaction that is happening.

Any idea how row versioning is happening on Primary replica.

Regards,
Udham Singh

3 answers

Dmitri Korotkevitch 101 Reputation points

2021-09-28T21:48:30.367+00:00

Hi All,

First of all - let me apologize regarding slight error in Apress blog post content. This is the old post and I corrected it in my upcoming troubleshooting and performance tuning book. :)

Udham is indeed correct - row versioning is not used on primary when it is not enabled. With readable secondaries, you would get 14-byte pointer added (beware of fragmentation impact). You would also have ghost cleanup (and version store cleanup when used) deferred based on oldest active transactions across all nodes. But SQL Server would not use version store on primary unless it is enabled or used by other processes (triggers, MARS, online index rebuild, etc).

Speaking of Longest Transaction Running Time - unfortunately, I don't know the answer. I cannot duplicate the problem in simple setup - active explicit transaction on secondary does not increase this counter on primary in my setup (would be easy to check in your environment with perf monitor, I suppose). I don't want to question monitoring strategy but I'd be a bit reluctant to use this performance counter to drive conclusions. It is not sampled frequently enough and it may be hard to correlate the information with the other sources. It is entirely possible that long running transaction on primary is gone by the time you received the alert.

So bottom line - why do you think long running transaction alert is triggered by secondaries activity instead of legit long running transaction on primary?

I would look at sys.dm_tran_database_transactions and other transactions DMV to confirm that the problem exists in the first place.

Sincerely,
Dmitri
Please sign in to rate this answer.

1 person found this answer helpful.
Udham Singh 41 Reputation points

2021-09-29T08:20:10.077+00:00

Really appreciate your response.
Since the environment where this alert is getting triggered is production so not really possible to replicate scenario. And don't have test setup handy at this movement.
If you can give a try on your test setup, below is the scenario when I am seeing the row versioning happening on Primary node and Longest Transaction Running Time counter is showing the activity on primary replica, hence ending up in triggering alert threshold less than 50. Same sessions are seen in dm_tran_active_snapshot_database_transactions DMVs.

There is a functionality that is considered as single transaction. So they begin the code execution under single transaction.

Select the data from a table based on the specific conditions lets say ID less than 100000.

Start the transaction - BEGIN TRAN
BEGIN THE LOOP lets say for 10000 times - assume we have 10K records to process
Based on each loop perform the DML operation (insert or update or delete) through SPs.
END LOOP

COMMIT or ROLLBACK

In the dm_tran_active_snapshot_database_transactions DMV, DML operation is showing getting captured. The scenario details I have collected from the DEV team after discussing the DML procedure code.

Erland Sommarskog 101.4K Reputation points MVP

2021-09-29T21:14:14.543+00:00

I can't avoid the feeling that that loop should be replaced with a single MERGE statement. Or at least three set-based INSERT, UPDATE and DELETE statements.

Udham Singh 41 Reputation points

2021-09-30T18:44:47.143+00:00

I got to replicate the scenario. And yes it is creating the row version on the Primary Replica and hence "Longest Transaction Running Time" counter is showing the activity on primary replica.

Environment details:
SQL version on all 4 nodes:
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64)
Mar 19 2021 19:41:38
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: )
2 nodes are in SYNC and 2 are in ASYNC.
Isolation level remain the default i.e. READ COMMITED for the session under which I am executing the transaction.

Transaction as follow:

Select * From table1
Select * From table2
Begin TRAN
Declare @ID int
Set @ID = 1
While @ID < 10000
BEGIN
Exec Updatetable1SP @ID
Exec DeleteInsertTable1SP @ID
Exec DeleteInsertTable2SP @ID
Set @ID = @ID + 1
END
Commit

I am not sure why row versioning is happening on Primary Replica under READ COMMITED Isolation level. I may connect with Microsoft Support team to understand this behavior or this could be some bug.

Dmitri Korotkevitch 101 Reputation points

2021-09-30T19:53:07.573+00:00

By "primary replica" you mean master, don't you? I assume so because you are trying to update data.

Do you have any triggers on the tables you are modifying? Those triggers may use row versioning with inserted/deleted virtual tables.

Udham Singh 41 Reputation points

2021-10-01T07:06:03.717+00:00

Primary Replica means on a user Database (part of AG secondary replica is readable) on Primary node. Both the table are simple and have 10K records in both.

Create table T1 (ID Int Identity (1,1), [Name] Char(20))
Go
Create table T2 (ID Int Identity (1,1), [Name] Char(20))
Go

No triggers are there on tables. Isolation level remain default only.

Erland Sommarskog 101.4K Reputation points MVP

2021-10-01T21:01:26.677+00:00

If the tables are that simple, there is even less reason to run this operation in a loop.

Udham Singh 41 Reputation points

2021-10-05T16:05:15.917+00:00

I created the simple table for replicating the scenario. In real environment they have there own setup and requirement of executing it in loops. lets not discuss on that.

I am wondering about the ROW VERSION happening on Primary replica. Why is that happening in the first place ?
Sign in to comment
Erland Sommarskog 101.4K Reputation points MVP

2021-09-24T21:52:27.903+00:00

I assume that at least one of your secondary replicas is a readable secondary? That will indeed triggering vesion-store handling on the primary, in the same manner as if you had enabled READ_COMMITTED_SNAPSHOT.
Please sign in to rate this answer.
Udham Singh 41 Reputation points

2021-09-25T09:00:26.727+00:00

Yes my secondary replicas are Readable. Well that make sense.
But still I don't understand it completly, could you share any blog post\link, where this is explained in detail.

Erland Sommarskog 101.4K Reputation points MVP

2021-09-25T09:20:20.73+00:00

This was my top hit on Google when I searched for "Readable secondary enables version store". I did not read it myself, but since I know the author well, I have faith in that it will be helpful to you. Dmitri knows his stuff.
https://www.apress.com/de/blog/all-blog-posts/readable-secondaries-in-sql-server/16064064

Udham Singh 41 Reputation points

2021-09-28T05:00:42.103+00:00

But I have understood from above shared blog and other blogs is:

When SI and/or RCSI are not enabled on the primary replica, but the secondary replica is enabled for read workload.
The row version is only generated on the secondary replica; because RCSI or SI is not enabled on the primary replica, there is really no need to create row versions there.
The row versions need to be generated on the secondary replica, which means that the 14-byte overhead needs to be added to the new and modified rows on the primary, because the primary and secondary replicas must be physically identical. "

So if row versioning is not happening on Primary replica then why "Longest Transaction Running Time" counter is showing the activity on primary replica ?

Udham Singh 41 Reputation points

2021-09-28T05:45:04.847+00:00

Today I had an occurrence of this alert, we had and TRNSACTION running on Primary replica under "BEGIN TRAN" for longer duration. On Secondary also same alert triggered at same time. Please see the details below.

From Primary:
tran_elapsed_time_ss : 876
WaitType : MISCELLANEOUS
open_tran :1
status : sleeping

From Secondary:
CMD : DB STARTUP
TRANSACTION_ISOLATION_LEVEL : READ COMMITTED
WaitType : REDO_THREAD_PENDING_WORK
open_tran :0
status : background
tran_elapsed_time_ss : 890
blocked: 0

Udham Singh 41 Reputation points

2021-09-28T11:43:10.3+00:00

This is what I have got. Any idea if Row versioning is not happening on Primary then why "Longest Running Tran TIme" counter is showing the activity.
Sign in to comment
YufeiShao-msft 7,056 Reputation points

2021-09-27T07:58:42.34+00:00

Hi @Udham Singh ，

This counter only shows activity when the database is under committed snapshot isolation level, it does not log any activity if the database is in any other isolation level.

But all queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set.
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-ver15
Please sign in to rate this answer.
Udham Singh 41 Reputation points

2021-09-28T05:04:40.713+00:00

"But all queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set."

On which replica, primary or secondary?

YufeiShao-msft 7,056 Reputation points

2021-09-28T08:20:23.787+00:00

secondary replica

Udham Singh 41 Reputation points

2021-09-28T09:31:24.907+00:00

okay, so if row versioning is not happening on Primary replica then why "Longest Transaction Running Time" counter is showing the activity on primary replica ? And ending up in triggering alert threshold > 50.
Sign in to comment