Optimize For Ad Hoc Workloads – Observations......

I've been watching this one particular server that has been throwing alerts for high physical memory usage from Red Gate's SQL Monitoring tool.  I was discussing this with a friend of mine, Clayton Hoyt and he mentioned that I might want to look at the "Optimize for Ad Hoc Workloads" advanced server configuration options.  I took him up on his advice and googled for the "Optimize For Ad Hoc Workloads" and "SQLSkills".  I found a wonderful article from Kimberly Tripp on just this topic.  Here is the link.

 http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx

The article suggests running the below query to determine the number of plans in cache and the total space that they are taking up.  So I ran the query on my memory hungry server and the following is what happened......

SELECT objtype AS [CacheType]

        , count_big(*) AS [Total Plans]

        , sum(cast(size_in_bytes as decimal(18,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(18,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

ORDER BY [Total MBs – USE Count 1] DESC

go

Initial Query Results:  This is what I started with.

CacheType   Total Plans   Total MBs     Avg Use Count Total MBs – USE Count 1  Total Plans 

———– ————- ————- ————- ———————— ————

Adhoc       32750         2352.641181       927           947.289619                 9167

Proc        842           387.390625        169428        6.882812                   29

Prepared    576           69.460937         15176         6.234375                   64

Trigger     35            21.867187         6154          0.828125                   3

Check       31            0.812500          11453         0.023437                   1

View        327           36.875000         8057          0.000000                   0

UsrTab      4             0.468750          1473          0.000000                   0

Then I turned on "Optimized for Ad Hoc Workload" and watched the total number of plans decrease along with the total amount of space depleat.  I kept monitoring during this time as well using SQL Monitor and the only thing that I noticed was the Buffer Free Pages dropped from 1.7 million to .7 million during this 4 hours I spent watching this server.

4 hours had passed, Query Results: 

CacheType   Total Plans  Total MBs    Avg Use Count Total MBs – USE Count 1   Total Plans 

 ———-  ———–  ———— ————- ————————- ————

Adhoc       42270        2264.423034        733           659.141784                11827

Proc        833          320.968750         173396        6.882812                  29

Prepared    653          84.187500          13557         2.773437                  26

Trigger     35           21.453125          6624          0.609375                  2

Check       31           0.812500           11582         0.023437                  1

View        327          36.875000          8159          0.000000                 

UsrTab      4            0.468750           1490          0.000000                 

Then I decided instead of waiting for the server to slowly correct itself I would go ahead and issue DBCC FREEPROCCACHE to clear the procedure cache.

After DBCC command had run, Query Results:

CacheType  Total Plans   Total MBs    Avg Use Count Total MBs – USE Count 1    Total Plans

———- ————- ———— ————- ————————– ————

Proc       70            21.031250          20            5.804687                   17

Adhoc      215           5.745986           3             0.691299                   128

Prepared   21            1.625000           5             0.523437                   9

Trigger    3             1.140625           6             0.382812                   1

View       28            1.656250           8             0.000000                  

Buffer Free Pages climbing from .7 million to 1.1 million. I thought I would see Buffer Free Pages go through the roof, but I'm guessing that data cache stepped in and took over what had become available.

What I expect to see in the next 12 hours is that the total plans and total MB's will grow again, but not to the size that it was in the first query.  I'm going to bed now and I'll check the server in the morning......

And...I'm back...

Results 12 hours after cache was cleared:

CacheType  Total Plans   Total MBs    Avg Use Count Total MBs – USE Count 1       Total Plans

———- ————- ———— ————- —————————– ————

Adhoc      61025         1456.307067        8             523.744567                    46369

Proc       348           138.406250         4961          8.828125                      40

Prepared   615           76.601562          279           3.718750                      46

Check      32            0.781250           279           0.218750                      11

Trigger    16            6.398437           1247          0.000000                     

View       322           30.742187          299           0.000000                     

UsrTab     3             0.250000           48            0.000000                     

Conclusion:  The total number of Adhoc plans in cache were higher than the original baseline, however, the space being used for Adhoc plans was significantly less with the "Optimize For Ad Hoc Workloads" configuration option set on.  Because this server houses databases that are accessed with an unusual amount of Adhoc queries I will leave this option on.