Querying SQL Server Index Statistics

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].
 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]

Following is a brief description of each of the fields returned from the above query:

• [ServerName]: Server from which Statistics are being retrieved.
• [DatabaseName]: Database from which Statistics are being retrieved.
• [SchemaName]: Schema of the object (Parent) containing the index.
• [ObjectName]: Name of the object containing the index.
• [ObjectType]: Type of object containing the index (Table/View).
• [IndexID]: Unique identifier for an index. This identifier is unique within an object.
• [IndexName]: Name of the Index.
• [IndexType]: Type of Index like Clustered, Non-clustered etc.
• [Column1]: 1st column of the index. This is mandatory as index will contain at least one column (Except for an entry for Heaps).
• [Column2]: 2nd column of the index. This is an optional column as index can contain more than one column.
• [Column3]: 3rd column of the index. This is an optional column as index can contain more than one column.
• [Column4]: 4th column of the index. This is an optional column as index can contain more than one column.
• [Column5]: 5th column of the index. This is an optional column as index can contain more than one column.
• [Column6]: 6th column of the index. This is an optional column as index can contain more than one column.
• [Column7]: 7th column of the index. This is an optional column as index can contain more than one column.
• [Column8]: 8th column of the index. This is an optional column as index can contain more than one column.
• [Column9]: 9th column of the index. This is an optional column as index can contain more than one column.
• [Column10]: 10th column of the index. This is an optional column as index can contain more than one column.
• [IsCoveringIndex]: Indicates whether the Index is a Covering Index. A Covering Index is a Non-clustered Index which can satisfy all the columns requested in a query without doing a lookup to the Clustered Index.
• [IsDisabled]: Indicates whether an index is disabled or not. Indexes can be enabled or disabled depending upon the need.

Covering Index Information

In the previous query, we saw that there is a flag which indicates whether an Index is a Covering Index or not. Now let’s query the details of a Covering Index. For this query example, let’s query the information related to “IX_ProductReview_ProductID_Name” index in “Production.ProductReview” table using the below query.

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]
   , CASE 
  WHEN [sIdxCol].[is_included_column] = 0x1 THEN 'Yes'
  WHEN [sIdxCol].[is_included_column] = 0x0 THEN 'No'
  WHEN [sIdxCol].[is_included_column] IS NULL THEN 'N/A'
  END AS [IsIncludedColumn]
    , [sIdxCol].[key_ordinal] AS [KeyOrdinal]
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].
 AS [sCol]
        ON [sIdxCol].[object_id] = [sCol].[object_id]
        AND [sIdxCol].[column_id] = [sCol].[column_id]
WHERE
    SCHEMA_NAME([sObj].[schema_id]) = 'Production'
 AND [sObj].[name] = 'ProductReview'
    AND [sIdx].[name] = 'IX_ProductReview_ProductID_Name'

Following is a brief description of each of the fields returned from the above query:

• [SchemaName]: Schema of the object (Parent) containing the index.
• [ObjectName]: Name of the object containing the index.
• [ObjectType]: Type of object containing the index (Table/View).
• [IndexID]: Unique identifier for an index. This identifier is unique within an object.
• [IndexName]: Name of the Index.
• [IndexType]: Type of Index like Clustered, Non-clustered etc.
• [ColumnName]: Name of the column which is part of the Index.
• [IsIncludedColumn]: Indicates whether the column is an Included Column. To eliminate the lookups in the query and satisfy all the columns requested in the query, additional columns are included in the Non-clustered Index using the INCLUDE clause.
• [KeyOrdinal]: Position of the column within the Index.

Existing Indexes Physical Statistics

Next let’s query the physical statistics of an index like Size, Percentage Fragmentation etc. using the below query.

SELECT
   @@SERVERNAME AS [ServerName]
   , DB_NAME() AS [DatabaseName]
   , 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]
   , 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]
   , ISNULL([sPtn].[partition_number], 1) AS [PartitionNumber]
   , [sdmfIPS].[alloc_unit_type_desc] AS [IndexAllocationUnitType]
   , [IdxSizeDetails].[IndexSizeInKB]
   , [sIdx].[fill_factor] AS [FillFactor]
   , CAST([sdmfIPS].[avg_fragmentation_in_percent] AS NUMERIC(5,2)) AS [AvgPctFrag]
