Sep 142010
 

This post is my contribution to T-SQL Tuesday #10, which is being hosted by Michael J. Swart (blog | twitter)

T-SQL TuesdayEach year my Boy Scout troop has a rummage sale as a fundraiser.  All the scouts and their families gather up their old clothes, books, toys, electronics, and any other treasures they no longer need and donate them to the troop, which sells them.  It’s basically a giant garage sale, except it’s done in the gym of the school we meet at.  Since everything sold comes from someone associated with the troop, many years ago it was decided to create an award for the person that donates the weirdest piece of crap most unique item each year.  This award comes in the form of a travelling trophy which started out as a child’s potty chair that was painted gold.  Each year, the winner is instructed to embellish the trophy with something new.  Here’s how it looks now!

As you can see, it’s grown from a simple potty chair mounted on a board into a lamp with a whole bunch of accessories, including a barbecue grill. I’m surprised nobody’s added a TV or fridge yet – then there’d really be no reason to leave!

So what does this have to do with indexes?  The bowels of this post contain something that I have embellished myself.  Last month Tom LaRock (blog | twitter) posted a script for helping to detect duplicate indexes.  He couldn’t claim credit for it as he said it was passed on to him a while ago and he’s unsure of the original source.  Similarly, I won’t claim credit for this other than to say I made a few changes I thought would be handy.  If you feel this script is lacking functionality that you think would be helpful, I invite you to change it and please share with the community when you’re done!

Why Duplicate Indexes Are Bad

Aside from serving no purpose, duplicate indexes can cause problems to occur which could require your attention, such as:

  • Wasted disk space – just like any other type of data, storing something more than once means it will take up space more than once as well.
  • Slower backups, restores, and integrity checks – more data means more time to read/write/check it all
  • Slower data modification operations – inserting, updating, and deleting rows will take longer when there’s more indexes that need updating
  • Increased chances of deadlock and blocking – More objects requiring locks for an operation means there’s a greater chance that another process has already acquired at least some of said locks
  • Increased transaction log growth and/or usage – The more writing/locking that’s happening due to an increased number of objects means that more will be written to the log

And I’m sure there’s plenty more potential issues than those I just listed. The simplest way to avoid this is to maintain only as many indexes as necessary. This script can help you find if any duplicates exist.

What’s New In This Version

  • I removed the columns that returned individual component columns of the indexes.  Since there was already a column that showed them all concatenated together, I couldn’t think of any reason for listing them individually other than it might help when programmatically dropping indexes, which shouldn’t be done anyway.  Indexes should only be dropped after careful review.
  • Added a column for displaying the size of the index.  Duplicate indexes are bad no matter what their size is, however this information may be helpful.
  • Added columns showing whether the index is filtered or has included columns.

Disclaimer

I have tested this script on SQL Server 2005 and later, and it will not run on earlier versions.  You should not trust any script you find on the internet (including this one!)  Make sure you completely understand what a script is doing before running it.  Always test on a testing or development server before using it in any production environment.

The Script

What you’ve really been waiting for.  You can see the entire script below, or just download the .zip of it here.  Enjoy!

-- This script will generate 3 reports that give an overall or high level
-- view of the indexes in a particular database. The sections are as follows:
-- 1.  Lists ALL indexes and constraints along with the key details of each
-- 2.  Lists any tables with potential Redundant indexes
-- 3.  Lists any tables with potential Reverse indexes

--  Create a table variable to hold the core index info
CREATE TABLE #AllIndexes (
   TableID INT NOT NULL,
   SchemaName SYSNAME NOT NULL,
   TableName SYSNAME NOT NULL,
   IndexID INT NULL,
   IndexName NVARCHAR(128) NULL,
   IndexType VARCHAR(12) NOT NULL,
   ConstraintType VARCHAR(11) NOT NULL,
   ObjectType VARCHAR(10) NOT NULL,
   AllColName NVARCHAR(2078) NULL,
   ColName1 NVARCHAR(128) NULL,
   ColName2 NVARCHAR(128) NULL,
   IndexSizeKB BIGINT NULL,
   HasFilter BIT NOT NULL,
   HasIncludedColumn BIT NOT NULL
);

DECLARE @ProductVersion NVARCHAR(128);
DECLARE @MajorVersion TINYINT;
DECLARE @loadIndexSQL NVARCHAR(4000);

SET @ProductVersion = CONVERT(NVARCHAR(128), SERVERPROPERTY('ProductVersion'));
SET @MajorVersion = CONVERT(TINYINT, LEFT(@ProductVersion, CHARINDEX('.', @ProductVersion) - 1));

