THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Snapshot Isolation Level

I just atteended a session where Ron Talmage showed the new Snapshot Isolation feature of SQL Server 2005. While it can be used to improve scalability of a traditional LOB application, it's really important to BI applications. Making a long story short, one of the available features is that you can take a "snapshot" of the database at a certain point in time and, without any lock (neither exclusive nor sharing), you can query the database viewing the same snapshot for a whole transaction (i.e. comprising several SELECT statement).

There are many examples of BI applications which can take advantage from this new feature:

  • DTS that feeds a DataMart while users are modifying the OLTP database
  • Several SQL queries for a single report that has to show consistent data

The only concern is that tempdb stores versioned rows, so it can become a bottleneck and grow in size if database update activity is very large while you are working on a snapshot. Anyway, there are a lot of scenario where snapshot isolation could be a great feature to improve scalability.

Published Tuesday, November 9, 2004 4:47 PM by Marco Russo (SQLBI)


No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement