StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPlease explain the query plan sql server chooses
text
Body
copied!<p>In <a href="http://blogs.msdn.com/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx" rel="nofollow noreferrer">this blog post</a>, I need clarification why SQL server would choose a particular type of scan:</p> <blockquote> <p>Let’s assume for simplicities sake that col1 is unique and is ever increasing in value, col2 has 1000 distinct values and there are 10,000,000 rows in the table, and that the clustered index consists of col1, and a nonclustered index exists on col2.</p> <p>Imagine the query execution plan created for the following initially passed parameters: @P1= 1 @P2=99</p> <p>These values would result in an optimal queryplan for the following statement using the substituted parameters:</p> <p>Select * from t where col1 > 1 or col2</p> <blockquote> <p>99 order by col1;</p> </blockquote> <p>Now, imagine the query execution plan if the initial parameter values were: @P1 = 6,000,000 and @P2 = 550.</p> <p>As before, an optimal queryplan would be created after substituting the passed parameters:</p> <p>Select * from t where col1 > 6000000 or col2 > 550 order by col1;</p> <p>These two identical parameterized SQL Statements would potentially create and cache very different execution plans due to the difference of the initially passed parameter values. However, since SQL Server only caches one execution plan per query, chances are very high that in the first case the query execution plan will utilize a clustered index scan because of the ‘col1 > 1’ parameter substitution. Whereas, in the second case a query execution plan using index seek would most likely be created.</p> </blockquote> <p>from: <a href="http://blogs.msdn.com/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx" rel="nofollow noreferrer">http://blogs.msdn.com/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx</a></p> <p><b>Why would the first query use a clustered index, and a index seek in the second query?</b></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload