Optimize for ad hoc workloads

Question

Post reply

Optimize for ad hoc workloads

Michael Valentine Jones

SSC Guru

Points: 64818
More actions
June 17, 2011 at 11:32 am

#389766

I have a server running SQL Server 2008 (SP1) - 10.0.2531.0 (X64) with a vendor supplied application that has a high level of ad hoc queries that are used once, so I set this option on:
exec sp_configure 'optimize for ad hoc workloads', 1;
When I run the query below to look at the plan sizes for single use plans, I see 8234 compiled plans that have a use count of one. They are using 549 MB of memory, so it is not an insignificant amount of memory being used for one time plans. I thought that SQL Server was supposed to compile these into a plan stub to save space when you have the “optimize for ad hoc workloads” option set on. Can anyone explain why these are compiled plans, instead of compiled plan stubs?
Type Use Count MB Plan Count Average Bytes
-------------------- --------- ---------- ----------- -------------
Compiled Plan Stub 1 4.64 15262 319
Compiled Plan Stub Total 4.64 15262 319
Compiled Plan 1 549.87 8234 70023
Compiled Plan 2 186.90 3701 52952
Compiled Plan 3 219.42 3938 58425
Compiled Plan 4 101.01 1645 64385
Compiled Plan 5 147.01 3424 45020
Compiled Plan 6 59.52 1186 52619
Compiled Plan 7 96.30 2159 46772
Compiled Plan 8 41.89 1047 41953
Compiled Plan 9 59.00 1376 44960
Compiled Plan 10 27.80 568 51329
Compiled Plan > 10 723.43 15104 50223
Compiled Plan Total 2212.15 42382 54730
Total All Types Total 2216.79 57644 40324
Query for results above:
-- Analyze plan cache usage and size
select
Type =
isnull(left(cacheobjtype,20),'Total All Types'),
[Use Count] =
isnull(
case
when usecounts < 11
then right(' '+convert(varchar(5),usecounts),5)
else ' > 10' end,'Total'),
MB =
convert(numeric(8,2),round(sum(convert(bigint,size_in_bytes))/(1024.00*1024.00),2)),
[Plan Count] = count(*),
[Average Bytes] =
convert(int,avg(convert(bigint,size_in_bytes)))
from
sys.dm_exec_cached_plans
where
objtype = 'Adhoc'
group by
cacheobjtype,
case
when usecounts < 11
then right(' '+convert(varchar(5),usecounts),5)
else ' > 10' end
with rollup
order by
cacheobjtype desc,
[Use Count]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 1

Two quick questions that may help:

1. Did you run the RECONFIGURE statement after setting the option? If not, this may not be active.

2. If you did run RECINFIGURE, does the amount of memory used by the cached plans change? If not, these may be cached plans from before you changed the optimization option. The new setting does not touch already cached plans. You will have to clear the cache manually.

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 2

The fact that you have 15000+ stubs implies that it is enabled; as Jerry mentioned, existing plans are not cleared

http://msdn.microsoft.com/en-us/library/cc645587.aspx

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

I ran the following code when I turned on "optimize for ad hoc workloads", so all the the plans in memory are from after the option was set.

exec sp_configure 'optimize for ad hoc workloads', 1;

reconfigure;

DBCC FREEPROCCACHE;

By the way, I have seen the same behaviour on another server:

Type Use Count MB Plan Count Average Bytes

-------------------- --------- ---------- ----------- -------------

Compiled Plan Stub 1 7.45 33286 234

Compiled Plan Stub Total 7.45 33286 234

Compiled Plan 1 193.56 3751 54109

Compiled Plan 2 27.40 915 31398

Compiled Plan 3 7.20 62 121822

Compiled Plan 4 .23 7 33938

Compiled Plan 5 4.54 6 793258

Compiled Plan 6 .09 3 32768

Compiled Plan 7 .26 12 22528

Compiled Plan 8 .14 5 29491

Compiled Plan 9 .29 6 50517

Compiled Plan > 10 4.88 186 27482

Compiled Plan Total 238.59 4953 50509

Total All Types Total 246.03 38239 6746

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 4

Could the query be running twice? The first time creates the stub, the second turns it into a compiled plan with usecount of 1.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

matt stockham (6/20/2011)
Could the query be running twice? The first time creates the stub, the second turns it into a compiled plan with usecount of 1.

I thought of that, but I don't know of any way to tell if that is it.

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 6

Just read at Bob Beauchemin'd blog:http://www.sqlskills.com/blogs/bobb/post/Performance-features-in-SQL-Server-2008-RC0-Optimize-for-Adhoc-Workloads.aspx

"stub for the non-parameterized version, plan for the parameterized version"

Could you check/confirm that ?

edited: copied wrong ref.

This was RC0. should have been optimized by now :ermm:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

ALZDBA (6/20/2011)
Just read at Bob Beauchemin'd blog:http://www.sqlskills.com/blogs/bobb/post/Performance-features-in-SQL-Server-2008-RC0-Optimize-for-Adhoc-Workloads.aspx
"stub for the non-parameterized version, plan for the parameterized version"
Could you check/confirm that ?

Are you sure that is the correct link? I didn't find those words anywhere on that link.

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 8

Strange.

http://www.sqlskills.com/blogs/bobb/post/Performance-features-in-SQL-Server-2008-RC0-Optimize-for-Adhoc-Workloads.aspx states:

-- stub for the non-parameterized version, plan for the parameterized version
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE [text] NOT LIKE '%dm_exec%'
ORDER BY p.usecounts DESC
-- query plan for stub query handle is not saved returns NULL
SELECT sql.text, p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE text NOT LIKE '%sys_dm_exec%' AND text NOT LIKE '%msparam_0%'
ORDER BY qs.EXECUTION_COUNT DESC