sys.dm_db_stats_histogram (Transact-SQL)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures d’Azure SQL DatabaseAzure 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.

Syntaxe

sys.dm_db_stats_histogram (object_id , stats_id)

Arguments

object_id

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

stats_id

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

Table retournée

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 ensemble échantillonné de lignes, les totaux stockés pour le nombre de lignes et le nombre de valeurs distinctes sont des estimations et n’ont pas besoin d’être entiers 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.

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 échantilloné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

Nécessite que l’utilisateur dispose d’autorisations de sélection sur les colonnes de statistiques ou que l’utilisateur possède la table ou que l’utilisateur soit 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 de base

L’exemple suivant crée et remplit une table de base. Crée ensuite des statistiques sur la colonne Region_Name.

CREATE TABLE Region
(
    Region_ID INT IDENTITY PRIMARY KEY,
    Region_Name VARCHAR (120) NOT NULL
);

INSERT Region (Region_Name)
VALUES ('Canada'), ('Denmark'), ('Iceland'), ('Peru');

CREATE STATISTICS Region_Stats ON Region(Region_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 Region.

SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('Region'), 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 Region avec un prédicat sur la colonne Region_Name.

SELECT * FROM Region
WHERE Region_Name = 'Canada';

L’exemple suivant examine la statistique créée précédemment sur la table Region et la colonne Region_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 AS ss
     INNER JOIN sys.stats_columns AS sc
         ON ss.stats_id = sc.stats_id
        AND ss.object_id = sc.object_id
     INNER JOIN sys.all_columns AS 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) AS shr
CROSS APPLY sys.dm_db_stats_histogram(ss.object_id, ss.stats_id) AS sh
WHERE ss.[object_id] = OBJECT_ID('Region')
      AND ac.name = 'Region_Name'
      AND sh.range_high_key = CAST ('Canada' AS CHAR (8));

Contenu connexe

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