Procedure for Index usage

I wrote a small stored procedure to shortcut querying Index usage in SQL Server.

Check out the stored procedure body below.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_IndexUsage]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[sp_IndexUsage]
GO

CREATE PROCEDURE [dbo].[sp_IndexUsage] (
	@TableName NVARCHAR(255),
	@IncludeDates BIT = 0
)
AS
BEGIN

	/*
		EXEC sp_IndexUsage @TableName = 'User'
		EXEC sp_IndexUsage @TableName = 'User', @IncludeDates = 1
	*/

	IF ISNULL(@IncludeDates, 0) = 0
	BEGIN
		SET @IncludeDates = 0;
	END

	IF (@TableName IS NULL)
	BEGIN
		RETURN;
	END

	IF (@IncludeDates = 1 )
	BEGIN
		SELECT
			OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
			I.[NAME] AS [INDEX NAME],
			I.Is_Disabled,
			USER_SEEKS, 
			USER_SCANS, 
			USER_LOOKUPS, 
			USER_UPDATES,
			LAST_USER_SEEK,
			LAST_USER_SCAN,
			LAST_USER_LOOKUP,
			LAST_USER_UPDATE
		FROM
			 SYS.DM_DB_INDEX_USAGE_STATS AS S 
			 INNER JOIN SYS.INDEXES AS I 
				ON I.[OBJECT_ID] = S.[OBJECT_ID] 
				AND I.INDEX_ID = S.INDEX_ID 
		WHERE
			OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
			AND	OBJECT_NAME(S.[OBJECT_ID]) = @TableName
	END
	ELSE
	BEGIN
		SELECT
			OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
			I.[NAME] AS [INDEX NAME],
			I.Is_Disabled,
			USER_SEEKS, 
			USER_SCANS, 
			USER_LOOKUPS, 
			USER_UPDATES
		FROM
			 SYS.DM_DB_INDEX_USAGE_STATS AS S 
			 INNER JOIN SYS.INDEXES AS I 
				ON I.[OBJECT_ID] = S.[OBJECT_ID] 
				AND I.INDEX_ID = S.INDEX_ID 
		WHERE
			OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
			AND	OBJECT_NAME(S.[OBJECT_ID]) = @TableName
	END

END
GO

As you may be able to see, you just need to provide the table name you wish to filter by. You can optionally chose to include last used dates.

I hope that helps!

Subscribe

One comment