Tempdb version store related question

Question

Post reply

Tempdb version store related question

vsamantha35

SSChampion

Points: 11551
More actions
September 28, 2020 at 5:14 am

#3791222
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
- This topic was modified 4 years, 2 months ago by vsamantha35.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

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://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

ref: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

This reply was modified 4 years, 2 months ago by Johan Bijnens.
This reply was modified 4 years, 2 months ago by Johan Bijnens.

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

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!