A Programmatic Interface to the Query Hint Recommendation Tool

The Query Hint Recommendation Tool (QHRT), available in SQL Server Management Studio (SSMS) 22, evaluates query hints against a specific query and identifies the query hint that corresponds to the best-performing execution plan. With QHRT, customers no longer need to manually test different hints – simply invoke QHRT for your expensive query and let it efficiently explore a variety of query hints on your behalf. To extend automated hint identification even further, the team has developed a PowerShell script to expose the QHRT capability, so you can efficiently tune a set of queries programmatically without manual trial and error.

Because QHRT is built on a flexible backend library (DLL), you aren't limited to running it manually. You can easily integrate it into whatever automation system you already use. Imagine setting up a nightly background job that automatically scans your database for slow queries, passes them to QHRT to find the best performance hints, and generate a list of fixes for you to review the next morning. As you develop confidence in the recommendations, you can take it one step further and apply the fixes automatically using Query Store Hints. In short, you can build a completely automated, "hands-free" tuning pipeline.

Requirements

• Windows PowerShell 5.1 or higher with the SqlServer PowerShell module installed
• SSMS 22 with the Query Hint Recommendation Tool component, or the Code Tools workload, installed

Getting started

To set up automated hint identification for your environment, follow these steps:

1. Save the script below as `ps_queryHint.ps1`
2. Create a file with one query in it, e.g., `C:\QHRT\query.sql`
3. Run the following PowerShell command, replacing `localhost` with the name of your database server, and `mydb` with the name of the database where the query executes:

   .\ps_queryHint.ps1 -Server 'localhost' -Database 'mydb' -QueryFile 'C:\QHRT\query.sql'

Parameters

Output

Running the script returns a PowerShell object with tuning results, for example:

Output from programmatic execution of QHRT

In this example, the output shows that the best hint for the query is a LOOP JOIN.  You can then apply that hint to the query, either within your code or using a Query Store Hint. 

Fields

• QueryId: Query identifier (derived from filename)
• LogFile: File with stepwise tuning logs with name using QueryId and timestamp, stored in the same folder as input query file
• Success: Indicates whether tuning completed without any errors
• BestHint: The query hint that led to the best performing execution plan
• OriginalElapsedTime: Execution time of the plan without any hint (in milliseconds)
• BestPlanElapsedTime: Execution time of the best plan found by QHRT (in milliseconds)
• TuningTime: Total time spent searching for the best hint (in seconds)

Extended list of query hints

The script provided below includes only a small number of hints. If the user is interested in exploring more hints (listed below), they can be saved in a text file and passed using the “-HintsFile” parameter

OPTION(HASH JOIN)

OPTION(MERGE JOIN)

OPTION(LOOP JOIN)

OPTION(FORCE ORDER)

