http://www.sqlservercentral.com/blogs/robert_davis/2010/04/23/Looking-forward-to-Optimize-for-Ad-hoc-Workloads-in-Sql-Server-2008/

Printed 2014/07/24 03:40PM

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

By Robert Davis, 2010/04/23

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.

 

CacheType

Total Plans

Total MBs

Avg Use Count

Total MBs - USE Count 1

Total Plans - USE Count 1

Adhoc

42836

4058.460937

8

3918.359375

41374

Prepared

32550

4824.5625

522

1052.101562

9330

Proc

411

236.789062

494797

10.023437

27

Check

35

0.945312

7

0.320312

11

UsrTab

5

0.3125

2

0.078125

3

View

405

38.1875

36

0

0

Total

76242

9159.257811

495372

4980.882811

50745


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.