FROM 
   [sys].[indexes] AS [sIdx]
   INNER JOIN [sys].[objects] AS [sObj]
      ON [sIdx].[object_id] = [sObj].[object_id]
   LEFT JOIN  [sys].[partitions] AS [sPtn]
      ON [sIdx].[object_id] = [sPtn].[object_id]
      AND [sIdx].[index_id] = [sPtn].[index_id]
   LEFT JOIN (
            SELECT
               [sIdx].[object_id]
               , [sIdx].[index_id]
               , SUM([sAU].[used_pages]) * 8 AS [IndexSizeInKB]
            FROM 
               [sys].[indexes] AS [sIdx]
               INNER JOIN [sys].[partitions] AS [sPtn]
                  ON [sIdx].[object_id] = [sPtn].[object_id]
                  AND [sIdx].[index_id] = [sPtn].[index_id]
               INNER JOIN [sys].[allocation_units] AS [sAU]
                  ON [sPtn].[partition_id] = [sAU].[container_id]
            GROUP BY [sIdx].[object_id], [sIdx].[index_id]
   ) [IdxSizeDetails]
      ON [sIdx].[object_id] = [IdxSizeDetails].[object_id]
      AND [sIdx].[index_id] = [IdxSizeDetails].[index_id]
   LEFT JOIN [sys].[dm_db_index_physical_stats] (DB_ID(),NULL,NULL,NULL,'LIMITED') [sdmfIPS]
      ON [sIdx].[object_id] = [sdmfIPS].[object_id]
      AND [sIdx].[index_id] = [sdmfIPS].[index_id]
      AND [sdmfIPS].[database_id] = DB_ID()
WHERE
   [sObj].[type] IN ('U','V')         -- Look in Tables & Views
   AND [sObj].[is_ms_shipped] = 0x0   -- Exclude System Generated Objects
   AND [sIdx].[is_disabled] = 0x0     -- Exclude Disabled Indexes

Following is a brief description of each of the fields returned from the above query:

• [ServerName]: Server from which Statistics are being retrieved.
• [DatabaseName]: Database from which Statistics are being retrieved.
• [SchemaName]: Schema of the object (Parent) containing the index.
• [ObjectName]: Name of the object containing the index.
• [ObjectType]: Type of object containing the index (Table/View).
• [IndexID]: Unique identifier for an index. This identifier is unique within an object.
• [IndexName]: Name of the Index.
• [IndexType]: Type of Index like Clustered, Non-clustered etc.
• [PartitionNumber]: Indicates the Partition Number.
• [IndexAllocationUnitType]: Indicates the storage allocation unit type. In SQL Server, Tables and Indexes are stored as a collection of 8KB pages. There are three types of allocation units – IN_ROW_DATA, LOB_DATA, and ROW_OVERFLOW_DATA. IN_ROW_DATA is used to manage the index (or data) rows containing all the data with the exception of Large Object (LOB) Data. LOB_DATA is used to manage Large Object (LOB) Data like Text, Image, and XML etc. ROW_OVERFLOW_DATA is used to manage variable length data stored in VARCHAR, NVARCHAR, VARBINARY, AND SQL_VARIANT data types which exceed 8060 byte row size limit.
• [IndexSizeInKB]: Current size of Index in Kilobytes (KBs).
• [FillFactor]: Fill Factor Percentage associated with the Index.
• [AvgPctFrag]: Represents the Percentage Fragmentation for Indexes (Logical Fragmentation) and Heaps (Extent Fragmentation). Lesser the fragmentation percentage the better the health of the index. When fragmentation increases, indexes need to be reorganized or rebuilt to bring the fragmentation percentage back into an acceptable range.

Existing Indexes Usage Statistics

Next let’s get the Usage Statistics of Indexes from a SQL Server Database. This information is useful in understanding how effectively the existing indexes are being used.

