Tuesday, March 6, 2012

Handling ad hoc workload using optimize for ad hoc workloads


How to find out if you need to enable “optimize for ad hoc workloads”


  1. Plan cache size:
SQL Server 2008 is calculated as following
% Memory of target (GB)
Total GB
SERVER1
75% visible target memory from 0-4GB
4
3
10% visible target memory from 4-64GB
56
5.6
5% visible target memory > 64GB
0
0


8.6

  1. SERVER1 stats

Run following query on the server to get the stats

-- Do not run this TSQL until SQL Server has been running for at least 3 hours
SET NOCOUNT ON

SELECT objtype AS [Cache Store Type],
        COUNT_BIG(*) AS [Total Num Of Plans],
        SUM(CAST(size_in_bytes as decimal(14,2))) / 1048576 AS [Total Size In MB],
        AVG(usecounts) AS [All Plans - Ave Use Count],
        SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(14,2)))/ 1048576
        AS [Size in MB of plans with a Use count = 1],
        SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Number of of plans with a Use count = 1]
       
        FROM sys.dm_exec_cached_plans
        GROUP BY objtype
        ORDER BY [Size in MB of plans with a Use count = 1] DESC

DECLARE @AdHocSizeInMB decimal (14,2), @TotalSizeInMB decimal (14,2)

SELECT @AdHocSizeInMB = SUM(CAST((CASE WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc'
THEN size_in_bytes ELSE 0 END) as decimal(14,2))) / 1048576,
        @TotalSizeInMB = SUM (CAST (size_in_bytes as decimal (14,2))) / 1048576
        FROM sys.dm_exec_cached_plans

SELECT @AdHocSizeInMB as [Current memory occupied by adhoc plans only used once (MB)],
         @TotalSizeInMB as [Total cache plan size (MB)],
         CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 as decimal(14,2))
         as [% of total cache plan occupied by adhoc plans only used once]
IF  @AdHocSizeInMB > 200 or ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25  -- 200MB or > 25%
        SELECT 'Switch on Optimize for ad hoc workloads as it will make a significant difference' as [Recommendation]
ELSE
        SELECT 'Setting Optimize for ad hoc workloads will make little difference' as [Recommendation]
GO

On an average 24% of cache is used by ad-hoc plans which are used once

CacheType
Total Plans
Total MBs
Avg Use Count
Total MBs - USE Count 1
Total Plans - USE Count 1
Adhoc
14004
4711.320312
37
1658.414062
2902
Proc
3812
2138.3125
306590
970.523437
1184
Prepared
570
52.882812
3755
14.898437
84
Trigger
7
1.101562
57
0.085937
1
UsrTab
2
0.4375
16
0
0
View
796
74.242187
50
0
0
Check
21
0.585937
75
0
0

Current memory occupied by adhoc plans only used once (MB)
Total cache plan size (MB)
% of total cache plan occupied by adhoc plans only used once
1666.85
6994.29
23.83

So on SERVER1 8.6 GB of cache is available to SQL and about 2GB is being used by ad-hoc workload.

  1. Advantage:
It is recommended that if more than 25% of procedure cache is used by ad-hoc queries then it is advisable to turn on. When this option is turned on, once the database engine has compiled a batch for the first time, instead of saving the full compiled plan (potentially of several tens of kilobytes) it instead saves just a tiny 18 byte “stub”. This saves considerable space in the procedure cache.(Ref http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/). In our case we will save about 2 GB memory if we turn on this option

  1. Disadvantages:
Even though batch is ad-hoc there is possibility that it will need to run again. In such case plan will be compiled twice. Hence we run into risk of over compilation.

  1. Conclusion:
We are at the borderline of threshold (25%) so at this time it will not make much difference if we turn on this option. Since we are in process of adding more memory to the server we should revisit this at that time. At this time I would focus more on reducing the number of ad-hoc plans by reviewing SPs for parameterization.

In case you decide to enable this option; here is the script to do so

sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

No comments:

Post a Comment