SQL Query to Find Unused Indexes

Introduction
T-SQL Query to Find Unread Indexes
T-SQL Query to Find Unused Indexes
Benefits of Identifying Unused Indexes
Conclusion

Introduction

In SQL Server, indexes play a crucial role in optimizing query performance. However, maintaining unused indexes can incur unnecessary overhead and storage costs. To identify and remove these unused indexes, SQL Server provides a powerful query that lists indexes never chosen for use by the query optimizer. In this blog post, we'll explore how to use this query effectively to enhance SQL Server performance.

T-SQL Query to Find Unread Indexes

The following T-SQL provides list of indexes which have never been choosen for use by the query optimizer since the service was started. The longer the server has been up, the more valuable this information is. These indexes are being maintained but are not being used.

use dbdocs; --Replace with your Database Name

select
    db_name(dius.database_id) as database_name,
    object_name(dius.object_id, dius.database_id) as table_name,
    i.name as index_name,
    i.type_desc as index_type,
    dius.user_seeks + dius.user_scans + dius.user_lookups as user_reads,
    dius.user_updates as user_writes
from
    sys.dm_db_index_usage_stats dius
    inner join
    sys.indexes i
        on dius.object_id = i.object_id and dius.index_id = i.index_id
where
    dius.database_id = db_id()
and
    objectproperty(dius.object_id, 'IsUserTable') = 1
and
    i.index_id <> 0
and
    dius.user_seeks + dius.user_scans + dius.user_lookups = 0
order by
    table_name,
    index_name;

unread indexes

T-SQL Query to Find Unused Indexes

The Following T-SQL provides list of indexes that have never been written to or read from since last service restart. The longer the server has been up, the more valuable this information is. This also indicates the underlying table has not been used.

use dbdocs; --Replace with your Database Name 

select
    object_name(i.object_id) as table_name,
    i.name as index_name,
    i.type_desc as index_type
from
    sys.indexes i
    inner join
    sys.objects o
        on i.object_id = o.object_id
where
    not exists
    (
        select
            dius.index_id
        from
            sys.dm_db_index_usage_stats dius
        where
            dius.object_id = i.object_id
        and
            dius.index_id = i.index_id
        and
            dius.database_id = db_id()
    )
and
    o.type = 'U'
and
    i.index_id <> 0
order by
    table_name,
    index_name;

unused-indexes

Benefits of Identifying Unused Indexes

  • Improved Performanc: Removing unused indexes reduces overhead during query optimization and improves overall database performance.
  • Resource Optimization: Eliminating unused indexes frees up storage space and reduces maintenance overhead.
  • Cost Reduction: By eliminating unnecessary indexes, organizations can reduce storage costs associated with maintaining unused indexes.
  • Simplified Management: Keeping a lean index structure simplifies index maintenance and management tasks.

Conclusion

Identifying and removing unused indexes is essential for optimizing SQL Server performance and resource utilization. By regularly running queries like the one provided in this blog post, database administrators can identify redundant indexes and take appropriate actions to improve database performance, reduce costs, and streamline database management.

Related content



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access