CODEWITHSUNDEEP
sql-serverperformancet-sqlcaching
Francesco Mantovani
Francesco Mantovani

Reputation: 12357

SQL Server :: when setup Optimize for Ad Hoc Workloads?

I was reading a very interesting post about how to setup Optimize for Ad Hoc Workloads.

The post is linked to a query made by Pinal Dave that allows you to retrieve the AdHoc %:

SELECT AdHoc_Plan_MB, 
       Total_Cache_MB, 
       AdHoc_Plan_MB * 100.0 / Total_Cache_MB AS 'AdHoc %'
FROM
(
    SELECT SUM(CASE
                   WHEN objtype = 'adhoc'
                   THEN CONVERT(BIGINT, size_in_bytes)
                   ELSE 0
               END) / 1048576.0 AdHoc_Plan_MB, 
           SUM(CONVERT(BIGINT, size_in_bytes)) / 1048576.0 Total_Cache_MB
    FROM sys.dm_exec_cached_plans
) T;

My AdHoc % is at 61%:

enter image description here

Pinal says "I, personally, prefer to turn Optimize for Ad Hoc Workloads settings on when I see AdHoc Percentages between 20-30%"

So what should I do if I have 61%.

Should I turn it ON or OFF?

Upvotes: 0

Views: 323

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

I, personally, prefer to turn Optimize for Ad Hoc Workloads settings on when I see AdHoc Percentages between 20-30%"

This is not enough of an indication to turn this setting on. An AdHoc Plan simply "Refers to Transact-SQL submitted as language events by using osql or sqlcmd instead of as remote procedure calls." -sys.dm_exec_cached_plans

The important thing is not whether a plan is AdHoc or not, but whether the plan is actually being reused.

Instead follow the recommendations in the docs. The important metric is not the number of AdHoc plans or the memory they use, but the number of single-use plans:

SELECT objtype, cacheobjtype, 
  AVG(usecounts) AS Avg_UseCount, 
  SUM(refcounts) AS AllRefObjects, 
  SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;

optimize for ad hoc workloads - recommendations

Upvotes: 1

Related Questions