How to find out if you need to enable “optimize for ad hoc workloads”
- 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 |
- 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.
- 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
- 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.
- 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