Home
  Home
Home
Search
Articles
Page Tag-Cloud
  Software
Software Tag-Cloud
Submit Patch
Building from Source
Open Source Definition
  Popular Tags
C Plus Plus
Source Code
Legacy
Class
Console
  Members
Login
Web-Email
Notable Members
  Official
Our Company
Copyright Information
Software EULA
GPL EULA
LGPL Eula
Pre-Release EULA
Privacy Policy
  Support
Make Contact
 
 
Finding Duplicate SQL Indexes
Without indexes your SQL Server performance would be nill, your CPU Time would be chewed up with scanning and your disks would be saturated with read requests, but with too many indexes you can/will experience Index Overload Disorder.

These two views work in tandem with one-another, the first view (vIndexesWithColumns) returns each of the indexes in your database with the names of the first 25 columns it covers. The second view (vDuplicateIndexes) returns the names of the indexes that cover the same columns. This does not indicate that the indexes is 100% duplicated, as the stat may have more than 25 columns, have different fill factors or use Index Include Columns, so do a bit of research on your indexes before you remove anything.

See also: Finding Duplicate SQL Statistics

CREATE VIEW vIndexesWithColumns 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,
	INDEX_COL( tbl.[name], idx.indid, 17 ) AS col17,
	INDEX_COL( tbl.[name], idx.indid, 18 ) AS col18,
	INDEX_COL( tbl.[name], idx.indid, 19 ) AS col19,
	INDEX_COL( tbl.[name], idx.indid, 20 ) AS col20,
	INDEX_COL( tbl.[name], idx.indid, 21 ) AS col21,
	INDEX_COL( tbl.[name], idx.indid, 22 ) AS col22,
	INDEX_COL( tbl.[name], idx.indid, 23 ) AS col23,
	INDEX_COL( tbl.[name], idx.indid, 24 ) AS col24,
	INDEX_COL( tbl.[name], idx.indid, 25 ) AS col25,
	dpages,
	used,
	rowcnt
FROM
	sysindexes idx
INNER JOIN sys.objects tbl
	ON idx.[id] = tbl.object_id
WHERE
	tbl.is_ms_shipped = 0
	AND indid > 0 
	AND INDEXPROPERTY(tbl.object_id, idx.[name], 'IsStatistics') = 0
GO

CREATE VIEW vDuplicateIndexes AS
SELECT
	l1.TableName, 
	l1.IndexName, 
	l2.IndexName AS DuplicateIndex, 
	l1.col1, 
	l1.col2, 
	l1.col3, 
	l1.col4, 
	l1.col5, 
	l1.dpages,
	l1.used,
	l1.rowcnt
FROM
	vIndexesWithColumns l1 
INNER JOIN vIndexesWithColumns l2
	ON l1.TableName = l2.TableName
	AND l1.IndexName <> l2.IndexName
	AND l1.col1 = l2.col1
	AND COALESCE(l1.col2,'') = COALESCE(l2.col2,'')
	AND COALESCE(l1.col3,'') = COALESCE(l2.col3,'')
	AND COALESCE(l1.col4,'') = COALESCE(l2.col4,'')
	AND COALESCE(l1.col5,'') = COALESCE(l2.col5,'')
	AND COALESCE(l1.col6,'') = COALESCE(l2.col6,'')
	AND COALESCE(l1.col7,'') = COALESCE(l2.col7,'')
	AND COALESCE(l1.col8,'') = COALESCE(l2.col8,'')
	AND COALESCE(l1.col9,'') = COALESCE(l2.col9,'')
	AND COALESCE(l1.col10,'') = COALESCE(l2.col10,'')
	AND COALESCE(l1.col11,'') = COALESCE(l2.col11,'')
	AND COALESCE(l1.col12,'') = COALESCE(l2.col12,'')
	AND COALESCE(l1.col13,'') = COALESCE(l2.col13,'')
	AND COALESCE(l1.col14,'') = COALESCE(l2.col14,'')
	AND COALESCE(l1.col15,'') = COALESCE(l2.col15,'')
	AND COALESCE(l1.col16,'') = COALESCE(l2.col16,'')
	AND COALESCE(l1.col17,'') = COALESCE(l2.col17,'')
	AND COALESCE(l1.col18,'') = COALESCE(l2.col18,'')
	AND COALESCE(l1.col19,'') = COALESCE(l2.col19,'')
	AND COALESCE(l1.col20,'') = COALESCE(l2.col20,'')
	AND COALESCE(l1.col21,'') = COALESCE(l2.col21,'')
	AND COALESCE(l1.col22,'') = COALESCE(l2.col22,'')
	AND COALESCE(l1.col23,'') = COALESCE(l2.col23,'')
	AND COALESCE(l1.col24,'') = COALESCE(l2.col24,'')
	AND COALESCE(l1.col25,'') = COALESCE(l2.col25,'')


Is this code snippet, product or advice warrantied against ill-effect and/or technical malaise? No. No it's not! Not expressed - Not implied - not at all.



Tags:
 Index    Maintenance    Snippet    SQL Server    View  

Created by Josh Patterson on 2/9/2013, last modified by Josh Patterson on 2/17/2013

No comments currently exists for this page. Why don't you add one?
First Previous Next Last 

Login or signup to leave a comment.
 
Copyright © 2017 NetworkDLS.
All rights reserved.
 
Privacy Policy | Our Company | Contact