Tempdb version store related question

• vsamantha35

  SSChampion

  Points: 11176

  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 1 month ago by  vsamantha35.

• Site Owners

  SSC Guru

  Points: 80372

  More actions

  September 29, 2020 at 6:10 am

  #3791517

  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: 997364

  More actions

  September 29, 2020 at 2:10 pm

  #3791645

  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".
  "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)

• Johan Bijnens

  SSC Guru

  Points: 134310

  More actions

  September 30, 2020 at 8:45 am

  #3791823

  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 weeks, 1 day ago by  Johan Bijnens.
  • This reply was modified 4 weeks, 1 day 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[/url] but most of the time this is me

• Jacob Wilkins

  One Orange Chip

  Points: 27911

  More actions

  September 30, 2020 at 5:40 pm

  #3792043

  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!