“Get Unused Indexes” Tool for SQL Server 2005

Scenario: Whether you inherit an existing application, or build one from scratch, to improve performance, you will want to periodically identify unused indexes.

Solution: Here’s a stored-procedure script I wrote, as adapted from Michael Campbell’s base script, to identify and store unused indexes for a given database. I schedule this stored procedure as a job and run it on a daily basis. I will conduct a trend analysis after after I have accumulated several days of data to identify the index deletion candidates.

Using the “IsPrimaryKey” and “IsUniqueConstraint” fields in the [DBA].[dbo].[UnusedIndexes] table, I can determine if I will need to write either a DROP INDEX or ALTER TABLE statement.

N.B. You will want to script out your index definitions before removing them.

Conventions: Two conventions bear comment.

  1. It’s my standard practice to create a fixed-space “DBA” database on a given SQL Server instance. It’s a workspace where I’ll perform DBA analysis tasks and, optionally, persist related metrics (akin to SQL Server’s usage of its “tempdb” database). I’ll place DBA objects, like this script’s stored procedure and related table, in the DBA database.
  2. I use a work table and bcp to export dynamic SQL to a text file. I then use the text file output for debugging. I.e. I copy and paste the text-file verbiage into a SQL Server query window for parsing/execution.

Script Highlights:

The procedure has three parameters:

  1. @DBName: The name of the database you want to analyze
  2. @DaysToRetain: The number of days for which you wish to retain unused index data in the UnusedIndexes work table. I have an arbitrary 21-day default
  3. @Debug: A bit to either enable or disable debug mode (the bcp-bit I mentioned above)

I divide this script into 5 sections:

  1. bcp provisioning: Ensure we can enable the “xp_cmdshell” option so we can execute bcp commands
  2. Removal of aged-out “unused index” data
  3. Build dynamic T-SQL statement (revolving around the “sys.dm_db_index_usage_stats” dynamic management view) to populate the “DBA.dbo.UnusedIndexes” work table
  4. Augment the information provided by the “sys.dm_db_index_usage_stats” DMV with information from the “sp_helpindex” system stored procedure
  5. Ascertain the last server restart date since this event resets the “sys.dm_db_index_usage_stats” DMV counters

The Script:

Note: I store these scripts on Box.net. Click on this link to download the below script.

USE DBA
GO

IF OBJECT_ID('dbo.GetUnusedIndexes', 'P') IS NOT NULL DROP PROCEDURE dbo.GetUnusedIndexes
GO

CREATE PROCEDURE dbo.GetUnusedIndexes

	@DBName									SYSNAME,
	@DaysToRetain							TINYINT	= 21,
	@Debug									BIT		= 0

AS

/*
* Author:	Michael Campbell; adapted by Mark Holahan
* Date:		Thursday, 23-June-2011
*
* Revised:
*			Thr, 23-Jun-2011: Changed [EXEC sp_helpindex] bit to factor in schema name as part of object namer
*
* Version:	5
* Purpose:	Script to find unused indexes: http://sqlserverperformance.idera.com/indexing/removing-unused-indexes/
*
* Theory:	Says BOL regarding the [sys.dm_db_index_usage_stats] DMV:
*
*			"The user_updates counter indicates the level of maintenance on the index caused by insert,
*			update, or delete operations on the underlying table or view. You can use this view to determine
*			which indexes are used only lightly all by your applications. You can also use the view to determine
*			which indexes are incurring maintenance overhead. You may want to consider dropping indexes that
*			incur maintenance overhead, but are not used for queries, or are only infrequently used for queries."
*
*			And
*
*			"The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition,
*			whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows
*			associated with the database are removed."
*
*			So, this is why we should definitely get a trend. If we ran this query right after the service got
*			restarted, we would have many indexes that appeared to be unused.
*
*/

SET NOCOUNT ON

IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE name = @DBName)
	BEGIN
		DECLARE @E VARCHAR(500)
		SET @E = 'The [' + @DBName + '] database does not exist.'
		+ CHAR(10) + 'Please provide a valid database name.'

		RAISERROR(@E, 16, 1) WITH NOWAIT
		RETURN
	END

-- Persisted work tables

