Query para localizar índices redundantes...

Devido a interferência de vários analistas no banco de dados, vários índices estavam redundantes e verifiquei a necessidade de limpar os índices que estavam “sobrando”. Para esta tarefa, criei esta query usando uma CTE e a função INDEX_COL  para determinar os campos indexados. Para esta query, utilizei uma outra query que encontrei neste link: http://www.sql-server-performance.com/2005/finding-duplicate-indexes/

 

WITH QUERY AS (

SELECT tbl.[name] AS TableName,

      idx.[name] AS IndexName,

      INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,

      INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,

      INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,

      INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,

      INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5,

      INDEX_COL( tbl.[name], idx.indid, 6 ) AS col6,

      INDEX_COL( tbl.[name], idx.indid, 7 ) AS col7,

      INDEX_COL( tbl.[name], idx.indid, 8 ) AS col8,

      INDEX_COL( tbl.[name], idx.indid, 9 ) AS col9,

      INDEX_COL( tbl.[name], idx.indid, 10 ) AS col10,

      INDEX_COL( tbl.[name], idx.indid, 11 ) AS col11,

      INDEX_COL( tbl.[name], idx.indid, 12 ) AS col12,

      INDEX_COL( tbl.[name], idx.indid, 13 ) AS col13,

      INDEX_COL( tbl.[name], idx.indid, 14 ) AS col14,

      INDEX_COL( tbl.[name], idx.indid, 15 ) AS col15,

      INDEX_COL( tbl.[name], idx.indid, 16 ) AS col16,

      dpages,

      used,

      rowcnt

FROM SYSINDEXES idx

INNER JOIN sysobjects tbl ON idx.[id] = tbl.[id]

WHERE indid > 0

      and type = 'U'

      AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') =0

      AND INDEX_COL( tbl.[name], idx.indid, 2 ) IS NOT NULL)

,QUERY2 AS (

SELECT tbl.[name] AS TableName,

      idx.[name] AS IndexName,

      INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,

      INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,

      INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,

      INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,

      INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5,

      INDEX_COL( tbl.[name], idx.indid, 6 ) AS col6,

      INDEX_COL( tbl.[name], idx.indid, 7 ) AS col7,

      INDEX_COL( tbl.[name], idx.indid, 8 ) AS col8,

      INDEX_COL( tbl.[name], idx.indid, 9 ) AS col9,

      INDEX_COL( tbl.[name], idx.indid, 10 ) AS col10,

      INDEX_COL( tbl.[name], idx.indid, 11 ) AS col11,

      INDEX_COL( tbl.[name], idx.indid, 12 ) AS col12,

      INDEX_COL( tbl.[name], idx.indid, 13 ) AS col13,

      INDEX_COL( tbl.[name], idx.indid, 14 ) AS col14,

      INDEX_COL( tbl.[name], idx.indid, 15 ) AS col15,

      INDEX_COL( tbl.[name], idx.indid, 16 ) AS col16,

      dpages,

      used,

      rowcnt

FROM SYSINDEXES idx

INNER JOIN sysobjects tbl ON idx.[id] = tbl.[id]

WHERE indid > 0

      and type = 'U'

      AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') =0

      AND INDEX_COL( tbl.[name], idx.indid, 2 ) IS NOT NULL)

 

SELECT

      Q1.*

FROM

      QUERY Q1

JOIN QUERY2 Q2 ON (Q1.COL1=Q2.COL1 AND Q1.COL2=Q2.COL2)

WHERE Q1.INDEXNAME<>Q2.INDEXNAME

 

Abs!