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 *
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="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 *
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>'
)
There are a number of important caveats and gotchas with USE PLAN, but who cares? Go impress your boss!