🔍 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? 😊
