This post is for SQLTuesday #007, hosted by Jorge Segarra (blog | twitter). The topic is to talk about your favorite new feature or enhancement in SQL Server 2008 R2. I am going to talk about some of the improvements in the core database engine.
Although the lion’s share of the new features in SQL Server 2008 R2 are positioned to support Office 2010, SharePoint 2010 and SQL Server Analysis and Reporting Services, there are some interesting and important improvements in the core database engine, that I previously wrote about back in February. Since then, I have uncovered a few more details about some more of the Engine improvements from some of my friends at Microsoft. The first improvement is Read Committed Snapshot Isolation/Snapshot Isolation support for FILESTREAM data. In SQL Server 2008, FILESTREAM was not allowed to be used with RCSI/SI, which prevented many people from using the FILESTREAM feature. In SQL 2008 R2, this limitation has been removed and FILESTREAM and RCSI/SI are supported to work together.
The second enhancement is that the SQL Remote BLOB Store APIs have been enhanced to provide an out-of-box provider (SQL RBS FILESTREAM Provider) that allows BLOB data to be stored in a SQL Database (local or Remote database) separately from the rest of the metadata. This provider utilizes the SQL FILESTREAM technology to store the BLOB data. The SQL RBS and RBS FILESTREAM providers are shipped as part of the SQL Server 2008 R2 Feature Pack as a separate installable component. With the Microsoft Office SharePoint 2010 release, SQL RBS has been adopted as the official Remote BLOB store API for SharePoint 2010 customers.
As you may be aware, SQL Server 2008 R2 now supports up to 256 logical processors when running on top of Windows Server 2008 R2. The biggest x64 machine that I am aware of has 128 logical processors, while there are IA64 machines with 256 logical processors. SQL Server 2008 R2 Enterprise Edition has a limit of 8 physical processors. With SQL Server 2008, there was a limit of 64 logical processors. SQL Server and Windows have been using the “logical processor” terminology based on the following nomenclature since there is some confusion around what these terms mean
- Logical Processor / Thread: One logical computing engine in the OS, application and driver view
- Core: One processing unit – may consist of one or more logical processors
- Processor / Package / Socket: One physical processor – may consist of one or more cores
The SQL Server Engine team did a lot of work on improving the scalability of some of the base components including Latches, Buffer Pool, Log Manager, XEvents etc. The Scheduler also went through changes to support more than 64 logical processors. In addition, they did work on the base primitives (inserts & updates, etc.). This means that you should see performance and scalability improvements even on much smaller machines, simply by upgrading to SQL Server 2008 R2. Only the SQL Server Relational Engine is G64 aware in SQL Server 2008 R2. Microsoft’s Madhan Arumugam and Fabricio Voznika did a nice presentation a few months ago that has more details about these improvements. There are six recent TPC-E results that have been submitted using SQL Server 2008 R2 running on Windows Server 2008 R2 that help prove how effective this low level work has been.
