SQLServer Memory Buffers DMV’s

Memory is very critical resource for Databases,  Sql server objects(Tables,indexes) are read from disk to memory buffers upon query request. often sql shops experience memory contention and users complain on system slow issues. Microsoft has provided a variety of DMV’s to  trouble shoot memory related issues.

 

The following DMV’s will  provide you current status of memory for each database,table,index. Which objects are consuming high memory buffers.

which buffers has empty free space, which results low density.

/*
https://msdn.microsoft.com/en-us/library/ms175048.aspx
physical_memory_kb : Specifies the total amount of physical memory on the machine. Not nullable.
virtual_memory_kb:Specifies the total amount of virtual address space available to the process in user mode. Not nullable.
committed_kb:Represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager. Not nullable.
committed_target_kb:Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager.
The target amount is calculated using a variety of inputs like:
•the current state of the system including its load
•the memory requested by current processes
•the amount of memory installed on the computer
•configuration parameters
If committed_target_kb is larger than committed_kb, the memory manager will try to obtain additional memory.
If committed_target_kb is smaller than committed_kb, the memory manager will try to shrink the amount of memory committed.
The committed_target_kb always includes stolen and reserved memory. Not nullable.

*/

/*Following query returns current  memory state of sql server*/
SELECT
physical_memory_kb,
virtual_memory_kb,
committed_kb,
committed_target_kb
FROM sys.dm_os_sys_info;

/*Highest pages in memory by database*/

SELECT b.name AS database_name ,
COUNT(*) * 8 / 1024 AS mb_used
FROM sys.dm_os_buffer_descriptors a
INNER JOIN sys.databases b ON a.database_id = b.database_id
GROUP BY b.name
ORDER BY COUNT(*) DESC;

/*Find Highest pages objects in memory buffers*/

/*
sys.allocation_units
Type of allocation unit:

0 = Dropped

1 = In-row data (all data types, except LOB data types)

2 = Large object (LOB) data (text, ntext, image, xml, large value types, and CLR user-defined types)

3 = Row-overflow data
ID of the storage container associated with the allocation unit.

If type = 1 or 3, container_id = sys.partitions.hobt_id.

If type is 2, then container_id = sys.partitions.partition_id.

0 = Allocation unit marked for deferred drop
*/

SELECT d.name AS objectName ,
e.name AS indexName ,
d.type_desc AS object_type_description ,
COUNT(*) AS buffer_cache_pages ,
COUNT(*) * 8 / 1024 AS buffer_cache_used_MB
FROM sys.dm_os_buffer_descriptors a
INNER JOIN sys.allocation_units b ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions c ON ( ( b.container_id = c.hobt_id
AND type IN ( 1, 3 )
)
OR ( b.container_id = c.partition_id
AND b.type IN ( 2 )
)
)
INNER JOIN sys.objects d ON c.object_id = d.object_id
INNER JOIN sys.indexes e ON d.object_id = e.object_id
AND c.index_id = e.index_id
WHERE b.type IN ( 1, 2, 3 )
AND d.is_ms_shipped = 0
AND a.database_id = DB_ID()
GROUP BY d.name ,
e.name ,
d.type_desc
ORDER BY COUNT(*) DESC;

 

/*Free page space by database*/

SELECT
(CASE WHEN ([database_id] = 32767)
THEN N’Resource Database’
ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
COUNT (*) * 8 / 1024 AS [MBUsed],
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];
GO

 

/*Buffer  empty space pages*/

 

SELECT e.name AS index_name ,
d.name AS object_name ,
d.type_desc AS object_type_description ,
COUNT(*) AS buffer_cache_total_pages ,
SUM(CASE WHEN a.is_modified = 1 THEN 1
ELSE 0
END) AS buffer_cache_dirty_pages ,
SUM(CASE WHEN a.is_modified = 1 THEN 0
ELSE 1
END) AS buffer_cache_clean_pages ,
SUM(CASE WHEN a.is_modified = 1 THEN 1
ELSE 0
END) * 8 / 1024 AS buffer_cache_dirty_page_MB ,
SUM(CASE WHEN a.is_modified = 1 THEN 0
ELSE 1
END) * 8 / 1024 AS buffer_cache_clean_page_MB
,
SUM (CASE WHEN (a.is_modified = 1)
THEN CAST (a.[free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [Dirty page Free Space],
SUM (CASE WHEN (a.is_modified = 1)
THEN 0 ELSE CAST (a.[free_space_in_bytes] AS BIGINT) END) AS [Clean Page Free Space]
FROM sys.dm_os_buffer_descriptors a
INNER JOIN sys.allocation_units b ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions c ON ( ( b.container_id = c.hobt_id
AND type IN ( 1, 3 )
)
OR ( b.container_id = c.partition_id
AND b.type IN ( 2 )
)
)
INNER JOIN sys.objects d ON c.object_id = d.object_id
INNER JOIN sys.indexes e ON d.object_id = e.object_id
AND c.index_id = e.index_id
WHERE b.type IN ( 1, 2, 3 )
AND d.is_ms_shipped = 0
AND a.database_id = DB_ID()
GROUP BY d.name ,
e.name ,
d.type_desc
ORDER BY COUNT(*) DESC;

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s