sys.dm_os_buffer_descriptors (Transact-SQL)sys.dm_os_buffer_descriptors (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

返回有关 SQL ServerSQL Server 缓冲池中当前所有数据页的信息。Returns information about all the data pages that are currently in the SQL ServerSQL 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. SQL ServerSQL Server中,此动态管理视图还返回有关缓冲池扩展文件中的数据页的信息。In SQL ServerSQL Server, this dynamic management view also returns information about the data pages in the buffer pool extension file. 有关详细信息,请参阅 缓冲池扩展For more information, see Buffer Pool Extension.

当从磁盘读取数据页时,该数据页被复制到 SQL ServerSQL Server 缓冲池并被缓存以供重复使用。When a data page is read from disk, the page is copied into the SQL ServerSQL Server buffer pool and cached for reuse. 每个缓存的数据页都有一个缓冲描述符。Each cached data page has one buffer descriptor. 缓冲描述符唯一地标识 SQL ServerSQL Server 实例中当前缓存的每个数据页。Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL ServerSQL Server. sys.dm_os_buffer_descriptors 返回所有用户数据库和系统数据库的缓存页。sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. 这包括与 Resource 数据库相关联的页。This includes pages that are associated with the Resource database.

注意: 若要从或调用此 Azure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse ,请使用名称 sys.dm_pdw_nodes_os_buffer_descriptorsNOTE: To call this from Azure Synapse AnalyticsAzure Synapse Analytics or 并行数据仓库Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_buffer_descriptors.

列名称Column name 数据类型Data type 说明Description
database_iddatabase_id intint 与缓冲池中的页关联的数据库 ID。ID of database associated with the page in the buffer pool. 可以为 Null。Is nullable.
file_idfile_id intint 存储页的持久化图像的文件 ID。ID of the file that stores the persisted image of the page. 可以为 Null。Is nullable.
page_idpage_id intint 文件中页面的 ID。ID of the page within the file. 可以为 Null。Is nullable.
page_levelpage_level intint 页的索引级别。Index level of the page. 可以为 Null。Is nullable.
allocation_unit_idallocation_unit_id bigintbigint 页的分配单元的 ID。ID of the allocation unit of the page. 可使用此值联接 sys.allocation_units。This value can be used to join sys.allocation_units. 可以为 Null。Is nullable.
page_typepage_type nvarchar(60)nvarchar(60) 页类型,例如数据页或索引页。Type of the page, such as: Data page or Index page. 可以为 Null。Is nullable.
row_countrow_count intint 页中的行数。Number of rows on the page. 可以为 Null。Is nullable.
free_space_in_bytesfree_space_in_bytes intint 页中的可用空间(字节)。Amount of available free space, in bytes, on the page. 可以为 Null。Is nullable.
is_modifiedis_modified bitbit 1 = 从磁盘读取页后已对其进行修改。1 = Page has been modified after it was read from the disk. 可以为 Null。Is nullable.
numa_nodenuma_node intint 缓冲区的非一致性内存访问节点。Nonuniform Memory Access node for the buffer. 可以为 Null。Is nullable.
read_microsecread_microsec bigintbigint 将此页读入缓冲区所需的实际时间(微秒)。The actual time (in microseconds) required to read the page into the buffer. 重用缓冲区时重置该数值。This number is reset when the buffer is reused. 可以为 Null。Is nullable.
is_in_bpool_extensionis_in_bpool_extension bitbit 1 = 页在缓冲池扩展中。1 = Page is in buffer pool extension. 可以为 Null。Is nullable.
pdw_node_idpdw_node_id intint 适用 于: Azure Synapse AnalyticsAzure Synapse Analytics并行数据仓库Parallel Data WarehouseApplies to: Azure Synapse AnalyticsAzure Synapse Analytics, 并行数据仓库Parallel Data Warehouse

此分发所在的节点的标识符。The identifier for the node that this distribution is on.

权限Permissions

在上 SQL ServerSQL Server ,需要 VIEW SERVER STATE 权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
在 SQL 数据库的基本、S0 和 S1 服务目标上,对于弹性池中的数据库, 服务器管理员 帐户或 Azure Active Directory 管理员 帐户是必需的。On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. 对于所有其他 SQL 数据库服务目标, VIEW DATABASE STATE 数据库中需要该权限。On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.

注解Remarks

sys.dm_os_buffer_descriptors 返回资源数据库正在使用的页。sys.dm_os_buffer_descriptors returns pages that are being used by the Resource database. sys.dm_os_buffer_descriptors 不会返回有关免费或被盗页面的信息,也不会返回有关在读取时出错的页的信息。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_descriptorssys.dm_os_buffer_descriptors sys.databasessys.databases database_iddatabase_id 多对一many-to-one
sys.dm_os_buffer_descriptorssys.dm_os_buffer_descriptors <userdb>.sys.allocation_units<userdb>.sys.allocation_units allocation_unit_idallocation_unit_id 多对一many-to-one
sys.dm_os_buffer_descriptorssys.dm_os_buffer_descriptors <userdb>.sys.database_files<userdb>.sys.database_files file_idfile_id 多对一many-to-one
sys.dm_os_buffer_descriptorssys.dm_os_buffer_descriptors sys.dm_os_buffer_pool_extension_configurationsys.dm_os_buffer_pool_extension_configuration file_idfile_id 多对一many-to-one

示例Examples

A.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.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)sys.allocation_units (Transact-SQL)

(Transact-sql 的与操作系统相关的动态管理视图 SQL Server) SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
Resource 数据库 Resource Database
sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)