Thursday, October 21, 2010

Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?

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.
plan1

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

plan2 
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;
BOL

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.
plan3 
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 

Here is the plan now.
plan4

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:

Preethiviraj Kulasingham said...

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

Dinesh Priyankara said...

Yes, read your post, you have explained it very well. Let's see whether we can dig on this more.

Preethiviraj Kulasingham said...

Thanks Dinesh for first issue. I worked a little bit more on parameter value issue.
Hope I have nailed down the issue

Tharindu Dhaneenja said...

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
--

Tharindu Dhaneenja said...

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
---