SET @loadIndexSQL = N'
	INSERT INTO #AllIndexes (TableID, SchemaName, TableName, IndexID, IndexName, IndexType, ConstraintType,
		ObjectType, AllColName, ColName1, ColName2, IndexSizeKB, HasFilter, HasIncludedColumn)
	SELECT o.object_id, -- TableID
		u.[name], -- SchemaName
		o.[name], -- TableName
		i.index_id, -- IndexID
		i.[name], -- IndexName
		CASE i.[type]
			WHEN 0 THEN ''HEAP''
			WHEN 1 THEN ''CL''
			WHEN 2 THEN ''NC''
			WHEN 3 THEN ''XML''
			ELSE ''UNKNOWN''
		END, -- IndexType
		CASE
			WHEN (i.is_primary_key) = 1 THEN ''PK''
			WHEN (i.is_unique) = 1 THEN ''UNQ''
			ELSE ''''
		END, -- ConstraintType
		CASE
			WHEN (i.is_unique_constraint) = 1 OR i.is_primary_key = 1 THEN ''CONSTRAINT''
			WHEN i.type = 0 THEN ''HEAP''
			WHEN i.type = 3 THEN ''XML INDEX''
			ELSE ''INDEX''
		END, -- ObjectType
		(SELECT COALESCE(c1.[name], '''')
			FROM sys.columns AS c1
			INNER JOIN sys.index_columns AS ic1 ON c1.object_id = ic1.object_id
				AND c1.column_id = ic1.column_id
				AND ic1.key_ordinal = 1
			WHERE ic1.object_id = i.object_id
				AND ic1.index_id = i.index_id) +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 2) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 2)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 3) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 3)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 4) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 4)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 5) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 5)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 6) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 6)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 7) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 7)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 8) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 8)
			END, -- AllColName
		(SELECT COALESCE(c1.[name], '''')
			FROM sys.columns AS c1
			INNER JOIN sys.index_columns AS ic1 ON c1.[object_id] = ic1.[object_id]
				AND c1.[column_id] = ic1.[column_id]
				AND ic1.[key_ordinal] = 1
			WHERE ic1.[object_id] = i.[object_id]
				AND ic1.[index_id] = i.[index_id]), -- ColName1
			CASE
				WHEN INDEX_COL(''['' + u.name + ''].[''+ o.name + '']'', i.index_id, 2) IS NULL THEN ''''
				ELSE INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.[index_id],2)
			END, -- ColName2
			ps.used_page_count * 8, -- IndexSizeKB' + CHAR(13);

			IF @MajorVersion >= 10
				SET @loadIndexSQL = @loadIndexSQL + 'i.has_filter';
			ELSE
				SET @loadIndexSQL = @loadIndexSQL + '0';

			SET @loadIndexSQL = @loadIndexSQL + ', -- HasFilter' + CHAR(13);

			IF @MajorVersion >= 9
				SET @loadIndexSQL = @loadIndexSQL + 'CASE WHEN (SELECT COUNT(*) FROM sys.index_columns ic WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1) >= 1 THEN 1 ELSE 0 END';
			ELSE
				SET @loadIndexSQL = @loadIndexSQL + '0';

			SET @loadIndexSQL = @loadIndexSQL + ' -- HasIncludedColumn
	FROM sys.objects o WITH (NOLOCK)
		INNER JOIN sys.schemas u WITH (NOLOCK) ON o.schema_id = u.schema_id
		LEFT OUTER JOIN sys.indexes i WITH (NOLOCK) ON o.object_id = i.object_id
		LEFT OUTER JOIN sys.dm_db_partition_stats ps WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.[index_id] = i.[index_id]
	WHERE o.[type] = ''U''
		AND o.[name] NOT IN (''dtproperties'')
		AND i.[name] NOT LIKE ''_WA_Sys_%'';
';

EXEC sp_executesql @loadIndexSQL;

-----------

SELECT 'Listing All Indexes' AS [Comments];

SELECT TableID, SchemaName, TableName, IndexID, IndexName, IndexType, ConstraintType, ObjectType, AllColName, IndexSizeKB, HasFilter, HasIncludedColumn
   FROM #AllIndexes
   ORDER BY TableName;

-----------
SELECT 'Listing Possible Redundant Index keys' AS [Comments];

SELECT DISTINCT i.TableName, i.IndexName,i.IndexType, i.ConstraintType, i.AllColName, i.IndexSizeKB, i.HasFilter, i.HasIncludedColumn
   FROM #AllIndexes AS i
   JOIN #AllIndexes AS i2 ON i.TableID = i2.TableID
      AND i.ColName1 = i2.ColName1
      AND i.IndexName <> i2.IndexName
      AND i.IndexType <> 'XML'
   ORDER BY i.TableName, i.AllColName;

----------
SELECT 'Listing Possible Reverse Index keys' AS [Comments];

SELECT DISTINCT I.TableName, I.IndexName, I.IndexType, I.ConstraintType, I.AllColName, I.IndexSizeKB, I.HasFilter, I.HasIncludedColumn
   FROM #AllIndexes AS I
   JOIN #AllIndexes AS I2 ON I.TableID = I2.TableID
      AND I.ColName1 = I2.ColName2
      AND I.ColName2 = I2.ColName1
      AND I.IndexName <> I2.IndexName
      AND I.IndexType <> 'XML';

DROP TABLE #AllIndexes;

  3 Responses to “Potty Chairs and Duplicate Indexes”

  1. That’s an amazing trophy (for lack of a better word.) Thanks for participating in T-SQL Tuesday this month.

    If I ever embellish this script I’ll be sure to pass it on.

  2. […] happens when a clustered index grows. What Struck Me: A frisbee. 9. Bob Pusateri (@SQLBob) Potty Chairs And Duplicate Indexes Provides an embellished script that has been making the blog rounds to report on redundant indexes. […]

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)