Oct 22, 2010

NO Recompilations when (parameter) values are different

Please read another post on this subject by Dinesh Priyankara.

This is another topic came in the last user group meeting: 
Does SQL Server generate a new plan (in other word recompile) the query if the parameter value is changed?
The actual answer is it depends:
First of all, SQL Server does not go through the recompilation process simply because the parameter is changed.
As I mentioned in the previous blog, there are only certain conditions where an existing execution plan is thrown away and a new plan is used.

Even though in my example, a new plan is created between these two statements, the value is not the cause.


SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= 1
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= 1000
When I ran the queries and captures the SQL:Recompile event, nothing was captured. Interestingly, ShowPlan XML for Query Compilation too, didn't give any number as the reason for recompilation. For me it looked like SQL Server didn't consider them as same query.
I removed the GO statement in between and tried too. The result was same. (except the column line number showed 1 and 2 instead of 1 always.
The image is attached.

Fortunately, SQL Server provides a few DMVs (dynamic management views) which allows us to check the execution plans

SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

So I did the test again.
First I cleared the procedure cache by issuing

Then I removed the GO command in between the statement and executed the two select statements in one batch followed by query on DMV.  Both statements were combined into a single query in the cached plan.
Then I added the GO statement and then executed again.
I saw an interesting thing.  instead of showing one query, it shows two queries. and each has it's own plan.  Additionally all statements were categorized as ad-hoc statements.  The prepared statement was missing.   
That means, parametrization didn't identify both queries as same. 
 I know that my computer uses simple parametrization. So I went ahead and change the setting to force parameterization.


This time I found out that the ad-hoc query started using the same execution plan.

Not only that, I also saw a new plan for the prepared statement with parametrized query.
Finally, I was able to learn something new. Even if your parameter values are totally different, SQL Server will not take that as a reason to recompile the query.
I was thinking that it was reusing the plan by looking at the query test of the execution plan.  Both queries, not only returned the same text, but also replaced the value 1 and 1000 in @1.  I assumed that SQL Server has identified them as parameters.

So, its time to learn something new. Even if the execution plan shows something don't trust it.  Use different queries and confirm.  
Thank you Dinesh for showing me something to learn.

No comments:

Post a Comment