July 12th was a good day for all SQL Server enthusiasts and professionals as Microsoft announced the release of both Service Pack 1 for SQL Server 2008 R2 as well as Community Technology Preview 3 for the next installment in the series, Codename Denali.
SP1 brings with it a number of stability and security fixes as well as some more advanced configuration options to fine-tune performance. The most interesting update from my point of view is the updated syntax on the FORCESEEK index hint, which now has extra options to further fine tune queries.
This can be used to specify individual indexes and columns on which to perform a seek. Normally of course, the optimiser should identify this itself but the FORCESEEK hint can be used to ensure the expected operation. There’s also the addition of a FORCESCAN hint which can be used in the extremely rare situation where you actually want to perform a scan and don’t want to risk a less than optimal execution plan based on the optimiser attempting to be too clever.
Codename Denali CTP 3 introduces several improvements and new features such as Business Intelligence Semantic Modelling (with the unfortunate acronym, BISM), the Silverlight based reporting and visualisation tool “Project Crescent”, designed to provide high levels of interactivity and customisation to reporting. I’ll investigate this in more detail in a future article, as I do a lot of work with Business Intelligence, using SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS) in many areas of my day job. The other big win in my eye is the introduction of column-store indexing. According to Microsoft, this can provide massive performance gains with even the largest datasets. This takes a different perspective on index storage, keeping the data in each column in its own page, rather than a page for each row. Due to the fact that columns will often repeat the same value a large number of times, this gives the option of much improved compression over standard indexes, reducing the number of pages in the index and can also reduce the number of page scans required in a query. As always, the best results will vary depending on your system, but it’s an exciting concept and one that’s worth testing out.
I’m hoping to take a more detailed look at both 2008 R2 SP1 and Denali CTP3 over the next few weeks, so I’ll try and expand further in a future article. For now though, why not head on over to Microsoft and download both via the links below?
SQL Server 2008 R2 SP1: Download page
SQL Server Codename Denali CTP3: Download page