SELECT
   @@SERVERNAME AS [ServerName]
   , DB_NAME() AS [DatabaseName]
   , 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]
   , 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]
   , [sdmvIUS].[user_seeks] AS [TotalUserSeeks]
   , [sdmvIUS].[user_scans] AS [TotalUserScans]
   , [sdmvIUS].[user_lookups] AS [TotalUserLookups]
   , [sdmvIUS].[user_updates] AS [TotalUserUpdates]
   , [sdmvIUS].[last_user_seek] AS [LastUserSeek]
   , [sdmvIUS].[last_user_scan] AS [LastUserScan]
   , [sdmvIUS].[last_user_lookup] AS [LastUserLookup]
   , [sdmvIUS].[last_user_update] AS [LastUserUpdate]
   , [sdmfIOPS].[leaf_insert_count] AS [LeafLevelInsertCount]
   , [sdmfIOPS].[leaf_update_count] AS [LeafLevelUpdateCount]
   , [sdmfIOPS].[leaf_delete_count] AS [LeafLevelDeleteCount]
FROM
   [sys].[indexes] AS [sIdx]
   INNER JOIN [sys].[objects] AS [sObj]
      ON [sIdx].[object_id] = [sObj].[object_id]
   LEFT JOIN [sys].[dm_db_index_usage_stats] AS [sdmvIUS]
      ON [sIdx].[object_id] = [sdmvIUS].[object_id]
      AND [sIdx].[index_id] = [sdmvIUS].[index_id]
      AND [sdmvIUS].[database_id] = DB_ID()
   LEFT JOIN [sys].[dm_db_index_operational_stats] (DB_ID(),NULL,NULL,NULL) AS [sdmfIOPS]
      ON [sIdx].[object_id] = [sdmfIOPS].[object_id]
      AND [sIdx].[index_id] = [sdmfIOPS].[index_id]
WHERE
   [sObj].[type] IN ('U','V')         -- Look in Tables & Views
   AND [sObj].[is_ms_shipped] = 0x0   -- Exclude System Generated Objects
   AND [sIdx].[is_disabled] = 0x0     -- Exclude Disabled Indexes

Following is a brief description of each of the fields returned from the above query:

• [ServerName]: Server from which Statistics are being retrieved.
• [DatabaseName]: Database from which Statistics are being retrieved.
• [SchemaName]: Schema of the object (Parent) containing the index.
• [ObjectName]: Name of the object containing the index.
• [ObjectType]: Type of object containing the index (Table/View).
• [IndexID]: Unique identifier for an index. This identifier is unique within an object.
• [IndexName]: Name of the Index.
• [IndexType]: Type of Index like Clustered, Non-clustered etc.
• [TotalUserSeeks]: Number of Seeks by User Queries.
• [TotalUserScans]: Number of Scans by User Queries.
• [TotalUserLookups]: Number of Bookmark Lookups by User Queries.
• [TotalUserUpdates]: Number of Updates by User Queries.
• [LastUserSeek]: Date and Time of the Last User Seek.
• [LastUserScan]: Date and Time of the Last User Scan.
• [LastUserLookup]: Date and Time of Last User Lookup.
• [LastUserUpdate]: Date and Time of Last User Update.
• [LeafLevelInsertCount]: Cumulative count of data Insertions at the Leaf-Level.
• [LeafLevelUpdateCount]: Cumulative count of data Updates at the Leaf-Level.
• [LeafLevelDeleteCount]: Cumulative count of data Deletions at the Leaf-Level.

Indexes are organized as a B-Tree structure. [LeafLevelInsertCount], [LeafLevelUpdateCount], and [LeafLevelDeleteCount] refers to the count of inserts, updates, and deletes occurring at the leaf level of the B-Tree respectively.

Missing Index Information

During the life cycle of a database/application, many objects are added/updated/removed from the database. Also, the querying pattern changes over a period of time as the business needs change and new requirements and queries are introduced. As a result of these changes, new indexes might be required in the database and some of the existing indexes might be outdated (of no use). In this section let’s query information about missing indexes.

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

Following is a brief description of each of the fields returned from the above query:

