Understanding Memory - SQL Server 2012 Internals and Troubleshooting - page 66

Buf er Pool
The buffer pool contains and manages SQL Server's data cache. Information on its contents can be
found in the sys.dm_os_buffer_descriptors DMV. For example, the following query returns the
amount of data cache usage per database, in MB:

SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id),database_id
ORDER BY 'Cached Size (MB)' DESC

Monitoring SQL Server's buffer pool is a great way to look out for memory pressure, and
Performance Monitor provides numerous counters to help you do this for quick insight, including
the following:

➤

MSSQL$<instance >:Memory Manager\Total Server Memory (KB) — Indicates the cur-
rent size of the buffer pool

➤

MSSQL$<instance >:Memory Manager\Target Server Memory (KB) — Indicates the
ideal size for the buffer pool. Total and Target should be almost the same on a server with
no memory pressure that has been running for a while. If Total is signii cantly less than
Target, then either the workload hasn't been sufi cient for SQL Server to grow any further
or SQL Server cannot grow the buffer pool due to memory pressure, in which case you can
investigate further.

➤

MSSQL$<instance >:Buffer Manager\Page Life Expectancy — Indicates the amount of
time, in seconds, that SQL Server expects a page that has been loaded into the buffer pool
to remain in cache. Under memory pressure, data pages are l ushed from cache far more
frequently. Microsoft recommends a minimum of 300 seconds for a good PLE; this threshold
continues to be debated within the SQL Server community, but one thing everyone agrees
on is that less than 300 seconds is bad. In systems with plenty of physical memory, this will
easily reach thousands of seconds.

Plan Cache
Execution plans can be time consuming and resource intensive to create; therefore, it makes sense
that if SQL Server has already found a good way to execute a piece of code, it should try to reuse it
for subsequent requests. The plan cache (also referred to as the procedure cache) is used to cache all
the execution plans in case they can be reused.

You can view the contents of the plan cache and determine its current size by using the
sys.dm_exec_cached_plans DMV or by running DBCC MEMORYSTATUS and looking for the
“Procedure Cache” section, where you'll i nd the number of plans in cache and the cache size, in
8KB pages.