Are you using Read Committed Snapshot isolation? Is it blowing up your TempDB?

TheSurfingDBA over 1 year ago 3 minute read time

At a previous DBA gig before joining SolarWinds, I investigated why TempDB grew so large on my production servers. I found that our main production databases were using Read Committed Snapshot Isolation (RCSI). This isolation level changes the type of concurrency that SQL Server will use. By default, SQL Server uses pessimistic locking, meaning writers block readers and readers block writers. RCSI (default in Azure SQL DB) is optimistic locking, much like Oracle^® or Postgres^®. RCSI creates a version store. The version store contains the committed rows, which is how a SELECT operation does not get blocked when another UPDATE/DELETE is operating on the same row because the SELECT reads the row or rows from the version store instead of the actual base table. When you enable this, the row must be stored somewhere, and TempDB happens to be the place (unless you are using Accelerated Database Recovery in SQL 2019 and beyond). A row is maintained in the version store when there are transactions operating on that row in question. When the transaction is committed, the row is cleaned up from the version store.

Row versions must be stored for as long as an active transaction needs to access it. Once every minute, a background thread removes row versions that are no longer needed and frees up the version store space in TempDB. A long-running transaction using RCSI prevents space in the version store from being released for rows involved in the transaction.

In my example, the Version store was over 500GBs, which was over ten times larger than my prod database. I tried to do a row count to see how many rows were in the version store. I let it run for a couple of hours, and it never returned any results. Epic fail!!

The following query will help you find the size of the Version Store in TempDB.

SELECT
SUM (user_object_reserved_page_count)*8/1024.0/1024.0 as user_obj_GB,
SUM (internal_object_reserved_page_count)*8/1024.0/1024.0 as internal_obj_GB,
SUM (version_store_reserved_page_count)*8/1024.0/1024.0  as version_store_G,
SUM (unallocated_extent_page_count)*8/1024.0/1024.0 as freespace_GB,
SUM (mixed_extent_page_count)*8/1024.0/1024.0 as mixedextent_GB
FROM sys.dm_db_file_space_usage

Continuing to investigate the issue, I kept seeing queries with this statement, Implicit_Transactions.

The default for SQL Server is Set Implicit_Transactions OFF. The following is from Microsoft Books Online regarding Set Implicit_Transactions...

When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode.

When OFF, it returns the connection to autocommit transaction mode.

I wanted to see if there any active transactions. This is tricky when using RCSI as sometimes active transactions will not show up with the usual tools such as...

DBCC OpenTran

The below query shows us active transactions and active transactions being used by RCSI.

SELECT
t.transaction_id, 
t.name,t.transaction_type, 
t.transaction_state,
s.transaction_id,
s.session_id,
s.elapsed_time_seconds/60/60.0 as hours_tran_has_been_open,  
p.status, 
p.cmd
FROM sys.dm_tran_active_transactions t
  JOIN sys.dm_tran_active_snapshot_database_transactions s
     ON t.transaction_id = s.transaction_id
  JOIN sys.sysprocesses p
     ON p.spid = s.session_id

If Implicit_Transactions is being set to ON and connections to the database are not being closed, or you have long-running transactions, the version store will grow, which can cause TempDB to grow, which will either fill the disk or cause a performance issue being that the version store is so large.

Currently, in the SQL Sentry Client, there is no visualization of TempDB usage other than the custom report builder functionality. Still, the SQL Sentry^® Portal gives us an excellent way to see what is going on inside TempDB.

Here we see the TempDB Summary chart, where not much is happening with the Version Store.

As space in the Version Store is consumed and/or released, it will be reflected in the TempDB Summary.

Taking it one step further, we can drill down to Top SQL, just like in the Performance Analysis Dashboard in the SQL Sentry Client. This will allow us from the Portal to see precisely what was running and consuming TempDB.

The quick and easy access to this type of data is invaluable to a DBA when trying to troubleshoot TempDB issues or gain a better understanding of your environment.

I hope this brief article helps with your understanding of RCSI and how SQL Sentry and the SQL Sentry Portal help dig deeper into this topic.