Plan Cache Pages by Database in SQL Server 2008R2

Question

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?

Answer 1

use db1;
select ... 
from db2..table
join db3..table;

Should the memory allocated for this plan be counted against db1, db2 or db3? Usually the three dbs in my example are the same and the issue is not perceived, but the problem is there none the less and this is why a view like sys.dm_exec_cached_plans does not project database_id, and sys.dm_exec_query_plan has the note:

For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

The thinking around this issues should be 'memory allocated for plans', 'memory allocated for execution' and don't consider such memory as 'pages'. This is even more so in the SQL Server 2012 memory model which makes the buffer pool and ordinary client of the memory broker.

For 'data' is all clear: a data page has an obvious database is coming from and sys.dm_os_buffer_descriptors will describe it exactly.

And for the second question: sys.dm_os_buffer_descriptors will only show data, not stolen pages.

Answer 2

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 cool 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.