Détecter les statistiques redondantes

Laisser une réponse

Il est conseillé de ne jamais avoir de statistiques redondantes dans une base de données. Il est hélas assez facile d’en avoir sans s’en rendre compte. La requête présentée ici vous permet de les détecter et de les éradiquer…

Voici un exemple simple qui vous montre comment obtenir des statistiques redondantes :

1 Рcr̩ation de la base

CREATE DATABASE DB_TEST_STATS;
GO

ALTER DATABASE DB_TEST_STATS
   SET AUTO_CREATE_STATISTICS ON;
GO

USE DB_TEST_STATS;
GO

CREATE TABLE T (C1 INT, C2 UNIQUEIDENTIFIER, C3 DATE)
GO

INSERT INTO T
SELECT FLOOR(RAND() * 123456), NEWID(), DATEADD(SECOND, RAND() * -100000000, GETDATE())
GO 1000

La base DB_TEST_STATS comporte une table de nom T avec 3 colonne. La dernière commande à remplie cette table avec 1000 lignes de manière aléatoire. À ce stade, aucune statistique n’existe sur cette table :

SELECT *
FROM   sys.stats
WHERE  object_id = OBJECT_ID('dbo.T');

2 Рg̩n̩ration automatique des statistiques

En lançant différentes requêtes, nous allons maintenant avoir différents statistiques créées :

SELECT *
FROM   T
WHERE  C1 = 123

SELECT *
FROM   T
WHERE  C2 > 'F840197D-A7EE-41EF-84E6-023286970753'

SELECT *
FROM   T
WHERE  C3  '1990-12-31'

En relançant la requête précédente :

SELECT *
FROM   sys.stats
WHERE  object_id = OBJECT_ID('dbo.T');

Nous voyons maintenant apparaître 3 statistiques pour cette table (attention les valeurs de la colonne « name » peuvent différer).

object_id   name                               stats_id    auto_created
----------- ---------------------------------- ----------- ------------
245575913   _WA_Sys_00000001_0EA330E9          2           1          
245575913   _WA_Sys_00000002_0EA330E9          3           1          
245575913   _WA_Sys_00000003_0EA330E9          4           1

Ces statistiques sont des statistiques de colonne.

3 – ajout d’index=> statistiques redondantes

Si nous ajoutons un index sur une des colonnes, par exemple la colonne C3 :

CREATE INDEX X ON T (C3);

Nous voyons apparaître une nouvelle statistique dont le nom est le même que l’index.

object_id   name                               stats_id    auto_created
----------- ---------------------------------- ----------- ------------
245575913   _WA_Sys_00000001_0EA330E9          2           1          
245575913   _WA_Sys_00000002_0EA330E9          3           1          
245575913   _WA_Sys_00000003_0EA330E9          4           1            
261575970   X                                  5           0

Néanmoins, deux de ces statistiques portent sur la même colonnes…
Est-il bien raisonnable de maintenir ces deux statistiques ? N’oubliez pas que la maintenance des statistiques a un coût….

Le même problème peut survenir si vous créez des index inclus. Mais vous ne pourrez pas supprimer ces statistiques car les statistiques d’index ne sont pas supprimables…

4 Рla requ̻te de d̩tection des statistiques redondantes

WITH T0 AS
(
SELECT s.object_id, s.name AS STAT_NAME, i.name AS INDEX_NAME,
       CASE WHEN i.is_primary_key = 1 THEN 'PRIMAY KEY'
            WHEN i.is_unique_constraint = 1 THEN 'UNIQUE'
       END AS CONSTRAINT_TYPE,
       i.has_filter AS HAS_FILTER,
       (SELECT TOP 1 c.name
        FROM   sys.stats_columns AS sc
               JOIN sys.COLUMNS AS c
                    ON sc.object_id = c.object_id
                    AND sc.column_id = c.column_id
        WHERE  s.object_id = sc.object_id  
          AND  s.stats_id = sc.stats_id
        ORDER BY sc.stats_column_id) AS STAT_COLUMN,
       STUFF((SELECT ', ' + c.name
              FROM   sys.stats_columns AS sc
                     JOIN sys.COLUMNS AS c
                          ON sc.object_id = c.object_id
                          AND sc.column_id = c.column_id
              WHERE  s.object_id = sc.object_id  
                AND  s.stats_id = sc.stats_id
              ORDER BY sc.stats_column_id
              FOR XML PATH('')), 1, 1, '') AS KEY_COLS  
FROM   sys.stats AS s
       LEFT OUTER JOIN sys.indexes AS i
            ON s.object_id = i.object_id
            AND s.name = i.name
)
SELECT A.*, B.STAT_NAME AS STAT_NAME2, B.INDEX_NAME AS INDEX_NAME2,
       B.CONSTRAINT_TYPE AS CONSTRAINT_TYPE2, B.HAS_FILTER AS HAS_FILTER2,
       B.KEY_COLS AS KEY_COLS2,
       N'DROP STATISTICS [' + s.name + N'].[' + o.name + N'].[' +
                         B.STAT_NAME + N'];' AS DROP_STAT
FROM   T0 AS A
       JOIN T0 AS B
            ON  A.STAT_COLUMN = B.STAT_COLUMN
            AND A.STAT_NAME  B.STAT_NAME
            AND A.object_id = B.object_id
       JOIN sys.objects AS o
            ON A.object_id = o.object_id
       JOIN sys.schemas AS s
            ON o.schema_id = s.schema_id                
WHERE  B.INDEX_NAME IS NULL
AND    o."type" IN ('V', 'U');

NOTA : l’éditeur de developpez.com ayant quelques défauts, si cette requête ne passe pas, utilisez le fichier joint :
Statistiques inutiles qui est en fait un fichier txt en non PDF !

À vous maintenant de décider !

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
Développez et administrez pour la performance avec SQL Server 2014

Développez et administrez pour la performance avec SQL Server 2014

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL Server

Laisser un commentaire