Example for how to get the query plan from SQL Server via .Net code,

Log-SQL-Plan.ps1

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'))
    }

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.