Forrest Shares Stuff

Maybe you’ve seen the Microsoft recommendations on tempdb, that multiple datafiles help reduce contention. They don’t simply say 1 file per CPU though, but to stop at 8 and slowly increase from there. Of course, my curiosity was piqued. Why increase slowly and carefully? Can we run into problems with too many datafiles? How many is too many?

Since there’s no official Microsoft guidance on breaking tempdb (geez, guys…), I looked to the wisdom of an ancient meme, and modified tempdb to have over 9000 datafiles.

That should be enough.

--Set space available in GB
DECLARE @TotalSizeGB DECIMAL(9,2) = 500
DECLARE @ExtraFileCount INT = 9000
DECLARE @SizeMB INT 

--Convert to MB
SET @SizeMB = @TotalSizeGB * 1024

--Find space allowed per file
DECLARE @ExistingFileCount INT = (SELECT COUNT(*) FROM tempdb.sys.database_files WHERE [type] = 0)
DECLARE @SizePerFile INT = FLOOR(@SizeMB/(@ExtraFileCount+@ExistingFileCount))

--Resize existing files
DECLARE @resizescript NVARCHAR(MAX)

SELECT @resizescript = ISNULL(@resizescript,'') +
'DBCC SHRINKFILE('+[name]+','+CONVERT(VARCHAR(20),@SizePerFile)+')'+CHAR(13)
FROM tempdb.sys.database_files WHERE [type] = 0

--sometimes it's hard to shrink tempdb files unless these are run first
PRINT 'USE tempdb
GO
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE(''All'')
GO'

PRINT @resizescript

