SQL Server 2005 performance mismatch (4)

Version Store

SQL Server 2005 provides a row version schema for implementing some of the features. Features that use the row version schema are listed below. For more information on the following features, refer to SQL Server Books Online.

Trigger

MARS

Online indexes

Based on row version isolation level: Need to set options at the database level

Row versions need to be shared across sessions. When a row version is reclaimed, the creator of the row version has no control. You need to find and kill the longest running transaction that prevents row version cleanup.

The following query returns 2 transactions that depend on the maximum version store run.

select top 2
transaction_id,
transaction_sequence_num,
elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC

This is an example input that shows a transaction with a serial number of 3 and a transaction ID of 8609 already running for 6,523 seconds.

transaction_id　　　 transaction_sequence_num elapsed_time_seconds
-------------------- ------------------------ --------------------
8609　　　　　　　　 3　　　　　　　　　　　　6523
20156　　　　　　　　25　　　　　　　　　　　 783

Because the 2nd transaction runs for a relatively short period of time, you can release a large number of version stores by killing the 1th transaction. However, there is no way to assess the release space by killing into the released version. You may need to kill some business to free up more space.

You can mitigate this problem by changing the tempdb properties for the version store or by eliminating the long queries at the snapshot isolation level as much as possible, or running under Read-committed-snapshot. You can use the following formula to roughly estimate the size of the row version store.

[Size of version Store] = 2 * [version store data generated per minute] * [Longest running time (minutes) of the Transacti On