Too Many Plans for the Same Query Hash

In today’s post, I’ll be providing a useful script to detect and troubleshoot when the query plan cache contains too many different plans for the same query hash, which could happen as a result of possible parameterization issues.

First, a short explanation of relevant terms

• query hash – This is a deterministic representation of a SQL query text (could be a SQL batch or a SQL statement). Multiple different query texts can have the exact same query hash if they’re “similar” enough (for example, differing only in letter capitalization, table aliases, literal scalar values, white spaces, etc.).
• query plan hash – This is a deterministic representation of a SQL execution plan. Here as well, multiple different execution plans can have the exact same query plan hash if they’re “similar” enough (for example, differing only in estimated row counts, but still retaining the same query plan tree).
• query plan handle – This is a server-wide unique identifier of an execution plan. There are no duplicates here. Each separate execution plan has a different query plan handle, no matter how little the difference.
• plan cache – SQL Server stores a cache of compiled execution plans as part of its memory buffer. The database engine automatically evicts “outdated” execution plans based on various rules (for example, when relevant database schema objects are changed, or when an execution plan is no longer being used). The important thing to remember, though, is that this plan cache shares the same memory space as the buffer cache (which is where SQL Server saves table data pages for fast retrieval). So, when the plan cache is bloated with too many compiled execution plans, that means less memory space for data.

Brent Ozar wrote an excellent blog post about this back in 2018, which lists additional problems that this can cause:

• Each separate execution plan means a separate compilation, which means that the query took a little longer to finish.
• Each execution plan is cached separately – meaning it takes up more memory.
• Each plan could be different – in cases where the different plans have a different number of estimated rows, SQL Server might choose to use (or avoid) an index.

[…] [All of] this can add up to more CPU time, more memory used for caching plans, and less memory that can be used for caching data.

source: Brent Ozar

Monitoring for the issue

To easily detect and troubleshoot this use case, I wrote this T-SQL script available in our Madeira Toolbox:

• Many Plans for Single Query Hash.sql

Running the above script with the parameter @RCA set to 0 will output a list of the top query hashes (sorted based on their size in bytes so that you’d see the most impactful queries first) that have multiple different query plan hashes cached for them.

Running the script with the parameter @RCA set to 1 will output the same list, with additional columns that will help you do deeper root cause analysis.

Unlike the similar script provided by Brent, my script counts the number of query plan hashes rather than the query plan handles per each query hash. I found that this results in far fewer “false positives”. But you can still set the parameter @CountByPlanHandleInsteadOfPlanHash to 1 if you want it to count based on query plan handles instead.

The column “MoreDetailsCmd” will be available for each query hash. You can copy-and-paste the contents of this column for each query hash and run it in another query window, and it will output the top 10 different query plans and query texts based on their usecount, including their corresponding execution stats.

For example:

SELECT TOP (10)
ClearPlanHandleFromCacheCmd = N'DBCC FREEPROCCACHE (' + CONVERT(nvarchar(max), qs.plan_handle, 1) + N');'
, qplan.query_plan, qs.query_plan_hash, txt.text, qs.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qplan
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE qs.query_hash = 0x150517EA5F3979B4
ORDER BY qs.execution_count DESC

Another column, called “StatsPerQueryPlanHashCmd” will also be available for each query hash. This one will contain a different command that will provide a sort of “summary” per each query plan hash. This should make it easier to analyze when there are many query plan handles, but far fewer query plan hashes (in other words: a bunch of query plans are very similar to each other).

For example:

;WITH QueryPlanHashes
AS
(
SELECT TOP (10) qs.query_plan_hash, qs.query_hash
, TotalDistinctExecPlans = COUNT(*)
, TotalExecutionCount = SUM(qs.execution_count)
, TotalWorkerTime = SUM(qs.total_worker_time)
, TotalElapsedTime = SUM(qs.total_elapsed_time)
, TotalPhysicalReads = SUM(qs.total_physical_reads)
, TotalLogicalReads = SUM(qs.total_logical_reads)
, TotalLogicalWrites = SUM(qs.total_logical_writes)
, TotalGrantKB = SUM(qs.total_grant_kb)
, TotalUsedGrantKB = SUM(qs.total_used_grant_kb)
FROM sys.dm_exec_query_stats AS qs
WHERE qs.query_hash = 0x150517EA5F3979B4
GROUP BY qs.query_plan_hash, qs.query_hash
ORDER BY TotalExecutionCount DESC, TotalElapsedTime DESC
)
SELECT qs.*
, ExampleQueryPlan = ex.query_plan
, ExampleQueryText = ex.text
FROM QueryPlanHashes AS qs
CROSS APPLY (
	SELECT TOP 1 qplan.query_plan, txt.text
	FROM sys.dm_exec_query_stats AS qs2
	CROSS APPLY sys.dm_exec_query_plan(qs2.plan_handle) AS qplan
	CROSS APPLY sys.dm_exec_sql_text(qs2.sql_handle) AS txt
	WHERE qs2.query_hash = qs.query_hash
	AND qs2.query_plan_hash = qs.query_plan_hash
	ORDER BY qs2.execution_count DESC
	) AS ex

