sys.dm_os_buffer_descriptors (Transact-SQL)
Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.
When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database.
Column name |
Data type |
Description |
|---|---|---|
database_id |
int |
ID of database associated with the page in the buffer pool. Is nullable. |
file_id |
int |
ID of the file that stores the persisted image of the page. Is nullable. |
page_id |
int |
ID of the page within the file. Is nullable. |
page_level |
int |
Index level of the page. Is nullable. |
allocation_unit_id |
bigint |
ID of the allocation unit of the page. This value can be used to join sys.allocation_units. Is nullable. Note sys.dm_os_buffer_descriptors might show nonexistent values in allocation_unit_id for clustered indexes that are created in versions of SQL Server earlier than SQL Server 2005. |
page_type |
nvarchar(60) |
Type of the page, such as: Data page or Index page. Is nullable. |
row_count |
int |
Number of rows on the page. Is nullable. |
free_space_in_bytes |
int |
Amount of available free space, in bytes, on the page. Is nullable. |
is_modified |
bit |
1 = Page has been modified after it was read from the disk. Is nullable. |
numa_node |
int |
Nonuniform Memory Access node for the buffer. Is nullable. |
read_microsec |
bigint |
The actual time (in microseconds) required to read the page into the buffer. This number is reset when the buffer is reused. Is nullable. |
Permissions
Requires VIEW SERVER STATE permission on the server.
Remarks
sys.dm_os_buffer_descriptors returns pages that are being used by the Resource database. sys.dm_os_buffer_descriptors does not return information about free or stolen pages, or about pages that had errors when they were read.
From |
To |
On |
Relationship |
|---|---|---|---|
sys.dm_os_buffer_descriptors |
sys.databases |
database_id |
many-to-one |
sys.dm_os_buffer_descriptors |
<userdb>.sys.allocation_units |
allocation_unit_id |
many-to-one |
sys.dm_os_buffer_descriptors |
<userdb>.sys.database_files |
file_id |
many-to-one |
Examples
A. Returning cached page count for each database
The following example returns the count of pages loaded for each database.
SELECT COUNT(*)AS cached_pages_count
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
B. Returning cached page count for each object in the current database
The following example returns the count of pages loaded for each object in the current database.
SELECT COUNT(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC;
See Also
Reference
sys.allocation_units (Transact-SQL)
Dynamic Management Views and Functions (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)