OPTION(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

OPTION(ORDER GROUP)

OPTION(HASH GROUP)

OPTION(CONCAT UNION)

OPTION(HASH UNION)

OPTION(MERGE UNION)

OPTION(EXPAND VIEWS)

OPTION(NO_PERFORMANCE_SPOOL)

OPTION(USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'))

OPTION(USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'))

OPTION(USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'))

OPTION(USE HINT ('ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'))

OPTION(USE HINT ('ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'))

OPTION(USE HINT ('DISALLOW_BATCH_MODE'))

OPTION(USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION'))

OPTION(USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

Summary

This script provides a simple, standalone way to programmatically invoke the Query Hint Recommendation Tool from PowerShell. Save it, adjust the parameters to match your environment, and run it against any query or set of queries for which you want to improve performance using hints. Happy tuning!

Script

The script below can saved as a PowerShell file, for example, ps_queryHint.ps1.

#requires -Version 5.1
#requires -PSEdition Desktop
param(
    [string]$Server = 'localhost',
    [string]$Database = 'dbname',
    [Parameter(Mandatory = $true)]
    [string]$QueryFile,
    [double]$Threshold = 80,
    [string]$IDE = '',
    [string]$HintsFile = ''
)
function Find-SSMSIDEPath {
    foreach ($base in 'C:\Program Files', 'C:\Program Files (x86)') {
        $result = Get-ChildItem $base -Filter "*SQL Server Management Studio*" `
            -Directory -EA 0 | Sort-Object Name -Descending |
            ForEach-Object { Join-Path $_.FullName 'Release\Common7\IDE' } |
            Where-Object { Test-Path $_ } | Select-Object -First 1
        if ($result) { return $result }
    }
}
# Auto-detect IDE path if not provided
if (-not $IDE) {
    $IDE = Find-SSMSIDEPath
    if (-not $IDE) {
        throw 'SQL Server Management Studio IDE folder not found. Please install SSMS or provide -IDE parameter with path.'
    }
    Write-Host "Auto-detected SSMS IDE path: $IDE" -ForegroundColor Cyan
}
function Get-CleanQueryText {
    param([string]$QueryText)
    $pattern = '(?is)\s*OPTION\s*\(.*?\)\s*;?\s*$'
    $cleaned = [regex]::Replace($QueryText, $pattern, '')
    return $cleaned.TrimEnd(';', "`r", "`n", ' ')
}
function Get-HintList {
    param([string]$HintsFilePath)
    if ($HintsFilePath -and (Test-Path $HintsFilePath -PathType Leaf)) {
        $content = Get-Content $HintsFilePath | Where-Object { $_.Trim() }
        return [System.Collections.Generic.List[string]]$content
    }
    return [System.Collections.Generic.List[string]]@(
        'OPTION (USE HINT (''FORCE_LEGACY_CARDINALITY_ESTIMATION''))',
        'OPTION (MAXDOP 8)',
        'OPTION (FORCE ORDER)',
        'OPTION (HASH JOIN)',
        'OPTION (LOOP JOIN)'
    )
function Initialize-QueryHintAssemblies {
    param([string]$IdePath)
    $qhPath = Join-Path $IdePath 'Extensions\Microsoft\SSMS.QueryHintTool'
    if (-not (Test-Path $qhPath -PathType Container)) {
        throw "QueryHint extension folder not found at: $qhPath"
    }
    Import-Module SqlServer -ErrorAction Stop
    $loadedAssemblies = [System.AppDomain]::CurrentDomain.GetAssemblies() |
        ForEach-Object { $_.GetName().Name }
    $dllNames = @('Microsoft.Scripting', 'Microsoft.Scripting.Metadata',
        'Microsoft.Dynamic', 'Microsoft.SqlServer.QueryHinting')
    foreach ($dll in $dllNames) {
        $dllPath = Join-Path $qhPath "$dll.dll"
        if (-not (Test-Path $dllPath -PathType Leaf)) {
            throw "$dll.dll not found at: $dllPath"
        }
        if ($loadedAssemblies -notcontains $dll) {
            Add-Type -Path $dllPath
        }
    }
}
function New-QueryTuningConfig {
    param(
        [string]$QueryId,
        [string]$QueryText,
        [System.Collections.Generic.List[string]]$HintList,
        [double]$ThresholdValue,
        [string]$WorkDir
    )
    $runStamp = Get-Date -Format 'yyyyMMdd_HHmmss'
    $config = New-Object QueryHinting.QueryTuningConfig
    $config.QueryId = $QueryId
    $config.QueryText = $QueryText
    $config.HintList = $HintList
    $config.Threshold = $ThresholdValue
    $config.LogToFile = $true
    $logFileName = "{0}_{1}.log" -f $QueryId, $runStamp
    $config.LogFile = Join-Path $WorkDir $logFileName
    $config.WorkDir = $WorkDir
    $config.Logging = 2
    return $config
}
function Show-TuningConfiguration {
    param([QueryHinting.QueryTuningConfig]$Config, [string]$ServerName,
        [string]$DatabaseName)
    Write-Host 'Starting tuning process...' -ForegroundColor Green
    [PSCustomObject]@{
        QueryId = $Config.QueryId
        Server = $ServerName
        Database = $DatabaseName
        Threshold = '{0}%' -f ([Math]::Round($Config.Threshold * 100, 0))
        LogFile = $Config.LogFile
    } | Format-List QueryId, Server, Database, Threshold, LogFile | Out-Host
}
function Show-TuningResult {
    param($Result, [QueryHinting.QueryTuningConfig]$Config)
    if ($Result) {
        Write-Host '--- Tuning Result ---' -ForegroundColor Green
        $Result | Select-Object Success, BestHint, `
            @{Name = 'OriginalElapsedTime'; Expression = { "$($_.OriginalElapsedTimeMs) msec" }},
            @{Name = 'BestPlanElapsedTime'; Expression = { "$($_.BestPlanElapsedTimeMs) msec" }},
            @{Name = 'ElapsedTimeImprovement'; Expression = { '{0}%' -f $_.ElapsedTimeImprovement }},
            @{Name = 'TuningTime'; Expression = { "$([Math]::Round($_.TuningTimeMs / 1000, 1)) sec" }},
            @{Name = 'Num of Hints'; Expression = { $_.HintCount }},
            @{Name = 'Distinct Plans'; Expression = { $_.ExecutedPlans - 2 }},
            @{Name = 'Successful Executions'; Expression = { $_.ExecutedPlans - $_.ExceptionPlans - 2 }} |
            Format-List | Out-Host
    }
    else {
        Write-Host "No tuning result returned. Check log file for details: $($Config.LogFile)" `
            -ForegroundColor Yellow
    }
}
function Invoke-QueryHintTuning {
    param([string]$ServerName, [string]$DatabaseName, [string]$QueryFilePath,
        [double]$ThresholdValue, [string]$IdePath, [string]$HintsFilePath)
    if (-not (Test-Path $QueryFilePath -PathType Leaf)) {
        throw "Query file not found at: $QueryFilePath"
    }
    if ($ThresholdValue -lt 0 -or $ThresholdValue -gt 100) {
        throw "Threshold must be between 0 and 100 (percentage). Received: $ThresholdValue"
    }
    $workDir = Split-Path -Parent $QueryFilePath
    $queryId = [IO.Path]::GetFileNameWithoutExtension($QueryFilePath)
    $thresholdRatio = [Math]::Round($ThresholdValue / 100, 2)
    $queryTextRaw = Get-Content $QueryFilePath -Raw
    $queryTextClean = Get-CleanQueryText -QueryText $queryTextRaw
    $hintList = Get-HintList -HintsFilePath $HintsFilePath
    Initialize-QueryHintAssemblies -IdePath $IdePath
    $config = New-QueryTuningConfig -QueryId $queryId -QueryText $queryTextClean `
        -HintList $hintList -ThresholdValue $thresholdRatio -WorkDir $workDir
    $conn = New-Object Microsoft.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Data Source=$ServerName;Initial Catalog=$DatabaseName;" +
        "Integrated Security=SSPI;TrustServerCertificate=True"
    $conn.Open()
    try {
        Show-TuningConfiguration -Config $config -ServerName $ServerName -DatabaseName $DatabaseName
        $result = (New-Object QueryHinting.QueryTuner).TuneQuery($conn, $config, $null)
        Show-TuningResult -Result $result -Config $config
        return $result
    }
    finally {
        $conn.Close()
    }
}
function Invoke-QueryHintBatch {
    param([string]$ServerName, [string]$DatabaseName, [string]$QueryPath,
        [double]$ThresholdValue, [string]$IdePath, [string]$HintsFilePath)
    if (-not (Test-Path $QueryPath)) {
        throw "Query path not found at: $QueryPath"
    }
    if (Test-Path $QueryPath -PathType Container) {
        $queryFiles = Get-ChildItem -Path $QueryPath -Filter '*.sql' -File | Sort-Object FullName
        if (-not $queryFiles -or $queryFiles.Count -eq 0) {
            throw "No .sql files found in folder: $QueryPath"
        }
    }
    else {
        if (-not (Test-Path $QueryPath -PathType Leaf)) {
            throw "Query file not found at: $QueryPath"
        }
        $queryFiles = @(Get-Item -Path $QueryPath)
    }
    $total = $queryFiles.Count
    $index = 0
    $failed = 0
    foreach ($file in $queryFiles) {
        $index++
        Write-Host ("[{0}/{1}] Processing {2}" -f $index, $total, $file.FullName) -ForegroundColor Cyan
        try {
            $result = Invoke-QueryHintTuning -ServerName $ServerName -DatabaseName $DatabaseName `
                -QueryFilePath $file.FullName -ThresholdValue $ThresholdValue -IdePath $IdePath `
                -HintsFilePath $HintsFilePath
            $resultObj = if ($result -is [array]) { if ($result.Count -gt 0) { $result[0] } else { $null } } else { $result }
            if (-not $resultObj -or -not [bool]$resultObj.Success) {
                $failed++
                Write-Warning "Tuning reported Success=False for: $($file.FullName)"
            }
        }
        catch {
            $failed++
            Write-Warning "Failed: $($file.FullName)`n$($_.Exception.Message)"
        }
    }
    $ok = $total - $failed
    Write-Host "Batch complete. Total: $total, Success: $ok, Failed: $failed" -ForegroundColor Green
}
Invoke-QueryHintBatch -ServerName $Server -DatabaseName $Database -QueryPath $QueryFile `
    -ThresholdValue $Threshold -IdePath $IDE -HintsFilePath $HintsFile