Stored Procedures in the procedure cache

I’ve written about auto-parameterization, forced parameterization, the ‘optimize for ad-hoc workloads’ option and sp_exectutesql.   Before pulling all of these ideas together to discuss query plan reuse there is another very important component that I haven’t talked about. That is stored procedures.  Like parameterized queries whose execution plans get stored in the plan cache and can be reused by similar queries, the execution plans for stored procedures also get stored in the plan cache.  From a plan cache use perspective stored procedures provide some benefits over parameterized queries.  They also however are different in some very important and nuanced ways.  The point of this post isn’t to examine all of the pros and cons of stored procedures but to look at how they and the individual statements in them interact with the plan cache.

First I need to create a table with some example data.

IF object_id(‘dbo.Skew’) IS NOT NULL
DROP TABLE dbo.Skew
CREATE TABLE dbo.Skew
(
Col1 int,
Col2 char(2)
)
GO

DECLARE @c int
SET @c = 0
WHILE @c < 1000
BEGIN
INSERT INTO Skew (Col1,Col2) VALUES (@c,’aa’)
SET @c = @c + 1
END
GO

INSERT INTO Skew (Col1,Col2) VALUES (1000,’aa’)
GO 1000

INSERT INTO Skew (Col1,Col2) VALUES (2000,’aa’)
GO 1000

INSERT INTO Skew (Col1,Col2) VALUES (3000,’aa’)
GO 1000

INSERT INTO Skew (Col1,Col2) VALUES (4000,’aa’)
GO 1000

INSERT INTO Skew (Col1,Col2) VALUES (5000,’aa’)
GO 1000

INSERT INTO Skew (Col1,Col2) VALUES (6000,’aa’)
GO 1000

INSERT INTO Skew (Col1,Col2) VALUES (7000,’aa’)
GO 1000

INSERT INTO Skew (Col1,Col2) VALUES (8000,’aa’)
GO 1000

INSERT INTO Skew (Col1,Col2) VALUES (9000,’aa’)
GO 1000

CREATE INDEX SkewIndex ON dbo.Skew (Col1)

This data is skewed so different search arguments will produce different query plans. The purpose of this is to show how, like other parameterized queries, a stored procedure can produce a query plan that may not be the best for subsequent queries with different search arguments.
The query I will be using for most of my examples:

CREATE PROC ExampleProc (@Col1 int, @Col2 nchar(2))
AS
SELECT Col2 FROM Skew where Col1 = @Col1
GO

And this can be used to look at the procedure cache:

select t.text, cp.bucketid, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype, qp.query_plan
from sys.dm_exec_cached_plans cp
outer apply sys.dm_exec_query_plan(cp.plan_handle) qp
outer apply sys.dm_exec_sql_text(cp.plan_handle) t
where (t.dbid < 32767 or t.dbid is null)
order by objtype,cacheobjtype,t.text

First I want to look at what happens when this query is run outside of a stored procedure, i.e. as an ad hoc query.
SELECT Col2 FROM Skew where Col1 = @Col1

Substituting @Col1 with 1 and running the query produces a plan that looks like this:

But running the query substituting @Col1 with 1000 produces an execution plan that looks like this:

If you recall from reading the post about auto and forced parameterization a parameterized query will create a plan that will continue to be used for future queries that only differ by search arguments.  In this instance the query was not auto-parameterized nor is forced parameterization in effect.  Because of this the optimizer is free to produce a plan that is best suited for the specific search arguments used in each execution.  The upside of this is that the execution plan produced is crafted for the best performance.  The downside is that a lot of queries with different search arguments will produce a lot of different plans and all of these plans will be cached.  This can be tested by changing the search argument to any number and examining the buildup of plans in the plan cache.

When this query is included in a stored procedure it operates much like a parameterized query.
The stored procedure is compiled and, because of parameter sniffing, the execution plan produced is optimized for the search arguments used on the initial compilation.  If the procedure is called again with values that would perform faster with a different execution plan SQL Server will not take advantage of a new plan without specific instructions from the author.
While a stored procedure can protect the plan cache from being filled by a lot of plans it can also cause SQL Server to use a sub-standard plan.
If I call the stored procedure with these parameters

EXEC ExampleProc 1,’aa’

The execution plan looks like this.

This plan is similar to the plan created when the query in the stored procedure (this one: SELECT Col2 FROM Skew where Col1 = 1) was run as an ad-hoc query.
If I change the parameter from 1 to 1000

EXEC ExampleProc 1000,’aa’

the stored procedure will keep the plan used during the initial compliation rather then using a table scan, which we know from running the ad hoc query earily would be the quicker way to retrive the data for this specific search argument. While table scan, which would be the most efficient way of getting the data back, SQL Server is stuck using the cached execution plan. While normally I would paste a picture of the query plan after this as proof of what is happing that would be pretty dumb because the query plan in the plan cache is the same exact plan I just posted.  However, one can see that the same cached plan is being used by looking at the usecounts column.  The number increments by one each time the plan is used.  I executed the stored procedure 5 times and the usecounts value is 5.  Not a coincidence.

