September 28, 2020 at 5:14 am
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
September 29, 2020 at 6:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 29, 2020 at 2:10 pm
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
Change is inevitable... Change for the better is not.
September 30, 2020 at 8:45 am
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
September 30, 2020 at 5:40 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy