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.

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 

Here is the plan now.

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.