Skip to content

Instantly share code, notes, and snippets.

@chrisoldwood
Last active March 29, 2019 10:53
Show Gist options
  • Save chrisoldwood/24add9241e48b6beb691aabe11e544e8 to your computer and use it in GitHub Desktop.
Save chrisoldwood/24add9241e48b6beb691aabe11e544e8 to your computer and use it in GitHub Desktop.
Example for how to get the query plan from SQL Server via .Net code,
Set-StrictMode -Version Latest
$ErrorActionPreference = 'stop'
$instance = '.\SQLEXPRESS'
$database = 'database'
$configuration = "Server=$instance;Database=$database;Trusted_Connection=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection $configuration
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = 'SET SHOWPLAN_XML ON'
[void]$command.ExecuteNonQuery()
$command.CommandText = @"
SELECT * FROM Table
"@
$reader = $command.ExecuteReader()
[void]$reader.Read()
$plan = $reader.GetValue(0)
$reader.Close();
$connection.Close()
#Write-Host $plan
function Has-Attribute($node, $name)
{
if ($node.Node.Attributes | where { $_.Name -eq $name }) { $true } else { $false }
}
function Get-AttributeValue($node, $name)
{
$node.Node.Attributes | where { $_.Name -eq $name } | foreach { $_.'#text' }
}
$namespace = @{ns = "http://schemas.microsoft.com/sqlserver/2004/07/showplan"}
Select-Xml -Content $plan -XPath '//ns:StmtSimple' -Namespace $namespace |
where { Has-Attribute $_ 'StatementSubTreeCost' } |
foreach {
Write-Host ('StatementSubTreeCost: {0}' -f (Get-AttributeValue $_ 'StatementSubTreeCost'))
Write-Host ('StatementEstRows: {0}' -f (Get-AttributeValue $_ 'StatementEstRows'))
}
@chrisoldwood
Copy link
Author

One way to avoid concurrently hitting a database with too many heavy queries is to sniff out the cost from the query plan up front and then react accordingly, e.g. serialize really heavy queries through a queue. This script requests the query plan from SQL Server and then extracts the total estimated cost and row count.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment