SQL Server 2005: Relational Engine Enhancements

SQL Server 2005 will offer changes in the relational-database engine and features for relational data warehousing. Looking for features that many people might not know about, I sought out the Group Program Manager for the SQL Server 2005 Relational Engine development team, Lubor Kollar. I expected to hear about partitioned tables and indexes and advanced T-SQL, but I got the curveball I was hoping for--Kollar wanted to talk about the really strong, really hidden features that most users will discover only when they upgrade their current databases and see the differences.

We spent a long time talking about an architectural switch from batch-level recompilations to statement-level recompilations. SQL Server 2000 performs batch-level recompilations when one statement in a query batch causes the entire batch to recompile. We often have to tune our applications or extraction, transformation, and loading (ETL) processes to write small batches or perform other workarounds to keep from incurring the performance overhead associated with these excess recompilations. SQL Server 2005 Beta 2 and the Community Technology Previews (CTPs) use the new statement-level recompilation architecture. The development team and beta testers have experienced significant performance increases because most of the batches contain multiple SQL statements. Before SQL Server 2005, if a single statement required recompilation it always resulted in recompiling the entire batch. Kollar also pointed me to an intriguing white paper, "Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005" at http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx . To understand more about how statistic changes and metadata changes in SQL Server 2000 effect recompilations, see the white paper, "Query Recompilation in SQL Server 2000" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp .

Other hidden performance increases in SQL Server 2005 include enhancements to the Parallel Query Option (PQO) and memory-management algorithms. The PQO has been around since SQL Server 7.0, but it was enhanced in SQL Server 2000 and has been further enhanced in SQL Server 2005. Tasks such as creating indexes on a multi-processor server are now 20-30 percent faster. Memory-management algorithms apply to data and metadata caching mechanisms in the relational engine. In SQL Server 2005, dynamic caches that use a common cache-management framework replace the mostly static SQL Server 2000 caches--letting SQL Server 2005 respond better to memory pressure and retain useful data in caches for extended periods of time.

Kollar and I also talked about the three types of snapshots in SQL Server 2005: database, snapshot isolation, and read-committed snapshot isolation. A database snapshot is basically a picture of an underlying database--what's fascinating is that when the snapshot is created it takes up virtually no space. The snapshot only tracks the differences in the database from the point in time that the snapshot was created; it's an in-memory bitmap that shows whether a page should be retrieved from the original database or from a set of persisted pages if the original page has been changed since the snapshot was created. Snapshot isolation and read-committed snapshot isolation use row versioning. Transactions that use snapshot isolation use rows that have their values committed at the time the transactions start. Transactions that use read-committed snapshot isolation use rows that have their values committed at the time the statement begins.

I enjoy talking to people to learn more about SQL Server 2005 business intelligence (BI) features. Please, let me know what you've discovered in Beta 2 or the latest CTP.