Tempdb version store related question

  • vsamantha35

    SSChampion

    Points: 11176

    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

    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

    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

    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

     

    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

    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