SQL – Etienne ZINZINDOHOUE

Un article de zinzineti    Pas de commentaires

Quand on parle de statistiques pour un serveur de base de données, il faut distinguer :
1 – les statistiques d’activités du SGBD : les indicateurs (compteurs) de performances collectés par le SGBD
2 – les statistiques d’optimisation de requêtes; ces statistiques sont créées/utilisées par l’optimiseur de requête pour estimer le nombre de lignes (cardinalité) renvoyées par une requête. Ces estimations de cardinalité permettent à l’optimiseur de requête de créer un plan de requête de haute qualité pour une réponse à la requête formulée.

Ce sont ces statistiques d’optimisation de requête que nous allons découvrir dans ce billet.

Les statistiques utilisées par le moteur SQL dans le cadre de l’optimisation de requête sont des objets qui contiennent
des informations statistiques sur la distribution des valeurs dans une ou plusieurs colonnes d’une table ou d’une vue indexée.
Le moteur de base de données stocke des statistiques pour une table ou vue indexée dans un objet des statistiques.
L’objet des statistiques est créé sur un index ou sur des colonnes non indexées d’une table.

Les statistiques sur les index sont créées automatiquement tandis que celles sur les colonnes non indexées peuvent être créées manuellement ou automatiquement.

Création (mise à jour) automatique des statistiques
====================================================================
Pour avoir les statistiques à jour il suffit de positionner l’option AUTO UPDATE STATISTICS à TRUE pour la base de données.

# L’optimiseur de requête crée des statistiques pour les index de tables ou de vues lors de la création des index. Ces statistiques sont créées sur les colonnes de clés de l’index

# L’optimiseur de requête crée des statistiques pour les colonnes uniques des prédicats de requête lorsque l’option AUTO_CREATE_STATISTICS est activée

Pour obtenir les colonnes relatives aux statistiques créées automatiquement :

SELECT OBJECT_NAME(s.object_id) AS [Nom_Table]
    ,COL_NAME(sc.object_id, sc.column_id) AS [Nom_Colonne]
    ,s.name AS [Nom_Statistique]
    , STATS_DATE (s.object_id,s.stats_id)   AS [Date MAJ des statistiques]
FROM sys.stats s INNER JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1  
--AND s.name LIKE '_WA%' -- Statistique automatique  
AND s.auto_created = 1   -- Statistique automatique  
ORDER BY [Nom_Table];

Remarque :
==========

Pour le filtre j’avais utilisé s.name LIKE ‘_WA%’ Et SQLPro suggère d’utiliser la clause s.auto_created = 1 ce qui est plus précis. Merci SQLPro

Visualiser les statistiques
====================================================================

Les statistiques sont stockées dans la table système sysindexes.

SELECT *
FROM sys.sysindexes

Cette requête ne permet ni une analyse fine ni une interprétation des valeurs affichées.
Pour une analyse des statistiques, il faut utiliser DBCC SHOW_STATISTICS.

DBCC SHOW_STATISTICS permet d’obtenir les statistics des index et des colonnes non indexées pour une table ou une vue indexée

–> Statistiques pour une colonne indexée

DBCC SHOW_STATISTICS ('dbo.T_TEST',IXNC_val_INCLUDE_id_creationdate)

–> Statistiques pour une colonne non indexée

DBCC SHOW_STATISTICS ('dbo.T_TEST',id)

–> Pour obtenir uniquement l’entête

DBCC SHOW_STATISTICS ('dbo.T_TEST',IXNC_val_INCLUDE_id_creationdate)
WITH STAT_HEADER

–> Pour obtenir uniquement la densité

DBCC SHOW_STATISTICS ('dbo.T_TEST',IXNC_val_INCLUDE_id_creationdate)
WITH DENSITY_VECTOR

–>Pour obtenir uniquement l’histogramme

DBCC SHOW_STATISTICS ('dbo.T_TEST',IXNC_val_INCLUDE_id_creationdate)
WITH HISTOGRAM

Densité-Sélectivité
====================================================================
La notion de densité et de sélectivité sont imortante pour mieux interpreter les résultats affichés par DBCC SHOW_STATISTICS

Pour une colonne, si la valeur de la densité est élévéee celà veut dire qu’il existe beaucoup d’occurences de valeur identique pour colonne.
Plus la densité est élévée, moins la colonne est sélective (sélectivité faible).

Dans quels cas peut-on être amené à créer manuellement des statistiques ?
====================================================================

On peut envisagez de créer des statistiques avec l’instruction CREATE STATISTICS dans l’un des cas suivants :

1.) l’Assistant Paramétrage du moteur de base de données suggère de créer des statistiques ;

2.) le prédicat de requête contient plusieurs colonnes corrélées qui ne figurent pas déjà dans le même index ;

3.) la requête effectue une sélection dans un sous-ensemble de données ;

4.) il manque des statistiques pour la requête.

Il existe plusieurs options de création de statistiques via l’instruction CREATE STATISTICS

–>Utilisation de CREATE STATISTICS avec SAMPLE number PERCENT
Créer les statistiques mySTAT_PERCENT_ID, à l’aide d’un exemple aléatoire de 5% des colonnes ID de la table T_TEST

CREATE STATISTICS STAT_ID
ON dbo.T_TEST (ID)
WITH SAMPLE 5 PERCENT;

–>Utilisation de CREATE STATISTICS avec FULLSCAN et NORECOMPUTE

Créer les statistiques mySTAT_FULLSCAN_ID, pour toutes les lignes des colonnes ID de la table T_TEST

CREATE STATISTICS mySTAT_FULLSCAN_NORECOMPUTE_ID
ON dbo.T_TEST (ID)
WITH FULLSCAN;

–>Utilisation de CREATE STATISTICS pour créer des statistiques filtrées
Créer les statistiques mySTAT_FILTRE_ID, pour toutes les lignes des colonnes ID de la table T_TEST

CREATE STATISTICS mySTAT_FULLSCAN_NORECOMPUTE_ID
ON dbo.T_TEST (ID)
WHERE ID >= 100 AND ID <= 1000;

Une discussion portant sur le sujet

Un article de Rudi Bruchez sur le sujet

Bonne lecture

—————————————————————-
Auteur : Etienne ZINZINDOHOUE