locked
Change 'Allow Snapshot' for tempdb itself? RRS feed

  • Question

  • Ok, so we have a database 'AAA' with Read Committed snapshot On, as per general advice regarding blocking issues.

    We were then approached to put another vendor's database 'BBB' on the same server.

    The 'BBB' database/software came with a particular instruction of turning on the 'Allow Snapshot' setting on 'tempdb' itself(!).

    Initial reaction: Hey, you're the new guest on this server, do you really have to muck about with settings on the tempdb system database that we were already using before you came along?

    My questions are whether turning on 'Allow Snapshot' on tempdb a) is a sound instruction b) will have any adverse effects on database 'AAA'?


    Tuesday, November 12, 2019 2:15 PM

Answers

  • Hi MattiasNilsson,

     

    No one seems to enable snapshot isolation on tempdb, and you can't start read commited snapshot isolation on tempdb.

     

    When you enable snapshot isolation for a database, tempdb stores row versions (data information before modification). Your read request will read this history information without getting a lock, greatly reducing the probability of blocking.

     

    I don't know why you must start snapshot isolation for tempdb, but this is definitely influential for other databases that have snapshot isolation enabled, because their row versions are stored in tempdb.

     

    And if your all databases have snapshot isolation enabled, it will cause tempdb to be filled with row versions.

    In a heavily updated database, this can affect the behavior of other queries that use tempdb, as well as the server itself. To prevent your tempdb from filling up, you want to disable SNAPSHOT isolation.

     

    For more details, please refer to https://logicalread.com/2018/06/07/sql-server-internals-snapshot-isolation-part-2/#.XcuhAFczaUk

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by MattiasNilsson Wednesday, November 13, 2019 1:14 PM
    Wednesday, November 13, 2019 6:38 AM
  • Hi MattiasNilsson,

     

    No one seems to enable snapshot isolation on tempdb, and you can't start read commited snapshot isolation on tempdb.

    You can atleast as per the BOL Tempdb document, I have not tried though. If you see the table you have list of default options and whether they can be modified or not. Although it does not makes sense.

    My questions are whether turning on 'Allow Snapshot' on tempdb a) is a sound instruction b) will have any adverse effects on database 'AAA'?

    Mattias to me it does not seems like sound advice, anyways since you have RCSI enabled for database AAA the tempdb is already using row versioning. If want to do for database BBB enable snapshot isolation( I believe this is what vendor wants, double check) this will do the same on tempdb.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by MattiasNilsson Wednesday, November 13, 2019 1:14 PM
    Wednesday, November 13, 2019 6:54 AM

All replies

  • Hi MattiasNilsson,

     

    No one seems to enable snapshot isolation on tempdb, and you can't start read commited snapshot isolation on tempdb.

     

    When you enable snapshot isolation for a database, tempdb stores row versions (data information before modification). Your read request will read this history information without getting a lock, greatly reducing the probability of blocking.

     

    I don't know why you must start snapshot isolation for tempdb, but this is definitely influential for other databases that have snapshot isolation enabled, because their row versions are stored in tempdb.

     

    And if your all databases have snapshot isolation enabled, it will cause tempdb to be filled with row versions.

    In a heavily updated database, this can affect the behavior of other queries that use tempdb, as well as the server itself. To prevent your tempdb from filling up, you want to disable SNAPSHOT isolation.

     

    For more details, please refer to https://logicalread.com/2018/06/07/sql-server-internals-snapshot-isolation-part-2/#.XcuhAFczaUk

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by MattiasNilsson Wednesday, November 13, 2019 1:14 PM
    Wednesday, November 13, 2019 6:38 AM
  • Hi MattiasNilsson,

     

    No one seems to enable snapshot isolation on tempdb, and you can't start read commited snapshot isolation on tempdb.

    You can atleast as per the BOL Tempdb document, I have not tried though. If you see the table you have list of default options and whether they can be modified or not. Although it does not makes sense.

    My questions are whether turning on 'Allow Snapshot' on tempdb a) is a sound instruction b) will have any adverse effects on database 'AAA'?

    Mattias to me it does not seems like sound advice, anyways since you have RCSI enabled for database AAA the tempdb is already using row versioning. If want to do for database BBB enable snapshot isolation( I believe this is what vendor wants, double check) this will do the same on tempdb.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by MattiasNilsson Wednesday, November 13, 2019 1:14 PM
    Wednesday, November 13, 2019 6:54 AM
  • When I try to set READ_COMMITTED_SNAPSHOT for tempdb, it will show as below:


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 13, 2019 7:10 AM
  • When I try to set READ_COMMITTED_SNAPSHOT for tempdb, it will show as below:


    READ_COMMITTED_SNAPSHOT is not allowed as per the table but ALLOW_SNAPSHOT_ISOLATION is allowed. Can i request you to quickly try it and let us know.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, November 13, 2019 7:13 AM
  • Hi Shanky_621,

     

    In my first reply, I didn't seem to express it clearly, so it caused a little confusion. What I want to express is that snapshot isolation is OK, but read committed snipshot can't be enabled on tempdb, It seems to mislead you.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 13, 2019 7:42 AM
  • Not a problem, thanks for clarifying.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, November 13, 2019 7:46 AM