Plan Cache Duplicates From Whitespace and Case Differences

Plan cache duplicates happen when the same query arrives in slightly different text. A space, a keyword in lower case or a comment is enough for SQL Server to cache a second plan. The two entries look different to the cache and identical to the optimizer.

Why the plan cache fills with lookalikes

Imagine you open the plan cache on a busy server and see thousands of plans, most used exactly once. Many of them look like twins. A developer’s code builds a query string by hand, and one code path adds a space or writes “select” in lower case. The cache does not care that you meant the same thing. It matches on the exact text.

Let me show you on a small scale. Everything below runs in your current database, and the last block removes only the plans it created.

Run two spellings of the same query

Both statements count the user tables. They differ in spacing, keyword case and a trailing comment. I run each one twice, so we can see the use counts climb.

EXEC(N'SELECT COUNT_BIG(*) AS UserTables FROM sys.objects WHERE type = N''U''; -- PlanDemo');
EXEC(N'select  COUNT_BIG(*)  from sys.objects where type = N''U''; -- PlanDemo variant');
EXEC(N'SELECT COUNT_BIG(*) AS UserTables FROM sys.objects WHERE type = N''U''; -- PlanDemo');
EXEC(N'select  COUNT_BIG(*)  from sys.objects where type = N''U''; -- PlanDemo variant');

Read the cache

The query below lists the cached plans whose text contains our marker. It leaves out its own text, so it does not count itself. I added the query hash and the plan hash, because those are the columns that tell lookalikes apart from strangers.

SELECT p.objtype, p.cacheobjtype, p.usecounts, p.size_in_bytes, q.query_hash, q.query_plan_hash
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
LEFT JOIN sys.dm_exec_query_stats AS q ON q.plan_handle = p.plan_handle
WHERE t.dbid = DB_ID()
  AND t.text LIKE N'%PlanDemo%'
  AND t.text NOT LIKE N'%sys.dm_exec_cached_plans%'
ORDER BY t.text, p.objtype, p.cacheobjtype;

Two rows, both Adhoc compiled plans, each with a use count of 2. Both show the same query hash and the same plan hash. Your size and hash values will differ from mine, but the pattern should match.

The next block groups the entries by query hash and then shows the text of each one.

SELECT q.query_hash, COUNT(*) AS cached_entries, SUM(p.usecounts) AS total_uses
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
JOIN sys.dm_exec_query_stats AS q ON q.plan_handle = p.plan_handle
WHERE t.dbid = DB_ID() AND t.text LIKE N'%PlanDemo%' AND t.text NOT LIKE N'%sys.dm_exec_cached_plans%'
GROUP BY q.query_hash;

SELECT t.text, p.usecounts
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.dbid = DB_ID() AND t.text LIKE N'%PlanDemo%' AND t.text NOT LIKE N'%sys.dm_exec_cached_plans%'
ORDER BY t.text;

One query hash, two cached entries, four uses in total. The text list shows why: one is upper case and tidy, the other is lower case with extra spaces. Two entries with the same query hash are a lead, not a verdict. Always read the text before you call them waste.

Fix it where the text is built

Spelling is the small problem. The bigger one is literal values pasted into the text. Compare three queries that differ only by a value with one parameterized query run three times.

EXEC(N'SELECT COUNT_BIG(*) AS TypeCount FROM sys.objects WHERE type = N''U''; -- LiteralDemo');
EXEC(N'SELECT COUNT_BIG(*) AS TypeCount FROM sys.objects WHERE type = N''V''; -- LiteralDemo');
EXEC(N'SELECT COUNT_BIG(*) AS TypeCount FROM sys.objects WHERE type = N''S''; -- LiteralDemo');

EXEC sys.sp_executesql N'SELECT COUNT_BIG(*) AS TypeCount FROM sys.objects WHERE type = @type; -- ParamDemo',
     N'@type nchar(1)', @type = N'U';
EXEC sys.sp_executesql N'SELECT COUNT_BIG(*) AS TypeCount FROM sys.objects WHERE type = @type; -- ParamDemo',
     N'@type nchar(1)', @type = N'V';
EXEC sys.sp_executesql N'SELECT COUNT_BIG(*) AS TypeCount FROM sys.objects WHERE type = @type; -- ParamDemo',
     N'@type nchar(1)', @type = N'S';

SELECT CASE WHEN t.text LIKE N'%LiteralDemo%' THEN 'Literal values' ELSE 'One parameter' END AS Style,
       COUNT(*) AS cached_entries, SUM(p.usecounts) AS total_uses
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.dbid = DB_ID() AND p.cacheobjtype = N'Compiled Plan'
  AND (t.text LIKE N'%LiteralDemo%' OR t.text LIKE N'%ParamDemo%')
  AND t.text NOT LIKE N'%sys.dm_exec_cached_plans%'
GROUP BY CASE WHEN t.text LIKE N'%LiteralDemo%' THEN 'Literal values' ELSE 'One parameter' END
ORDER BY Style;

Literal values give 3 cached entries, each used once. The parameterized query gives 1 entry, used 3 times. That is the fix: keep the statement text stable and pass values as parameters, or put the query in a stored procedure.

Is it worth fixing on your server?

A few administrative variants are not a reason to rewrite an application. Look for the same query hash repeated many times, each plan used once or twice. Measure compile activity before and after any change, on a comparable workload.

Please don’t clear the whole cache to feel better. It hides the symptom for a minute while the server compiles everything again. To clean up after the demo, the last block removes only our own plans, one handle at a time. It needs permission to alter server state.

DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'DBCC FREEPROCCACHE (' + CONVERT(nvarchar(130), p.plan_handle, 1) + N');'
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.dbid = DB_ID()
  AND (t.text LIKE N'%PlanDemo%' OR t.text LIKE N'%LiteralDemo%' OR t.text LIKE N'%ParamDemo%')
  AND t.text NOT LIKE N'%sys.dm_exec_cached_plans%';
EXEC (@sql);

Fix the code that builds the text, and the cache takes care of itself.

A shared query hash is not proof of waste, it is a lead to read.

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