SQL Server Forced Plans Overwite the Query Hash

Plan Forcing Overwrites the Query Hash

If you ‘force’ a plan (using any method, including Plan Guides, Query Store, and Automatic Plan Correction) the resulting plan will have its query_hash overwitten by the query_plan_hash.

In other words, the plan hash and query plan hash will be given the query plan hash value. The real query hash value is simply lost. 🤦‍♂️

This will break anything you have that uses query_hash for any purpose, including scripts and tools.

Example

One example way to reproduce this:

1. Create a table.
2. Select from it, capturing the execution plan.
3. Copy the XML showplan for the select.
4. Run the select again with the showplan XML in a USE PLAN hint.

Code:

DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1 (c1 integer NOT NULL);
GO
SELECT c1 
FROM dbo.T1
OPTION (USE PLAN N'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.4165.4"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT c1 &#xd;&#xa;FROM dbo.T1" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x31B14566F8F8A256" QueryPlanHash="0x10D77A8E4F35F994" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="80"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104857" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="12103680"></OptimizerHardwareDependentProperties><RelOp NodeId="0" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.0032035" EstimateCPU="7.96e-05" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="" Schema="[dbo]" Table="[T1]" Column="c1"></ColumnReference></OutputList><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="" Schema="[dbo]" Table="[T1]" Column="c1"></ColumnReference></DefinedValue></DefinedValues><Object Database="" Schema="[dbo]" Table="[T1]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>');

The ‘forced’ execution plan has identical query_hash and query_plan_hash values:

QueryHash="0x10D77A8E4F35F994"
QueryPlanHash="0x10D77A8E4F35F994"

Notice the identical hashes.

Online db<>fiddle demo

Impact

This bug affects SQL Server 2008 to 2022 CU16 inclusive.

Until Microsoft fix this (and backport it to 2008 🤣) any plans subject to ‘forcing’ will have an incorrect query hash. 🥳

I first remember seeing this type of behaviour publicly reported in a series of posts by @sqL_handle, in a post on 𝕏 by Tomáš Zíka, and most recently in a blog post by Kendra Little.

“Morally Equivalent” Plans

On a related point, there seems to be some confusion around the meaning of a ‘morally equivalent’ execution plan—a term coined by Conor Cunningham from the Microsoft query optimizer team. It can be a useful shorthand term, but only if you understand what it is shorthand for.

When you ‘force’ an execution plan, the plan produced may not exactly match the template. The intention is for the produced plan to be very similar to the guide plan.

Microsoft acknowledge this in the documentation (emphasis added):

The resulting execution plan forced by this feature will be the same or similar to the plan being forced. Because the resulting plan may not be identical to the plan specified by the plan guide, the performance of the plans may vary. In rare cases, the performance difference may be significant and negative; in that case, the administrator must remove the forced plan.

Guides, not rules

The term “plan guide” is rather more technically accurate than “forced plan”.

The XML representation of a plan is used to guide the query optimizer through its normal optimization process. The XML is parsed into a “hint tree” with a similar shape as the internal tree of logical operators formed by parsing and transforming the original T-SQL statement into a form the query processor finds convenient to work with.

The hint tree guides the query optimizer as it progresses through its normal cost-based optimization search. For clarity, these are not T-SQL hints; SQL Server uses the shape, properties, and node types in the hint tree to guide the search.

Guided search

Well, not quite normal search because all optimizer features are enabled in case they are needed to reach the goal. In older versions of SQL Server, this meant a guided plan search always went straight into the search 2 (full optimization) strategy. In recent versions, accelerated plan forcing allows guided search in search 1.

Working top down, any transformation or optimization that could not—or does not—produce the output described by the matching hint tree node is discarded. The effect is to constrain the optimizer to exploration paths that result in the same structure as described by the hints.

Differences

SQL Server uses the supplied XML representation as a guide rather than a strict template for several reasons.

1. Showplan XML—like any textual plan representation—is not a complete description of the internal executable plan, which is essentially a dynamic program capable of being directly executed to produce the results and effects logically described by the T-SQL statement. In other words, there simply isn’t enough information in the source to directly produce an executable plan.
2. Following the optimizer’s usual paths ensures the output is a plan the optimizer could have produced naturally. It is therefore guaranteed to respect all the logical semantics of the source T-SQL.
3. Allowing a user or program to submit arbitrary executables described by XML could open the way to security problems or undefined behaviour at the server.

All this means the plan eventually selected by the optimizer will often not match the guide plan exactly. It is intended to match all the important aspects captured in the XML, but the implementation is not exhaustive even given the limited source detail available. For example, the guiding process does not currently consider nested loops prefetching as an important property to match.

Reasons

Some of the implementation coverage shortfall is due to inherent limitations of the optimization framework. For example, the exact position of filters and compute scalars cannot be specified for architectural reasons.

Many other limitations exist. Some could be addressed fairly easily in principle, but you have to remember Microsoft is a small company with a limited budget and important features like Big Data Clusters to work on instead.

Effects

The plan produced via the guiding process will be visually very similar to the supplied XML plan. It may differ in details considered less important or simply not captured in the XML representation.

Since the output fingerprint of the final plan (query_plan_hash) depends on all the executable plan details (including many not visible to users in any form), the finished plan will often have a different query_plan_hash from the original. The input fingerprint based on the input logical tree (query_hash) will, however, be the same (bugs aside).

Finishing up

The term “morally equivalent” is intended to capture many of the nuances above. I don’t know how successful it is, or what morals have to do with anything, but there we are.

A plan guide can produce plans with minor variations compared with the source plan. Sometimes, these ‘minor variations’ can have large performance impacts. There was a time when Microsoft were interested in hearing about such cases. I have no idea if that’s still true.

Thanks for reading.