Supprimer les statistiques en doublon

Lorsque vous créez un index sur une colonne, les statistiques auto-créées s’il en existait ne sont pas supprimées. Cela va donc inutilement allonger les temps de traitement des reconstructions de statistiques.

Ce script identifie les statistiques auto-créées en doublon : à supprimer car leur liste de colonnes est identique à celle d’une autre statistique. La commande DROP STATISTICS est générée.

/*-------------------------------------------------------------------
[SCRIPT]  Duplicated statistics
[DATABASE] current
[DESCRIPTION] identity auto created stats which have also an index on the same column(s)
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 20200318
-------------------------------------------------------------------*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET LOCK_TIMEOUT 10000;
IF OBJECT_ID('tempdb..#all_stats') IS NOT NULL
    DROP TABLE #all_stats;

SELECT s.object_id,
       s.name AS stats_name,
       s.stats_id,
       s.auto_created,
       s.has_filter,
       s.filter_definition,
       tc.table_rows,
       (
           SELECT TOP (1)
                  c.name
           FROM sys.stats_columns AS sc
               LEFT JOIN(sys.indexes i
               JOIN sys.index_columns AS ic
                   ON ic.object_id = i.object_id
                      AND i.index_id = ic.index_id)
                   ON i.object_id = sc.object_id
                      AND i.name = s.name
                      AND ic.column_id = sc.column_id
               JOIN sys.columns AS c
                   ON c.object_id = sc.object_id
                      AND c.column_id = sc.column_id
           WHERE s.stats_id = sc.stats_id
                 AND s.object_id = sc.object_id
           ORDER BY CASE
                        WHEN s.auto_created = 1
                             OR s.user_created = 1 THEN
                            sc.stats_column_id
                        ELSE
                            ic.key_ordinal
                    END
       ) AS stats_first_column,
       STUFF(
       (
           SELECT ', ' + c.name AS [text()]
           FROM sys.stats_columns AS sc
               LEFT JOIN(sys.indexes i
               JOIN sys.index_columns AS ic
                   ON ic.object_id = i.object_id
                      AND i.index_id = ic.index_id)
                   ON i.object_id = sc.object_id
                      AND i.name = s.name
                      AND ic.column_id = sc.column_id
               JOIN sys.columns AS c
                   ON c.object_id = sc.object_id
                      AND c.column_id = sc.column_id
           WHERE s.stats_id = sc.stats_id
                 AND s.object_id = sc.object_id
           ORDER BY CASE
                        WHEN s.auto_created = 1
                             OR s.user_created = 1 THEN
                            sc.stats_column_id
                        ELSE
                            ic.key_ordinal
                    END
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) AS stats_column_list
INTO #all_stats
FROM sys.stats AS s
    JOIN sys.objects o
        ON s.object_id = o.object_id
    JOIN
    (
        SELECT t.object_id,
               SUM(p.rows) AS table_rows
        FROM sys.objects t
            JOIN sys.indexes i
                ON t.object_id = i.object_id
            JOIN sys.partitions p
                ON i.object_id = p.object_id
                   AND i.index_id = p.index_id
        WHERE t.type = 'U'
              AND i.index_id < 2
        GROUP BY t.object_id
    ) AS tc
        ON o.object_id = tc.object_id
    CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
      AND OBJECTPROPERTY(s.object_id, 'IsMSShipped') = 0
      AND OBJECT_NAME(s.object_id) <> 'sysdiagrams'; -- seulement tables utilisateur


SELECT DISTINCT
    --	        ROW_NUMBER() OVER ( PARTITION BY s1.object_id, s1.column_name ORDER BY s1.column_name ) AS stats_no
       OBJECT_SCHEMA_NAME(s1.object_id) AS schema_name,
       OBJECT_NAME(s1.object_id) AS table_name,
       s1.table_rows,
       s1.stats_name AS stats_name,
       s1.stats_column_list,
       s2.stats_name AS identical_stats_name,
       s2.stats_column_list,
       s2.filter_definition,
       'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(s1.object_id) + '].[' + OBJECT_NAME(s1.object_id) + '].[' + s1.stats_name
       + ']; ' AS DropCommand
FROM #all_stats AS s1
    INNER JOIN #all_stats AS s2
        ON (
               s1.object_id = s2.object_id
               AND s1.stats_id != s2.stats_id
           ) -- same object
           AND s1.auto_created = 1 -- drop only auto created stats
           AND s1.stats_first_column = s2.stats_first_column -- same first column
           AND s1.stats_column_list = s2.stats_column_list -- same column list
           AND ISNULL(s1.filter_definition, '') = ISNULL(s2.filter_definition, ''); -- same filter

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

/*-------------------------------------------------------------------

[SCRIPT] Duplicated statistics

[DATABASE] current

[DESCRIPTION] identity auto created stats which have also an index on the same column(s)

[MAJ PAR] DATAFLY - Arian Papillon

[DATEMAJ] 20200318

-------------------------------------------------------------------*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET LOCK_TIMEOUT 10000;

IF OBJECT_ID('tempdb..#all_stats') IS NOT NULL

DROP TABLE #all_stats;

SELECT s.object_id,

s.name AS stats_name,

s.stats_id,

s.auto_created,

s.has_filter,

s.filter_definition,

tc.table_rows,

(

SELECT TOP (1)

c.name

FROM sys.stats_columns AS sc

LEFT JOIN(sys.indexes i

JOIN sys.index_columns AS ic

ON ic.object_id = i.object_id

AND i.index_id = ic.index_id)

ON i.object_id = sc.object_id

AND i.name = s.name

AND ic.column_id = sc.column_id

JOIN sys.columns AS c

ON c.object_id = sc.object_id

AND c.column_id = sc.column_id

WHERE s.stats_id = sc.stats_id

AND s.object_id = sc.object_id

ORDER BY CASE

WHEN s.auto_created = 1

OR s.user_created = 1 THEN

sc.stats_column_id

ELSE

ic.key_ordinal

END

) AS stats_first_column,

STUFF(

(

SELECT ', ' + c.name AS [text()]

FROM sys.stats_columns AS sc

LEFT JOIN(sys.indexes i

JOIN sys.index_columns AS ic

ON ic.object_id = i.object_id

AND i.index_id = ic.index_id)

ON i.object_id = sc.object_id

AND i.name = s.name

AND ic.column_id = sc.column_id

JOIN sys.columns AS c

ON c.object_id = sc.object_id

AND c.column_id = sc.column_id

WHERE s.stats_id = sc.stats_id

AND s.object_id = sc.object_id

ORDER BY CASE

WHEN s.auto_created = 1

OR s.user_created = 1 THEN

sc.stats_column_id

ELSE

ic.key_ordinal

END

FOR XML PATH('')

) AS stats_column_list

INTO #all_stats

FROM sys.stats AS s

JOIN sys.objects o

ON s.object_id = o.object_id

JOIN

(

SELECT t.object_id,

SUM(p.rows) AS table_rows

FROM sys.objects t

JOIN sys.indexes i

ON t.object_id = i.object_id

JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

WHERE t.type = 'U'

AND i.index_id < 2

GROUP BY t.object_id

) AS tc

ON o.object_id = tc.object_id

CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp

WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1

AND OBJECTPROPERTY(s.object_id, 'IsMSShipped') = 0

AND OBJECT_NAME(s.object_id) <> 'sysdiagrams'; -- seulement tables utilisateur

SELECT DISTINCT

-- ROW_NUMBER() OVER ( PARTITION BY s1.object_id, s1.column_name ORDER BY s1.column_name ) AS stats_no

OBJECT_SCHEMA_NAME(s1.object_id) AS schema_name,

OBJECT_NAME(s1.object_id) AS table_name,

s1.table_rows,

s1.stats_name AS stats_name,

s1.stats_column_list,

s2.stats_name AS identical_stats_name,

s2.stats_column_list,

s2.filter_definition,

'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(s1.object_id) + '].[' + OBJECT_NAME(s1.object_id) + '].[' + s1.stats_name

+ ']; ' AS DropCommand

FROM #all_stats AS s1

INNER JOIN #all_stats AS s2

ON (

s1.object_id = s2.object_id

AND s1.stats_id != s2.stats_id

) -- same object

AND s1.auto_created = 1 -- drop only auto created stats

AND s1.stats_first_column = s2.stats_first_column -- same first column

AND s1.stats_column_list = s2.stats_column_list -- same column list

AND ISNULL(s1.filter_definition, '') = ISNULL(s2.filter_definition, ''); -- same filter