Review these results and look for “red flags” such as these:

1. Nearly identical query texts that utilize literal values instead of parameters – with identical query plan trees
2. Nearly identical query texts that utilize literal values instead of parameters – with vastly different query plan trees
3. SQL commands that utilize parameters, but differ from each other due to the size/max length of these parameters
4. There are multiple execution plans, but most of them are outdated

For each of these red flags, I provide possible causes, recommended solutions, and links to additional resources.

And also, added a section on reducing recompilations using Plan Guides.

Red Flags

1. Nearly identical query texts that utilize literal values instead of parameters – with identical query plan trees

Possible cause(s):

• Ad-hoc queries and/or dynamic SQL commands that use literal values (or SQL injection) instead of parameters.

Recommended Solution(s):

• Rewrite the SQL commands to utilize parameters. For example by re-creating it as a parameterized stored procedure.
• You could also try turning on Forced Parameterization, but that would be risky as it has a database-wide impact, and may affect queries that you don’t necessarily want to be affected.
• If it’s not possible to make application changes to fix a specific query, you could try creating a Plan Guide for it as a sort of “duct-tape” solution (creating a plan guide may not always be possible in every scenario).
  • There are a couple of Query Hints that you could use in a Plan Guide to prevent/reduce recompilations:
    • OPTION(PARAMETERIZATION FORCED) – more details here about specifying query parameterization behavior by using plan guides.
    • OPTION(KEEPFIXED PLAN) – this prevents recompilations unless due to a schema change. Don’t confuse this with the KEEP PLAN hint that is only relevant to commands involving temporary tables, which are not supported by plan guides.

See Also:

• Can Forced Parameterization Go Wrong? | Brent Ozar
• Forced Parameterization | BlitzCache
• Stabilizing Execution Plans: Plan Guides and NORECOMPUTE | Brent Ozar
• Working with Plan Guides:
  • sp_get_query_template (Transact-SQL) | Microsoft Docs
  • sp_create_plan_guide (Transact-SQL) | Microsoft Docs
  • sys.fn_validate_plan_guide (Transact-SQL) | Microsoft Docs

2. Nearly identical query texts that utilize literal values instead of parameters – with vastly different query plan trees

Possible cause(s):

• Skewed data can cause different execution plans to be compiled for different parameter/literal values, with different indexes and/or join operators.
• This can also happen when different applications use different set attributes.

Recommend Solution(s):

There are a few different options here:

• It may be possible to do some index tuning for this query so that it would perform better for all use cases. Some (or all) of the execution plans you’ll find could even have missing index details that may help with this.
• Consider using query hints, table hints, and/or join hints to force a specific execution plan that would be “good enough” for all use cases. This is often not recommended and will require a deep analysis of all execution plan variants by the DBA to decide on the optimal hints and avoid an overall negative impact. Also, if it is not possible to change the query at the application code, this could still be possible by creating a Plan Guide instead (creating a plan guide may not always be possible in every scenario).
• Consider implementing some kind of a “conditional branching” logic to isolate different use cases that require different execution plans (for example, based on specific parameter values) so that they’d be handled by separate stored procedures.
• If the query in question is not being executed too frequently, consider using OPTION(RECOMPILE) or WITH RECOMPILE to avoid caching the execution plan entirely, while also optimizing parameter sniffing for each execution. This too can be forced using a Plan Guide, if changing the application code is impossible.

See Also:

• Conditional Branching and OPTION(RECOMPILE) | SQL Chit Chat 
• Hints (Transact-SQL) | Microsoft Docs
• Working with Plan Guides:
  • sp_get_query_template (Transact-SQL) | Microsoft Docs
  • sp_create_plan_guide (Transact-SQL) | Microsoft Docs
  • sys.fn_validate_plan_guide (Transact-SQL) | Microsoft Docs

3. SQL commands that utilize parameters, but differ from each other due to the size/max length of these parameters

Possible cause(s):

• The application is using AddWithValue or an equivalent, instead of specifying strongly-typed parameters. This auto-configures the parameter type and length based on its actual value during runtime and may be different between executions.
• It may also happen due to different code paths in the application executing the same ad-hoc query, but they’re inconsistently written in terms of the parameter definition.

