Looking forward to Optimize for Ad hoc Workloads in Sql Server 2008

One of the features of SQL Server 2008 that I think my application will be able to take advantage of once we migrate is Optimize for Ad hoc Workloads. In short, what this feature does is not store the cached plan the first time it is used. It stores just the plan hash that it can use to identify when a matching plan is created the second time. When the plan is created a second time, it is cached. This can be used to alleviate a lot of wasted cache space for plans that never get reused. Obviously, this is only advantageous if you have a large amount of cache dedicated to single-use plans. And boy howdy, my application does have that.

The Query

For the below stats, I am using a slightly modified version of the query posted by Kimberly Tripp (blog|twitter). The only real difference here is that this query also produces a Total line at the bottom with everything summed.

For more information on Optimize for Ad hoc Workloads and this query, I recommend reading Kimberly Tripp’s blog post: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx

WITH CacheSums

As (SELECT 1 As OrderBy,

            objtype AS [CacheType]

        , count_big(*) AS [Total Plans]

        , sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]

        , avg(usecounts) AS [Avg Use Count]

        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]

        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

      FROM sys.dm_exec_cached_plans

      GROUP BY objtype

      UNION ALL

      SELECT 2 AS OrderBy,

            'Total' AS [CacheType]

        , count_big(*) AS [Total Plans]

        , sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]

        , avg(usecounts) AS [Avg Use Count]

        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]

        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

      FROM sys.dm_exec_cached_plans)

SELECT CacheType, [Total Plans], [Total MBs], [Avg Use Count], [Total MBs - USE Count 1]

FROM CacheSums

ORDER BY OrderBy, [Total MBs - USE Count 1] DESC

My Results

These results are from my main production SQL Server that has 120 GB of RAM allocated as its maximum RAM.