Back to “A DMV a Day”

It turns out that there are several known issues with ad-hoc plan caching in SQL Server 2005 RTM and SP1 that have been corrected in hot fixes that are rolled up in SQL Server 2005 SP2. This blog post discusses the issue in more detail.

Recently, I detected that a 32GB database server that I use was using between 12-13GB of memory in the plan cache. This was causing internal memory pressure, and causing Page Life Expectancy (PLE) to go down into the sub 200 range (which is bad).

These DMV queries will help you detect this.

— Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP)
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS ‘Plan Cache Size(GB)’
FROM sys.dm_os_memory_cache_counters
WHERE type = ‘CACHESTORE_SQLCP’
OR type = ‘CACHESTORE_OBJCP’

— Top 10 consumers of memory from Buffer Pool
SELECT TOP 10 type, sum(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

— UseCounts and # of plans for Adhoc plans
SELECT usecounts, count(*) as no_of_plans
FROM sys.dm_Exec_Cached_plans
WHERE cacheobjtype = ‘Compiled Plan’
AND objtype = ‘Adhoc’
GROUP BY usecounts
ORDER BY usecounts

Use this DMV query to find the offenders:

— Find the ad-hoc queries that are bloating the plan cache
SELECT TOP(100) *
FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = ‘Compiled Plan’
AND objtype = ‘Adhoc’ AND usecounts = 1
–AND size_in_bytes < 200000
ORDER BY size_in_bytes DESC

Microsoft has made changes in SP2 that will help prevent this plan cache bloating problem. In the meantime, if you have lots of ad-hoc plans with a usecount of one, you can use a little trick to keep them from going into the plan cache. Just append OPTION (RECOMPILE) to the end of your ad-hoc SQL statement, and that will tell SQL Server to not put it into the cache. Then, you have to run DBCC FREEPROCCACHE to clear out the contents of the cache server-wide. You can also use a more surgical approach by using DBCC FLUSHPROCINDB.

–Determine the id of your database
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = ‘yourdatabasename’)

–Flush the procedure cache for your database
DBCC FLUSHPROCINDB (@intDBID)

Whichever method you use should be done during off-peak hours if possible.

 

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

20 Responses to Back to “A DMV a Day”

  1. John says:

    Glen, these queries are great and should be in any developer / DBA\’s arsenal.  I had no clue how to look through the plan cache unti now.  It\’s provided me with some obvious things that I need to cleanup as a developer to make more efficient.
     
    Great work and thanks for the post, you\’ve made my life much easier.

  2. absolutforyou says:

    indeed very made this blog..thanks for these instructions that you have put to disposition for all peoplehave a nice day

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s