--tempdb file location will have to be manually edited
DECLARE @i INT = 1
DECLARE @iText VARCHAR(20)
WHILE @i <= @ExtraFileCount
	BEGIN
	SET @iText = CONVERT(VARCHAR(20),@i)
	PRINT 'ALTER DATABASE tempdb ADD FILE (NAME = ''HorribleIdea'+@iText+''', FILENAME = ''L:\tempdb\HorribleIdea'+@iText+'.ndf'', SIZE = '+CONVERT(VARCHAR(20),@SizePerFile)+'MB, FILEGROWTH = 0)
GO'

	SET @i += 1
END

40 minutes later, I’m ready to see if anything breaks.

The obvious starting point is creating a temp table, and sure enough, it’s slower than before, about 30X slower.

DROP TABLE IF EXISTS #DBZepisode
CREATE TABLE #DBZepisode ( 
ID INT IDENTITY PRIMARY KEY, 
Content CHAR(8000) 
) 

INSERT #DBZepisode 
SELECT TOP 100000 'Pointless dialogue'
FROM master..spt_values a 
CROSS JOIN master..spt_values b 
OPTION(MAXDOP 1)

Also interesting is that scans are really slow. Realllllllly slow.

--Picking a filter that won't return any rows
SELECT *
FROM #DBZepisode
WHERE Content = 'Brief combat scene'
OPTION(MAXDOP 1)

Even though there’s obviously a problem here, nothing shows up in the wait stats. (Below is a simple differential script I use).

------------Run at start--------------------------
DROP TABLE IF EXISTS #waits
SELECT *
INTO #waits
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('LOGMGR_QUEUE','CHECKPOINT_QUEUE','LAZYWRITER_SLEEP','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','BROKER_TO_FLUSH','SLEEP_TASK','SP_SERVER_DIAGNOSTICS_SLEEP','BROKER_TASK_STOP','HADR_WORK_QUEUE','HADR_LOGCAPTURE_WAIT',
'BROKER_TRANSMITTER','HADR_CLUSAPI_CALL','QDS_ASYNC_QUEUE','REDO_THREAD_PENDING_WORK','HADR_TIMER_TASK')

-------------Run at end----------------------------
SELECT ws.wait_type, ws.wait_time_ms-w.wait_time_ms AS wait_ms, ws.waiting_tasks_count-w.waiting_tasks_count AS wait_count
FROM sys.dm_os_wait_stats ws
JOIN #waits w ON ws.wait_type = w.wait_type
WHERE ws.waiting_tasks_count > w.waiting_tasks_count
ORDER BY wait_ms DESC

What’s SQL Server burning CPU on? To figure this out I turn to PerfView (ht Josh).

GetNextScanIAM? The engine appears to be spending most of its time traversing the IAM chain, which means it’s using an Allocation Order Scan. In short, SQL Server has multiple access methods. An Allocation Order Scan is only available under certain conditions, which my query on a large local temp table apparently qualifies for. The pages are located from the mapping pages (IAMs), instead of going page-to-page along the leaf level. In normal circumstances, I find an allocation order scan to be faster, but here, it chokes, because there are 5k+ IAMs.

I want to test my hypothesis though. I know that allocation order scans don’t occur when there’s an ORDER BY, so what happens when I add that?

SELECT *
FROM #DBZepisode
WHERE Content = 'Brief combat scene'
ORDER BY ID
OPTION(MAXDOP 1)

Wow. From 22387ms of CPU down to 60.

The fun doesn’t stop here of course (by “fun” I mean the horrible performance problems you might expect from having thousands of datafiles).

It turns out MAX-length variables start having issues after a certain size, since they can be stored in tempdb. Again, the wait stats are silent, but PerfView makes me suspect these variables are written to tempdb as part of a LOB.

DECLARE @ReallyLongYell VARCHAR(MAX) = 'RAHHHH'

SELECT TOP 10000 @ReallyLongYell += REPLICATE('H',60)
FROM master..spt_values a
CROSS JOIN master..spt_values b

The version store (used for optimistic isolation levels) lives in tempdb, so I thought I’d test it out. I crafted a decent workload, confirmed it added significantly to the version store, and then watched it have no problems whatsoever. In fact, it appears to be faster! Oh well…I did learn that the version store usually (for my test) doesn’t cause physical writes to tempdb, but just hangs out in memory until cleaned up.

Spills get written to tempdb, but I didn’t see any performance issues there either, well, other than the fact that there’s a spill happening.

Oh hey, remember how SQL Server rebuilds tempdb every time it restarts? And that I mentioned creating over 9000 files took 40 minutes? You can guess what happens next. Though the SQL Server service restarted within seconds, logins failed for a half hour while tempdb was rebuilding. Fun!

Even a developer would have recognized (probably) that adding thousands of datafiles is a Bad Idea. But this is a Hold My Beer demo, and ended up teaching me a few things about SQL Server. I hope it helped you too!

I think of trace flags like buttons in a Bond car. Find the right one and you have a missile launcher. Press a different one and you’re now frantically trying to stop the self-destruct mechanism. Still, I don’t think I’d be able to to resist pushing buttons, and that’s what I get to do with SQL Server.

There are some really interesting trace flags that expose the internal workings of SQL Server. And there are many more that change the internal workings. Although nobody’s found the self-destruct flag yet, there are some that are pretty close.

So of course, I’m going to try every trace flag.

I’m doing this on a test server of course, one that I don’t mind crashing and burning. My goal is to find flags that change output to the message tab. If you want to see an impressive search method that looks for plan-affecting flags, my SQL senpai Joe Obbish has a post here.

Obviously, testing 11k+ flags one at a time in SSMS is not performant, so I needed a way to grab message output programmatically. Stealing code from here and here, I hacked together a powershell script that I fully expect someone to critique. Even I, a DBA, know that global variables smell funny.

function Get-Msg{

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=MyBreakableTestServer;Database=TestDB;Integrated Security=SSPI;";
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $Global:a += $event.Message}; 
$conn.add_InfoMessage($handler); 
$conn.FireInfoMessageEventOnUserErrors = $true;

$Global:a = ""

$conn.Open();

$cmd = $conn.CreateCommand(); 
$cmd.CommandText = "
SELECT thisisanexamplequery
FROM sometable
OPTION(
RECOMPILE,
QUERYTRACEON 3604,
QUERYTRACEON $args
)
";
$res = $cmd.ExecuteNonQuery();

if($Global:a.Length -ne 0){Write-Host "$args"};

$conn.Close();
}


for ($i = 1; $i -le 11036; $i++) {Get-Msg $i}

There’s a really cool trace flag that adds info on hash joins to the message output, so maybe there’s something that adds info on spills? I’ll use my spilling query (slightly modified) from a previous post to check.

function Get-Msg{

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=MyBreakableTestServer;Database=TestDB;Integrated Security=SSPI;";
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $Global:a += $event.Message}; 
$conn.add_InfoMessage($handler); 
$conn.FireInfoMessageEventOnUserErrors = $true;

$Global:a = ""

$conn.Open();

$cmd = $conn.CreateCommand(); 
$cmd.CommandText = "
SELECT ID
FROM dbo.SpillMe
ORDER BY gooey
OPTION(
RECOMPILE,
MAXDOP 1,
MAX_GRANT_PERCENT = .000001,
QUERYTRACEON 3604,
QUERYTRACEON $args
)
";
$res = $cmd.ExecuteNonQuery();

if($Global:a.Length -ne 0){Write-Host "$args"};

$conn.Close();
}


for ($i = 1; $i -le 11036; $i++) {Get-Msg $i}

Alas, though a number of interesting trace flags show up, none of them seem to include spill details. :'(

Some trace flags modify the output of other trace flags. I’m really interested in 8615, which shows the final memo. Let’s see if anything modifies its output (I get the length of the plain 8615 message, then see if any flag changes the length of message output).

function Get-Msg{

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=MyBreakableTestServer;Database=TestDB;Integrated Security=SSPI;";
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $Global:a += $event.Message}; 
$conn.add_InfoMessage($handler); 
$conn.FireInfoMessageEventOnUserErrors = $true;

$Global:a = ""

$conn.Open();

$cmd = $conn.CreateCommand(); 
$cmd.CommandText = "
SELECT ID
FROM dbo.SpillMe
ORDER BY gooey
OPTION(
RECOMPILE,
QUERYTRACEON 3604,
QUERYTRACEON 8757, --skip TRIVIAL to guarantee we get a memo
QUERYTRACEON 8615,
QUERYTRACEON $args
)
";
$res = $cmd.ExecuteNonQuery();

#$Global:a.Length; 

if($Global:a.Length -ne 483){Write-Host "$args"};

$conn.Close();
}


for ($i = 8600; $i -le 8800; $i++) {Get-Msg $i}

Success! I get a number of known trace flags, but also 8672, which I can’t find good info on. That one’s going in the backlog of things to blog about (a backblog?).

I have quite a few variations I want to try with this method, as well as potential improvements. Using DBCC TRACEON instead of QUERYTRACEON seems an obvious one, along with only getting estimated plans (when applicable) to save time.

Still looking for the missile launcher though.

Ever see a SELECT query with WRITELOG waits? I did, and exclaimed, “Oooh, interesting!” to the annoyance of all my cube neighbors. There was no data changing to require logging, so where was it coming from? After a little thought, I hypothesized that a spill to tempdb generates log activity.

But can I prove it?

The first ingredient for this experiment is a spill, and probably the most common operator to spill would be the sort, so let’s build a table to sort:

DROP TABLE IF EXISTS #Valdez
CREATE TABLE #Valdez (
ID INT IDENTITY PRIMARY KEY,
gooey BINARY(16)
)

INSERT #Valdez
SELECT TOP 10000 NEWID()
FROM master..spt_values a
CROSS JOIN master..spt_values b

Ok, now we need a query that will spill. A spill occurs when SQL Server doesn’t provide enough memory to an operator, and it has to use space in tempdb to complete the operation. So we need a way to get SQL Server to under-provision memory. The most common way this occurs in the wild is when the actual number of rows passed to a sort is far higher than what the optimizer estimated. But this is SCIENCE[!] so let’s screw with the memory grant instead.

SELECT ID
FROM #Valdez
ORDER BY gooey
OPTION(
MAX_GRANT_PERCENT = .000001
)

Now we need to read the log generated. Start with a CHECKPOINT command. Since tempdb is in simple recovery (and there are no other ongoing transactions because this is a test instance…right?), this will clear the logs. Then run the spilling query (I use a trash variable to prevent displaying results) and follow it up with the handy-dandy undocumented fn_dblog().

USE tempdb
GO

CHECKPOINT
GO

DECLARE @trash int

SELECT @trash = ID
FROM #Valdez
ORDER BY gooey
OPTION(
MAX_GRANT_PERCENT = .000001
)

SELECT Operation, Context, [Page ID], [Transaction Name], Description, [Log Record Length]
FROM sys.fn_dblog(NULL,NULL)

 

QED

I see a lot of activity on PFS, GAM, and IAM pages that makes me think this is minimally efficiently logged – only the allocations without the content of the spill. It’s even possible to find one of these sort pages by checking allocated pages mentioned in the description column. You can go digging for one yourself, if it’s the kind of thing to make you exclaim, “Oooh, interesting!”

It’s hard to ask a question about SQL Server to which the answer isn’t, “It depends.” Unless of course you’re asking if you should use Auto-Shrink (the answer is NO! and an angry glare).  Edge cases are everywhere, and hidden gotchas lurk around the corner even for well-known features.

But what about the maximum size of a row on page? Microsoft and even error messages say that you can’t go past 8060 bytes. Surely we can trust that this is an accurate limit?

Hold my beer.

Start with a basic setup, then try to go past 8060 bytes.

USE master
GO

DROP DATABASE IF EXISTS ForrestSaysHi
CREATE DATABASE ForrestSaysHi
GO

USE ForrestSaysHi
GO

--too wide
CREATE TABLE dbo.TooWideTable (
ID int,
junk CHAR(8000),
filler CHAR(100)
)

Yup, there’s the error message.

Right-size a column to get exactly 8060 bytes. We can even use DBCC PAGE to confirm its length.

--just right
CREATE TABLE dbo.WideTable (
ID int,
junk CHAR(8000),
filler CHAR(49)
)
INSERT dbo.WideTable
VALUES (1,REPLICATE('a',8000),REPLICATE('b',49))
GO

--check the row size with DBCC PAGE
DECLARE @page INT

SELECT TOP 1 @page = allocated_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID('ForrestSaysHi'),OBJECT_ID('ForrestSaysHi.dbo.WideTable'),NULL,NULL,'Detailed')
WHERE is_allocated = 1
AND is_iam_page = 0

DBCC TRACEON(3604)

DBCC PAGE('ForrestSaysHi',1,@page,1)

Make a couple changes, add another row…

USE master
GO

ALTER DATABASE ForrestSaysHi
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE ForrestSaysHi
GO

CREATE CLUSTERED INDEX CX_blahblah ON dbo.WideTable(ID)
GO

INSERT dbo.WideTable
VALUES (1,REPLICATE('a',8000),REPLICATE('b',49))
GO

--check the row size with DBCC PAGE
DECLARE @page INT

--grab the last row
SELECT TOP 1 @page = allocated_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID('ForrestSaysHi'),OBJECT_ID('ForrestSaysHi.dbo.WideTable'),NULL,NULL,'Detailed')
WHERE is_allocated = 1
AND is_iam_page = 0
ORDER BY allocated_page_page_id DESC

DBCC TRACEON(3604)

DBCC PAGE('ForrestSaysHi',1,@page,1)

And now we have 8082 bytes!

Once you stop applauding this incredible feat, you might stop to ask how I managed it. What’s going on is combination of two tricks. The first has to do with enabling Snapshot Isolation. Snapshot is an isolation level that allows users to view data consistent to a point in time without locking the row. SQL Server manages this by storing old versions of the row in tempdb, and keeping a pointer to those versions in a 14-byte versioning tag added to the row.

The last 8 bytes come from the clustered index I added. Notice how there’s a clustered index, but no primary key? It’s how I was able to add two rows with the same ID of “1”. SQL Server has to uniquely identify rows though (it’s how it points back to the source row from non-clustered indexes), so it adds a “uniquefier” taking up a total of 8 bytes to the second row.

Like any good Hold My Beer stunt, this one ends with a big error. Watch what happens if you try to update that second row.

--succeeds
UPDATE TOP (1) ForrestSaysHi.dbo.WideTable
SET filler = REPLICATE('z',49)

--fails (don't try this at home, kids!)
UPDATE ForrestSaysHi.dbo.WideTable
SET filler = REPLICATE('z',49)

I wouldn’t be surprised if there’s a way to push this limit even higher. Spend some time investigating row length for yourself, and see if you can break my record of 8082 bytes. Then, when someone asks how you maintain your grand DBA physique, just say “competitive rowing.”

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.