Posted by Erland Sommarskog on 01/25/07 22:49

rbg (rbg.net@gmail.com) writes:
> I have a question on this, if someone can help me with that it will be
> great.
> In my SQL query that selects data from table, I have a where clause
> which states :
>
> where PermitID like @WorkType
> order by WorkStart DESC
>
> @WorkType is a input parameter to the Stored proc and its value is
> '01%'
>
> When I use the above where clause, all the Sorts in the ESTIMATED Query
> Execution plan show me a COST of 28%.
>
> However if I change the query manually to say:
>
> where PermitID like '01%'
> order by WorkStart DESC
>
> The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
> and at the beginning of the PLAN, there is a Bookmark Lookup which
> includes the above where clause.
> Whereas with the FIRST example , the BookMark Lookup in the beginning
> doesn't show that where condition.
>
> Can anyone help me better understand this anomaly?

That WHERE clause was not in your original post. Nor does the column
name seem entirely familiar. I gather that what you posted yesterday
was a sample from an MSDN article, but it can be tricky to give accurate
answers, ir you don't post your actual code.

But some optimizer bascis: when SQL Server builds the query plan
for a stored procedure, it builds the plan for the procedure as a
whole. This means that it does not know what values that variables
will have at time for execution. The same applies to parameters, but
in this case it does at least know the input value, and uses this
value as guidance. (This is known as parameter sbiffing.)

But SQL Server does not build a query plan every time a procedure
is executed. Instead the plan is put in cache, and the cached plan
will be reused - even if the procedure is called with input values
for which the cached plan is no good.

In your case, assume the the procedure was first called with
WorkType '%01'. For this input value any index on PermitID is not
very useful, so most likely you will get a table scan instead.

On the other hand, when you hardcode a value. SQL Server have full
information, and the odds for a good plan are much better.

To test this theory, you can say:

EXEC your_sp @WorkTyoe WITH RECOMPILE

if you get a berrer plan, the problem was that you had a plan created
for a differnt value in the cache.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]