Category: Metadata

Index Usage in Dynamic Management Views

By , May 15, 2012 21:37

This is just a short one to post a script for a procedure to return index usage either for all non-Microsoft indexes or just for a single non-Microsoft.

There are loads of variations on this on the net and this is just my version:
 

CREATE PROCEDURE up_indexusage
	@table_name sysname = null
AS
SELECT 
	OBJECT_NAME(i.[object_id]) AS 'table_name', 
	i.name AS 'index_name',
	i.type_desc, 
	s.user_seeks, 
	s.last_user_seek,
	s.user_scans,
	s.last_user_scan,
	s.user_lookups,
	s.last_user_lookup,
	s.user_updates,
	s.last_user_update
FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s 
	ON s.object_id = i.object_id
	AND s.index_id = i.index_id
	AND (s.database_id = DB_ID() OR s.database_id IS NULL)
WHERE (OBJECTPROPERTYEX(i.[object_id], 'IsMsShipped') = 0)
AND	(OBJECT_NAME(i.[object_id]) = 
		CASE 
			WHEN @table_name IS NULL THEN OBJECT_NAME(i.[object_id])
			ELSE @table_name
		END
	)
AND (s.database_id = DB_ID() OR s.database_id IS NULL)
ORDER BY OBJECT_NAME(i.[object_id]), i.index_id
GO

 
One very important thing to note (which I didn’t know and only saw for the first time today): These index usage statistics are reset when a mirrored database fails over.
Books Online state “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.”
There is no explicit mention of mirroring (failover) there :-|.

So caution should be exercised when using a statement like this to identify unwanted indexes. It would be best to be sure the server has been running and no failover has taken place for an extended period of time.

Thanks for reading.

Displaying Included fields for a NONCLUSTERED Index

By , May 12, 2012 08:58
The standard stored procedure sp_helpindex unfortunately doesn’t display the included fields which can be used with NONCLUSTERED indexes.
I have written a user stored procedure to get around this. I wanted to use a set based solution but couldn’t do so within a reasonable amount of time, so I have reverted to a cursor (something I haven’t written in a log time).
The user stored procedure saves the results of sp_helpindex to a temporary table and the procedes to gather the included fields from the metadata views provided by SQL server.
CREATE PROCEDURE up_helpindex
 @object_name sysname
AS

SET NOCOUNT ON;

IF OBJECT_ID('tempdb.dbo.#HelpIndexResults') IS NOT NULL
 DROP TABLE #HelpIndexResults;

CREATE TABLE #HelpIndexResults (
index_name sysname,
index_description VARCHAR(210),
index_keys NVARCHAR(2078),
include_keys NVARCHAR(2078) DEFAULT ''
);

--why reinvent the wheel? - use standard procedures
INSERT INTO #HelpIndexResults (index_name, index_description, index_keys)
 EXECUTE sp_helpindex @object_name;

IF @@ROWCOUNT = 0
 RETURN;

DECLARE @index_name sysname = '';
DECLARE @col_order_reversed INT;
DECLARE @column_nameĀ  sysname;

--now get the INCLUDE keys
DECLARE include_field_cursor CURSOR FOR
SELECT
 ROW_NUMBER() OVER (PARTITION BY ic.index_id ORDER BY ic.index_id, ic.index_column_id DESC),
 i.name AS index_name,
 c.name AS column_name
FROM sys.indexes i INNER JOIN sys.index_columns ic
 ON ic.object_id = i.object_id
 AND ic.index_id = i.index_id
INNER JOIN sys.columns c
 ON c.object_id = ic.object_id
 AND c.column_id = ic.column_id
WHERE ic.is_included_column = 1
ORDER BY ic.index_column_id;

OPEN include_field_cursor;

FETCH NEXT FROM include_field_cursor
 INTO @col_order_reversed, @index_name, @column_name;

WHILE @@FETCH_STATUS = 0
BEGIN
 UPDATE #HelpIndexResults
 SET include_keys += CASE
 WHEN @col_order_reversed > 1 THEN @column_name + ', '
 WHEN @col_order_reversed = 1 THEN @column_name
 END
 WHERE index_name = @index_name;
 FETCH NEXT FROM include_field_cursor
 INTO @col_order_reversed, @index_name, @column_name;
END

CLOSE include_field_cursor;
DEALLOCATE include_field_cursor;

UPDATE #HelpIndexResults
SET include_keys = ''
WHERE include_keys = '';

SELECT index_name, index_description, index_keys, include_keys
FROM #HelpIndexResults
ORDER BY index_name;

SET NOCOUNT OFF;
GO
I would be interested to see any comments about getting this done in a set-base fashion.
Thanks for reading.

Panorama Theme by Themocracy