none
Impact of setting read_committed_snapshot to ON a SQL Server 2008 R2 (64 bit) database

    Question

  • Hi,

    There is a request from the client to change the setting of a SQL Server 2008 R2 database.

    What would be the impact if we set read_committed_snapshot to ON

    There would be load on tempdb and possibly higher CPU usage. Is it worth the risk?


    MM

    Thursday, July 04, 2013 4:54 AM

Answers

  •  

    What would be the impact if we set read_committed_snapshot to ON

    There would be load on tempdb and possibly higher CPU usage. Is it worth the risk?

    From a resource usage perspective, there will be more load on tempdb due to the row version store.  Also, an additional 14 bytes per row overhead is needed.  On the plus side, locking is reduced which can improve concurrency and improve overall performance.

    See http://msdn.microsoft.com/en-us/library/ms175492(v=sql.105).aspx

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, July 04, 2013 5:05 AM
    Moderator

All replies

  •  

    What would be the impact if we set read_committed_snapshot to ON

    There would be load on tempdb and possibly higher CPU usage. Is it worth the risk?

    From a resource usage perspective, there will be more load on tempdb due to the row version store.  Also, an additional 14 bytes per row overhead is needed.  On the plus side, locking is reduced which can improve concurrency and improve overall performance.

    See http://msdn.microsoft.com/en-us/library/ms175492(v=sql.105).aspx

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, July 04, 2013 5:05 AM
    Moderator
  • There is a request from the client to change the setting of a SQL Server 2008 R2 database.

    What would be the impact if we set read_committed_snapshot to ON

    I think Dan gave you all the answer there can be, but I'm fascinated by this bit about "the client" requesting the change.  Where do you get clients who know about such things, and what is their concern?

    Josh

    Thursday, July 04, 2013 9:25 PM
  • There is a request from the client to change the setting of a SQL Server 2008 R2 database.

    What would be the impact if we set read_committed_snapshot to ON

    I think Dan gave you all the answer there can be, but I'm fascinated by this bit about "the client" requesting the change.  Where do you get clients who know about such things, and what is their concern?

    Josh

    I've got a client application we run on our own servers, and this is part of their 'database configuration' guide. This is a fairly large-scale time and attendance software, and I think it was solely to prevent locks causing issues on reads in other parts of the software. It certainly removed the occasional "unable to execute page/query due to a database error" problems the previous version had :)
    Friday, July 05, 2013 2:12 AM
  • My client directly checked with the third party software vendor and they suggested that to them. So, the client wanted to verify with us if this is ok and what is the impact etc.


    MM

    Friday, July 05, 2013 3:29 AM