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.
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:
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)
WHERE SalesPersonID =
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]
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.
Elisabeth Rédei | SQL Server Consultant and Architect | MCITP, MCT | http://sqlblog.com/blogs/elisabeth_redei | www.dbdirections.com | http://www.linkedin.com/elisabethredei |
- Marked as answer by Xiao-Min Tan – MSFTModerator Monday, February 22, 2010 6:54 AM