查找重复的索引

Paul Nielsen

from： http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx

www.SQLServerBible.com

Find Duplicate Indexes

These queries mix a few basic DMVs with a little relational division to indentify every set of duplicate indexes.

 

This is the result of a collaborative effort. I started with a group by version of this query about a week ago and posted it on the private MVP Newsgroups. Itzik Ben-Gan replied with a pretty cool variation, and then I made some minor edits to handle partial dups and include column logic.

 

The first query finds exact matches. The indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination. The only caution would be to check for index hints. 

 

-- exact duplicates

with

 

indexcols as

(

select

 

object_id as id, index_id as indid, name,

(

 

select case keyno when 0 then NULL else colid end as [data()]

from

 

sys.sysindexkeys as k

where

 

k.id = i.object_id

and

 

k.indid = i.index_id

order

 

by keyno, colid

for

 

xml path('')) as cols,

(

 

select case keyno when 0 then colid else NULL end as [data()]

from

 

sys.sysindexkeys as k

where

 

k.id = i.object_id

and

 

k.indid = i.index_id

order

 

by colid

for

 

xml path('')) as inc

from

 

sys.indexes as i

)

select

object_schema_name

(c1.id) + '.' + object_name(c1.id) as 'table',

c1

.name as 'index',

c2

.name as 'exactduplicate'

from

 

indexcols as c1

join

 

indexcols as c2

on

 

c1.id = c2.id

and

 

c1.indid < c2.indid

and

 

c1.cols = c2.cols

and

 

c1.inc = c2.inc;

Be careful when dropping a partial duplicate index if the two indexes differ greatly in width. For example, if Ix1 is a very wide index with 12 columns, and Ix2 is a narrow two-column index that shares the first two columns, you may want to leave Ix2 as a faster, tighter, narrower index.