If you want your query to run faster, you need to understand the optimizer. SQL server database engine uses query optimizer to generate an optimal execution path. The optimal execution path is nothing but the path that offers best performance.
Before the query executes, the optimizer assigns a cost based on CPU and disk I/O usage for different execution paths. The optimizer then uses the least expensive path and executes the query.
When we execute a stored procedure for the first time, its query plan is stored in cache. It will reside in the memory as long as memory is available or until it is recompiled.
After generating the query plan, if you add an index to a table that is used by the stored procedure, it may not be used by the stored procedure.
Hence whenever you do modifications for a table that is used by the stored procedure, it is best practice to use SP_RECOMPILE or you can drop and recreate the stored procedure.
A stored procedure automatically recompiles its query plan whenever you drop an index used by a table within a stored procedure.
To check if a stored procedure plan is recompiled the SP:Recompile SQL Server Profiler event can be monitored.
Automatic recompilation occurs whenever the following changes happen:
- Executing an ALTER TABLE statement.
- Creating or dropping indexes.
- Executing UPDATE STATISTICS.
- Table data modifications.
Let us consider the following example:
CREATE PROCEDURE sp_Test @LName char(30) AS
SELECT LastName
FROM Users
WHERE LastName like @LName + '%'
CREATE INDEX LastName_idx ON Users(LastName)
EXEC sp_Test 'B'
If the table contains numerous records that have a last name beginning with the letter B, the optimizer is likely to perform a table scan. Now consider what happens when a user executes the query with 'BALA' as a parameter:
EXEC sp_Test 'BALA'
If the table contains only a few records that have a last name equal to BALA, the optimizer may use an index.
As you can see, the optimizer chooses different query plans based on the value of the parameter passed into the stored procedure. The problem arises when the first query plan is stored in memory.
By default, different users receive the same query plan based on the first execution of the stored procedure. If the first user of the stored procedure passed 'B' as a parameter, the query plan would be very inefficient for other users who specify 'BALA' as a parameter.
To avoid this problem, use the WITH RECOMPILE option when you execute the procedure. This forces the procedure to regenerate its query plan, as in the following:
EXEC ... WITH RECOMPILE
The revised query plan is available only for the current execution of the procedure. Subsequent executions of the procedure without the recompile option revert back to the old query plan.
Additionally, you can use the WITH RECOMPILE option when you create the procedure, as in the following:
CREATE PROCEDURE ... WITH RECOMPILE
This option forces the optimizer to recompile the query plan each time the stored procedure is executed.
Questions:
1. Which of the following option lets your stored procedure recompile each time when it is executed? Choose all that apply.
A. Add the RECOMPILE query hint to one of the stored procedure statements.
B. Include the WITH RECOMPILE option in stored procedure’s definition.
C. Include WITH RECOMPILE option when you run the stored procedure.
D. Use the sp_recompile system stored procedure.
2. Automatic recompilation occurs whenever the following changes happen. Choose all that apply.
A. Executing an ALTER TABLE statement.
B. Creating or dropping indexes.
C. Executing UPDATE STATISTICS.
D. Table data modifications.
Show Answers:
