Tempdb version store related question
-
Hi All,
Recently, we observed the tempdb has grown out of proportion and filled up all the disk space ( 1 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,
snapshot_isolation_state_desc
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
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
I don't even know how to spell "read_committed_snapshot" but we recently had a 3rd party vendor create a database that uses it and your problem does have a concern for me so, hopefully, this "bump" of your post will draw some attention.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It is indeed a concept that uses tempdb to get it things done. ( versionstore )
The difference between both:
- 'snapshot isolation level" is a transaction setting ( which has to be allowed by the database - is_read_committed_snapshot_on )
It's the transaction which activates this usage. ( application modification needed )
- 'READ_COMMITTED_SNAPSHOT ON' makes snapshot the default isolation level for connections of a given database !
It's the database that lets the connection use this isolation level, if no other specified by the connection.
( No application modification needed )
ref: SQL SERVER – Difference Between Read Committed Snapshot and Snapshot Isolation Level
The goal is not to (b)lock ongoing S/I/U/D operations.
However, it keeps a lock on the objects involved ! You cannot modify objects involved in a snapshot transaction, so it is of upmost importance all applications close such transactions as soon as possible ( also keep connection pooling in mind ).
Have a look at this article Klaus Aschenbrenner published in 2018: "The unwanted Side Effects of enabling Read Committed Snapshot Isolation"
Also interesting read By Linchi Shea (2007): "Performance Impact: The Potential Cost of Read_Committed_Snapshot"
btw: These links were the ones that got me started investigating it back in the days:
ref: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you haven't already, it's probably worth querying tempdb.sys.dm_db_file_space_usage to see whether it even is version store using all the space.
If it is, then you can start by checking sys.dm_tran_active_snapshot_database_transactions and checking if there are any sessions with high elapsed time, investigating them as necessary.
Cheers!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply