Extending sp_helpindex to get more useful information about index in SQL Server

This post is to help people who had tough time to get information about index and its included columns in SQL Server. Whenever I need, I always depend on object explorer and get the definition of the index.So I thought of compiling a snippet as below that can provide these information handy.

I created a procedure called – sp_helpindexExtended as below and the Tablename as parameter(this is an optional parameter).

Procedure Definition


Create Proc sp_helpindexExtended (@TableName sysname = NULL) 
as 
Begin 
	  ;With cte as 
	  (
		 Select Object_name(A.object_id) objectname,A.name index_name,
			Lower(type_desc) + Case When IS_Unique =1 then ', unique' Else '' end +
			Case when is_primary_key =1 Then ', primary key' Else '' End+
			Case when IS_Unique_Constraint = 1 Then ',unique key' Else '' End  index_description,
			Case when is_included_column=0 then c.name  + '('+
            Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End +')'  Else NULL end ColumnName,
            Case when is_included_column=1 then c.name  + '('+
            Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End +')'  Else NULL end IncludedColumnnames
            ,Is_Unique,Is_Primary_Key,IS_Unique_Constraint,Fill_factor,key_ordinal
    From sys.indexes A  
    Inner Join sys.index_columns B On A.object_id = B.object_id And A.index_id = B.index_id 
    Inner Join sys.columns C On c.object_id = B.object_id  And C.column_id  = B.column_id 
	Where A.Object_ID = Case when @TableName is not null Then OBJECT_ID(@TableName) Else A.object_id End
	)	SELECT objectname,index_name,index_description,
       stuff(( SELECT ','+ColumnName AS [text()] FROM cte p2
          WHERE p2.objectname = p1.objectname and p2.index_name = p1.index_name
          ORDER BY key_ordinal asc FOR XML PATH('') ), 1, 1,'') AS index_keys,
		stuff( ( SELECT IsNull(','+IncludedColumnnames ,'')  FROM cte p2
          WHERE p2.objectname = p1.objectname and p2.index_name = p1.index_name
          ORDER BY key_ordinal asc FOR XML PATH('') ), 1, 1,'')  AS included_keys
		  ,fill_factor 
      FROM cte p1 GROUP BY objectname,index_name ,index_description,fill_factor 
	 order by objectname asc
End

Procedure Usage

-- To get for all tables
exec sp_helpindexextended
-- To get only for a table
exec sp_helpindexextended 'tablename'
Advertisements

How to identify Obsolete/unused Indexes in SQL Server

What is an obsolete Index?

Obsolete or Unused Index is an index without any seeks/scans or lookups (applicable only for Clustered).So if the sum of all seeks+scans+lookups is zero, that is an indication the index is not being used for any read access. These indexes will be an overhead in your application as those needs to be updated and maintained unnecessarily for any change in the key columns values.

Here is a very simple script to identify obsolete indexes in your environment.

Note: The script validates the below points:

1. Indexes with 0 read access (seek+scan+lookups)
2. Only applicable to non clustered indexes
3. Those indexes should not be part of PRIMARY or UNIQUE constraints


;WITH INDEXUSAGESTATS AS
(
	SELECT  
			I.OBJECT_ID,  OBJECT_NAME(I.OBJECT_ID) AS TABLE_NAME,  I.INDEX_ID,
			SUM(I.USER_SEEKS) AS SEEKS,  SUM(I.USER_SCANS) AS SCANS,  SUM(I.USER_LOOKUPS) AS LOOKUPS, SUM(I.USER_UPDATES) AS WRITES
	FROM SYS.TABLES T
	INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS I  ON T.OBJECT_ID = I.OBJECT_ID
	GROUP BY
			I.OBJECT_ID, I.INDEX_ID
 )
SELECT
		'DROP INDEX ' + QUOTENAME(IUS.TABLE_NAME) + '.' + QUOTENAME(I.NAME) AS DROPINDEXSTATEMENT,
		IUS.TABLE_NAME AS TABLENAME, I.NAME AS INDEXNAME, I.TYPE_DESC AS INDEXTYPE,
		IUS.SEEKS AS SEEKS, IUS.SCANS AS SCANS, IUS.LOOKUPS AS LOOKUPS, IUS.WRITES  AS TOTALWRITES,
		IUS.SEEKS + IUS.SCANS + IUS.LOOKUPS AS TOTALACCESSES
FROM INDEXUSAGESTATS AS IUS
INNER JOIN SYS.INDEXES I 	ON IUS.OBJECT_ID = I.OBJECT_ID  AND IUS.INDEX_ID = I.INDEX_ID
INNER JOIN SYS.TABLES B ON I.OBJECT_ID = B.OBJECT_ID
WHERE 
		/*OBSOLETE INDEX IS THOSE WITHOUT ANY SEEKS, SCANS and LOOKUPS*/
		IUS.SEEKS + IUS.SCANS + IUS.LOOKUPS = 0 
		/*QUERY EVALUATES ONLY NONCLUSTERED INDEXES THAT CAN NIETHER BE PRIMARY OR UNIQUE KEY*/
		AND I.TYPE_DESC = 'NONCLUSTERED'  AND I.IS_PRIMARY_KEY = 0  AND I.IS_UNIQUE = 0 
ORDER BY IUS.TABLE_NAME, I.NAME