Search results matching tags 'SQL Server 2008 R2' and 'forcescan'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+2008+R2,forcescan&orTags=0Search results matching tags 'SQL Server 2008 R2' and 'forcescan'en-USCommunityServer 2.1 SP2 (Build: 61129.1)SQL Server 2008 R2 SP1 CTP is now availablehttp://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/22/sql-server-2008-r2-sp1-ctp-is-now-available.aspxFri, 22 Apr 2011 23:37:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:35117AaronBertrand<p>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!</p><p>I want to talk about a couple of things that are included in this update that may be of interest [update: <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/25/more-changes-you-might-not-have-noticed-in-the-sql-server-2008-r2-sp1-ctp.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/25/more-changes-you-might-not-have-noticed-in-the-sql-server-2008-r2-sp1-ctp.aspx" target="_blank">I've added a lot more information in another post</a>]. 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:<br></p><p><br><b>sys.dm_exec_query_stats</b></p><p>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: </p><blockquote><table bgcolor="#eeeeee" cellpadding="0" cellspacing="0"><tr><td><pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="black">sql_handle</font><font color="gray">, </font><font color="black">total_rows</font><font color="gray">, </font><font color="black">last_rows</font><font color="gray">, </font><font color="black">min_rows</font><font color="gray">, </font><font color="black">max_rows<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font color="green">sys</font><font color="black">.</font><font color="green">dm_exec_query_stats;</font></pre></td></tr></table></blockquote><p>This can be useful to see if changes to a query's statistics over time is correlated with row counts returned by the query. <br></p><p><br><b>FORCESEEK</b></p><p>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:<br></p><blockquote><table bgcolor="#eeeeee" cellpadding="0" cellspacing="0"><tr><td><pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE TABLE </font><font color="black">dbo.foo<br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">a </font><font color="blue">INT</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">b </font><font color="blue">INT</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c </font><font color="blue">INT</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">d </font><font color="blue">INT</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">CONSTRAINT </font><font color="black">PK </font><font color="blue">PRIMARY KEY</font><font color="gray">(</font><font color="black">a</font><font color="gray">)<br>);<br><br></font><font color="blue">CREATE INDEX </font><font color="black">x </font><font color="blue">ON </font><font color="black">dbo.foo</font><font color="gray">(</font><font color="black">b</font><font color="gray">, </font><font color="black">a</font><font color="gray">, </font><font color="black">c</font><font color="gray">);</font></pre></td></tr></table></blockquote>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:<br><blockquote><table bgcolor="#eeeeee" cellpadding="0" cellspacing="0"><tr><td><pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="black">a</font><font color="gray">, </font><font color="black">b <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font color="black">dbo.foo<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">WHERE </font><font color="black">a =</font><font color="blue"> </font><font color="black">1 </font><font color="gray">AND </font><font color="black">b =</font><font color="blue"> </font><font color="black">2</font><font color="gray">;<br> <br></font><font color="blue">SELECT </font><font color="black">a</font><font color="gray">, </font><font color="black">b <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font color="black">dbo.foo </font><font color="blue">WITH </font><font color="gray">(</font><font color="black"></font><font><font color="blue">FORCESEEK</font></font><font color="gray">)<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">WHERE </font><font color="black">a =</font><font color="blue"> </font><font color="black">1 </font><font color="gray">AND </font><font color="black">b =</font><font color="blue"> </font><font color="black">2</font><font color="gray">;</font></pre></td></tr></table></blockquote><p>Here is the (identical) plan for each of these two queries:</p><blockquote><p><img src="http://bertrandaaron.files.wordpress.com/2011/04/default.png" border="0" height="132" width="314"><br></p></blockquote><p>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.<br></p><blockquote><table bgcolor="#eeeeee" cellpadding="0" cellspacing="0"><tr><td><pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="black">a</font><font color="gray">, </font><font color="black">b <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font color="black">dbo.foo </font><font color="blue">WITH </font><font color="gray">(</font><font><font color="blue">FORCESEEK</font></font><font color="black"></font><font color="gray">(</font><font color="black">x</font><font color="gray">(</font><font color="black">b</font><font color="gray">, </font><font color="black">a</font><font color="gray">)))<br>&nbsp;&nbsp;&nbsp; </font><font color="blue">WHERE </font><font color="black">a =</font><font color="blue"> </font><font color="black">1 </font><font color="gray">AND </font><font color="black">b =</font><font color="blue"> </font><font color="black">2</font><font color="gray">; </font></pre></td></tr></table></blockquote><p>This plan does exactly what we tell it, seek on both columns of index "x": </p><blockquote><p><img src="http://bertrandaaron.files.wordpress.com/2011/04/force_seek.png" height="129" width="261"><br></p></blockquote><p>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:</p><blockquote><table bgcolor="#eeeeee" cellpadding="0" cellspacing="0"><tr><td><pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="black">a</font><font color="gray">, </font><font color="black">b <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font color="black">dbo.foo </font><font color="blue">WITH </font><font color="gray">(</font><font><font color="blue">FORCESEEK</font></font><font color="gray">(</font><font color="black">x</font><font color="gray">(</font><font color="black">b</font><font color="gray">, </font><font color="black">c</font><font color="gray">)))<br>&nbsp;&nbsp;&nbsp; </font><font color="blue">WHERE </font><font color="black">a =</font><font color="blue"> </font><font color="black">1 </font><font color="gray">AND </font><font color="black">b =</font><font color="blue"> </font><font color="black">2</font><font color="black"></font><font color="gray">;</font></pre></td></tr></table></blockquote><p>You will get this error, even though both b and c are key columns in the index:</p><blockquote><table bgcolor="#eeeeee" cellpadding="0" cellspacing="0"><tr><td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:#ee2222;">Msg 362, Level 16, State 1, Line 1<br>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'.</div></td></tr></table></blockquote><p>And if you try to reference an INCLUDE column instead of a key column:</p><blockquote><table bgcolor="#eeeeee" cellpadding="0" cellspacing="0"><tr><td><pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE INDEX </font><font color="black">y </font><font color="blue">ON </font><font color="black">dbo.foo</font><font color="gray">(</font><font color="black">b</font><font color="gray">) </font><font><font><font><font color="blue">INCLUDE</font></font></font></font><font color="gray">(</font><font color="black">a</font><font color="gray">);<br><br></font><font color="blue">SELECT </font><font color="black">a</font><font color="gray">, </font><font color="black">b <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font color="black">dbo.foo </font><font color="blue">WITH </font><font color="gray">(</font><font><font color="blue">FORCESEEK</font></font><font color="gray">(</font><font color="black">y</font><font color="gray">(</font><font color="black">b</font><font color="gray">, </font><font color="black">a</font><font color="gray">)))<br>&nbsp;&nbsp;&nbsp; </font><font color="blue">WHERE </font><font color="black">a =</font><font color="blue"> </font><font color="black">1 </font><font color="gray">AND </font><font color="black">b =</font><font color="blue"> </font><font color="black">2</font><font color="gray">;</font></pre></td></tr></table></blockquote><p>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): <br></p><blockquote><table bgcolor="#eeeeee" cellpadding="0" cellspacing="0"><tr><td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:#ee2222;">Msg 365, Level 16, State 1, Line 1<br>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'. </div></td></tr></table></blockquote><p><b><br>FORCESCAN</b> </p><p>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:</p><blockquote><table bgcolor="#eeeeee" cellpadding="0" cellspacing="0"><tr><td><pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="black">a</font><font color="gray">, </font><font color="black">b <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font color="black">dbo.foo </font><font color="blue">WITH </font><font color="gray">(</font><font><font color="blue">FORCESCAN</font></font><font color="gray">)<br>&nbsp;&nbsp;&nbsp; </font><font color="blue">WHERE </font><font color="black">a =</font><font color="blue"> </font><font color="black">1 </font><font color="gray">AND </font><font color="black">b =</font><font color="blue"> </font><font color="black">2</font><font color="gray">; </font></pre></td></tr></table></blockquote><p>Yields the following plan:</p><blockquote><p><img src="http://bertrandaaron.files.wordpress.com/2011/04/force_scan1.png">&nbsp;</p></blockquote><p> <b>Other items</b></p><p>The Service Pack 1 CTP includes all of the fixes from the first 6 cumulative updates for SQL Server 2008 R2 (but not from <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/18/cumulative-update-7-for-sql-server-2008-r2-is-available.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/18/cumulative-update-7-for-sql-server-2008-r2-is-available.aspx" target="_blank">cumulative update #7</a> - 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 <a href="http://social.technet.microsoft.com/wiki/contents/articles/microsoft-sql-server-2008-r2-sp1-release-notes.aspx" title="http://social.technet.microsoft.com/wiki/contents/articles/microsoft-sql-server-2008-r2-sp1-release-notes.aspx" target="_blank">CTP release notes</a>. 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 <a href="http://blogs.msdn.com/b/hanspo/archive/2010/11/29/new-limit-for-number-of-partitions-in-sql-server-2008-sp2.aspx" title="http://blogs.msdn.com/b/hanspo/archive/2010/11/29/new-limit-for-number-of-partitions-in-sql-server-2008-sp2.aspx" target="_blank">master.dbo.sp_db_increased_partitions</a>.<br></p>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 <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/21/downgrading-a-database-you-can-t-get-there-from-here.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/21/downgrading-a-database-you-can-t-get-there-from-here.aspx" target="_blank">read about that situation here</a>). 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.<br><p><b><br>Download</b> <br></p>SP1 CTP for "normal" SKUs (and also a stand-alone download for upgrading client tools only):<br><blockquote><p><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df" title="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df" target="_blank">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df</a></p></blockquote><p>SP1 CTP for the various Express Editions (Express, Express with Tools, Express with Advanced Services):<br></p><blockquote><p><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3" title="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3" target="_blank">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3</a> <br></p></blockquote><p>SP1 CTP Feature Packs (please don't ask me what all of these files are, but <a href="http://koprowskit.eu/geek/2011/04/en-sql-server-2008-r2-sp1-ctp-14-feature-packs-are-available/" title="http://koprowskit.eu/geek/2011/04/en-sql-server-2008-r2-sp1-ctp-14-feature-packs-are-available/" target="_blank">someone else did a pretty good job</a>):</p><blockquote><p><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409" title="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409" target="_blank">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409</a> <br></p></blockquote><p>The build # is 10.50.2425.&nbsp;</p><p><br><b>Caveats </b><br></p><p>Note that <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/18/cumulative-update-7-for-sql-server-2008-r2-is-available.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/18/cumulative-update-7-for-sql-server-2008-r2-is-available.aspx" target="_blank">cumulative update #7</a> 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.</p><p>And just in case you're wondering, NO, you cannot apply this service pack to SQL Server 2008.<br></p><p>&nbsp; <br></p>