I recently turned on OPTIMIZE FOR AD HOC WORKLOADS for the first time on one of my servers.  When I went back and looked at sys.dm_exec_cached_plans the next day I had a bit of a shock.  There were over 9000 compiled plans with only one use.  I had thought that wasn’t possible.  If a plan gets stored as a “compiled plan” that means it should have more than one use right?  So I started digging in.  First thing is the test script:

 ---------------------------------------------------------
-- Create output table
CREATE TABLE #OptimizeRun (
Run varchar(100),
TimesQueryRun Int,
TotalLogicalReads Int,
PlanCacheSize Int,
CacheType varchar(100),
ObjectType varchar(100),
QueryText varchar(max)
)
GO
---------------------------------------------------------
-- Make sure OPTIMIZE FOR ADHOC WORKLOADS is turned off
sp_configure 'optimize for ad hoc workloads', 0
GO
RECONFIGURE
GO
-- Clear the procedure cache
DBCC FREEPROCCACHE
GO
-- First run without OPTMIZE ...
GO
SELECT * FROM Person.BusinessEntity
GO
INSERT INTO #OptimizeRun
SELECT
'NoOptimize, Run 1',
usecounts AS TimesQueryRun,
total_logical_reads AS TotalLogicalReads,
size_in_bytes AS PlanCacheSize,
cacheobjtype AS CacheType,
objtype AS ObjectType,
[text] AS QueryText
FROM sys.dm_exec_cached_plans plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_exec_query_stats qstats
ON qstats.plan_handle = plans.plan_handle
WHERE [text] LIKE 'SELECT * FROM Person.BusinessEntity%'
GO
-- Second run without OPTMIZE ...
GO
SELECT * FROM Person.BusinessEntity
GO
INSERT INTO #OptimizeRun
SELECT
'NoOptimize, Run 2',
usecounts AS TimesQueryRun,
total_logical_reads AS TotalLogicalReads,
size_in_bytes AS PlanCacheSize,
cacheobjtype AS CacheType,
objtype AS ObjectType,
[text] AS QueryText
FROM sys.dm_exec_cached_plans plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_exec_query_stats qstats
ON qstats.plan_handle = plans.plan_handle
WHERE [text] LIKE 'SELECT * FROM Person.BusinessEntity%'
GO
---------------------------------------------------------
-- Make sure OPTIMIZE FOR ADHOC WORKLOADS is turned on
sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO
-- Clear the procedure cache
DBCC FREEPROCCACHE
GO
-- First run with OPTMIZE ...
GO
SELECT * FROM Person.BusinessEntity
GO
INSERT INTO #OptimizeRun
SELECT
'Optimize, Run 1',
usecounts AS TimesQueryRun,
total_logical_reads AS TotalLogicalReads,
size_in_bytes AS PlanCacheSize,
cacheobjtype AS CacheType,
objtype AS ObjectType,
[text] AS QueryText
FROM sys.dm_exec_cached_plans plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_exec_query_stats qstats
ON qstats.plan_handle = plans.plan_handle
WHERE [text] LIKE 'SELECT * FROM Person.BusinessEntity%'
GO
-- Second run with OPTMIZE ...
GO
SELECT * FROM Person.BusinessEntity
GO
INSERT INTO #OptimizeRun
SELECT
'Optimize, Run 2',
usecounts AS TimesQueryRun,
total_logical_reads AS TotalLogicalReads,
size_in_bytes AS PlanCacheSize,
cacheobjtype AS CacheType,
objtype AS ObjectType,
[text] AS QueryText
FROM sys.dm_exec_cached_plans plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_exec_query_stats qstats
ON qstats.plan_handle = plans.plan_handle
WHERE [text] LIKE 'SELECT * FROM Person.BusinessEntity%'
GO
-- Third run with OPTMIZE ...
GO
SELECT * FROM Person.BusinessEntity
GO
INSERT INTO #OptimizeRun
SELECT
'Optimize, Run 3',
usecounts AS TimesQueryRun,
total_logical_reads AS TotalLogicalReads,
size_in_bytes AS PlanCacheSize,
cacheobjtype AS CacheType,
objtype AS ObjectType,
[text] AS QueryText
FROM sys.dm_exec_cached_plans plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_exec_query_stats qstats
ON qstats.plan_handle = plans.plan_handle
WHERE [text] LIKE 'SELECT * FROM Person.BusinessEntity%'
GO 

I ran this on a standard AdventureWorks2008 database. First a few notes from the time spent creating the script.

1. When comments were included in the batch with my test script my test script was stored as separate stubs for each run. This is why the “GO” statement right after the comment. Otherwise I ended up with 3 different stubs in the second half of the script.
2. I hadn’t realized that you can use plan_handle and sql_handle interchangeably in sys.dm_exec_sql_text. Or at least so it seems. My testing bears it out so if I’m wrong someone please let me know.

I’ve found 2 reasons for my single use compiled plans, although there certainly may be more. Firstly I found that when I ran a stored procedure for the first time I always ended up with a compiled plan not a stub. I’m a little surprised by this and will have to do some more reading to try to figure out why this is the case. I would have expected this to stub out also.

The second thing I found is a bit more complicated and requires the output from my test script.

For the first two tests I had turned OPTIMIZE FOR AD HOC WORKLOADS off and you can see the first run created the compiled clan, took up about 32mb, had 98 logical reads and of course was only run once. The second run updated this information so we now have a total of 196 logical reads and two runs. So far exactly what I had expected. Next we turn OPTIMIZE FOR AD HOC WORKLOADS on. Now on the first run we get a compiled plan stub. This only takes up 320 bytes (MUCH smaller) and still has the expected 1 run and 98 total reads.

On Run 2 I got some unexpected results. I get my compiled plan just like I expected but there is only 1 run and 98 total logical reads. Now I know I ran it 2ce so the only logical solution is that when the stub gets dropped and the compiled plan gets added none of the previous run’s information get’s carried over. I did add one more run just to make sure I get the 2 runs, 196 total reads etc as I expected.

I’m not sure if this is considered expected behavior or a bug but it’s certainly something to keep in mind.