2. [What To Do] When an Index is Not Used

Indexes are designed to improve query performance by allowing faster data retrieval. However, sometimes indexes are not utilized, leading to slower queries. Understanding why and making necessary adjustments can enhance database efficiency.

• Identify unused indexes using DMVs

Use sys.dm_db_index_usage_stats to find unused indexes.

SELECT 
  OBJECT_NAME(s.object_id) AS TableName, 
  i.name AS IndexName, 
  s.user_seeks, s.user_scans, s.user_lookups, s.user_updates 
FROM sys.dm_db_index_usage_stats s 
JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id 
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 
AND s.database_id = DB_ID('YourDatabaseName')
AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0;        

• Review the query's execution plan

Check if the query is using indexes.

SET SHOWPLAN_XML ON;
GO
-- Your query here
SELECT * FROM Orders WHERE OrderDate > '2023-01-01';
GO
SET SHOWPLAN_XML OFF;        

• Ensure statistics are up to date

Refresh statistics to ensure the optimizer has current data.

UPDATE STATISTICS dbo.Orders;        

• Check index selectivity

Ensure indexes have high selectivity for optimal usage.

SELECT 
  c.name, 
  COUNT(DISTINCT c.name) AS Selectivity 
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY c.name;        

• Consider index redesign or removal

Remove or redesign indexes that are never used.

-- Drop an unused index
DROP INDEX idx_Unused ON dbo.Orders;        

• Verify that the query matches the index design

Ensure queries are optimized to use available indexes.

-- Ensure query uses index
SELECT * FROM Orders WHERE CustomerID = 123;        

Summary: Unused indexes can be identified using DMVs, reviewing execution plans, ensuring up-to-date statistics, checking index selectivity, and verifying query design. Unused indexes should be redesigned or removed to improve performance.