Sql-server – Plan Cache Pages by Database in SQL Server 2008R2

memorysql-server-2008-r2

Is it possible to get a break down of how many plan cache pages are being used per database? Perfmon seems to only have the total for the server, but maybe there is a DMV with more detail?

Also, does the buffer pool (sys.dm_os_buffer_descriptors) include the plan cache or is that separate?

Best Answer

Here are queries that will give you buffer breakdown and plan cache breakdown per database. Note that plan cache is broken down by MB, not by pages - I don't know of a handy way, off the top of my head, to calculate the pages used by the plan cache (you'd think it's simple division, but not necessarily).

-- buffer pool breakdown

SELECT 
  db = DB_NAME(database_id),
  c = COUNT(DISTINCT page_id),
  size_MB = CONVERT(DECIMAL(12,2), COUNT(DISTINCT page_id)*8.0/1024) 
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767
GROUP BY database_id
ORDER BY c DESC;

-- plan cache breakdown

SELECT 
  db = DB_NAME(t.[dbid]), 
  size_MB = CONVERT(DECIMAL(12,2), SUM(p.size_in_bytes)/1024.0/1024)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
WHERE t.[dbid] < 32767
GROUP BY t.[dbid]
ORDER BY size_MB DESC;

On my test instance there are cases where plan cache is larger than the buffer pool for a given database.

The buffer pool contains both the data cache and the plan cache. But AFAIK sys.dm_os_buffer_descriptors only contains information about the data cache (index and data pages), so it does not necessarily reflect the entire buffer pool. The cases I saw above seem to support that theory.

Related Question