http://dinesql.blogspot.com/2010/10/do-we-need-to-recompile-stored.html
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.
DBCC FREEPROCCACHE
GO
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= 1
GO
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= 1000
GO
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
