mssqltips logo

Querying SQL Server Index Statistics

By:   |   Updated: 2013-06-21   |   Comments (11)   |   Related: More > 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]

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


Last Updated: 2013-06-21


get scripts

next tip button



About the author
MSSQLTips author Dattatrey Sindol Datta has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, October 07, 2014 - 1:11:08 PM - Vikas MS Back To Top

This link really helped me in getting the information on indexes usage. Thanks a lot. 


Tuesday, August 05, 2014 - 7:11:54 PM - Dmitri Back To Top

The article is great, but you definitely incorrectly calculate the index sizes. Please make sure it is really in KB, but not in some other units so we need to divide the wize by 64 or something.

 

 

 


Thursday, January 30, 2014 - 3:24:55 AM - Andy Back To Top

Is there a QUERY TO FIND STATUS OF THE INDEXES OF A TABLE WITHOUT USING SYSTEM OBJECTS QUERY?


Tuesday, January 07, 2014 - 4:44:15 PM - Matt Back To Top

I think you need to add a join condition to the last left join in the Physical Statistics query:

and ISNULL([sPtn].[partition_number], 1) = isnull([sdmfIPS].[partition_number], 1)

Thank you for the very useful post.


Monday, July 01, 2013 - 2:35:16 PM - Ed Lyons Back To Top

I am running the queries on SQL Server 2005 database.  Seems that the PIVOT feature is not supported on those that include that command as well as a syntax error with the missing "(" character.  Not sure why you don't experience the same problems.  Thank you for getting back to me.  Ed


Monday, July 01, 2013 - 12:49:54 PM - Dattatrey Sindol (Datta) Back To Top

Hi Ed Lyons,

 

After reading your comment, I re-ran all the queries in the above tip in my local environment and all are running fine. Please note that these queries have been tested with SQL Server 2012 and should work with 2005, 2008, and 2008 R2 with minimal to no changes.

 

Please let me know on which version of SQL Server you are running these queries.

 

Best Regards,

Dattatrey Sindol (Datta)


Monday, July 01, 2013 - 12:46:58 PM - Dattatrey Sindol (Datta) Back To Top

Hi Uwe,

 

The query that I have given in the above tip is pretty much same as what you mentioned. In the tip, if there is no record for any index in the "[sys].[dm_db_index_usage_stats]" DMV then that index is treated as unused.

 

Hope that answers your question.

 

Best Regards,

Dattatrey Sindol (Datta)


Monday, July 01, 2013 - 11:20:53 AM - Ed Lyons Back To Top

I am experiencing errors in your TSQL code when I run it in Query Manager. 

Error in line 42 missing a "(" in Get Existing Usage Data routine

Existing Indexes Physical Statistics gives an error as well.  Would you please insure that all of these routines run properly before posting them.  Thanks.

 


Saturday, June 29, 2013 - 3:06:40 AM - Uwe Ricken Back To Top

Hallo Datta,

good tips - but for the unused indexes you should consider indexes as unused which have no indexe_seeks, index_scans and index_lookups

SELECT*FROMsys.dm_db_index_usage_stats
WHEREuser_seeks= 0 AND
user_scans= 0 AND
user_lookups= 0

 

As you have mentioned in your post - the above query is not recommended directly after a restart of the server because theses statistiscs will be removed from the dmv after each restart. If a system is up and running for several weeks THIS is an indicator whether an index is in use or useless :)


Friday, June 21, 2013 - 11:50:13 AM - Gopal Back To Top

Excellent one. Thank you Datta.


Friday, June 21, 2013 - 8:24:40 AM - BalaKrishna Back To Top

nice info thank you



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools