Tempdb issue

Question

Hi All,

Recently, we observed the tempdb has grown out of proportion and filled up all the disk space ( 2 TB tempdb data drive).

At that moment we arent able to get shrink the tempdb as well and it was keep growing and growing.
Upon troubleshooting a little further came to know for some db's we see "is_read_committed_snapshot"
and for some db's "snapshot_isolation_state" was turned on. I used below query to pull that info.

There are around 9 databases for which these properties have been set to true.

select
sysDB.database_id,
upper(sysDB.Name) as 'Database Name',
is_read_committed_snapshot_on,
snapshot_isolation_state
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid
where database_id > 4
and snapshot_isolation_state =1 or is_read_committed_snapshot_on = 1

Based on the scenario, I have few questions around them. If anyone has used these, kindly share your thoughts.

1. What is the difference between "read_committed_snapshot" and "snapshot_isolation" when it comes to version store behavior or do I have to turn on both for enabling row versions in tempdb to reduce blocking?
2. How can we track down the spids, queries , sizeofversion store for that particular spid which using up the version store?

Thanks,
Sam

Answer 1

Hi @Sam ,

What is the difference between "read_committed_snapshot" and "snapshot_isolation" when it comes to version store behavior ...?

Quote from this doc. : readcommittedsnapshot-and-snapshotisolation-levels-in-sql-server

To sum up, both the SNAPSHOT and the READ COMMITTED isolation levels (with READ_COMMITTED_SNAPSHOT is ON) use row versioning to manage isolation degree in transactions. While the SNAPSHOT isolation level protects from all phenomena, READ COMMITTED isolation level with enabled READ_COMMITTED_SNAPSHOT option prevents only dirty reads. Whereas the SNAPSHOT is a transaction isolation level, the READ_COMMITTED_SNAPSHOT is just a database option that can change the behavior of the READ COMMITTED isolation level in preventing dirty reads.

or do I have to turn on both for enabling row versions in tempdb to reduce blocking?

It depends on what kind of data you want to see, or your tolerance for the accuracy of the data.

Row version control is not a panacea to eliminate blocking and deadlocks. Before deciding to use it, the following two questions must be considered.

1. Whether the end user accepts the running result under the line version control

In different transaction stages, some are blocked, some read the old version value, and some can read the new version value. What behavior do you or your users expect? Do you want to read the latest version of the data even if it is blocked, or can you tolerate reading the old version? Some applications rely on the locking and blocking behavior of read isolation, such as operations such as generating a serial number. Change to version control, and the original processing logic will not work properly. So before adopting the new isolation level, be sure to do a good job of testing to ensure that the application will run as expected.

2. Can SQL Server support the additional load caused by row version control

When row version control is enabled, SQL Server will store the row version in tempdb. The more data is modified and the more information needs to be stored, the greater the additional load on SQL Server. Therefore, if an application is to switch from other isolation levels to version control, special testing is required to ensure that the existing software and hardware configuration can support the additional load and the application can achieve a similar response speed.

The following is for reference only, I hope it will help you:

How can we track down the spids, queries , size of version store for that particular spid which using up the version store?

The space usage of tempdb cannot be tracked with sp_spaceused,can use the management view sys.dm_db_file_space_usage.

The next codes may be helpful:

use tempdb  
  
-- View tempdb usage from the file level  
dbcc showfilestats  
  
-- Query 1  
-- Return all session information that has made a space application  
Select 'Tempdb' as DB, getdate() as Time,  
 SUM (user_object_reserved_page_count)*8 as user_objects_kb,  
 SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,  
 SUM (version_store_reserved_page_count)*8 as version_store_kb,  
 SUM (unallocated_extent_page_count)*8 as freespace_kb  
From sys.dm_db_file_space_usage  
Where database_id = 2  
  
  
-- Query 2  
--This management view can reflect the overall allocation of tempdb space at the time   
SELECT t1.session_id,  
t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count,  
t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,  
t3.*  
from sys.dm_db_session_space_usage t1 ,  
sys.dm_exec_sessions as t3  
where  
t1.session_id = t3.session_id  
and (t1.internal_objects_alloc_page_count>0  
or t1.user_objects_alloc_page_count >0  
or t1.internal_objects_dealloc_page_count>0  
or t1.user_objects_dealloc_page_count>0)  
  
-- Query 3  
-- Return the running statement of the session that is running and has made a space request  
  
SELECT t1.session_id,  
st.text  
from sys.dm_db_session_space_usage as t1,  
sys.dm_exec_requests as t4  
CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st  
where t1.session_id = t4.session_id  
 and t1.session_id >50  
and (t1.internal_objects_alloc_page_count>0  
or t1.user_objects_alloc_page_count >0  
or t1.internal_objects_dealloc_page_count>0  
or t1.user_objects_dealloc_page_count>0)  

More information: my-version-store-is-huge.html

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

read_committed_snapshot change the behaviour of the READ COMMITTED isolation level to use the version store. This is often a good way to avoid readers block writers and vice versa. With RCSI queries typically see the data that was committed when the query started.

ALLOW_SNAPSHOT_ISOLATON permits the transaction isolation level SNAPSHOT. In a SNAPSHOT transaction, a process sees the database as it was the the transaction started. A long running snapshot transaction could prevent the version store from being purged.

I think it can be difficult to track down version-store usage to a specific spid, as it is more of a collective effort.

Answer 3

Thanks a Ton Mia and Erland. Very helpful.