IF OBJECT_ID('DBA.dbo.UnusedIndexes', 'U') IS NULL
	BEGIN
		CREATE TABLE DBA.dbo.UnusedIndexes
		(
			PK										INT IDENTITY
			CONSTRAINT PK_UnusedIndexes PRIMARY KEY,
			BatchID									UNIQUEIDENTIFIER NOT NULL,
			BatchDate								DATETIME NOT NULL,
			ServerName								SYSNAME NOT NULL,
			DatabaseName							SYSNAME NOT NULL,
			SchemaName								SYSNAME NOT NULL,
			TableName								SYSNAME NOT NULL,
			IndexName								SYSNAME NULL,
			IsPrimaryKey							BIT NOT NULL
			CONSTRAINT DF_UnusedIndexes_IsPrimaryKey DEFAULT (0),
			IsUniqueConstraint						BIT NOT NULL
			CONSTRAINT DF_UnusedIndexes_IsUniqueConstraint DEFAULT (0),
			[Reads]									BIGINT NOT NULL
			CONSTRAINT DF_UnusedIndexes_Reads DEFAULT (0),
			[Writes]								BIGINT NOT NULL
			CONSTRAINT DF_UnusedIndexes_Writes DEFAULT (0),
			[Rows]									BIGINT NOT NULL
			CONSTRAINT DF_UnusedIndexes_Rows DEFAULT (0),
			IndexType								VARCHAR(14) NOT NULL,
			IndexDescription						VARCHAR(210) NULL,
			IndexKeys								NVARCHAR(2078) NULL,
			LastServerRestart						DATETIME NULL,
			DaysSinceLastRestart					AS (DATEDIFF(dd, LastServerRestart, GETDATE()) )
		)

		CREATE NONCLUSTERED INDEX NC_UnusedIndexes_ExistenceCheck
		ON [dbo].[UnusedIndexes] ([IndexName])
		INCLUDE ([BatchDate], [ServerName], [DatabaseName], [SchemaName], [TableName], [LastServerRestart])

	END;

DECLARE

	@AgeOutDate										DATETIME,
	@BatchDate										DATETIME,
	@BatchID										UNIQUEIDENTIFIER,
	@Cmd											NVARCHAR(4000),
	@tempdbCreateDate								DATETIME,
	@dbid											INT,
	@ErrorMessage									VARCHAR(500),
	@InstanceName									SYSNAME,
	@MachineName									SYSNAME,
	@No												BIT,
	@PK												SMALLINT,
	@RetVal											INT,
	@SQL											VARCHAR(8000),
	@SQLServerInstanceName							SYSNAME,
	@TableName										SYSNAME,
	@Yes											BIT,
	@ValueInUse										SQL_VARIANT,
	@XP_CMDSHELL									INT

-- Constants
SET @Yes					= 1
SET @No						= 0
SET @XP_CMDSHELL			= 16390 -- select configuration_id from master.sys.configurations WHERE name = 'XP_CMDSHELL'

-- Ephemeral Work tables

IF OBJECT_ID('tempdb.dbo.#PathExistenceTest', 'U') IS NOT NULL DROP TABLE #PathExistenceTest;

CREATE TABLE #PathExistenceTest
(
	Subdirectory									SYSNAME,
	Depth											BIT
);

IF @Debug = @YES

	BEGIN
		-- "real" table b/c you can't bcp from a temp table or table variable
		IF OBJECT_ID('DBA.dbo.bcpSQLScript', 'U') IS NOT NULL DROP TABLE DBA.dbo.bcpSQLScript

		CREATE TABLE DBA.dbo.bcpSQLScript
		(
			PK										INT IDENTITY(1,1) NOT NULL,
			Sequence								TINYINT NOT NULL,
			SQLCommand								VARCHAR(8000) NULL,
			DTStamp									DATETIME DEFAULT GETDATE()
		);
	END

-- Table Variables

-- For unique table list for given batch ID
DECLARE @TableList TABLE
(
	PK												SMALLINT IDENTITY NOT NULL,
	SchemaName										SYSNAME NOT NULL,
	TableName										SYSNAME NOT NULL,
	HelpIndexCommand								VARCHAR(500) NULL
);

-- For sp_helpindex output
DECLARE @hi TABLE
(
	TableName										SYSNAME NULL,
	index_name										SYSNAME NOT NULL,
	index_description								VARCHAR(210) NOT NULL,
	index_keys										NVARCHAR(2078) NOT NULL
)

-- Variables
SET @BatchID				= NEWID()
SET @BatchDate				= GETDATE()
SELECT @dbid				= DB_ID(@DBName)

SET @InstanceName			= CAST(SERVERPROPERTY('InstanceName') AS SYSNAME)
SET @MachineName			= CAST(SERVERPROPERTY('MachineName') AS SYSNAME)

IF @InstanceName IS NULL
	SET @SQLServerInstanceName = @MachineName
ELSE
	SET @SQLServerInstanceName = @MachineName + '\' + @InstanceName

