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;
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;
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