Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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

Comments

Posted by Kimberly L. Tripp on 23 April 2010

Cool. Thanks for posting the numbers - and, I like your totals too. I would have just lamely added a non-tabular COMPUTE clause.

Oh and wait, it's cool that you've got the numbers... but, er, the numbers aren't cool. ;-)

It's definite that you will benefit from this feature. I also wonder if it won't be a good for you to also consider clearing cache when you're wasting more than 1GB.

Cheers Robert!

kt

Posted by Robert Davis on 23 April 2010

By the way, we inherited the application and are working on rearchitecting it.

Posted by Anonymous on 23 April 2010

This post was mentioned on Twitter by sqlmanager: SQLServer Central Blogs. Looking forward to Optimize for Ad hoc Workloads in #Sql #Server 2008: One of the feature... http://bit.ly/c5xASd

Leave a Comment

Please register or log in to leave a comment.