Index Usage Statistics in SQL Server

Introduction
Index usage statistics in sql server with T-SQL
T-SQL description
Conclusion

Introduction

In the world of database management, keeping your databases running smoothly and efficiently is crucial. One essential aspect of this is understanding how your indexes are being used. Luckily, SQL Server provides a way to gather statistics on index usage, offering valuable insights into your database's performance.

Index usage statistics in sql server with T-SQL

This SQL query retrieves information from the sys.dm_db_index_usage_stats and sys.indexes dynamic management views, providing statistis on the usage of indexes..

use dbdocs; --Replace this with your DBNAME
select
    db_name(dius.database_id) as database_name,
    dius.database_id,
    object_name(dius.object_id, dius.database_id) as table_name,
    dius.object_id as table_id,
    i.name as index_name,
    dius.index_id,
    i.type_desc as index_type,
    i.is_unique,
    dius.user_seeks,
    dius.user_scans,
    dius.user_lookups,
    dius.user_seeks + dius.user_scans + dius.user_lookups as user_reads,
    dius.user_updates as user_writes,
    dius.last_user_seek,
    dius.last_user_scan,
    dius.last_user_lookup,
    dius.last_user_update
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
    i.index_id <> 0
order by
    table_name,
    index_name;

Index Usage Stats

T-SQL description

Now, let's break down each component and understand its significance

Column           | Description
---------------- | ------------------------------------------------------------
database_name    | Database name.
database_id      | ID of the database.
table_name       | Table name.
table_id         | ID of the table.
index_name       | Index name.
index_id         | ID of the index.
index_type       | Type of index, CLUSTERED or NONCLUSTERED.
is_unique        | Is the index unique (1) or not (0).
user_seeks       | Number of seeks by user queries.
user_scans       | Number of leaf node scans by user queries.
user_lookups     | Number of clustered index bookmark lookups by user queries.
user_reads       | Sum of user_seeks, user_scans, and user_lookups for a total number of reads.
user_writes      | Number of updates by user queries.
last_user_seek   | Time of last user seek.
last_user_scan   | Time of last user scan.
last_user_lookup | Time of last user lookup.
last_user_update | Time of last user update.
By analyzing this information, you can gain insights into which indexes are being used and how often. This can help you optimize your database performance by identifying unused or underused indexes, as well as heavily accessed indexes that may require tuning.

Conclusion

Keep in mind that this information is reset when the server restarts, so the longer the server has been up, the more valuable these statistics become. Monitoring index usage regularly can help you maintain an efficient and well-performing database environment.

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