Just as I was about to shut my machine down on a Friday, Microsoft goes and releases something we've been waiting on for a long time: a Community Technology Preview (CTP) for Service Pack 1 is finally available for testing!

I want to talk about a couple of things that are included in this update that may be of interest [update: I've added a lot more information in another post]. Now, I'll say up front, that a long, long time ago, Microsoft promised they would stop adding new features in service packs, but if the last few releases are any indication, they are clearly not interested in keeping their word (and in fact you might have a hard time getting them to admit they ever promised that in the first place). Anyway, here is a quick glance at a couple of new features that have been added for SQL Server 2008 R2 SP1:

sys.dm_exec_query_stats

This DMV is not new, of course, but it has received a minor upgrade. If you are currently using this DMV for tracking individual queries, you will now be able to see row counts: total, last, min and max. Four new columns have been added:

SELECT sql_handle, total_rows, last_rows, min_rows, max_rows
    FROM sys.dm_exec_query_stats;

This can be useful to see if changes to a query's statistics over time is correlated with row counts returned by the query.

FORCESEEK

Like sys.dm_exec_query_stats, FORCESEEK is not new, but it has also received an upgrade. In addition to being able to tell the optimizer that you want a seek, you can now be more specific, and tell it which index / columns you want to seek. Let's consider this trivial example:

CREATE TABLE dbo.foo
(
    a INT,
    b INT,
    c INT,
    d INT,
    CONSTRAINT PK PRIMARY KEY(a)
);
 
CREATE INDEX x ON dbo.foo(b, a, c);

Now we can run a query against the a and b columns, however by default and with a simple FORCESEEK hint, we get a clustered index seek, as you might expect:

SELECT a, b 
    FROM dbo.foo
    WHERE a = 1 AND b = 2;
 
SELECT a, b 
    FROM dbo.foo WITH (FORCESEEK)
    WHERE a = 1 AND b = 2;

Here is the (identical) plan for each of these two queries:

Now, the expanded FORCESEEK option will allow you to specify a specific index to seek on, and which columns to use. This is a trivialized case, so it is something you probably expect that the optimizer should be able to do anyway, but now you can guarantee it.

SELECT a, b 
    FROM dbo.foo WITH (FORCESEEK(x(b, a)))
    WHERE a = 1 AND b = 2;

This plan does exactly what we tell it, seek on both columns of index "x":

You have to be very careful with this feature, however. If you don't specify columns in the key order of the index, for example:

SELECT a, b 
    FROM dbo.foo WITH (FORCESEEK(x(b, c)))
    WHERE a = 1 AND b = 2;

You will get this error, even though both b and c are key columns in the index:

Msg 362, Level 16, State 1, Line 1
The query processor could not produce a query plan because the name 'c' in the FORCESEEK hint on table or view 'foo' did not match the key column names of the index 'x'.

And if you try to reference an INCLUDE column instead of a key column:

CREATE INDEX y ON dbo.foo(b) INCLUDE(a);
 
SELECT a, b 
    FROM dbo.foo WITH (FORCESEEK(y(b, a)))
    WHERE a = 1 AND b = 2;

You'll get this error (but only because there aren't that many key columns – if you had an index on (b,c) INCLUDE (a), you'd get Msg 362 as above):

Msg 365, Level 16, State 1, Line 1
The query processor could not produce a query plan because the FORCESEEK hint on table or view 'foo' specified more seek columns than the number of key columns in index 'y'.

FORCESCAN

While rare, you may have cases where you want to force a table or index scan. Consider a case where you *know* a scan is "good enough" and you don't want to take a chance that the optimizer will come up with a suboptimal plan trying to force a seek. Borrowing from the example above, we can change the hint to FORCESCAN:

SELECT a, b 
    FROM dbo.foo WITH (FORCESCAN)
    WHERE a = 1 AND b = 2;

Yields the following plan:

Other items

The Service Pack 1 CTP includes all of the fixes from the first 6 cumulative updates for SQL Server 2008 R2 (but not from cumulative update #7 – more on that below). There are also features that don't excite me personally a whole lot, but may interest you: if you are using DACPAC, there is a new DAC Fx to allow for in-place upgrades, and a disk space control in PowerPivot for SharePoint. You can read more about these features in the CTP release notes. One that is not mentioned in the release notes is the new support for up to 15,000 partitions, first seen in SQL Server 2008 SP2, which you can enable for any database using master.dbo.sp_db_increased_partitions.

I hope that these changes will also be present in the next CTP of Denali, and I hope that after Denali is released, they don't add more features to new 2008 R2 service packs. Right now there is a very peculiar case when you have SQL Server 2008 SP2, enable support for up to 15,000 partitions, and then try to upgrade to SQL Server 2008 R2 RTM (you can read about that situation here). I suspect this is one of the reasons you are seeing the CTP for this service pack this early; while not very common, the current scenario is certainly an upgrade blocker.

Download

SP1 CTP for "normal" SKUs (and also a stand-alone download for upgrading client tools only):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df

SP1 CTP for the various Express Editions (Express, Express with Tools, Express with Advanced Services):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3

SP1 CTP Feature Packs (please don't ask me what all of these files are, but someone else did a pretty good job):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409

The build # is 10.50.2425. 

Caveats

Note that cumulative update #7 is *NOT* included in this service pack; so, if you are relying on any of those fixes, you should hold out until after SP1 is released *and* after the first subsequent cumulative update is released, as that is when the branch will most likely be caught up.

And just in case you're wondering, NO, you cannot apply this service pack to SQL Server 2008.