Find Queries Using an Index in the SQL Server Plan Cache

You can find queries using an index by searching the cached plans for the name of the index. Every plan lists the indexes it reads, so one search over the plan XML answers the question.

Build Something to Search

The demo creates a database named IndexQueriesDemo. It holds a Sales table with 100,000 rows and two indexes, one on CustomerID and one on ProductID. Run the scripts on a test server.

IF DB_ID(N'IndexQueriesDemo') IS NULL CREATE DATABASE IndexQueriesDemo;
GO
USE IndexQueriesDemo;
GO
DROP TABLE IF EXISTS dbo.Sales;
CREATE TABLE dbo.Sales (
    SaleID     int           NOT NULL PRIMARY KEY,
    CustomerID int           NOT NULL,
    ProductID  int           NOT NULL,
    Amount     decimal(10,2) NOT NULL,
    Note       char(60)      NOT NULL DEFAULT 'tea'
);
WITH Numbers AS (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT INTO dbo.Sales (SaleID, CustomerID, ProductID, Amount)
SELECT n, n % 5000, n % 400, n % 800 + 0.25 FROM Numbers;
CREATE INDEX IX_Sales_Customer ON dbo.Sales (CustomerID) INCLUDE (Amount);
CREATE INDEX IX_Sales_Product ON dbo.Sales (ProductID) INCLUDE (Amount);

Four stored procedures then give the cache something to remember. Two read by customer, one reads by product, and one counts every sale.

CREATE OR ALTER PROCEDURE dbo.CustomerTotal @CustomerID int
AS
    SELECT SUM(Amount) AS CustomerTotal FROM dbo.Sales WHERE CustomerID = @CustomerID;
GO
CREATE OR ALTER PROCEDURE dbo.BigCustomers
AS
    SELECT COUNT(*) AS BigCustomers FROM dbo.Sales WHERE CustomerID BETWEEN 100 AND 200 AND Amount > 700;
GO
CREATE OR ALTER PROCEDURE dbo.ProductTotal @ProductID int
AS
    SELECT SUM(Amount) AS ProductTotal FROM dbo.Sales WHERE ProductID = @ProductID;
GO
CREATE OR ALTER PROCEDURE dbo.AllSales
AS
    SELECT COUNT(*) AS AllSales FROM dbo.Sales;

The next script clears the plan cache of this database only, and runs the procedures. CustomerTotal runs three times, and ProductTotal runs twice.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC dbo.CustomerTotal @CustomerID = 42;
GO 3
EXEC dbo.BigCustomers;
GO
EXEC dbo.ProductTotal @ProductID = 7;
GO 2
EXEC dbo.AllSales;

The Search

To find queries using an index, run the procedure below. It first lists the plans compiled in the current database, using the plan attribute dbid. Only those plans go through the XML search. Reading plan XML is slow, and a big cache holds thousands of plans.

The search itself uses the exist method of the XML type. It looks for an Object element whose Index attribute equals the bracketed index name. The match is exact, so a similar name such as IX_Sales_Customer2 does not match. Reading the cache needs the VIEW SERVER STATE permission, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

CREATE OR ALTER PROCEDURE dbo.FindQueriesUsingIndex @IndexName sysname
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Bracketed nvarchar(130) = QUOTENAME(@IndexName);
    SELECT cp.plan_handle, cp.usecounts
    INTO #ThisDatabase
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
    WHERE pa.attribute = N'dbid' AND CONVERT(int, pa.value) = DB_ID() AND cp.cacheobjtype = N'Compiled Plan';
    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT COALESCE(OBJECT_NAME(st.objectid, st.dbid), LEFT(st.text, 100)) AS QueryOrProcedure, t.usecounts AS Runs, qs.AvgReads
    FROM #ThisDatabase AS t
    CROSS APPLY sys.dm_exec_query_plan(t.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(t.plan_handle) AS st
    CROSS APPLY (SELECT SUM(s.total_logical_reads) / NULLIF(SUM(s.execution_count), 0) AS AvgReads
                 FROM sys.dm_exec_query_stats AS s WHERE s.plan_handle = t.plan_handle) AS qs
    WHERE qp.query_plan.exist('//Object[@Index = sql:variable("@Bracketed")]') = 1
      AND st.text NOT LIKE N'%dm_exec%'
    ORDER BY t.usecounts DESC, QueryOrProcedure;
END;

Now ask for the customer index.

EXEC dbo.FindQueriesUsingIndex @IndexName = N'IX_Sales_Customer';

Both procedures that filter on CustomerID use the index. Runs is the use count of the cached plan. AvgReads is the average logical reads per statement run, from the query statistics. A query that is not a stored procedure shows the first 100 characters of its text instead of a name. The two together show how busy and how expensive each query is.

Ask for the product index next.

EXEC dbo.FindQueriesUsingIndex @IndexName = N'IX_Sales_Product';

ProductTotal is expected. AllSales is a surprise. It has no WHERE clause, yet it reads the product index. Any index that holds every row can answer a count, and SQL Server picked this one. A search by index name finds such queries too, and a search of the query text would miss them.

Ad Hoc Queries Count Too

The search is not limited to stored procedures. Run a plain query, and search for the customer index again.

SELECT MAX(Amount) AS TopAmount FROM dbo.Sales WHERE CustomerID = 42;
GO
EXEC dbo.FindQueriesUsingIndex @IndexName = N'IX_Sales_Customer';

The new row has no name, so it shows the text of the query. Now you can tell which statement uses the index.

When the Search Finds Nothing

The next script adds a filtered index on large amounts and searches for it.

CREATE INDEX IX_Sales_Big ON dbo.Sales (Amount) WHERE Amount >= 790;
GO
EXEC dbo.FindQueriesUsingIndex @IndexName = N'IX_Sales_Big';

The result is empty. No cached plan uses IX_Sales_Big. That is a hint, not a verdict. The plan cache holds only the plans that are still in memory. A plan that left the cache, or that has not run since the last restart, is invisible. Watch an index over weeks before you drop it. The post Unused Index Script: Find Indexes That Only Cost You Writes counts reads and writes over time.

An Unused Index Still Costs Writes

An index that no query reads still has to follow every change to its table. A filtered index follows only the rows that match its filter. The next procedure reads the insert counters of two indexes. leaf_insert_count is the number of rows added to the leaf level of the index since SQL Server loaded it.

CREATE OR ALTER PROCEDURE dbo.ShowWrites
AS
BEGIN
    SET NOCOUNT ON;
    SELECT i.name AS IndexName, ISNULL(os.leaf_insert_count, 0) AS LeafInserts
    FROM sys.indexes AS i
    CROSS APPLY sys.dm_db_index_operational_stats(DB_ID(), i.object_id, i.index_id, NULL) AS os
    WHERE i.object_id = OBJECT_ID(N'dbo.Sales') AND i.name IN (N'IX_Sales_Product', N'IX_Sales_Big')
    ORDER BY i.name;
END;

EXEC dbo.ShowWrites;

Both counters start at zero, and they start again after a restart. Now insert one sale of 10.00, which does not match the filter.

INSERT INTO dbo.Sales (SaleID, CustomerID, ProductID, Amount) VALUES (200001, 1, 1, 10.00);

EXEC dbo.ShowWrites;

The product index grew by one row, and the filtered index did not. Now insert a sale of 795.00, which matches.

INSERT INTO dbo.Sales (SaleID, CustomerID, ProductID, Amount) VALUES (200002, 1, 1, 795.00);

EXEC dbo.ShowWrites;

Now the filtered index counts one insert as well. An unused index with a narrow filter is cheap. An unused index without one costs a write on every insert.

Limits of the Plan Cache Search

You could argue that a plain text search of the plan, with a LIKE on the index name, is simpler. It is simpler. It also matches comments, longer names and anything else that contains the same letters. The XML search matches the index and nothing else.

The cache changes all the time. For a permanent record, use Query Store or an Extended Events session. They keep history. For a quick look, the search above is enough. Run it off peak hours on a large server, because it reads plan XML. For one query with many plans, read Group by Query Hash: Find One Query With Many Plans.

What to Remember

To find queries using an index, search the cached plans for its exact name, as the procedure above does. Filter by database first. Treat an empty result as a hint and not as proof. An unused index still costs writes, unless it is filtered. When you finish with the demo, remove the database.

USE master;
GO
DROP DATABASE IndexQueriesDemo;

An index is not unused when the cache is silent, it is unused when a month of history is.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.