Post reply

read_committed_snapshot is off?

  • September 12, 2011 at 4:29 am

    #247780

    HI All,

    One of my client recomendation is read_committed_snapshot is to be 'ON'??

    I checked with my databases its off [the value is zero ] by using the below query ,

    SELECT is_read_committed_snapshot_on FROM

    sys.databases WHERE name= 'databasename'

    TO improve the perfromance is it required to 'ON' the read_committed_snapshot .

    If i turn "ON" what are the advantages and disadvantages ???

    Thanks ,

    Lavanya

  • September 12, 2011 at 4:38 am

    #1379873

    http://msdn.microsoft.com/en-us/library/ms345124.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 12, 2011 at 6:01 pm

    #1380376

    Adv: Read_committed_snapshot is used to reduce Blocking

    Disv: 1. Heavy use of TempDB.

    2. Db needs to be in single user mode or no user connections should exist at the time of making this change.

    As TEMPDB is going to be heavily used, it is recommended to optimize the TempDB before enabling this option.

    Thank You,

    Best Regards,

    SQLBuddy

  • September 13, 2011 at 2:08 am

    #1380448

    hi ,

    Is it required to run on system databases also ?

    what are the temp db changes i need to do before turn on this activity .

  • September 13, 2011 at 5:11 pm

    #1380986

    The basic recommendations is to split Tempdb into multiple files and put them on seperate spindle, Size them adequately & Properly, set a good auto growth size.

    Check this one :

    http://searchsqlserver.techtarget.com/tip/Optimize-tempdb-in-SQL-Server-by-striping-and-splitting-to-multiple-files

    Thank You,

    Best Regards,

    SQLBuddy

  • September 13, 2011 at 5:13 pm

    #1380987

    Lavanyasri (9/13/2011)

    hi ,

    Is it required to run on system databases also ?

    what are the temp db changes i need to do before turn on this activity .

    Please, please go and read that article I referenced.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 14, 2011 at 7:01 pm

    #1381698

    Follow Gails Advice and also check this article for optimizing Tempdb

    http://technet.microsoft.com/en-us/library/cc966545.aspx

    Thank You,

    Best Regards,

    SQLBuddy

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply