Post reply

Isolation level question.

  • March 29, 2021 at 9:14 pm

    #3863377

    Hi Gurus,

    Just getting little confused about "ALTER DATABASE [MYDB] SET ALLOW_SNAPSHOT_ISOLATION ON"  when and how do you guys use it? How it is related to "SET READ_COMMITTED_SNAPSHOT ON" and "SET TRANSACTION ISOLATION LEVEL SNAPSHOT"

    Also how to see using Tsql and Gui if "SET ALLOW_SNAPSHOT_ISOLATION "ON or OFF?

    Thanks

  • March 29, 2021 at 10:21 pm

    #3863397

    This statement:

    ALTER DATABASE [MYDB] SET ALLOW_SNAPSHOT_ISOLATION ON

    causes the db to start snapshot processing -- adding 14 bytes to each row and storing any needed row versions in tempdb -- and allows a later task to optionally use it.

    This statement:

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    requests using it.  The db must allow it first.  The only way for a task to use SNAPSHOT isolation in "ALLOW" mode is to change the TIL.

    In contrast, with this statement:

    ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON

    The db starts snapshot processing and all later tasks will use snapshot for that db, they don't have a choice.  You do not have to change the TIL, so no code changes are needed to get snapshot to be used.

    You can look at this system view to determine whether it's in use for your session:

    SELECT transaction_isolation_level /*5=SNAPSHOT*/

    FROM sys.dm_exec_requests

    WHERE session_id = @@SPID

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • March 30, 2021 at 2:32 pm

    #3863624

    Thanks a lot Scott for you reply. Do you recommend RCSI is on?

  • March 30, 2021 at 3:46 pm

    #3863667

    ONLY if it's actually NEEDED, just because of the overhead to activate it.

    I have (many) hundreds of dbs.  I have RCSI on for about a dozen or so of them.  Naturally your requirements may vary, but I would *never* turn it on for all user dbs.  The much-maligned (and a bit misunderstood) "WITH (NOLOCK)" often works just as well and with vastly less overhead.

    After you turn on RCSI for a db, you will need to review table fragmentation after waiting a while.  RCSI adds 14 bytes per row and thus can really cause a lot of page splits and fragmentation.  You should prepare for the possibility that you'll need to rebuild some/most of the (larger) tables/indexes.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 4 posts - 1 through 3 (of 3 total)

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