sys.dm_db_stats_histogram (Transact-SQL)

Article
07/15/2024

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance

Renvoie l’histogramme des statistiques pour l’objet de base de données spécifié (table ou vue indexée) dans la base de données SQL Server actuelle. Semblable à DBCC SHOW_STATISTICS WITH HISTOGRAM.

Remarque

Ce DMF est disponible à partir de SQL Server 2016 (13.x) SP1 CU2

Syntaxe

sys.dm_db_stats_histogram (object_id, stats_id)

Arguments

object_id
ID de l'objet dans la base de données active dont les propriétés d'une de ses statistiques sont demandées. l’object_id est int.

stats_id
ID des statistiques pour l’object_id. spécifié. L’ID des statistiques peut être obtenu à partir de la vue de gestion dynamique sys.stats . stats_id correspond à int.

Table retournée

Nom de la colonne	Type de données	Description
object_id	int	ID de l'objet (table ou vue indexée) pour lequel retourner les propriétés de l'objet de statistiques.
stats_id	int	ID de l'objet de statistiques. Unique dans la table ou la vue indexée. Pour plus d’informations, consultez sys.stats (Transact-SQL).
step_number	int	Le nombre d'étape dans l'histogramme.
range_high_key	sql_variant	Valeur de colonne de limite supérieure pour une étape d'histogramme. La valeur de colonne est également appelée « valeur de clé ».
range_rows	real	Nombre estimé de lignes dont la valeur de colonne est comprise dans une étape d'histogramme, à l'exception de la limite supérieure.
equal_rows	real	Nombre estimé de lignes dont la valeur de colonne est égale à la limite supérieure de l'étape d'histogramme.
distinct_range_rows	bigint	Nombre estimé de lignes ayant une valeur de colonne distincte dans une étape d'histogramme, à l'exception de la limite supérieure.
average_range_rows	real	Nombre moyen de lignes avec des valeurs de colonne en double dans une étape d’histogramme, à l’exclusion de la limite supérieure (`RANGE_ROWS / DISTINCT_RANGE_ROWS` pour `DISTINCT_RANGE_ROWS > 0`).

Notes

L’ensemble de résultats pour sys.dm_db_stats_histogram renvoie des informations similaires à DBCC SHOW_STATISTICS WITH HISTOGRAM et inclut également object_id, stats_id et step_number.

Étant donné que la colonne range_high_key est un type de données sql_variant, vous devrez peut-être utiliser CAST ou CONVERT si un prédicat effectue une comparaison avec une constante non-chaîne.

Histogramme

Un histogramme mesure la fréquence des occurrences de chaque valeur distincte dans un jeu de données. L'optimiseur de requête calcule un histogramme sur les valeurs de colonnes de la première colonne clé de l'objet de statistiques, en sélectionnant les valeurs de colonnes au moyen d'un échantillonnage statistique des lignes ou d'une analyse complète de toutes les lignes dans la table ou la vue. Si l'histogramme est créé à partir d'un jeu de lignes échantillonnées, les totaux stockés pour le nombre de lignes et le nombre de valeurs distinctes sont des estimations et ne doivent pas nécessairement être des nombres entiers.

Pour créer l'histogramme, l'optimiseur de requête trie les valeurs de colonnes, calcule le nombre de valeurs qui correspondent à chaque valeur de colonne distincte, puis regroupe les valeurs de colonnes dans 200 étapes d'histogramme contiguës au maximum. Chaque étape inclut une plage de valeurs de colonnes suivie d'une valeur de colonne de limite supérieure. La plage comprend toutes les valeurs de colonnes possibles entre des valeurs limites, à l'exception des valeurs limites elles-mêmes. La plus basse des valeurs de colonnes triées est la valeur de limite supérieure pour la première étape d'histogramme.

Le diagramme suivant illustre un histogramme avec six étapes : La zone située à gauche de la première valeur limite supérieure représente la première étape.

Image montrant comment un histogramme est calculé à partir de valeurs de colonnes échantillonnées.

Pour chaque étape d'histogramme :

La ligne en gras représente la valeur limite supérieure (range_high_key) et le nombre d’occurrences (equal_rows) correspondant.
La zone pleine située à gauche de range_high_key représente la plage de valeurs de colonnes et le nombre moyen d’occurrences de chacune des valeurs de colonnes (average_range_rows). Pour la première étape de l’histogramme, la valeur de average_range_rows est toujours égale à 0.
Les lignes pointillées représentent les valeurs échantillonnées utilisées pour estimer le nombre total de valeurs distinctes dans la plage (distinct_range_rows) et le nombre total de valeurs dans la plage (range_rows). L’optimiseur de requête utilise range_rows et distinct_range_rows pour calculer average_range_rows, et ne stocke pas les valeurs échantillonnées.

L'optimiseur de requête définit les étapes d'histogramme en fonction de leur importance statistique. Il utilise un algorithme de nombre maximal de différences pour réduire le nombre d'étapes dans l'histogramme tout en augmentant la différence entre les valeurs limites. Le nombre maximal d'étapes est 200. Le nombre d'étapes d'histogramme peut être inférieur au nombre de valeurs distinctes, même pour les colonnes comportant moins de 200 points de limite. Par exemple, une colonne avec 100 valeurs distinctes peut avoir un histogramme comportant moins de 100 points de limite.

autorisations

L'utilisateur doit avoir sélectionné des autorisations sur les colonnes de statistiques, ou bien il doit être le propriétaire de la table, ou encore il doit être membre du rôle serveur fixe sysadmin, du rôle de base de données fixe db_owner ou du rôle de base de données fixe db_ddladmin.

Exemples

R. Exemple simple

L’exemple suivant crée et remplit un tableau simple. Crée ensuite des statistiques sur la colonne Country_Name.

CREATE TABLE Country
(Country_ID int IDENTITY PRIMARY KEY,
Country_Name varchar(120) NOT NULL);
INSERT Country (Country_Name) VALUES ('Canada'), ('Denmark'), ('Iceland'), ('Peru');

CREATE STATISTICS Country_Stats  
    ON Country (Country_Name) ;

La clé primaire occupe stat_id le numéro 1, donc appelez sys.dm_db_stats_histogram le stat_id numéro 2 pour renvoyer l’histogramme des statistiques pour la table Country.

SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('Country'), 2);

B. Requête utile :

SELECT hist.step_number, hist.range_high_key, hist.range_rows, 
    hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'<statistic_name>';

C. Requête utile :

L’exemple suivant sélectionne dans une table Country avec un prédicat sur la colonne Country_Name.

SELECT * FROM Country 
WHERE Country_Name = 'Canada';

L’exemple suivant examine la statistique créée précédemment sur la table Country et la colonne Country_Name pour l’étape d’histogramme correspondant au prédicat dans la requête ci-dessus.

SELECT ss.name, ss.stats_id, shr.steps, shr.rows, shr.rows_sampled, 
    shr.modification_counter, shr.last_updated, sh.range_rows, sh.equal_rows
FROM sys.stats ss
INNER JOIN sys.stats_columns sc 
    ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac 
    ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
CROSS APPLY sys.dm_db_stats_histogram(ss.object_id, ss.stats_id) sh
WHERE ss.[object_id] = OBJECT_ID('Country') 
    AND ac.name = 'Country_Name'
    AND sh.range_high_key = CAST('Canada' AS CHAR(8));

Étapes suivantes

DBCC SHOW_STATISTICS (Transact-SQL)
Vues et fonctions de gestion dynamique relatives aux objets (Transact-SQL)
sys.dm_db_stats_properties (Transact-SQL)

Partage via