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.