How to Check Which Index is Used for a Specific Query in SQL Server?

 

🔍 How to Check Which Index is Used for a Specific Query in SQL Server?

When you run a query in SQL Server, you can check whether an Index Seek, Index Scan, or Table Scan was used by analyzing the Execution Plan. Below are multiple ways to check which index was used.

---

🔹 Method 1: Use the Execution Plan

You can enable the execution plan before running your query.

Step 1: Enable Actual Execution Plan

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT * FROM HumanResources.Department WHERE DepartmentName = 'HR';
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Step 2: Check the Output

After running the query:

• If an Index Seek appears → SQL Server is using an index efficiently ✅
• If an Index Scan appears → SQL Server is scanning the entire index (can be optimized) ⚠️
• If a Table Scan appears → No index is used; the full table is scanned ❌

---

🔹 Method 2: Display the Estimated Execution Plan

You can view the Estimated Execution Plan before running the query.

Step 1: Enable Estimated Plan

SET SHOWPLAN_ALL ON;
GO
SELECT * FROM HumanResources.Department WHERE DepartmentName = 'HR';
GO
SET SHOWPLAN_ALL OFF;

✔ This does not execute the query but shows the expected plan.
✔ Check the Index Name in the output to see which index is used.

---

🔹 Method 3: Using sys.dm_db_index_usage_stats

You can check index usage statistics from system views.

SELECT 
    OBJECT_NAME(ius.object_id) AS TableName, 
    i.name AS IndexName, 
    i.type_desc AS IndexType, 
    ius.user_seeks, 
    ius.user_scans, 
    ius.user_lookups, 
    ius.user_updates
FROM sys.dm_db_index_usage_stats AS ius
JOIN sys.indexes AS i 
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECT_NAME(ius.object_id) = 'Department';

✔ Shows how often an index was used for seeks, scans, lookups, and updates.
✔ If user_scans is high, an index scan is happening (might need tuning).
✔ If user_seeks is high, an index is being used efficiently.

---

🔹 Method 4: Using sp_helpindex to List All Indexes

EXEC sp_helpindex 'HumanResources.Department';

✔ Displays all indexes on the table, but does not show if a query is using them.

---

📝 Summary

Method	Purpose
Execution Plan (CTRL + M)	Shows if an index is used (Seek, Scan, or Table Scan).
SET STATISTICS IO ON;	Shows logical reads (lower is better).
sys.dm_db_index_usage_stats	Checks how often an index is used.
sp_helpindex	Lists all indexes on a table.

🔍 Understanding sys.dm_db_index_usage_stats in SQL Server

The sys.dm_db_index_usage_stats system view in SQL Server provides detailed statistics about how indexes are used, helping you analyze their effectiveness.

---

🔹 Why is sys.dm_db_index_usage_stats Important?

✔ Helps you determine if an index is actually being used.
✔ Shows if an index is causing unnecessary overhead.
✔ Identifies indexes that should be removed or optimized.

---

🔹 Query to Check Index Usage

You can use the following query to analyze index usage for a specific table:

SELECT 
    OBJECT_NAME(ius.object_id) AS TableName, 
    i.name AS IndexName, 
    i.type_desc AS IndexType, 
    ius.user_seeks, 
    ius.user_scans, 
    ius.user_lookups, 
    ius.user_updates, 
    ius.last_user_seek, 
    ius.last_user_scan, 
    ius.last_user_lookup, 
    ius.last_user_update
FROM sys.dm_db_index_usage_stats AS ius
JOIN sys.indexes AS i 
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECT_NAME(ius.object_id) = 'Department';

📌 What Each Column Means

Column	Description
TableName	Name of the table containing the index.
IndexName	Name of the index being used.
IndexType	Type of the index (Clustered, Nonclustered, etc.).
user_seeks	Number of index seeks (efficient index usage).
user_scans	Number of index scans (less efficient, but still uses index).
user_lookups	Number of key lookups (indicates missing includes in index).
user_updates	Number of times the index is modified (updated/inserted).
last_user_seek	Last time an Index Seek was performed.
last_user_scan	Last time an Index Scan was performed.
last_user_lookup	Last time a Key Lookup was performed.
last_user_update	Last time the index was updated.

---

🔹 How to Interpret the Results

Scenario	Meaning
user_seeks is high	The index is being used efficiently. ✅
user_scans is high	Queries are scanning the index instead of seeking (may need tuning). ⚠️
user_lookups is high	A key lookup is happening—may need to include more columns in the index. ❌
user_updates is high	The index is frequently updated (can cause performance overhead). ⚠️
No rows returned	The index is not being used and may be a candidate for deletion. 🗑

---

🔹 Finding Unused Indexes (Safe to Drop)

You can check for indexes that are never used by running:

SELECT 
    OBJECT_NAME(i.object_id) AS TableName, 
    i.name AS IndexName, 
    i.type_desc AS IndexType
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECT_NAME(i.object_id) = 'Department'
AND i.index_id > 1  -- Ignore Clustered Index
AND ius.index_id IS NULL;

✔ Indexes that appear here are not being used and may be safe to drop.

---

📝 Summary

✔ sys.dm_db_index_usage_stats helps analyze how indexes are used in SQL Server.
✔ user_seeks is good, while high user_scans or user_lookups might indicate inefficiencies.
✔ Use it to identify unused indexes that can be removed to improve performance.

🔍 Understanding dm_db in sys.dm_db_index_usage_stats

In SQL Server Dynamic Management Views (DMVs), the prefix dm_db stands for "Dynamic Management - Database level". It indicates that the DMV provides database-specific performance and diagnostic information.

---

🔹 Breakdown of sys.dm_db_index_usage_stats

• sys → Part of the system catalog (stores system-level metadata).
• dm_db → Dynamic Management view for a specific database (not instance-wide).
• index_usage_stats → Provides statistics on index usage.

---

🔹 What Does dm_db Mean?

The dm_db prefix appears in DMVs that focus on a single database, meaning: ✔ The DMV retrieves information only for the current database where the query is executed.
✔ If you switch databases (USE AnotherDatabase), the view will show results for that new database.
✔ It does not provide instance-wide information (for that, use dm_os or dm_exec DMVs).

---

🔹 Other Common dm_db DMVs

DMV	Purpose
sys.dm_db_index_physical_stats	Provides fragmentation details for indexes.
sys.dm_db_partition_stats	Shows row and page count for partitions.
sys.dm_db_index_operational_stats	Tracks low-level index usage metrics (locks, waits).
sys.dm_db_stats_properties	Gives details on table statistics.

---

📝 Summary

✔ dm_db means the DMV operates at the database level.
✔ sys.dm_db_index_usage_stats helps track index usage for the current database.
✔ To analyze index performance across multiple databases, you must switch databases and re-run queries.

Would you like help with a specific DMV? 😊