Skip to content

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

Latest commit

 

History

History
126 lines (107 loc) · 6.83 KB

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

File metadata and controls

126 lines (107 loc) · 6.83 KB

title: "sys.dm_os_buffer_descriptors (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "08/14/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-database" ms.service: "" ms.component: "dmv's" ms.reviewer: "" ms.suite: "sql" 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: "stevestein" ms.author: "sstein" manager: "craigg" ms.workload: "Inactive" monikerRange: "= azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions"

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], requires the VIEW DATABASE STATE permission in the database.

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)