Who is consuming most of the Buffer Pool?

Buffer Pool includes data cache and plan cache. Usually, data cache consumes most of the memory in buffer pool.

Data Cache

Every data page that is read from disk is written to data cache before being used.

How much space each database is using in the data cache?

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

Dirty page
A dirty page is a page that has changed in memory since it was loaded from disk and is different from the on-disk page.

How many dirty pages exist in buffer cache for each database?

SELECT db_name(database_id) AS ‘Database’,count(page_id) AS ‘Dirty Pages’
FROM sys.dm_os_buffer_descriptors WHERE is_modified =1
GROUP BY db_name(database_id) ORDER BY count(page_id) DESC

Top 10 consumers of memory

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

CACHESTORE_SQLCP—SQL plans (dynamic or prepared SQL)
CACHESTORE_OBJCP—Object plans (stored procedures, functions, and triggers)

Finding indexes and tables that use the most buffer space

SELECT OBJECT_NAME(p.object_id) AS ‘ObjectName’, p.object_id,
p.index_id, COUNT(*)/128 AS ‘buffer size(MB)’, COUNT(*)
AS ‘buffer_count’
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = db_id()
GROUP BY p.object_id, p.index_id
ORDER BY buffer_count DESC

Advertisements

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