-- 1) If we're debugging, we want to bcp stuff out. See if we can bcp...

IF @Debug = @Yes
	BEGIN TRY
		-- Enable advanced options
		EXEC sp_configure 'show advanced option', '1'; RECONFIGURE;

		-- Ensure xp_cmdshell's on so we can bcp out
		EXEC sp_configure 'xp_cmdshell', '1'; RECONFIGURE;

		-- Test...
		SELECT @ValueInUse = Value_In_Use
		FROM master.sys.configurations
		WHERE configuration_id = @XP_CMDSHELL

		IF CONVERT(INT, @ValueInUse) <> @YES
			BEGIN
				SET @ErrorMessage = CHAR(13) + 'Although we attempted to enable the ''xp_cmdshell'' server option, this option is still disabled.' + CHAR(13)
					+ 'This script depends upon this extended stored procedure''s availability. Please investigate.'
					+ CHAR(13) + 'NOTE: ''xp_cmdshell'' execution requires ''CONTROL SERVER'' permission (See SQL Server Books Online for more information).'

				RAISERROR(@ErrorMessage, 16, 1) WITH NOWAIT
			END

	 END TRY

	BEGIN CATCH
		SELECT
			ERROR_NUMBER() AS ErrorNumber
			,ERROR_MESSAGE() AS ErrorMessage
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_LINE() AS ErrorLine
			,ERROR_PROCEDURE() AS ErrorProcedure

			RETURN
	END CATCH

-- 2) Remove records older than a given number of units from DBA.dbo.UnusedIndexes for
--	given database

IF @Debug = @No

	BEGIN

		SET @AgeOutDate = DATEADD(dd, (@DaysToRetain * -1), GETDATE())

		DELETE
		FROM DBA.dbo.UnusedIndexes
		WHERE
				BatchDate < @AgeOutDate
			AND
				DatabaseName = @DBName;
	END

-- 3) Build statement to populate the DBA.dbo.UnusedIndexes for given database

