Month: July 2018

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&#xD;&#xA;  MAX(CASE WHEN ID1 = 1 THEN 1 ELSE 0 END)&#xD;&#xA;, MAX(CASE WHEN ID1 = 2 THEN 1 ELSE 0 END)&#xD;&#xA;, MAX(CASE WHEN ID1 = 3 THEN 1 ELSE 0 END)&#xD;&#xA;, MAX(CASE WHEN ID1 = 4 THEN 1 ELSE 0 END)&#xD;&#xA;, MAX(CASE WHEN ID1 = 5 THEN 1 ELSE 0 END) &#xD;&#xA;, MAX(CASE WHEN ID1 = 6 THEN 1 ELSE 0 END)&#xD;&#xA;, MAX(CASE WHEN ID1 = 7 THEN 1 ELSE 0 END)&#xD;&#xA;, MAX(CASE WHEN ID1 = 8 THEN 1 ELSE 0 END) &#xD;&#xA;, MAX(CASE WHEN ID1 = 9 THEN 1 ELSE 0 END)&#xD;&#xA;, MAX(CASE WHEN ID1 = 10 THEN 1 ELSE 0 END)&#xD;&#xA;--, MAX(CASE WHEN ID1 = 11 THEN 1 ELSE 0 END)&#xD;&#xA;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="&#xD;&#xA;SELECT ID&#xD;&#xA;FROM dbo.tableA a&#xD;&#xA;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 *&#xD;&#xA;FROM dbo.tableA a&#xD;&#xA;INNER LOOP JOIN dbo.tableB b&#xD;&#xA;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.

There’s a lot of good SQL Server information out there, so much so that I was very surprised I couldn’t find a good write-up of using the USE PLAN hint. I even checked the second page of Google results! So, in hopes of being the first to write a guide, or more likely to join the other dozen bloggers who wrote about this but didn’t bother with SEO, I present to you How to Use the USE PLAN Hint.

Say you have a simple query: you want to join two single-row heaps on their ID columns (I was going to say something about this being an unrealistic demo, then I realized that you probably work with developers too).

Setup:

DROP TABLE IF EXISTS dbo.tableA
DROP TABLE IF EXISTS dbo.tableB

CREATE TABLE dbo.tableA (ID INT)
CREATE TABLE dbo.tableB (ID INT)

INSERT dbo.tableA VALUES (1)
INSERT dbo.tableB VALUES (1)

Simple Query:

SELECT *
FROM dbo.tableA a
INNER JOIN dbo.tableB b
ON b.ID = a.ID

As you can see, this produces a hash join, even with such a low rowcount.

“But wait,” you say, “I can use the exotic USE PLAN hint to force a nested loop join! This kind of skill will finally demonstrate to my boss that I’m ready to be a Senior DBA!”

So here’s what you do: add the LOOP hint and get the estimated plan XML from right-clicking the plan. (Actual plan XML will work too).

Add the following to the end of your query, and paste the XML inside the single quotes. Mind the whitespace though, a linebreak at the beginning causes an error.

OPTION (
USE PLAN N''
)

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 *&#xD;&#xA;FROM dbo.tableA a&#xD;&#xA;INNER LOOP JOIN dbo.tableB b&#xD;&#xA;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="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>
                <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>
              </NestedLoops>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>'
)

Voila! You have a loop join!

You can even manually edit the XML to switch the order of the table join!

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 *&#xD;&#xA;FROM dbo.tableA a&#xD;&#xA;INNER LOOP JOIN dbo.tableB b&#xD;&#xA;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>'
)

There are a number of important caveats and gotchas with USE PLAN, but who cares? Go impress your boss!

Have you ever wondered how many plus signs you can put in front of a number before SQL Server throws a fit?

SELECT +++++++++++1

You haven’t?! That’s probably because you have production issues to solve, and I was just bored one afternoon. But are you curious now? Add enough +s and you’ll eventually get an error saying

Some part of your SQL Statement is nested too deeply.

If you’re like me (nerdy, intrigued, and without any production issues to solve right away), you’ll probably use something similar to the binary search algorithm to find the most + signs possible. Lucky for you, I already wrote the algorithm in T-SQL

DECLARE @MaxSuccess int = 1, --lower bound for testing, largest known success
@MinFailure int = 2000, --upper bound for testing, smallest known failure
@NumToTest int = 500,
@sql nvarchar(max)

WHILE @MaxSuccess <> @NumToTest
BEGIN
  BEGIN TRY
    SET @sql = 'SET PARSEONLY ON SELECT '+REPLICATE('+',@NumToTest)+'1'
    EXEC sp_executesql @sql
    --code below only runs on success
    SET @MaxSuccess = @NumToTest
    SET @NumToTest = FLOOR((@NumToTest+@MinFailure)/2)
  END TRY

  BEGIN CATCH
    SET @MinFailure = @NumToTest
    SET @NumToTest = FLOOR((@MaxSuccess+@NumToTest)/2)
  END CATCH
END

SELECT @MaxSuccess AS 'Max success with PARSEONLY on'

Adding SET PARSEONLY to the query counts against the number of + signs. Add 1 to the final result as a true max if you’re mashing the + key N times.

I get 1015 + signs as a max in SQL Server 2016. Different versions got different results for me.

Bonus question: how many – signs can you use? Seriously, try it out by replacing the + with a – in the script. Then chuckle as you figure out what happened.

The first post on a blog feels to me like the objective statement on a resume. I see everyone else doing it, so I feel like I have to also, even if it’s bland and everyone already knows what it’s going to say.

Seeking to utilize my experience and problem-solving abilities to effectively share knowledge and increase community collaboration!

Meh. I have my reasons for starting a blog, and as far as I can tell, they’re the standard ones. I’m not going to bore you with them. This post is just so I can feel like I’m making progress on WordPress.