There are a few ways to mitigate the problem of a statement in a procedure using an substandard query plan.

1. One could create the procedure with the WITH RECOMPILE option
CREATE PROC ExampleProc (@Col1 int, @Col2 nchar(2))
WITH RECOMPILE
AS
SELECT Col2 FROM Skew where Col1 = @Col1
GO

WITH RECOMPILE is a misnomer. It doesn’t actually force the query to recompile, technically. When running a procedure that was created with WITH RECOMPILE the procedure is compiled, the procedure is executed and then the plan that was created is discarded.  A better description of WITH RECOMPILE would be “WITH DON’T SAVE IN THE PLAN CACHE” but I guess Microsoft didn’t want us to have to type all of that.

It is impossible to query the plan cache to see what plan is used (cause it isn’t cached for us to look at) so instead I will enable viewing the execution plan through the “Include Actual Execution Plan” button on the SSMS toolbar.

Clearing the procedure cache using DBCC FREEPROCCACHE, then executing the stored procedure with the first parameter being 1 then this plan is created.

And this is what is in the proc cache:

The SET STATISTICS_XML commands are associated with enabling and disabling the showing of the execution plan in SSMS and can be ignored.  The more optimal plan was created and the plan wasn’t saved in the plan cache.

Changing the first parameter of the stored proc to 1000 the new plan looks like this:

Looking at the procedure cache shows that the plan was not cached. Unlike the previous examples the plan changed as the search argument changed.

2. Use WITH RECOMPILE when executing the stored procedure.
Adding WITH RECOMPILE to the EXEC command will cause that specific execution of the procedure to compile from scratch and to not be cached.  If the procedure had been executed previously without the WITH RECOMPILE option and cached that instance in the plan cache will be ignored by future executions of the procedure that use WITH RECOMPILE.  Freeing the procedure cache and running the query a few times can show it in the plan cache. First I have to remove WITH RECOMPILE from the procedure itself and recreate it so it will get saved in the cache.

I ran the query five times with the first parameter equal to 1 to show that the usecount is now 5 and that the plan includes a bookmark lookup.  The cached query plan looks like this:

Now running the procedure again with the WITH RECOMPILE option, the 1st parameter set to 1000 and Include Actual Execution Plan turned on:

EXEC ExampleProc 1000,’aa’ WITH RECOMPILE

The plan cache shows that the use count is still 5 and the execution plan produced shows that a table scan is used instead of the bookmark lookup that is in the cached plan.

Executing the procedure again without the WITH RECOMPILE option and with the 1st parameter as 1000 causes the usecount to be incremented by one and the plan used is the cached sub-optimal plan.

3. Use OPTION (RECOMPILE) on an individual query in the stored procedure.

CREATE PROC ExampleProc (@Col1 int, @Col2 nchar(2))
AS
SELECT Col2 FROM Skew where Col1 = @Col1 OPTION (RECOMPILE)
GO

Watching the usecounts of this procedure in the plan cache shows that the procedure itself is being reused but looking at the query plan each time also shows that this specific statement is being recompiled with each use and that the optimal query plan is being created depending on the value being passed into the first parameter.  Adding a second statement in the procedure allows one to see the difference between how different statements are handled by SQL Server.

CREATE PROC ExampleProc (@Col1 int, @Col2 nchar(2))
AS
SELECT Col2 FROM Skew where Col1 = @Col1
SELECT Col2 FROM Skew where Col1 = @Col1 OPTION (RECOMPILE)
GO

This query shows each of the SQL Statements in the batch:

select
case
when sql_handle is not null then
case
when qs.statement_end_offset <> -1 then
substring(t.text,statement_start_offset/2+1,(qs.statement_end_offset/2-qs.statement_start_offset/2)+1)
else substring(t.text,statement_start_offset/2+1,(len(t.text)*2-qs.statement_start_offset)/2+1)
end
else null
end [query_text],
cp.usecounts,qs.execution_count,qs.plan_generation_num,cp.cacheobjtype,cp.objtype,qp.query_plan,qs.*
from sys.dm_exec_cached_plans cp
outer apply sys.dm_exec_query_plan(cp.plan_handle) qp
outer apply sys.dm_exec_sql_text(cp.plan_handle) t
left join sys.dm_exec_query_stats qs
on qs.plan_handle = cp.plan_handle
where (t.dbid < 32767 or t.dbid is null)
order by objtype,cacheobjtype,t.text

Clearing the procedure cache, running this 5 times and looking at the plan cache shows us what is going on:

EXEC ExampleProc 1000,’aa’

The usecounts column is related to the stored procedure itself.  The execution _count column is for the SQL Statements.  In this example the select without the RECOMPILE option has executed the same number of times the procedure has been used.  The select with the RECOMPILE option has been executed once but the plan generation number shows that it has been compiled 6 times, once for the initial compilation and 5 times for the actual executions of the procedure.

The statement level recompile options is great for those times when a small number of statements in a procedure perform best when the plan for that query is optimized each instance it is run.