BEGIN TRY
	SET @SQL =

	'USE' + SPACE(1) + @DBName + ';'
	+ CHAR(10) + 'SELECT'
	+ CHAR(10) + CHAR(9) + CHAR(39) + CAST(@BatchID AS CHAR(36)) + CHAR(39) + SPACE(1) + 'BatchID' + CHAR(44)
	+ CHAR(10) + CHAR(9) + CHAR(39) + CONVERT(VARCHAR(26), @BatchDate, 109) + CHAR(39) + SPACE(1) + 'BatchDate' + CHAR(44)
	+ CHAR(10) + CHAR(9) + CHAR(39) + @SQLServerInstanceName + CHAR(39) + SPACE(1) + 'ServerName' + CHAR(44)
	+ CHAR(10) + CHAR(9) + CHAR(39) + @DBName + CHAR(39) + SPACE(1) + 'DatabaseName' + CHAR(44)
	+ CHAR(10) + CHAR(9) + 'SCHEMA_NAME(t.schema_id) SchemaName' + CHAR(44)
	+ CHAR(10) + CHAR(9) + 'TableName	= OBJECT_NAME(ixs.OBJECT_ID' + CHAR(44) + SPACE(1) + CAST(@dbid AS VARCHAR(5)) + ')' + CHAR(44)
	+ CHAR(10) + CHAR(9) + 'IndexName	= ixs.name' + CHAR(44)
	+ CHAR(10) + CHAR(9) + 'IsPrimaryKey = ixs.is_primary_key' + CHAR(44)
	+ CHAR(10) + CHAR(9) + 'IsUniqueConstraint = ixs.is_unique_constraint' + CHAR(44)
	+ CHAR(10) + CHAR(9) + '(usage.user_seeks + usage.user_scans + usage.user_lookups)' + CHAR(9) + CHAR(9) + CHAR(9) + '[Reads]' + CHAR(44)
	+ CHAR(10) + CHAR(9) + 'usage.[user_updates]' + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + '[Writes]' + CHAR(44)
	+ CHAR(10) + CHAR(9) + '('
	+ CHAR(10) + CHAR(9) + 'SELECT SUM(sp.[rows])'
	+ CHAR(10) + CHAR(9) + 'FROM sys.partitions' + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + 'sp'
	+ CHAR(10) + CHAR(9) + 'WHERE'
	+ CHAR(10) + CHAR(9) +  CHAR(9) + 'usage.OBJECT_ID = sp.object_id'
	+ CHAR(10) + CHAR(9) + 'AND'
	+ CHAR(10) + CHAR(9) +  CHAR(9) + 'sp.index_id = usage.index_id'
	+ CHAR(10) + CHAR(9) + ')' + CHAR(9) + CHAR(9) + CHAR(9) + '[Rows]' + CHAR(44)
	+ CHAR(10) + CHAR(9) + 'CASE'
	+ CHAR(10) + CHAR(9) + + CHAR(9) + 'WHEN ixs.type = 0 THEN ' + CHAR(39) + 'Heap' + CHAR(39)
	+ CHAR(10) + CHAR(9) + + CHAR(9) + 'WHEN ixs.type = 1 THEN ' + CHAR(39) + 'Clustered' + CHAR(39)
	+ CHAR(10) + CHAR(9) + + CHAR(9) + 'WHEN ixs.type = 2 THEN ' + CHAR(39) + 'Non Clustered' + CHAR(39)
	+ CHAR(10) + CHAR(9) + + CHAR(9) + 'WHEN ixs.type = 3 THEN ' + CHAR(39) + 'XML' + CHAR(39)
	+ CHAR(10) + CHAR(9) + 'END IndexType'
	+ CHAR(10) + CHAR(9) + 'FROM' + SPACE(1) + @DBName + '.sys.dm_db_index_usage_stats' + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + 'usage'
	+ CHAR(10) + 'INNER JOIN' + SPACE(1) + @DBName + '.sys.indexes' + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + 'ixs'
	+ CHAR(10) + CHAR(9) + 'ON'
	+ CHAR(10) + CHAR(9) + CHAR(9) + 'usage.[object_id] = ixs.[object_id]'
	+ CHAR(10) + CHAR(9) + 'AND'
	+ CHAR(10) + CHAR(9) + CHAR(9) + 'usage.[index_id] = ixs.[index_id]'
	+ CHAR(10) + 'INNER JOIN' + SPACE(1) + @DBName + '.sys.tables' + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + 't'
	+ CHAR(10) + CHAR(9) + 'ON usage.[object_id] = t.[object_id]'
	+ CHAR(10) + 'WHERE'
	+ CHAR(10) + CHAR(9) + '(usage.user_seeks + usage.user_scans + usage.user_lookups) = 0 -- readless'
	+ CHAR(10) + 'AND'
	+ CHAR(10) + CHAR(9) + 'usage.Database_ID =' + SPACE(1) + CAST(@dbid AS VARCHAR(5))
	+ CHAR(10) + 'AND'
	+ CHAR(10) + CHAR(9) + 'OBJECTPROPERTY(usage.[object_id], ' + CHAR(39) + 'IsUserTable' + CHAR(39) + ') = 1'
	+ CHAR(10) + 'ORDER BY TableName, IndexName ASC;'

	IF @Debug = @Yes

		BEGIN
			-- Put into a work table so we can bcp out and see entire dynamic SQL string in a text file
			INSERT DBA.dbo.bcpSQLScript(Sequence, SQLCommand)
				SELECT 1, @SQL

			-- Does the bcp target local directory exist?
			TRUNCATE TABLE #PathExistenceTest
			INSERT #PathExistenceTest(Subdirectory, Depth)
				EXEC master.dbo.xp_dirtree 'C:\Temp'

			IF (SELECT COUNT(1) FROM #PathExistenceTest) = 0
				BEGIN
					-- Create the directory
					EXEC @RetVal = master.dbo.xp_create_subdir 'C:\Temp'
				END

			IF @RetVal = 0
				BEGIN
					IF (SELECT COUNT(1) FROM [DBA].dbo.bcpSQLScript) > 0
						BEGIN
							SET @Cmd = 'bcp' + SPACE(1) + '"SELECT SQLCommand FROM DBA.dbo.bcpSQLScript WHERE Sequence = 1;" queryout C:\Temp\UnusedIndexes.sql -o C:\Temp\UnusedIndexesOutput.txt -e C:\Temp\UnusedIndexesError.err -c -S' + SPACE(1) + @SQLServerInstanceName + SPACE(1) + '-T'
							SELECT ISNULL(@Cmd, 'NULL BABY!!')
							IF @Cmd IS NOT NULL	EXEC Master.dbo.xp_cmdshell @Cmd
						END
				END
			ELSE
				SELECT 'Unable to create the bcp target directory.'
		END

	IF @Debug = @No
		BEGIN

			INSERT DBA.dbo.UnusedIndexes( BatchID, BatchDate, ServerName, DatabaseName, SchemaName, TableName, IndexName, IsPrimaryKey, IsUniqueConstraint, Reads, Writes, [Rows], IndexType )
				EXEC (@SQL)
		END

 END TRY

BEGIN CATCH

	SELECT
		ERROR_NUMBER() AS ErrorNumber
		,ERROR_MESSAGE() AS ErrorMessage
		,ERROR_SEVERITY() AS ErrorSeverity
		,ERROR_STATE() AS ErrorState
		,ERROR_LINE() AS ErrorLine
		,ERROR_PROCEDURE() AS ErrorProcedure

		GOTO Cleanup

END CATCH

-- 4) Provide add'tl index info we didn't get from initial query

