Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases.

Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be the culprit.

We monitor the TempDB usage through sys.dm_db_file_space_usage and once we see that the version store is increasing (as reported by version_store_reserved_page_count) we would like to identity the transactions that are activily using the version store.

I'm using the following statement to find transactions that are using the version store:

SELECT db_name(spu.database_id) as database_name,
       at.transaction_begin_time as begin_time,
       case 
         when at.transaction_state in (0,1) then 'init'
         when at.transaction_state = 2 then 'active'
         when at.transaction_state = 3 then 'ended'
         when at.transaction_state = 4 then 'committing'
         when at.transaction_state = 6 then 'comitted'
         when at.transaction_state = 7 then 'rolling back'
         when at.transaction_state = 6 then 'rolled back'
         else 'other'
       end as transaction_state,
       ast.elapsed_time_seconds as elapsed_seconds,
       ses.program_name, 
       ses.row_count,
       (spu.user_objects_alloc_page_count * 8) AS user_objects_kb,
       (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb,
       (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb,
       (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb
FROM sys.dm_tran_active_snapshot_database_transactions ast
  JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id
  JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
  JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
ORDER BY elapsed_time_seconds DESC
;

But this doesn't help me in identifying how much space each transaction is actually using in the version store.

Is there a way to get the information about the space usage in the version store per transaction (or even better: per statement)?

Edit: The "potential duplicate" (How to identify which query is filling up the tempdb transaction log?) does not take the version store into account (only temp tables, table variables, and space used for sort and hash operations).

In fact the accepted solution will not show anything for transactions that only use the version store (at least not for me)

share|improve this question
    
Aside, but related: if you're doing PerfMon logging, there are several counters in the Transactions area that may be of interest, including free space in tempdb, version generation rate, and the version store size. –  Jon Seigel Mar 11 '13 at 14:08
    
Is it the data file(s) or the log file (or both) that's expanding? –  Jon Seigel Mar 11 '13 at 14:16
    
@AaronBertrand: that question only deals with "traditional" TempDB usage (temp tables, sort operations and those things). It does not cover the space used by the version store. –  a_horse_with_no_name Mar 11 '13 at 14:16
    
@JonSeigel: thanks I will check out PerfMon (I'll need to get back to our customer regarding the logfile vs. datafile - I currently don't know) –  a_horse_with_no_name Mar 11 '13 at 14:20
    
It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it? –  Aaron Bertrand Mar 11 '13 at 14:36
show 1 more comment

1 Answer

up vote 5 down vote accepted

It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it?

You can track this by object, though, which can help you narrow down which modules are causing the churn. Have a look at sys.dm_tran_top_version_generators:

USE [your database];
GO
SELECT obj = 
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  vs.aggregated_record_length_in_bytes
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1);

And on SQL Server 2008+, you can also figure out which modules reference these tables by adding sys.dm_sql_referencing_entities:

SELECT 
  obj = QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  referenced_by = QUOTENAME(r.referencing_schema_name)
  + '.' + QUOTENAME(r.referencing_entity_name),
  vs.aggregated_record_length_in_bytes AS size
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
CROSS APPLY sys.dm_sql_referencing_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)), 'OBJECT'
) AS r
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1)
ORDER BY size DESC, referenced_by;

This assumes that none of the version store could be created by ad hoc queries. However, it doesn't tell you which of those modules could be causing it - hopefully the naming scheme is logical and helps you narrow it down a bit.

(On 2005 you might be able to go through sysdepends and other old-style dependency views but I'm not 100% sure how reliable that would be.)

share|improve this answer
    
That looks promising. Is it safe (i.e. "correct") to do a sum(aggregated_record_length_in_bytes) to find the size each table/object is using? –  a_horse_with_no_name Mar 11 '13 at 15:12
    
Well if you have partitioned tables, you might want to know by partition, but otherwise that is per table. If you feel safer using SUM, go ahead, it would really depend on your schema and how granular you want to get. –  Aaron Bertrand Mar 11 '13 at 15:19
    
Thanks. We don't use partitions, so using it without sum() should be safe. The sys.dm_sql_referencing_entities does not seem to be available in SQL Server 2005 though. But the we don't really that additional information. The base table names will be enough to find the "bad guy". –  a_horse_with_no_name Mar 11 '13 at 15:23
    
Ah, I see you added the SQL Server 2005 tag later. Sorry. –  Aaron Bertrand Mar 11 '13 at 15:25
    
No problem (I did mention 2005 in the question though). It's good to know this for more modern versions as well. –  a_horse_with_no_name Mar 11 '13 at 15:26
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.