Site icon Eitan Blumin's blog

Too Many Plans for the Same Query Hash

Eitan Blumin

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

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

[…] [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:

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):

Recommended Solution(s):

See Also:

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

Possible cause(s):

Recommend Solution(s):

There are a few different options here:

See Also:

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

Possible cause(s):

Recommended Solution(s):

See Also:

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:

See Also:

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:

Additional Resources

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

Share them in the comments below!

Exit mobile version