IF @Debug = @No
BEGIN
	BEGIN TRY
		INSERT @TableList(SchemaName, TableName)
			SELECT
			DISTINCT SchemaName, TableName
			FROM DBA.dbo.UnusedIndexes
			WHERE BatchID = @BatchID

		-- Make an sp_helpindex command per table
		UPDATE @TableList
		SET HelpIndexCommand = 'USE' + SPACE(1) + @DBName + ';' + 'EXEC sp_helpindex N' + CHAR(39) + SchemaName + '.' + TableName + CHAR(39) + ';'

		WHILE EXISTS( SELECT TOP 1 1 FROM @TableList )
			BEGIN
				SELECT TOP 1 @PK = PK, @TableName = TableName, @SQL = HelpIndexCommand FROM @TableList

				IF @Debug = @Yes PRINT @SQL

				INSERT @hi(index_name, index_description, index_keys)
					EXEC (@SQL)

				UPDATE @hi
				SET TableName = @TableName
				WHERE TableName IS NULL;

				DELETE FROM @TableList WHERE PK = @PK

			END

		-- Update the DBA.dbo.UnusedIndexes IndexDescription & IndexKeys fields
		-- using the @hi table variable
		UPDATE ui
		SET
			ui.IndexDescription = hi.index_description,
			ui.IndexKeys		= hi.index_keys
		FROM DBA.dbo.UnusedIndexes						ui
		INNER JOIN @hi									hi
			ON
				ui.TableName = hi.TableName
			AND
				ui.IndexName = hi.index_name
		WHERE ui.BatchID = @BatchID;

	 END TRY

	BEGIN CATCH

		SELECT
			ERROR_NUMBER() AS ErrorNumber
			,ERROR_MESSAGE() AS ErrorMessage
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_LINE() AS ErrorLine
			,ERROR_PROCEDURE() AS ErrorProcedure

			GOTO Cleanup

	END CATCH

END

-- 5) Ascertain the last server restart date since this dynamic mgmt view or function or whatever it is
--	sys.dm_db_index_usage_stats resets/server restart

IF @Debug = @No
	BEGIN TRY

		SELECT @tempdbCreateDate = create_date
		FROM master.sys.databases
		WHERE name = 'tempdb'

		UPDATE DBA.dbo.UnusedIndexes
		SET LastServerRestart = @tempdbCreateDate
		WHERE LastServerRestart IS NULL;

	 END TRY

	BEGIN CATCH

		SELECT
			ERROR_NUMBER() AS ErrorNumber
			,ERROR_MESSAGE() AS ErrorMessage
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_LINE() AS ErrorLine
			,ERROR_PROCEDURE() AS ErrorProcedure

			GOTO Cleanup

	END CATCH

Cleanup:

-- Ensure xp_cmdshell's off
EXEC sp_configure 'xp_cmdshell', '0';
RECONFIGURE;

IF OBJECT_ID('DBA.dbo.bcpSQLScript', 'U') IS NOT NULL DROP TABLE DBA.dbo.bcpSQLScript;

Execution Example: Here’s an example of how to execute this script:

DECLARE
	@DBName			SYSNAME,
	@DaysToRetain	TINYINT,
	@Debug			BIT

SET @DBName			= 'AdventureWorks'
SET @DaysToRetain	= 21
SET @Debug			= 0

EXECUTE [DBA].[dbo].[GetUnusedIndexes]
  @DBName,
  @DaysToRetain,
  @Debug

Here’s the output I got after running this stored procedure:

(Now we don’t want to indiscriminately delete primary keys, but you get the idea)

Conclusion: Consider identifying and jettisoning unused indexes on a periodic basis to help improve SQL Server performance. A script, like what I’ve shared above, can help achieve this goal. See “Get Unused Index Deletion Candidates” Tool for SQL Server 2005 for the second of this two-article series related to unused indexes.

This entry was posted in Alphabetical, DBA Best Practice, DBA Tools, Performance Tuning and tagged , , , , . Bookmark the permalink.