2

I have a bloated plan cache because of Adhoc queries. I have SQL job that goes in and purges Adhoc plans every hour. (This process was adopted from Kimberly Tripp in regards to Plan cache bloat). When I look at the top 50 adhoc plans I see this (with much more content in each row) for all 50:

Select '100', 0, ''; begin try dbcc inputbuffer(100) with no_infomsgs end try begin catch select 'RPC Event',0,'(unknown)' end catch; Select '102', 0, '';

How can I prevent that from filling up my plan cache? BTW.. I do have optimize for ad hoc workloads on.

Thanks, Shelz

  • 1
    So how exactly is your plan cache "bloated"? You know the job of the plan cache is to store execution plans for reuse, right? By "adhoc" plans do you actually mean "single-use" plans? – Aaron Bertrand Jan 31 '18 at 2:29
  • 1
    When I run Glen Barrys query: -- Find single-use, ad-hoc and prepared queries that are bloating the plan cache (Query 43) (Ad hoc Queries) SELECT TOP(50) [text] AS [QueryText], cp.cacheobjtype, cp.objtype, cp.size_in_bytes/1024 AS [Plan Size in KB] FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cp.cacheobjtype = N'Compiled Plan' AND cp.objtype IN (N'Adhoc', N'Prepared') AND cp.usecounts = 1 ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE); It tells about the ad hoc queries. I'd like to figure out where it is coming from – Shellz Jan 31 '18 at 2:33
  • All that tells you is that your system uses a lot of ad hoc queries. That doesn't mean they're bad or that there is a problem. A lot of systems use only ad hoc queries. What problem do you think you're solving by blowing them all away every hour? This feels to me like pulling over and cleaning the windshield every time a drop of rain hits it... – Aaron Bertrand Jan 31 '18 at 3:38
  • 1
    When I run sp_BlitzCache here are the results: Plan Cache Information You have 5169 total plans in your cache, with 100.00% plans created in the past 24 hours, 58.00% created in the past 4 hours, and 43.00% created in the past 1 hour. Is it normal to have that much churn on the plans? Really I would like to know what the ad hoc query is? – Shellz Jan 31 '18 at 16:25
-2

Following can be used to clean adhoc and prepared query plan cache . This is from Glenn Berry - Eight Different Ways to Clear the SQL Server Plan Cache

DBCC FREESYSTEMCACHE ('SQL Plans') 

It also says:

“I would argue that running DBCC FREEPROCCACHE does not cause that much distress with a modern processor, even on a very busy OLTP system. It will cause a pretty minor CPU spike for a few seconds on most systems as the query plans get recompiled as they are executed. “

Following query can give adhoc queries in the plan cache

SELECT  DISTINCT 
        QCP.objtype
        ,MultipleQ.PlanCount
        ,qStat.query_hash
        ,sText.text AS QueryText
        ,size_in_bytes
        ,qStat.creation_time
FROM ( 
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Adhoc'
ORDER BY MultipleQ.PlanCount DESC

If you have Query Store you can use “Delete ad-hoc queries” as defined in Monitoring performance by using the Query Store

Enabling optimize for adhoc workloads can be found in Kimberly Tripp - Plan cache and optimizing for adhoc workloads

  • I was trying to get this answered: When I run sp_BlitzCache here are the results: Plan Cache Information You have 5169 total plans in your cache, with 100.00% plans created in the past 24 hours, 58.00% created in the past 4 hours, and 43.00% created in the past 1 hour. Is it normal to have that much churn on the plans? Really I would like to know what the ad hoc query is? – Shellz Jan 31 '18 at 16:25 – Shellz Jan 24 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.