Table Usage in the Plan Cache: Count Queries That Touch a Table

Table usage in the plan cache shows how many times the cached queries of a database touched each table. The plan cache holds the answer twice. The statistics view counts executions, and the plan XML names every table a plan reads. Join the two and you can rank your tables by use.

The Question and Its Source

Which tables does the workload use, and how many times? Knowing the table usage in the plan cache helps with index work, cleanup and migration plans. A table that no cached query touches is a candidate for review. A table that every query touches deserves the best indexes.

SQL Server keeps one row per cached statement in sys.dm_exec_query_stats, with an execution count. Each statement points to a cached plan, and the plan is XML that lists every object it reads. The counts are as good as the cache. They start when the plan enters the cache and vanish when it leaves.

Build the Demo

The demo database is named TableUseCountDemo, so run the script on a test server. It creates two small tables, an invoice table and its lines. The last statement clears the plan cache of this database only, so the setup statements do not count. It needs SQL Server 2016 or later.

IF DB_ID(N'TableUseCountDemo') IS NULL CREATE DATABASE TableUseCountDemo;
GO
USE TableUseCountDemo;
GO
DROP TABLE IF EXISTS dbo.InvoiceLines, dbo.Invoices;
CREATE TABLE dbo.Invoices (InvoiceID int NOT NULL PRIMARY KEY, CustomerName nvarchar(40) NOT NULL);
CREATE TABLE dbo.InvoiceLines (LineID int NOT NULL PRIMARY KEY, InvoiceID int NOT NULL, Amount decimal(9,2) NOT NULL);
GO
INSERT dbo.Invoices SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), N'Customer' FROM sys.all_objects;
INSERT dbo.InvoiceLines SELECT TOP (400) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 100, 9.5 FROM sys.all_objects;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Now run a small workload. The number after GO repeats the batch. The first query reads the invoice table five times. The join reads both tables seven times. The count reads the lines table three times, but it names its result column Invoices. That alias is the trap. Each batch holds one statement, which matters later.

SELECT TOP (1) * FROM dbo.Invoices;
GO 5
SELECT i.InvoiceID, l.Amount FROM dbo.Invoices AS i JOIN dbo.InvoiceLines AS l ON l.InvoiceID = i.InvoiceID WHERE i.InvoiceID = 23;
GO 7
SELECT COUNT(*) AS Invoices FROM dbo.InvoiceLines;
GO 3

The Simple Search and Its Limits

The quick way to find the queries that use a table is to search the text of the cached statements. The next query looks for the table name and skips its own text.

SELECT dest.text, qs.execution_count
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS dest
WHERE dest.text LIKE N'%Invoices%' AND dest.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY qs.execution_count;

The search finds the two real queries. It also finds a query that never reads the Invoices table, only because of a column alias. A name in a comment would match too. A query that reaches the table through a view would be missed. The search cannot say whether a match is a table, a column or a word. It also reads the cache of the whole instance. On a shared server it can list statements of other databases.

Read the Table Names From the Plan

The plan XML does not guess. Every table a plan reads appears in an Object element with its schema and name. The next query shreds the plan of each statement in this database and counts the executions per table. It works in two steps. The first step copies the plan handles that belong to this database into a temporary table. The second step reads the XML of those plans only, so the expensive part runs on the plans you need.

SELECT qs.plan_handle, qs.sql_handle, qs.statement_start_offset, qs.statement_end_offset, qs.execution_count
INTO #DbStats
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
WHERE pa.attribute = N'dbid' AND pa.value = DB_ID();
GO
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT x.TableName, SUM(s.execution_count) AS Executions, COUNT(*) AS Statements
FROM #DbStats AS s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS qp
CROSS APPLY (SELECT DISTINCT o.value('@Table', 'sysname') AS TableName
             FROM qp.query_plan.nodes('//Object[@Schema="[dbo]"][@Database="[TableUseCountDemo]"]') AS t(o)) AS x
WHERE qp.query_plan IS NOT NULL
GROUP BY x.TableName
ORDER BY x.TableName;

The Invoices table was touched 12 times by 2 statements. The first query ran 5 times and the join ran 7 times. The lines table was touched 10 times, by the join with 7 runs and the count with 3. The alias is gone, because the count reads only the lines table. The text search said 15 for Invoices. The plan says 12.

Replace TableUseCountDemo in the Object filter with the name of your database. The brackets are part of the value.

Run these queries on a test server first. The second step parses the XML of every plan in the database. That costs CPU on a busy server with a large cache. The copy to a temporary table keeps the cost down, and a quiet period keeps it lower.

List the Queries Behind One Table

Once the counts show a busy table, list the statements behind it. The next query uses the exist method on the plan XML to keep only plans that read the lines table. SUBSTRING and the statement offsets cut each statement out of its batch text.

SELECT SUBSTRING(st.text, s.statement_start_offset / 2 + 1,
       (CASE WHEN s.statement_end_offset = -1 THEN DATALENGTH(st.text) ELSE s.statement_end_offset END - s.statement_start_offset) / 2 + 1) AS StatementText,
       s.execution_count
FROM #DbStats AS s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS st
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //Object[@Table="[InvoiceLines]"]') = 1
ORDER BY s.execution_count DESC;
DROP TABLE #DbStats;

What the Cache Forgets

The results are only as good as the cache. A restart empties it. Memory pressure evicts plans, a recompile replaces them, and a plan for a statement with OPTION (RECOMPILE) is never kept. The server option optimize for ad hoc workloads changes this. A statement that ran once leaves only a stub with no plan XML. A table that no cached plan names can still be in use. Run the query after a normal working day, not after a restart.

Plans for a batch with several statements share one XML document. A batch with two statements shares one plan XML, so each table in it is counted for both statements. Run the batch below 3 times, and then repeat the two steps above. Each table count grows by 6, not by 3, although each table was read 3 times. That is why every demo batch holds one statement. For a real workload, read the Statements column as an upper bound.

SELECT COUNT(*) FROM dbo.InvoiceLines; SELECT COUNT(*) FROM dbo.Invoices;
GO 3

You could argue that the text search is enough, because it is shorter and cheap. For a quick look, it is. The plan based query takes more CPU and more typing. In return it never counts an alias or a comment as a table. It names the tables a statement reads.

What to Remember

Measure table usage in the plan cache by joining the execution counts with the plan XML. Filter by database first, because the XML work is the expensive part. Keep batches to one statement when you test. The cache holds only plans that are still cached, so a total can be too low. A batch with several statements can make a table count too high.

When you finish testing, remove the example database.

USE master;
GO
IF DB_ID(N'TableUseCountDemo') IS NOT NULL
BEGIN
    ALTER DATABASE TableUseCountDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TableUseCountDemo;
END;

The plan cache is not a history, it is whatever SQL Server has not forgotten yet.

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