Same Query Plan, Different Cache Entry in SQL Server

The same query plan can sit in the cache many times, because text and session settings decide each entry. A capital letter, an extra space or one more value in a list is enough to make a new one.

Two Queries, Same Result, Same Plan

The demo uses a small database named SameQueryCacheDemo. It holds one table, TeaOrders, with 2,000 orders. Quantity runs from 1 to 5. Run the script on a test server.

IF DB_ID(N'SameQueryCacheDemo') IS NULL CREATE DATABASE SameQueryCacheDemo;
GO
USE SameQueryCacheDemo;
GO
DROP TABLE IF EXISTS dbo.TeaOrders;
CREATE TABLE dbo.TeaOrders (
    OrderID  int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Tea      nvarchar(30) NOT NULL,
    Quantity int          NOT NULL
);
WITH Numbers AS (
    SELECT TOP (2000) 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.TeaOrders (Tea, Quantity)
SELECT CHOOSE(1 + n % 4, N'Herbal', N'Green', N'Oolong', N'Mint'), 1 + (n * 7) % 5
FROM Numbers;

Every query below counts orders by tea. The second one adds 0 to the IN list. No order has quantity 0, so the result is identical. The first line clears the plan cache of this one database. Other databases keep their plans.

Each query sits in its own batch, because SQL Server caches one entry for each batch. The script also runs the first query a second time, and adds a trailing comment to a copy of it.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
SELECT Tea, COUNT(*) AS Orders FROM dbo.TeaOrders WHERE Quantity IN (1, 2) GROUP BY Tea ORDER BY Tea;
GO
SELECT Tea, COUNT(*) AS Orders FROM dbo.TeaOrders WHERE Quantity IN (0, 1, 2) GROUP BY Tea ORDER BY Tea;
GO
select Tea, COUNT(*) AS Orders from dbo.TeaOrders where Quantity IN (1, 2) group by Tea order by Tea;
GO
SELECT Tea, COUNT(*) AS Orders FROM dbo.TeaOrders WHERE Quantity IN (1, 2) GROUP BY Tea ORDER BY Tea; -- a comment
GO
SELECT Tea, COUNT(*) AS Orders FROM dbo.TeaOrders WHERE Quantity IN (1, 2) GROUP BY Tea ORDER BY Tea;

Every run returns the same four rows. Each one uses the same query plan too: one clustered index scan, a sort and a stream aggregate.

What the Cache Stores

SQL Server compares the text of a batch with the text of the batches it already holds. The match is exact, character by character. The next query lists the entries for this table. query_hash is a fingerprint of the query logic. Letter case, spacing and comments do not change it. The cache still compares the exact text, so each spelling gets its own entry. query_plan_hash is a fingerprint of the plan. DENSE_RANK turns each fingerprint into a small group number, so the table is easy to read.

SELECT st.text AS QueryText, cp.usecounts AS UseCount,
       DENSE_RANK() OVER (ORDER BY qs.query_hash) AS HashGroup,
       DENSE_RANK() OVER (ORDER BY qs.query_plan_hash) AS PlanGroup
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE st.text LIKE N'%dbo.TeaOrders%' AND st.text NOT LIKE N'%dm_exec%'
  AND EXISTS (SELECT 1 FROM sys.dm_exec_plan_attributes(cp.plan_handle) AS pa WHERE pa.attribute = N'dbid' AND CONVERT(int, pa.value) = DB_ID())
ORDER BY st.text COLLATE Latin1_General_BIN2;

Five runs created four entries. The first query ran twice with identical text, so one entry shows a UseCount of 2. The lowercase copy, the commented copy and the longer IN list each got a different cache entry.

PlanGroup is 1 on every row, so the plans are identical. HashGroup shows that the lowercase copy and the commented copy share the fingerprint of the first query. Case, spacing and comments do not change it. The longer IN list is different logic, so it has its own hash group. An extra space inside the text also makes a new entry with the same fingerprints.

Same Text, Different Cache Entry

Identical text does not guarantee one entry. The cache key also holds session settings that can change the result of a query. DATEFIRST is one of them. It decides which day counts as the first day of the week. Language and date format are others. The demo assumes a US English session, where DATEFIRST is 7.

SELECT COUNT(*) AS DateFirstTest FROM dbo.TeaOrders;
GO
SET DATEFIRST 1;
GO
SELECT COUNT(*) AS DateFirstTest FROM dbo.TeaOrders;
GO
SET DATEFIRST 7;

SELECT cp.usecounts AS UseCount, pa.value AS DateFirst,
       DENSE_RANK() OVER (ORDER BY qs.query_plan_hash) AS PlanGroup
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE st.text LIKE N'%DateFirstTest%' AND st.text NOT LIKE N'%dm_exec%' AND pa.attribute = N'date_first'
  AND EXISTS (SELECT 1 FROM sys.dm_exec_plan_attributes(cp.plan_handle) AS db WHERE db.attribute = N'dbid' AND CONVERT(int, db.value) = DB_ID())
ORDER BY pa.value;

The text is identical in both runs, yet each run got a different cache entry. One was compiled with DATEFIRST 7, the default for US English. The other was compiled with DATEFIRST 1. The plan fingerprint matches again. SQL Server cannot know that this query ignores dates, so it keeps the entries apart.

This is why one application can create more entries than another for the same procedure. A connection that sets different options gets its own copy. Several SET options, such as ANSI_NULLS and QUOTED_IDENTIFIER, are part of the key too.

Why Extra Entries Cost You

Each extra entry for the same query plan holds a compiled plan. SQL Server had to build it and now has to store it. In this demo each of the first three entries takes 56 KB of cache. A thousand text variants of one query, at 56 KB each, would take about 55 MB. Every new text is also compiled before it runs.

You could argue that duplicates are harmless, since the plans are identical and memory is cheap. For a few hundred fixed queries that is true. It stops being true when an application builds text with literal values. Every new value becomes new text, and the cache fills with copies.

Simple queries are different. SQL Server can parameterize them itself, which is called simple parameterization. The Adhoc rows are then small shells that point to one Prepared plan. The search above lists only the shells, because the Prepared text writes the names in square brackets.

To find such queries, group the entries by query_hash and keep the groups with more than one entry. Your hash values will differ from the ones below.

SELECT x.query_hash AS QueryHash, COUNT(*) AS CacheEntries, SUM(x.size_in_bytes) / 1024 AS CacheKB
FROM (SELECT cp.plan_handle, cp.size_in_bytes, MIN(qs.query_hash) AS query_hash
      FROM sys.dm_exec_cached_plans AS cp
      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
      JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
      WHERE st.text LIKE N'%dbo.TeaOrders%' AND st.text NOT LIKE N'%dm_exec%'
        AND EXISTS (SELECT 1 FROM sys.dm_exec_plan_attributes(cp.plan_handle) AS pa WHERE pa.attribute = N'dbid' AND CONVERT(int, pa.value) = DB_ID())
      GROUP BY cp.plan_handle, cp.size_in_bytes) AS x
GROUP BY x.query_hash
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

The first group is the family of the first query: the original, the lowercase copy and the commented copy. The second group is the DATEFIRST query. To search a whole server, drop the two lines that filter on the table name. For another way to read the cache, see Plan Cache Size in SQL Server: List Every Cached Plan.

Share One Entry

The best fix is one piece of text for every value. Pass the value as a parameter. The batch below runs through sp_executesql twice, with quantity 3 and then 4.

EXEC sys.sp_executesql N'SELECT Tea, COUNT(*) AS Orders FROM dbo.TeaOrders WHERE Quantity = @Qty GROUP BY Tea ORDER BY Tea;', N'@Qty int', @Qty = 3;
GO
EXEC sys.sp_executesql N'SELECT Tea, COUNT(*) AS Orders FROM dbo.TeaOrders WHERE Quantity = @Qty GROUP BY Tea ORDER BY Tea;', N'@Qty int', @Qty = 4;
GO
SELECT cp.objtype AS ObjType, cp.usecounts AS UseCount, st.text AS QueryText
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.text LIKE N'%@Qty%' AND st.text NOT LIKE N'%dm_exec%';

Two runs with two values share one entry, and its UseCount is 2. The text is the same, so the cache finds it. Writing queries in one style also helps, because one function or one data access layer then produces one spelling.

Two options can help when you cannot change the application. The setting optimize for ad hoc workloads stores a small stub the first time a query runs. Forced parameterization is a database option that turns literals into parameters. Both change how the whole server or database behaves, so test them first.

What to Remember

The same query plan can own several cache entries, each with one exact text and one set of session settings. Two entries with the same plan are normal. Group by query_hash to see how many copies one query has.

Write each query in one way, and pass values as parameters. Then the plan that SQL Server built for the first run serves every run after it. For one query with many plans, read Group by Query Hash: Find One Query With Many Plans. When you finish with the demo, remove the database.

USE master;
GO
DROP DATABASE SameQueryCacheDemo;

A cache entry is not a query, it is one spelling of it.

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