Recommended Solution(s):

• Change the application to use the Add method instead of AddWithValue when adding parameters in order to make them strongly typed and consistent in size.
• If there’s an issue with multiple application paths executing the same query with inconsistent parameter definitions, then obviously suggest making it consistent. For example, by creating a stored procedure, and/or creating a single method for running the query and defining the parameters. This single method would then be executed by the different code paths.

See Also:

• AddWithValue is Evil | DBADelta

4. There are multiple execution plans, but most of them are outdated

Possible cause(s):

Based on the details you get per each query hash, you could possibly notice that the “creation_time” and “last_execution_time” columns have mostly old dates.

This is a likely indication that this query hash was created a long time ago, and different execution plans were generated for it during this time. But only a handful of these (or maybe even just one) is the true “relevant” one. Furthermore, due to not a lot of stress on the plan cache, these old execution plans were not evicted from the cache.

Recommend Solution(s):

Using the script you used, you should notice that you have a couple of interesting columns:

• ClearSqlHandleFromCacheCmd – This is a command using DBCC FREEPROCCACHE to clear out all execution plans belonging to a specific SQL handle. You can run this command per a specific query hash to “flush it out” of the plan cache. Upon next execution, the query will be compiled again with only the latest and most relevant execution plan(s).
• ClearPlanHandleFromCacheCmd – This is a command available per each execution plan. This too uses DBCC FREEPROCCACHE, but this time to clear out a specific execution plan handle. You can run this command for a specific execution plan for more “granular” control over which execution plan(s) you want to “flush” out of the plan cache. If this execution plan will be needed again in the future, it’ll be compiled again.

See Also:

• DBCC FREEPROCCACHE (Transact-SQL) | Microsoft Docs
• sys.dm_exec_query_stats (Transact-SQL) | Microsoft Docs

More on reducing recompilations using Plan Guides

The following text is quoted from Microsoft Docs:

Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server. Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them.

Plan guides cannot be used in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2016. Plan guides are visible in any edition. You can also attach a database that contains plan guides to any edition. Plan guides remain intact when you restore or attach a database to an upgraded version of SQL Server.

You can specify query parameterization behavior using Plan Guides.

You do this by creating a TEMPLATE plan guide on the parameterized form of the query, and specifying the PARAMETERIZATION FORCED query hint in the sp_create_plan_guide stored procedure. You can consider this kind of plan guide as a way to enable forced parameterization only on a certain class of queries, instead of all queries. For more information on simple parameterization, see the Query Processing Architecture Guide.

Let’s assume that you have determined that you do not want to enable forced parameterization on all queries in the database. However, you do want to avoid compilation costs on all queries of a specific structure/template, but differ only in their constant literal values. In other words, you want the query to be parameterized so that a query plan for this kind of query is reused. In this case, complete the following steps:

1. Retrieve the parameterized form of the query. The only safe way to obtain this value for use in sp_create_plan_guide is by using the sp_get_query_template system stored procedure.
2. Create the plan guide on the parameterized form of the query, specifying the PARAMETERIZATION FORCED query hint.
3. You can alternatively consider using the query hint KEEPFIXED PLAN, which forces the Query Optimizer not to recompile a query because of changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query recompiles only if the schema of the underlying tables changes, or if sp_recompile runs against those tables. Don’t confuse this with the KEEP PLAN hint that is only relevant to commands involving temporary tables, which are not supported by plan guides.

The following example script can be used both to obtain the parameterized query and then create a plan guide on it:

DECLARE @stmt nvarchar(max); 
DECLARE @params nvarchar(max); 

EXEC sp_get_query_template 
N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel AS pm 
INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 50', 
@stmt OUTPUT, 
@params OUTPUT;

EXEC sp_create_plan_guide 
N'TemplateGuide1', 
@stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';

After creating a plan guide, it’s important to validate it to make sure that it can be functional. You can do so using the fn_validate_plan_guide function like so:

SELECT * 
FROM sys.plan_guides 
CROSS APPLY fn_validate_plan_guide(plan_guide_id); 

If an empty result set is returned, all plan guides are valid.

See Also:

• Plan Guides | Microsoft Docs
• Create a Plan Guide for Parameterized Queries | Microsoft Docs
• Specify Query Parameterization Behavior by Using Plan Guides | Microsoft Docs

Additional Resources

• Multiple Plans | BlitzCache
• Why Multiple Plans for One Query are Bad | Brent Ozar

Got any ideas about more “red flags”, possible causes, and recommended solutions?

Share them in the comments below!