locked
Turning On Snapshot Isolation Gotchas RRS feed

Answers

  • It is unclear what isolation level have you enabled RCSI or SI?

    Downsides:

    1.       Excessive tempdb usage due to version store activity. Think about session that deletes 1M rows. All those rows must be copied to version store regardless of session transaction isolation level and/or if there are other sessions that running in optimistic isolation levels at the moment when deletion started.
    2.       Extra fragmentation – SQL Server adds 14-byte version tag (version store pointer) to the rows in the data files when they are modified. This tag stayed until index is rebuild
    3.       Development challenges – again, error 3960 with snapshot isolation level. Another example in both isolation levels – trigger or code based referential integrity. You can always solve it by adding with (READCOMMITTED) hint if needed. 

    While switching to RCSI could be good emergency technique to remove blocking between readers and writers (if you can live with overhead AND readers are using read committed), I would suggest to find root cause of the blocking. Confirm that you have locking issues – check if there are shared lock waits in wait stats, that there is no lock escalations that block readers, check that queries are optimized, etc.  


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 27, 2015 5:16 AM
    Answerer
  • I am wondering what kinds of proactive monitoring is best suited for monitoring this snapshot isolation?.

    I usually run queries to see tempdb size, version store size and long running active transactions and occasionally perform counter on SQL Server Transactions.

    Thanks,


    I90Runner

    Hi I90Runner,

    As described in this article, to monitor snapshot isolation processes, SQL Server mainly provides tools in the form of Dynamic Management Views and performance countersin Windows System Monitor. From your description, you have done the similar monitor steps.

    There is also a blog about monitoring snapshot isolation for your reference.

    Monitoring Snapshot Isolation with Perfmon in SQL Server (video)

    Thanks,
    Lydia Zhang



    Lydia Zhang
    TechNet Community Support





    Tuesday, April 28, 2015 8:41 AM

All replies

  • It is unclear what isolation level have you enabled RCSI or SI?

    Downsides:

    1.       Excessive tempdb usage due to version store activity. Think about session that deletes 1M rows. All those rows must be copied to version store regardless of session transaction isolation level and/or if there are other sessions that running in optimistic isolation levels at the moment when deletion started.
    2.       Extra fragmentation – SQL Server adds 14-byte version tag (version store pointer) to the rows in the data files when they are modified. This tag stayed until index is rebuild
    3.       Development challenges – again, error 3960 with snapshot isolation level. Another example in both isolation levels – trigger or code based referential integrity. You can always solve it by adding with (READCOMMITTED) hint if needed. 

    While switching to RCSI could be good emergency technique to remove blocking between readers and writers (if you can live with overhead AND readers are using read committed), I would suggest to find root cause of the blocking. Confirm that you have locking issues – check if there are shared lock waits in wait stats, that there is no lock escalations that block readers, check that queries are optimized, etc.  


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 27, 2015 5:16 AM
    Answerer
  • Thank you Uri Dimant. I am wondering what kinds of proactive monitoring is best suited for monitoring this snapshot isolation?.

    I usually run queries to see tempdb size, version store size and long running active transactions and occasionally perform counter on SQL Server Transactions.

    Thanks,


    I90Runner

    Tuesday, April 28, 2015 12:44 AM
  • I am wondering what kinds of proactive monitoring is best suited for monitoring this snapshot isolation?.

    I usually run queries to see tempdb size, version store size and long running active transactions and occasionally perform counter on SQL Server Transactions.

    Thanks,


    I90Runner

    Hi I90Runner,

    As described in this article, to monitor snapshot isolation processes, SQL Server mainly provides tools in the form of Dynamic Management Views and performance countersin Windows System Monitor. From your description, you have done the similar monitor steps.

    There is also a blog about monitoring snapshot isolation for your reference.

    Monitoring Snapshot Isolation with Perfmon in SQL Server (video)

    Thanks,
    Lydia Zhang



    Lydia Zhang
    TechNet Community Support





    Tuesday, April 28, 2015 8:41 AM