Skip to content

sys-dm-os-buffer-descriptors-transact-sql.md

Latest commit

 

History

History
120 lines (102 loc) · 6.81 KB

sys-dm-os-buffer-descriptors-transact-sql.md

File metadata and controls

120 lines (102 loc) · 6.81 KB
title sys.dm_os_buffer_descriptors (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 08/14/2017
ms.prod sql-non-specified
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-reference
f1_keywords
sys.dm_os_buffer_descriptors_TSQL
dm_os_buffer_descriptors_TSQL
sys.dm_os_buffer_descriptors
dm_os_buffer_descriptors
dev_langs
TSQL
helpviewer_keywords
sys.dm_os_buffer_descriptors dynamic management view
ms.assetid 012aab95-8888-4f35-9ea3-b5dff6e3f60f
caps.latest.revision 48
author JennieHubbard
ms.author jhubbard
manager jhubbard

sys.dm_os_buffer_descriptors (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]

Returns information about all the data pages that are currently in the [!INCLUDEssNoVersion] 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. In [!INCLUDEssCurrent], this dynamic management view also returns information about the data pages in the buffer pool extension file. For more information, see Buffer Pool Extension.

When a data page is read from disk, the page is copied into the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion]. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database.

NOTE: To call this from [!INCLUDEssSDWfull] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_buffer_descriptors.

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.
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.
is_in_bpool_extension bit 1 = Page is in buffer pool extension. Is nullable.
pdw_node_id int Applies to: [!INCLUDEssSDWfull], [!INCLUDEssPDW]

The identifier for the node that this distribution is on.

Permissions

On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDEssSDS_md] Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On [!INCLUDEssSDS_md] Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

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
sys.dm_os_buffer_descriptors sys.dm_os_buffer_pool_extension_configuration 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

sys.allocation_units (Transact-SQL)

SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
Resource Database
sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)