Category: Uncategorized

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.