Tuesday, January 22, 2013

Optimize for Ad hoc Workloads

I read a post today that talked about the instance-level setting referred to as "Optimize for Ad hoc Workloads". The post focused on how SQL Server stores either compiled plans or compiled plan stubs. Equally important to understanding the technical details of how plans are cached is knowing how much storage each cache type is consuming, whether it be stored procedures, prepared statements, ad hoc queries, etc. With that information you can make a determination as to how your instance my be best configured based on the workload data. 

Here is the query I run periodically to examine my workload:


SET NOCOUNT ON

SELECT  objtype AS [Cache Store Type]
       ,COUNT_BIG(*) AS [Total Plans]
       ,SUM(CAST(size_in_bytes AS DECIMAL(14,2))) / 1048576 AS [Total MBs]
       ,AVG(usecounts) AS [Avg Use Count]
       ,SUM(CASE WHEN usecounts = 1 THEN 1
                 ELSE 0
            END) AS [Total Plans - USE Count 1]
       ,SUM(CAST(( CASE WHEN usecounts = 1 THEN size_in_bytes
                        ELSE 0
                   END ) AS DECIMAL(14,2))) / 1048576 AS [Total MBs - USE Count 1]
FROM    sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - 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 [Total MBs - USE Count 1]
       ,@TotalSizeInMB AS [Total MBs - entire cache]
       ,CAST(( @AdHocSizeInMB / @TotalSizeInMB ) * 100 AS DECIMAL(14,2)) AS [% of cache 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