Query for researching for getting a quick overview of index usage and structure, at the same

 In researching how to tune up indexing, I was always switching back and forth between different screens and resultsets. There's two main sets of information that you need:

- For a particular table, how much are all the indexes being used?

- What is the structure of all the indexes on a table?

Having these two pieces of information at the same time can be really useful.

Here's a quick script that I put together to do that:

 -- Get index usage  
 if object_id('tempdb..#IndexUsage') is not null drop table tempdb..#IndexUsage   
 if object_id('tempdb..#IndexStructure') is not null drop table tempdb..#IndexStructure  
 Declare @TableName varchar(100) = 'Users'  
 SELECT i.name AS IndexName  
      ,ius.user_seeks AS Seeks  
      ,ius.user_scans AS Scans  
      ,ius.user_lookups AS Lookups  
 INTO #IndexUsage  
 FROM sys.indexes i  
 INNER JOIN sys.dm_db_index_usage_stats ius ON ius.index_id = i.index_id  
      AND ius.OBJECT_ID = i.OBJECT_ID  
 INNER JOIN (  
      SELECT sch.name  
           ,sch.schema_id  
           ,o.OBJECT_ID  
           ,o.create_date  
      FROM sys.schemas sch  
      INNER JOIN sys.objects o ON o.schema_id = sch.schema_id  
      ) s ON s.OBJECT_ID = i.OBJECT_ID  
 LEFT JOIN (  
      SELECT OBJECT_ID  
           ,index_id  
           ,SUM(used_page_count) AS usedpages  
           ,SUM(CASE   
                     WHEN (index_id < 2)  
                          THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
                     ELSE lob_used_page_count + row_overflow_used_page_count  
                     END) AS pages  
      FROM sys.dm_db_partition_stats  
      GROUP BY object_id  
           ,index_id  
      ) AS ps ON i.object_id = ps.object_id  
      AND i.index_id = ps.index_id  
 WHERE ius.database_id = DB_ID()  
      AND OBJECT_NAME(i.OBJECT_ID) = @TableName  
 ORDER BY i.name  
 -- Get index structure with included fields  
 SELECT   
   OBJECT_NAME(i.[object_id]) TableName ,  
   i.[name] IndexName ,  
   c.[name] ColumnName ,  
   ic.is_included_column ,  
   i.index_id ,  
   i.type_desc ,  
   i.is_unique ,  
   i.data_space_id ,  
   i.ignore_dup_key ,  
   i.is_primary_key ,  
   i.is_unique_constraint  
 INTO #IndexStructure  
 FROM   
   sys.indexes i  
   JOIN sys.index_columns ic ON ic.object_id = i.object_id and i.index_id = ic.index_id  
   JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id  
 Where   
   OBJECT_NAME(i.[object_id]) = @TableName  
 ORDER BY   
    tableName ,  
    ic.index_id ,  
    ic.index_column_id  
 -- Combine into one output   
 ;With   
   IndexStructure as   
     (Select Distinct  
       IndexName  
       , type_desc = case when type_desc = 'nonclustered' then 'no' else 'yes' end  
       , is_unique = case When is_unique = 0 then 'no' else 'yes' end  
       , is_primary_key = case When is_primary_key = 0 then 'no' else 'yes' end  
     From #IndexStructure   
     )  
   ,IndexedFields as   
     (select IndexName, IndexedFields = STRING_AGG (CONVERT(NVARCHAR(max),ColumnName), ', ')   
     from #IndexStructure Where is_included_column = 0 Group by IndexName)  
   ,IncludedFields as   
     (select IndexName, IncludedFields = STRING_AGG (CONVERT(NVARCHAR(max),ColumnName), ', ')   
     from #IndexStructure Where is_included_column = 1 Group by IndexName)  
 Select   
      IndexStructure.IndexName  
   ,IndexUsage.Seeks  
   ,IndexUsage.Scans  
   ,IndexUsage.Lookups  
      ,IsClustered = type_desc  
      ,IsUnique = is_unique  
      ,IsPrimaryKey = is_primary_key  
      ,IndexedFields.IndexedFields  
      ,IncludedFields = IsNull(IncludedFields.IncludedFields, '-')  
 From IndexStructure   
   JOIN #IndexUsage IndexUsage on IndexUsage.IndexName = IndexStructure .IndexName  
      Join IndexedFields on IndexStructure.IndexName = IndexedFields.IndexName  
      Left Join IncludedFields on IndexStructure.IndexName = IncludedFields.IndexName  
 -- Where Seeks < 500 and Scans < 500   
 Order by   
   Seeks asc