none
Snapshot isolation ON for tempdb

    Question

  • Is there any risk associated with turning on Snapshot Isolation for tempdb ?

     I have turned on Snapshot isolation for the main database at the database level. In the code, the transaction isolation level is set to Snapshot Isolation.

    There are few SQL statements where global temp tables are created and it gives the following error when I try to execute the query which access global temp tables:


    Snapshot isolation transaction failed accessing database 'tempdb' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

    Please let me know if there are any disadvantages with turning on SnapShot Isolation for tempdb at the database level.

    Friday, February 12, 2010 4:41 PM

Answers

  • Hello,


    Only the transactions that are run in SNAPSHOT isolation level will have an effect and it's probably easier for you than for anyone else to answer how it will affect tempdb :)

    There is plenty of information out there on how SNAPSHOT isolation level will affect your tempdb. There is a nice summarizing blog post from the MS SQL Server storage engine team: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/31/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example.aspx and of course the popular whitepaper "Working with tempdb in SQL Server 2005" (http://msdn.microsoft.com/en-us/library/cc966545.aspx) which talks about it and also various other aspects of tempdb utilization.

    Books Online has some nice scriptlets for monitoring the tempdb version store : "Troubleshooting Insufficient Disk Space in tempdb ".

    To get a hunch, perhaps you can trace some representative workload and re-run it on a test server using the SNAPSHOT isolation level (and monitor with some of the scriptlets from BoL for instance)?

    You will notice that the version store is used for many things - even when SNAPSHOT isolation level is not turned ON (such as temporary work objects used during query execution and triggers). E.g., this simple query in Adventureworks will use 8 pages in the version store in READ COMMITTED:


    BEGIN TRANSACTION

    UPDATE Sales.SalesPerson
    SET SalesYTD = SalesYTD + SubTotal
    FROM Sales.SalesPerson AS sp
    JOIN Sales.SalesOrderHeader AS so
        ON sp.SalesPersonID = so.SalesPersonID
        AND so.OrderDate = (SELECT MAX(OrderDate)
                            FROM Sales.SalesOrderHeader
                            WHERE SalesPersonID =
                                  sp.SalesPersonID)

    ROLLBACK

    The following query:

    SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
    (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
    FROM sys.dm_db_file_space_usage

    .. returns:

    version_store_pages_used version_store_space_in_MB
    8                                       0.062500

    What I am trying to say with the above exercise is that you need to have some benchmark numbers from your current tempdb utilization before you monitor the effect of SNAPSHOT isolation.

    Kind Regards,


    /Elisabeth


    Elisabeth Rédei | SQL Server Consultant and Architect | MCITP, MCT | http://sqlblog.com/blogs/elisabeth_redei | www.dbdirections.com | http://www.linkedin.com/elisabethredei |
    Saturday, February 13, 2010 11:07 AM