While Preethi was presenting his presentation on Query Plans at the SQL Server Sri Lanka User Group meeting, he showed a situation where query plan gets recompiled when a value set for WHERE clause is vary. Although it recompiles, I doubt that it will not be the same if the query is a parameterized query with a stored procedure. There were couple of arguments on this favoring both sides; thought to test it and see.
Let me take simple two queries. The queries below are same except the value passed for the WHERE clause.
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= 1
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= 1000
SQL Server selects one plan for the first one but it does not use the same for the next though it is same.
This shows clearly that the statement has been recompiled since the value was different. But this is not going to happen if the logic is implemented with a stored procedure;
CREATE PROC GetOrderDetails @Number int
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID <= @Number
GO
The plan for the procedure will be generated at the first execution. Once generated, the generated plan will be used for next executions even though the value of parameters are vary greatly. See the code below;
EXEC GetOrderDetails 1
EXEC GetOrderDetails 1000
You can see that the same plan which was generated for the first execution has been used for second execution even though it is not the best plan for the value 1000. All we have to remember is, stored procedures are not getting re-compiled when values of parameters are vary. If you expect a situation like this, use WITH RECOMPILE with the stored procedure. This instructs SQL Server to not to stored the plan generated, hence it creates a plan at every execution. If it is rare, use WITH RECOMPILE when you call the stored procedure. This creates the plan again when it is executed.
ALTER PROC GetOrderDetails @Number int
WITH RECOMPILE
AS
...
-- OR
EXEC GetOrderDetails 1000
WITH RECOMPILE
The next is whether the stored procedure gets recompiled when an index is added to a table used in it. See what BOL says;
It clearly says that the stored procedure will not get recompiled when an index is added to a table used with it. But what I found is the opposite of it. Sometime one of the statements gets compiled, sometime the entire stored procedure gets compiled. This is how I tested;
This code creates a new table and then creates a stored procedure. The execution of the stored procedure creates a plan and it is used for subsequent execution.
SELECT *
INTO TempSalesOrderDetail
FROM Sales.SalesOrderDetail
CREATE PROC GetTempOrderDetails
AS
SELECT SalesOrderDetailID FROM TempSalesOrderDetail
ORDER BY SalesOrderDetailID
GO
EXEC GetTempOrderDetails
Here is the generated plan for it.
As per the BOL, stored procedure does not get recompiled when an index is added…
CREATE INDEX IX_1 ON TempSalesOrderDetail (SalesOrderDetailID)
GO
EXEC GetTempOrderDetails
Now the plan is different, means the stored procedure has been recompiled. There might be a situation where the behavior as same as BOL says, will see whether we can find a such situation.
5 comments:
Interesting to see that we have worked on this almost the same time. I have written a blog on the second point (Recompilation) where I have captured the recompilation event.
http://preethiviraj.blogspot.com/2010/10/recompilations-after-index-creation.html
Yes, read your post, you have explained it very well. Let's see whether we can dig on this more.
Thanks Dinesh for first issue. I worked a little bit more on parameter value issue.
Hope I have nailed down the issue
hi Dinesh,
Thanks for summarizing this, in details.Apart from using "WITH RECOMPILE" we can use this method also but problem is SQL injection issue,
--
use AdventureWorks2008
go
drop proc getorderdetails2
go
create proc getorderdetails2 @number int
as
declare @ssql nvarchar(1000)
select @ssql='select * from Sales.SalesOrderDetail where SalesOrderDetailID <= '+cast(@number as varchar);
print @ssql
exec (@ssql)
go
exec dbo.getorderdetails2 1
exec dbo.getorderdetails2 1000
--
hi Dinesh,
Thanks for summarizing this in detail, apart from using "WITH RECOMPILE" we can use this method also but only problem is SQL injection.
---
use AdventureWorks2008
go
drop proc getorderdetails2
go
create proc getorderdetails2 @number int
as
declare @ssql nvarchar(1000)
select @ssql='select * from Sales.SalesOrderDetail where SalesOrderDetailID <= '+cast(@number as varchar);
print @ssql
exec (@ssql)
go
exec dbo.getorderdetails2 1
exec dbo.getorderdetails2 1000
---
Post a Comment