Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server 2008 R2 SP1 not earth-shaking, but useful

SQL Server 2008 R2 Service Pack 1 has a number of new features, including a query hint, new system objects and support for up to 15,000 partitions.

Aside from the usual round of bug fixes, a few new features have snuck in under the wire with SQL Server 2008 R2 Service Pack 1 (SP1). They are small changes, however, so I can see why Microsoft decided to give them to us now rather than force us to wait for the release of its upcoming version of SQL Server, code-named Denali, to go gold, especially since many of them haven’t shown up in either of the two public previews of Denali. Here's a rundown of what's new.

The new FORCESCAN query hint
Anyone who has taken the trouble to optimize queries knows the difference between a scan and a seek. A scan involves reading every row in a table to answer a query; a seek uses the table’s leaf data to find rows that answer the same query. You typically get the best results with a seek when the results involve less than 10% to 15% of the table’s data. A scan is optimal when you have a high number of likely hits, so it’s easier to read the whole table into memory and return results from there instead of going through the extra step of parsing the index.

The new FORCESCAN hint ensures the query optimizer doesn’t use a seek in a given operation; instead it uses a scan. Of course, if you put a query together using this hint you’re going to be responsible for any poor performance, so use it carefully. It works as you would expect:

SELECT user_type FROM user_table WITH (FORCESCAN)

This is assuming the user_type column is relatively un-unique -- what database wonks call a “low-cardinality” column -- and won’t benefit much from being indexed.

As the query optimizer in SQL Server grows that much more sophisticated, I’ve been shying away from using hints in my own programming, but there are clearly cases in which it’s valuable. For instance, let’s say you’re putting together a test suite. Forcing table scans is one way to torture-test the system. FORCESCAN would come in handy there.

New functionality for the FORCESEEK query hint
If FORCESEEK sounds like the inverse counterpart to FORCESCAN, you’re right. It’s another query hint that forces the use of a seek, though it’s been around for some time. In SQL Server 2008 R2 SP1, it’s been tweaked to allow you to specify which index or column you want to seek with:

SELECT user_name FROM user_table
WITH (FORCESEEK(user_table_index(user_name))

As you can probably guess from this example, there’s an index on user_table named user_table_index, which has user_name (a uniquely constrained value) as one of its columns. This forces the query optimizer to use that index and that column to do the seek. You can seek on multiple columns as well, but you need to specify them in the same order they’re presented in the index.

One possible application for this: you’re creating a system in which indexes are being generated on the fly and you want them to be specified “by hand,” either in an automatically generated stored procedure or through generated in-line SQL.

New system objects
New system objects tend to sneak in across versions of SQL Server, often with little fanfare, but intrepid users uncover them and do their best to document them. Here are some of the new ones that have emerged this time around:

  • sys.dm_os_volume_stats: When queried with a database and file ID, this dynamic management function returns information about the disk on which your database files are stored. It tells you, among other things, how much free space there is on the volume and whether the volume supports compression.
  • sys.dm_os_windows_info: Returns data about the version of Windows that SQL Server is running on, including the service pack level and operating system language.
  • sys.dm_server_registry: Returns registry information relevant to the current installation of SQL Server.
  • sys.dm_server_services: Returns running services relevant to SQL Server, including the last startup time and whether a service is running in a clustered instance.

Support for 512e drives
New hard drives are coming onto the market that use a new “512e” format -- they’re formatted internally with 4K sectors but report themselves as having 512-byte sector sizes to their hosts unless a host is also 4K-aware. I wrote about this issue for, since it has performance impacts for applications like SQL Server. SP1 lets SQL Server 2008 R2 detect and correctly make use of such drives, as long as you’re running Windows Server 2008 R2 or better. (Lesser versions of Windows are not 4K-sector aware.)

Data-tier Application Component Framework (DAC Fx)
Database schema management is one of the things most database administrators shy away from as being a terrible chore, but SQL Server has been gradually adding tools to make the job mildly less onerous. DAC, or the Data-tier Application Component, is one such tool: It makes it easier for data-tier objects (that is, everything in your database) to be written and deployed. SP1 includes a new DAC Fx and DAC Upgrade Wizard, which can upgrade schemas for existing databases in SQL Server or Microsoft’s cloud database service SQL Azure, wherever DAC is supported.

SharePoint PowerPivot disk space control
Microsoft PowerPivot data is often cached in SharePoint, but until now you didn’t have direct control over the caching mechanisms. SQL Server 2008 R2 SP1 lets you control the amount of time an inactive database is cached in memory and how long cached PowerPivot files are kept on disk before being purged. This is useful if you have only a few PowerPivot items that you want to keep cached for longer periods of time because the underlying data doesn’t change often. 

Up to 15,000 partitions on a single table via sys.sp_db_increased_partitions
The sys.sp_db_increased_partitions stored procedure allowed you to enable the increased_partitions setting for a given table. Originally this meant support for up to 1,000 partitions, but the limit has since been raised to 15,000.

Be sure to check out the TechNet wiki notes on SQL Server 2008 R2 SP1 for the most up-to-date information on these and other changes.

Serdar Yegulalp has been writing about computers and IT for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.


Dig Deeper on Microsoft SQL Server 2008 R2

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.