THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
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 09, 2004 4:47 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement