Querying SQL Server Index Statistics

By: Dattatrey Sindol | Updated: 2013-06-21 | Comments (11) | Related: > Indexing

Problem

Indexes are a great way to improve the performance of queries. However, they require regular monitoring and maintenance to ensure that the system continues to function smoothly. If indexes are not monitored regularly and appropriate action not taken then it can have a negative impact on the system's performance. Fortunately SQL Server has a feature which makes it easy for us to monitor the indexes and do some analysis on their utilization. In this tip we will take a look at different ways to get information about SQL Server indexes.

Solution

SQL Server has various built-in features to assist developers and administrators in managing SQL Server. Among these features are Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). We will refer to these collectively as DMVs for the rest of this tip.

SQL Server exposes a set of information through DMVs and one set of information is SQL Server Index Statistics. We will use several DMVs and System Tables/Catalog Views to get important index statistics from a SQL Server database.

In this tip we will retrieve the following index related information and statistics by querying DMVs and System Tables/Catalog Views:

Existing Index Information
Existing Index Physical Statistics
Existing Index Usage Statistics
Missing Index Information
Unused Index Information

Existing Index Information

To start with, we will retrieve the information about existing indexes like Index ID/Name, Indexed Columns, and Parent Objects (Tables/Views) etc.

SELECT
   @@SERVERNAME AS [ServerName]
   , DB_NAME() AS [DatabaseName]
   , [SchemaName]
   , [ObjectName]
   , [ObjectType]
   , [IndexID]
   , [IndexName]
   , [IndexType]
   , COALESCE([0],[1],'') AS [Column1]
   , ISNULL([2],'') AS [Column2]
   , ISNULL([3],'') AS [Column3]
   , ISNULL([4],'') AS [Column4]
   , ISNULL([5],'') AS [Column5]
   , ISNULL([6],'') AS [Column6]
   , ISNULL([7],'') AS [Column7]
   , ISNULL([8],'') AS [Column8]
   , ISNULL([9],'') AS [Column9]
   , ISNULL([10],'') AS [Column10]
   , CASE 
      WHEN [IsIncludedColumn] = 0x1 THEN 'Yes'
      WHEN [IsIncludedColumn] = 0x0 THEN 'No'
      WHEN [IsIncludedColumn] IS NULL THEN 'N/A'
     END AS [IsCoveringIndex]
   , [IsDisabled]
FROM (
   SELECT
      SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]
      , [sObj].[name] AS [ObjectName]
      , CASE
         WHEN [sObj].[type] = 'U' THEN 'Table'
         WHEN [sObj].[type] = 'V' THEN 'View'
         END AS [ObjectType]
      , [sIdx].[index_id] AS [IndexID]  -- 0: Heap; 1: Clustered Idx; > 1: Nonclustered Idx;
      , ISNULL([sIdx].[name], 'N/A') AS [IndexName]
      , CASE
         WHEN [sIdx].[type] = 0 THEN 'Heap'
         WHEN [sIdx].[type] = 1 THEN 'Clustered'
         WHEN [sIdx].[type] = 2 THEN 'Nonclustered'
         WHEN [sIdx].[type] = 3 THEN 'XML'
         WHEN [sIdx].[type] = 4 THEN 'Spatial'
         WHEN [sIdx].[type] = 5 THEN 'Reserved for future use'
         WHEN [sIdx].[type] = 6 THEN 'Nonclustered columnstore index'
        END AS [IndexType]
      , [sCol].[name] AS [ColumnName]
   , [sIdxCol].[is_included_column] AS [IsIncludedColumn]
      , [sIdxCol].[key_ordinal] AS [KeyOrdinal]
      , [sIdx].[is_disabled] AS [IsDisabled]
   FROM 
      [sys].[indexes] AS [sIdx]
      INNER JOIN [sys].[objects] AS [sObj]
         ON [sIdx].[object_id] = [sObj].[object_id]
      LEFT JOIN [sys].[index_columns] AS [sIdxCol]
         ON [sIdx].[object_id] = [sIdxCol].[object_id]
         AND [sIdx].[index_id] = [sIdxCol].[index_id]
      LEFT JOIN [sys].[columns] AS [sCol]
         ON [sIdxCol].[object_id] = [sCol].[object_id]
         AND [sIdxCol].[column_id] = [sCol].[column_id]
   WHERE
      [sObj].[type] IN ('U','V')      -- Look in Tables & Views
      AND [sObj].[is_ms_shipped] = 0x0  -- Exclude System Generated Objects
) AS [UnpivotedData]
PIVOT 
(
   MIN([ColumnName])
   FOR [KeyOrdinal] IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) AS [ColumnPivot]