sys.dm_os_buffer_descriptors (Transact-SQL)

Article
12/19/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Retourne des informations sur toutes les pages de données qui se trouvent actuellement dans le pool de mémoires tampons SQL Server. Le résultat de cet affichage peut être utilisé pour déterminer la répartition des pages de base de données dans le pool de mémoires tampons par base de données, objet ou type. Dans SQL Server, cette vue de gestion dynamique retourne également des informations sur les pages de données du fichier d’extension du pool de mémoires tampons. Pour plus d’informations, consultez l’extension du pool de mémoires tampons.

Lorsqu’une page de données est lue à partir du disque, la page est copiée dans le pool de mémoires tampons SQL Server et mise en cache pour réutilisation. Chaque page de données mise en cache est associée à un descripteur de mémoire tampon. Les descripteurs de mémoire tampon identifient de manière unique chaque page de données actuellement mise en cache dans une instance de SQL Server. sys.dm_os_buffer_descriptors retourne les pages mises en cache pour toutes les bases de données utilisateur et système. Cela inclut les pages qui sont associées à la base de données Resource.

Remarque

Pour l’appeler à partir d’Azure Synapse Analytics ou d’Analytics Platform System (PDW), utilisez le nom sys.dm_pdw_nodes_os_buffer_descriptors. Cette syntaxe n’est pas prise en charge par le pool SQL serverless dans Azure Synapse Analytics.

Nom de la colonne	Type de données	Description
database_id	int	Identificateur de la base de données associée à la page dans le pool de mémoires tampons. Autorise la valeur NULL. Dans Azure SQL Database, les valeurs sont uniques au sein d’une base de données unique ou d’un pool élastique, mais pas dans un serveur logique.
file_id	int	Identificateur du fichier qui contient l'image persistante de la page. Autorise la valeur NULL.
page_id	int	Identificateur de la page dans le fichier. Autorise la valeur NULL.
page_level	int	Niveau d'index de la page. Autorise la valeur NULL.
allocation_unit_id	bigint	Identificateur de l'unité d'allocation de la page. Cette valeur peut être utilisée pour la jointure de sys.allocation_units. Autorise la valeur NULL.
page_type	nvarchar(60)	Type de la page, par exemple page de données ou page d'index. Autorise la valeur NULL.
row_count	int	Nombre de lignes dans la page. Autorise la valeur NULL.
free_space_in_bytes	int	Quantité d'espace disponible dans la page, en octets. Autorise la valeur NULL.
is_modified	bit	1 = la page a été modifiée après avoir été lue sur le disque. Autorise la valeur NULL.
numa_node	int	Nœud NUMA (Nonuniform Memory Access) pour la mémoire tampon. Autorise la valeur NULL.
read_microsec	bigint	Temps réel (en microsecondes) requis pour lire la page dans la mémoire tampon. Ce nombre est réinitialisé lorsque la mémoire tampon est réutilisée. Autorise la valeur NULL.
is_in_bpool_extension	bit	1 = La page est dans l’extension du pool de mémoires tampons. Autorise la valeur NULL.
pdw_node_id	int	S’applique à : Azure Synapse Analytics, Analytics Platform System (PDW) Identificateur du nœud sur lequel cette distribution est activée.

Autorisations

Sur SQL Server et SQL Managed Instance, l’autorisation VIEW SERVER STATE est requise.

Sur les objectifs de service SQL Database Basic, S0 et S1, et pour les bases de données dans des pools élastiques, le compte d’administrateur du serveur, le compte d’administrateur Microsoft Entra ou l’appartenance au ##MS_ServerStateReader##rôle serveur est requis. Sur tous les autres objectifs de service SQL Database, l’autorisation VIEW DATABASE STATE sur la base de données ou l’appartenance au rôle serveur ##MS_ServerStateReader## est requise.

Autorisations pour SQL Server 2022 (et versions plus récentes)

Nécessite l’autorisation VIEW SERVER PERFORMANCE STATE sur le serveur.

Notes

sys.dm_os_buffer_descriptors retourne des pages utilisées par la base de données Resource. sys.dm_os_buffer_descriptors ne retourne pas d’informations sur les pages gratuites ou volées, ou sur les pages ayant des erreurs lorsqu’elles ont été lues.

Du	À	Activé	Relationship
sys.dm_os_buffer_descriptors	sys.databases	database_id	plusieurs-à-un
sys.dm_os_buffer_descriptors	<userdb>.sys.allocation_units	allocation_unit_id	plusieurs-à-un
sys.dm_os_buffer_descriptors	<userdb>.sys.database_files	file_id	plusieurs-à-un
sys.dm_os_buffer_descriptors	sys.dm_os_buffer_pool_extension_configuration	file_id	plusieurs-à-un

Exemples

R. Retour du nombre de pages mises en cache pour chaque base de données

L'exemple suivant retourne le nombre de pages chargées pour chaque base de données.

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. Renvoi du nombre de pages mises en cache pour chaque objet de la base de données active

L'exemple suivant retourne le nombre de pages chargées pour chaque objet de la base de données active.

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;

Voir aussi

sys.allocation_units (Transact-SQL)

Vues de gestion dynamique SQL Server liées au système d'exploitation (Transact-SQL)
Base de données Resource
sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)