Indexing DMVs

Are your indexes healthy, used, or missing? This collection covers five DMVs. The operational-stats query shows leaf insert/update/delete counts per index; the two usage-stats queries show seeks/scans/lookups/updates - one plain, one pivoted so each index's key columns line up side by side for quick eyeballing; the fragmentation section runs sys.dm_db_index_physical_stats at all three modes (Limited, Sampled, Detailed) plus a sorted report of the worst offenders; and the final query joins the three missing-index DMVs to surface the optimizer's index suggestions with their expected benefit.

/*
sys.dm_db_index_operational_stats
sys.dm_db_index_usage_stats
sys.dm_db_index_physical_stats 
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
*/



-------------------------------
-- Get index operational stats - tip 1239
-------------------------------
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1


-- Get index usage stats - tip 1239
-------------------------------
SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         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 



-------------------------------
-- Get index usage stats - tip 1545
-------------------------------
SELECT db_name(), PVT.SCHEMANAME, PVT.TABLENAME, PVT.INDEXNAME, PVT.INDEX_ID, [1] AS COL1, [2] AS COL2, [3] AS COL3, 
       [4] AS COL4,  [5] AS COL5, [6] AS COL6, [7] AS COL7, B.USER_SEEKS, 
       B.USER_SCANS, B.USER_LOOKUPS, b.user_updates , C.ROWS 
FROM   (SELECT A.NAME AS TABLENAME, SCHEMA_NAME (a.schema_id) as SCHEMANAME,
               A.OBJECT_ID, 
               B.NAME AS INDEXNAME, 
               B.INDEX_ID, 
               D.NAME AS COLUMNNAME, 
               C.KEY_ORDINAL
        FROM   SYS.OBJECTS A 
               INNER JOIN SYS.INDEXES B 
                 ON A.OBJECT_ID = B.OBJECT_ID 
               LEFT JOIN SYS.INDEX_COLUMNS C 
                 ON B.OBJECT_ID = C.OBJECT_ID 
                    AND B.INDEX_ID = C.INDEX_ID 
               LEFT JOIN SYS.COLUMNS D 
                 ON C.OBJECT_ID = D.OBJECT_ID 
                    AND C.COLUMN_ID = D.COLUMN_ID 
        WHERE  A.TYPE = 'U') P 
       PIVOT 
       (MIN(COLUMNNAME) 
        FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT 
       LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS B 
         ON PVT.OBJECT_ID = B.OBJECT_ID 
            AND PVT.INDEX_ID = B.INDEX_ID 
            AND B.DATABASE_ID = DB_ID() 
	   LEFT JOIN SYS.SYSINDEXES C
		 ON PVT.OBJECT_ID = C.ID
			AND PVT.INDEX_ID = C.INDID
				
				
-------------------------------
-- Index fragmentation stats - tip 2979
-------------------------------		
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'Limited')

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'Sampled')

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'Detailed')


SELECT DB_NAME(DATABASE_ID) AS 'DatabaseName',
       OBJECT_NAME([OBJECT_ID]) AS 'TableName',
       INDEX_ID,
       CAST(INDEX_TYPE_DESC AS VARCHAR(20)) AS INDEX_TYPE_DESC,
       AVG_FRAGMENTATION_IN_PERCENT, page_count, index_type_desc, fragment_count, avg_fragment_size_in_pages
FROM   SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL )
ORDER BY avg_fragmentation_in_percent desc, page_count desc
				
------------
-- Missing index stats - tip 2979
-------------------------------
SELECT
   @@SERVERNAME AS [ServerName]
   , DB_NAME() AS [DatabaseName]
   , SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]
   , [sObj].[name] AS [ObjectName]
   , CASE [sObj].[type]
      WHEN 'U' THEN 'Table'
      WHEN 'V' THEN 'View'
      ELSE 'Unknown'
     END AS [ObjectType]
   , [sdmvMID].[equality_columns] AS [EqualityColumns]
   , [sdmvMID].[inequality_columns] AS [InequalityColumns]
   , [sdmvMID].[included_columns] AS [IncludedColumns]
   , [sdmvMIGS].[user_seeks] AS [ExpectedIndexSeeksByUserQueries]
   , [sdmvMIGS].[user_scans] AS [ExpectedIndexScansByUserQueries]
   , [sdmvMIGS].[last_user_seek] AS [ExpectedLastIndexSeekByUserQueries]
   , [sdmvMIGS].[last_user_scan] AS [ExpectedLastIndexScanByUserQueries]
   , [sdmvMIGS].[avg_total_user_cost] AS [ExpectedAvgUserQueriesCostReduction]
   , [sdmvMIGS].[avg_user_impact] AS [ExpectedAvgUserQueriesBenefitPct]
FROM 
   [sys].[dm_db_missing_index_details] AS [sdmvMID]
   LEFT JOIN [sys].[dm_db_missing_index_groups] AS [sdmvMIG]
      ON [sdmvMID].[index_handle] = [sdmvMIG].[index_handle]
   LEFT JOIN [sys].[dm_db_missing_index_group_stats] AS [sdmvMIGS]
      ON [sdmvMIG].[index_group_handle] = [sdmvMIGS].[group_handle]
   INNER JOIN [sys].[objects] AS [sObj]
      ON [sdmvMID].[object_id] = [sObj].[object_id]
WHERE
   [sdmvMID].[database_id] = DB_ID()  -- Look in the Current Database
   AND [sObj].[type] IN ('U','V')     -- Look in Tables & Views
   AND [sObj].[is_ms_shipped] = 0x0   -- Exclude System Generated Objects