Powershell to Search for Trace Flags

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.