Three Engine Improvements in SQL Server 2008 R2 (Updated)

The upcoming release (by May 2010) of SQL Server 2008 R2 has been primarily focused on enhancements in the BI area to support the upcoming releases of Office 2010 and SharePoint 2010. This left precious little development resources to devote to improvements to the basic relational database engine in SQL Server 2008.

So far, I have only found three significant Engine improvements in SQL Server 2008 R2, which I will list here.

1. Support for more than 64 processor cores (if you are running on Windows Server 2008 R2)

Microsoft did a lot of work on the scheduler in Windows Server 2008 R2 to make it more efficient and able to support more than 64 processor cores. SQL Server 2008 R2 will be able to take advantage of these improvements and support more than 64 processor cores. Even if you have a smaller machine with fewer than 64 processor cores, you should see some performance benefit from the scheduler improvements in Windows Server 2008 R2.

2. Support for Unicode Compression

SQL Server 2008 R2 Enterprise Edition will use Simple Compression Scheme for Unicode Data (SCSU) to compress Unicode data. This only works on NCHAR or NVARCHAR data types, not on NVARCHAR(MAX) and not on NTEXT (which is deprecated). It is enabled as part of Page or Row data compression. If you have an existing table or index that is using data compression in SQL Server 2008, you will have to rebuild the index(s) after you upgrade to SQL Server 2008 R2 to get Unicode Compression on the existing data.  If you insert a new row in a table or if you update a Unicode column in an existing row, that column will get Unicode compression.  Microsoft’s Sunil Agarwal discusses Unicode Compression here and here

3. Improvements to the hashing algorithm used to calculate the lock hash value in the SQL Server Lock Manager

This improvement has the potential to dramatically improve concurrency in some very high volume situations by eliminating blocking caused by hash key collisions. Microsoft’s Juergen Thomas talks about this improvement here.

These three core Engine improvements have the potential to give you some nice performance improvements with very little development effort. There is actually no development effort in terms of code changes, just the effort in migrating to SQL Server 2008 R2 with regression testing, and connection string changes if you move to a new server or instance.

The current Nehalem-EP (Xeon 5500 series), and upcoming Nehalem-EX processors are so much better than any processor from two-three years ago, and memory density is so much higher, that you may be able to make a compelling case for upgrading to new hardware and possibly doing some server consolidation, running on Windows Server 2008 R2 and SQL Server 2008 R2. Hopefully, this has given you some ammunition to help convince your company to consider this idea.

I wanted to thank Sunil Agarwal for giving me some clarifying detail on these improvements.