Post reply

RCSI isolation level

  • October 3, 2012 at 4:09 am

    #393301

    Hi,

    I want reads not to be locked by writers but I don't want to use the (NOLOCK) hint since it's a bad practice.

    I looked up a little and made some tests with RCSI isolation level...

    It works fine but... tempdb nightmare (our clients, like I said in previous posts, don't have great machines)...

    The number of rows on the tables I want to process aren't that much, aprox. 2.000 on the invoice table and 100.000 on the invoiceproducts table).

    I could use the READ UNCOMMITTED isolation level on readers but that's the same as NOLOCK....

    Is there another isolation level that works similar to RCSI but doesn't have the performance issue on tempdb?!

    Thanks,

    Pedro


    If you need to work better, try working less...

  • October 3, 2012 at 6:41 am

    #1544871

    No, http://msdn.microsoft.com/en-us/library/ms173763%28v=sql.105%29.aspx

  • October 3, 2012 at 6:51 am

    #1544878

    It may not be that much impact. Depends on how long transactions are.

    Anyway, read committed snapshot has the lower tempDB impact, snapshot isolation is usually more impact.

    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

  • October 4, 2012 at 5:20 am

    #1545453

    GilaMonster (10/3/2012)

    Anyway, read committed snapshot has the lower tempDB impact, snapshot isolation is usually more impact.

    Is that absolutely true?

    The way i understand it is that with snapshot isolation you first need to set the database option "ALLOW_SNAPSHOT_ISOLATION" to ON and then switch on at the query level by setting the isolation level to "SNAPSHOT".

    With Read Committed Snapshot Isolation you just need to turn on the database option "READ_COMMITTED_SNAPSHOT", this will be available to all queries using the default Read Committed isolation and does not need to be explicitly defined.

    In some cases surely this could affect more incoming queries and have a bigger effect on TempDB.

    Would be pleased to hear your thoughts.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 4, 2012 at 6:22 am

    #1545478

    As soon as snapshot isolation is allowed (alter database ... allow snapshot isolation), row versions get stored regardless whether any queries are running in snapshot isolation or not.

    With snapshot isolation the unit of isolation in the transaction, with read committed snapshot it's the statement, so unless you never have transactions consisting of more than one statement, snapshot will require that the versions be retained longer than for read committed snapshot and hence will impact tempDB more (assuming, of course, that there are queries running using snapshot, as it would be a total waste of resources to enable it and not have queries using it).

    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

  • October 4, 2012 at 6:24 am

    #1545480

    thanks for the reply Gail

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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