Joe Obbish shared an especially interesting problem with me recently. He had a query on a table with a single column, but introducing a USE PLAN hint would cause a compilation time of 40+ seconds. Here’s a simplified repro of his issue in case you want to stop reading here and work on this puzzle yourself.
USE TestDB
GO
SET STATISTICS TIME ON
GO
/* --Run these to create table
DROP TABLE IF EXISTS Joe_is_a_cool_guy;
CREATE TABLE dbo.Joe_is_a_cool_guy (
ID1 BIGINT
);
INSERT INTO dbo.Joe_is_a_cool_guy WITH (TABLOCK)
SELECT
q.RN
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) q;
*/
SELECT
MAX(CASE WHEN ID1 = 1 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 2 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 3 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 4 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 5 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 6 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 7 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 8 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 9 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 10 THEN 1 ELSE 0 END)
--, MAX(CASE WHEN ID1 = 11 THEN 1 ELSE 0 END) --40s compilation occurs if this line is included. Make sure to update the USE PLAN hint if including!
FROM dbo.Joe_is_a_cool_guy
OPTION (MAXDOP 1, RECOMPILE
,USE PLAN N'<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.4206.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="21.5885" StatementText="SELECT
 MAX(CASE WHEN ID1 = 1 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 2 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 3 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 4 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 5 THEN 1 ELSE 0 END) 
, MAX(CASE WHEN ID1 = 6 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 7 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 8 THEN 1 ELSE 0 END) 
, MAX(CASE WHEN ID1 = 9 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 10 THEN 1 ELSE 0 END)
--, MAX(CASE WHEN ID1 = 11 THEN 1 ELSE 0 END)
FROM dbo.Joe_is_a_cool_guy" StatementType="SELECT" QueryHash="0xE556184306068E5C" QueryPlanHash="0xD484277C0DDA9D26" RetrievedFromCache="false" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="48" CompileTime="4" CompileCPU="4" CompileMemory="896">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="8960000" EstimatedPagesCached="17920000" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="2059715760" />
<TraceFlags IsCompileTime="true">
<TraceFlag Value="3459" Scope="Global" />
<TraceFlag Value="9587" Scope="Global" />
</TraceFlags>
<RelOp AvgRowSize="48" EstimateCPU="3.85878" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="0" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="21.5885">
<OutputList>
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1004" />
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="MAX([Expr1013])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1013" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="MAX([Expr1014])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1014" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="MAX([Expr1015])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1015" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="MAX([Expr1016])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1016" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="MAX([Expr1017])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1017" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="MAX([Expr1018])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1018" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1009" />
<ScalarOperator ScalarString="MAX([Expr1019])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1019" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1010" />
<ScalarOperator ScalarString="MAX([Expr1020])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1020" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1011" />
<ScalarOperator ScalarString="MAX([Expr1021])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1021" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1012" />
<ScalarOperator ScalarString="MAX([Expr1022])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1022" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="48" EstimateCPU="0.64313" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6431300" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="17.7297">
<OutputList>
<ColumnReference Column="Expr1013" />
<ColumnReference Column="Expr1014" />
<ColumnReference Column="Expr1015" />
<ColumnReference Column="Expr1016" />
<ColumnReference Column="Expr1017" />
<ColumnReference Column="Expr1018" />
<ColumnReference Column="Expr1019" />
<ColumnReference Column="Expr1020" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1022" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1013" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(1) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1014" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(2) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1015" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(3) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(3)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1016" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(4) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1017" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(5) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(5)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(6) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(6)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1019" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(7) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(7)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1020" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(8) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(8)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1021" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(9) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(9)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1022" />
<ScalarOperator ScalarString="CASE WHEN [TestDB].[dbo].[Joe_is_a_cool_guy].[ID1]=(10) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(10)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="15" EstimateCPU="7.07458" EstimateIO="10.012" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6431300" EstimatedRowsRead="6431300" LogicalOp="Table Scan" NodeId="2" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="17.0866" TableCardinality="6431300">
<OutputList>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</OutputList>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" Column="ID1" />
</DefinedValue>
</DefinedValues>
<Object Database="[TestDB]" Schema="[dbo]" Table="[Joe_is_a_cool_guy]" IndexKind="Heap" Storage="RowStore" />
</TableScan>
</RelOp>
</ComputeScalar>
</RelOp>
</StreamAggregate>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
'
)
On my 2016 test instance, I see a compilation time of 4 ms without the USE PLAN hint, and 3708 ms with it. Adding more to the SELECT statement quickly increases the time taken, but only with USE PLAN. What’s going on?
Before diving in, a quick note. While I try to use simple explanations, I’m also going to get down and nerdy here, using some technical terms and undocumented trace flags. Pretty much everything I know comes from Paul the Magnificent’s series here, so if you don’t know what a “memo group” or “input tree” is and actually want to learn, start there.
The first clue comes from Microsoft, who says that “You can only force plans with USE PLAN that can be produced by the optimizer’s normal search strategy.” So, apparently the optimizer is used to validate the hint. Let’s prove this.
Start with a basic setup and query that would be trivial (a query so simple that SQL Server says “Screw it, I’m not optimizing that. Have an obvious plan.”)
/* --Setup USE TestDB GO DROP TABLE IF EXISTS dbo.tableA GO CREATE TABLE dbo.tableA (ID INT) INSERT dbo.tableA VALUES (1) */ SELECT ID FROM dbo.tableA a WHERE a.ID = 1
Add a USE PLAN hint and it gets a full optimization! We can even use a trace flag like 8619 to see that SQL Server really is doing the optimization work it bypassed earlier.
SELECT ID
FROM dbo.tableA a
WHERE a.ID = 1
OPTION
(
RECOMPILE,
USE PLAN N'<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.4206.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="0.0032831" StatementText="
SELECT ID
FROM dbo.tableA a
WHERE a.ID = 1" StatementType="SELECT" ParameterizedText="(@1 tinyint)SELECT [ID] FROM [dbo].[tableA] [a] WHERE [a].[ID]=@1" QueryHash="0x9221C7D3F204464E" QueryPlanHash="0x5FDD2F749EB29DF1" RetrievedFromCache="false" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="104">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="8960000" EstimatedPagesCached="17920000" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="2060146848" />
<RelOp AvgRowSize="11" EstimateCPU="7.96E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Table Scan" NodeId="0" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">
<OutputList>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableA]" Alias="[a]" Column="ID" />
</OutputList>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableA]" Alias="[a]" Column="ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[TestDB]" Schema="[dbo]" Table="[tableA]" Alias="[a]" IndexKind="Heap" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[TestDB].[dbo].[tableA].[ID] as [a].[ID]=CONVERT_IMPLICIT(int,[@1],0)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableA]" Alias="[a]" Column="ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1002">
<ScalarOperator>
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@1" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</TableScan>
</RelOp>
<ParameterList>
<ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>',
QUERYTRACEON 3604, --send output to message tab
QUERYTRACEON 8619 --applied transformation rules
)
OK, so if USE PLAN needs the optimizer for validation, does it at least change the starting point of the search that the optimizer does? Nope!
Using the example of changing table order from here with 8605 and 8608, we can see that the input tree and initial memo remain the same. (Again, these are undocumented trace flags, which means everyone gets to point and laugh at you for causing a production outage if you use them there.)
SELECT *
FROM dbo.tableA a
INNER JOIN dbo.tableB b
ON b.ID = a.ID
OPTION (
QUERYTRACEON 3604, --send output to message tab
QUERYTRACEON 8605, --input tree
QUERYTRACEON 8608 --initial memo
)
SELECT *
FROM dbo.tableA a
INNER JOIN dbo.tableB b
ON b.ID = a.ID
OPTION (
USE PLAN N'<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5026.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.00657068" StatementText="SELECT *
FROM dbo.tableA a
INNER LOOP JOIN dbo.tableB b
ON b.ID = a.ID" StatementType="SELECT" QueryHash="0x5126A10B217E55B6" QueryPlanHash="0x3700F7E4E3143DF3" RetrievedFromCache="false" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="152">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1056000" EstimatedPagesCached="3168000" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="364530648" />
<RelOp AvgRowSize="15" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657068">
<OutputList>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableA]" Alias="[a]" Column="ID" />
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableB]" Alias="[b]" Column="ID" />
</OutputList>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[TestDB].[dbo].[tableA].[ID] as [a].[ID]=[TestDB].[dbo].[tableB].[ID] as [b].[ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableA]" Alias="[a]" Column="ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableB]" Alias="[b]" Column="ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Table Scan" NodeId="2" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">
<OutputList>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableB]" Alias="[b]" Column="ID" />
</OutputList>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableB]" Alias="[b]" Column="ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[TestDB]" Schema="[dbo]" Table="[tableB]" Alias="[b]" IndexKind="Heap" Storage="RowStore" />
</TableScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Table Scan" NodeId="1" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">
<OutputList>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableA]" Alias="[a]" Column="ID" />
</OutputList>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[tableA]" Alias="[a]" Column="ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[TestDB]" Schema="[dbo]" Table="[tableA]" Alias="[a]" IndexKind="Heap" Storage="RowStore" />
</TableScan>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>',
QUERYTRACEON 3604, --send output to message tab
QUERYTRACEON 8605, --input tree
QUERYTRACEON 8608 --initial memo
)
Alright, so the optimizer always runs to validate the hint, but why does it take so much longer for Joe’s query?
That last bit of information comes from trace flag 8675, which exposes information about phases. Basically, SQL Server has to make tradeoffs between finding an excellent plan and actually getting it to you before the eventual heat death of the universe. Because of this, it does cost-based optimization in three phases. Each phase adds new options for SQL Server to test out, and it only goes to the next phase if the query looks expensive and complicated enough.
Here’s a query with no hint.
And here’s the same query with USE PLAN.
You can see this occur for any plan with a USE PLAN hint, even one that would be otherwise Trivial. They go straight into Search(2) optimization, the most comprehensive search space. This makes sense, because the goal is to check that the plan you supply is at all possible, even if it takes some strange rules to get there.
So what does the optimizer spend so much time on in Joe’s query? It’s testing hundreds of thousands of different ways to order the case statements.