• [ServerName]: Server from which Statistics are being retrieved.
• [DatabaseName]: Database from which Statistics are being retrieved.
• [SchemaName]: Schema of the object (Parent) containing the index.
• [ObjectName]: Name of the object containing the index.
• [ObjectType]: Type of object containing the index (Table/View).
• [EqualityColumns]: Comma-separated list of columns that contribute to the equality predicates (Usually JOIN Condition)
• [InequalityColumns]: Comma-separated list of columns that contribute to the inequality predicates, other than “=” operator (Usually WHERE clauses with “>”, “<” etc. operators).
• [IncludedColumns]: Comma-separated list of columns that are required as covering columns for the query. These are possible candidates for columns in covering indexes.
• [ExpectedIndexSeeksByUserQueries]: Recommended index could have been used for these many number of Seeks caused by the User Queries.
• [ExpectedIndexScansByUserQueries]: Recommended index could have been used for these many number of Scans caused by the User Queries.
• [ExpectedLastIndexSeekByUserQueries]: Date and Time of the Last Seek that would have been caused by User Queries if the recommended index was present.
• [ExpectedLastIndexScanByUserQueries]: Date and Time of the Last Scan that would have been caused by User Queries if the recommended index was present.
• [ExpectedAvgUserQueriesCostReduction]: Average cost of the User Queries that could be reduced by recommended index.
• [ExpectedAvgUserQueriesBenefitPct]: Average Percentage Benefit that the User Queries can get if the recommended index is created.

Unused Index Information

In the previous section, we queried the missing index information. In this section, let’s get the list of unused indexes.

SELECT
   @@SERVERNAME AS [ServerName]
   , DB_NAME() AS [DatabaseName]
   , 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]
   , 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]
FROM
   [sys].[indexes] AS [sIdx]
   INNER JOIN [sys].[objects] AS [sObj]
      ON [sIdx].[object_id] = [sObj].[object_id]
WHERE
   NOT EXISTS (
               SELECT *
               FROM [sys].[dm_db_index_usage_stats] AS [sdmfIUS]
               WHERE 
                  [sIdx].[object_id] = [sdmfIUS].[object_id]
                  AND [sIdx].[index_id] = [sdmfIUS].[index_id]
                  AND [sdmfIUS].[database_id] = DB_ID()
            )
   AND [sObj].[type] IN ('U','V')     -- Look in Tables & Views
   AND [sObj].[is_ms_shipped] = 0x0   -- Exclude System Generated Objects
   AND [sIdx].[is_disabled] = 0x0     -- Exclude Disabled Indexes

Following is a brief description of each of the fields returned from the above query:

• [ServerName]: Server from which Statistics are being retrieved.
• [DatabaseName]: Database from which Statistics are being retrieved.
• [SchemaName]: Schema of the object (Parent) containing the index.
• [ObjectName]: Name of the object containing the Unused index.
• [ObjectType]: Type of object containing the Unused index (Table/View).
• [IndexID]: Unique identifier for an index. This identifier is unique within an object.
• [IndexName]: Name of the Unused Index.
• [IndexType]: Type of Unused Index like Clustered, Non-clustered etc.

Please note the following with references to the queries/approaches suggested above:

• These queries should be run on each individual user database from which index statistics need to be retrieved.
• Most of the DMVs are reset when SQL Server is restarted. Meaning, the contents of most of the DMVs are based on the activities that have taken place since last SQL Server restart. Hence the statistics returned by these DMVs may not be always reliable depending upon when the SQL Server was restarted. Refer to the MSDN KB Articles on DMVs to know which DMVs are reset and which are not, when SQL Server is restarted.
• Statistics returned from the above queries are indicative only and can help you gain some understanding about your indexes, and are a good starting point for further analysis on indexes. Before making any index related changes (Creating, Modifying, and Deleting Indexes etc.), do some analysis on each of the indexes and based on your analysis, take appropriate action.
• Above queries have been tested with SQL Server 2012. However, these queries should work with SQL Server 2005, 2008, and 2008 R2 with minimal or no modifications.

Next Steps

• Review the scripts in this tip and start extending them to meet your analysis/monitoring/documentation requirements.
• Refer to the MSDN KB Article for each of the Dynamic Management Views/Functions and System Tables/Catalog Views used in this tip and determine what additional information can be retrieved as part of the above queries.
• Check out other SQL Server Scripts related tips