PowerShell Function to Get Query Store Data Across Multiple Databases

In SQL Server 2016, Microsoft introduced to us the Query Store.  This has been proven to be a very beneficial feature especially at my job, saved the day on Christmas Eve. One of the limitations if see is when you have server with several databases on it trying to find the one that has the problem. So until Microsoft gives use a better tool or someone builds one I wrote a quick PowerShell function to query across all the databases and return data to a grid so you can sort and find that troublesome query.

To call it simply provide the server, which metric you want, the top number of queries to return, who many hours to look back, and the minimum execution count.

Get-QueryStore -Server "." -Metric "cpu" -Top 10 -Hours 1 -MinExecutionCount 10

Here is the function. Enjoy! Be warned if you have a lot of databases say like 300 on the same server it will take a long time to run. So you may want to adjust the QueryTimeout parameter near the bottom or add a WHERE clause to the sys.databases table.

function Get-QueryStore() {
<# 
.SYNOPSIS This function queries all databases for query store information on an instance and displays it in data grid. 

.PARAMETER Server The Instance to collection information from .PARAMETER Metric Specifies which resource metric you want to query by executions, duration, cpu, reads, writes, memory, rowcount. 

.PARAMETER Top Number of results to return from each database. .PARAMETER Hours Number of hours to look back. 

.PARAMETER MinExecutionCount Minium number of execution counts to show up in grid. 

.EXAMPLE This returns the top 10 queries consuming CPU in each db on the server specified in the last hour. 
Get-QueryStore -Server "." -Metric "cpu" -Top 10 -Hours 1 -MinExecutionCount 10 
#>

    Param
    (
    [Parameter(Mandatory=$true)]
    [string] $Server,
	
    [Parameter(Mandatory=$true)]
    [ValidateSet("executions", "duration", “cpu", "reads", "writes", "memory", "rowcount")] 
    [string] $Metric,
	
    [Parameter(Mandatory=$true)]
    [int] $Top,

    [Parameter(Mandatory=$true)]
    [int] $Hours,
    
    [Parameter(Mandatory=$true)]
    [int] $MinExecutionCount
    )

    if ($Metric -eq "executions")
    {$Field = "count_executions"}
    elseif ($Metric -eq "cpu")
    {$Field = "cpu_time"}
    elseif ($Metric -eq "reads")
    {$Field = "logical_io_reads"}
    elseif ($Metric -eq "writes")
    {$Field = "logical_io_writes"}
    elseif ($Metric -eq "memory")
    {$Field = "query_max_used_memory"}
    else
    {$Field = $Metric}

    if ($Metric -eq "executions")
    {
        $sql = @"
            DECLARE @SQL NVARCHAR(MAX) = ''

            SELECT @SQL += REPLACE(REPLACE('UNION ALL
							            SELECT * FROM (SELECT TOP $($Top) "{{@DatabaseName}}" as DBName, sum(rs.$($Field)) $($Field), 
                                                            min(rs.first_execution_time) first_execution_time, max(rs.last_execution_time) last_execution_time, 
                                                            GETUTCDATE() AS CurrentUTCTime, q.query_id, qt.query_text_id, p.plan_id, qt.query_sql_text   
											            FROM {{@DatabaseName}}.sys.query_store_query_text AS qt   
												            JOIN {{@DatabaseName}}.sys.query_store_query AS q   
													            ON qt.query_text_id = q.query_text_id   
												            JOIN {{@DatabaseName}}.sys.query_store_plan AS p   
													            ON q.query_id = p.query_id   
												            JOIN {{@DatabaseName}}.sys.query_store_runtime_stats AS rs   
													            ON p.plan_id = rs.plan_id  
											            WHERE rs.last_execution_time > DATEADD(hour, -$($Hours), GETUTCDATE())  
                                                        GROUP BY q.query_id, qt.query_text_id, p.plan_id,  qt.query_sql_text
                                                        ORDER BY sum(rs.$($Field)) DESC) t
                                        '
				            , '{{@DatabaseName}}', name)
				            ,'"','''')
			            FROM sys.databases 
			            ;

            SET @SQL = STUFF(@SQL, 1, 9, '')
            SET @SQL += 'ORDER BY $($Field) DESC'

            EXECUTE (@SQL) 
"@
    }
    else
    {
        $sql = @"
            DECLARE @SQL NVARCHAR(MAX) = ''

            SELECT @SQL += REPLACE(REPLACE('UNION ALL
							            SELECT * FROM (SELECT TOP $($Top) "{{@DatabaseName}}" as DBName, 
                                                            sum(rs.avg_$($Field) * rs.count_executions) / sum(rs.count_executions) avg_$($Field), 
                                                            sum(rs.count_executions) count_executions, sum(rs.avg_$($Field) * rs.count_executions) total_$($Field),
                                                            max(last_$($Field)) last_$($Field), min(min_$($Field)) min_$($Field), max(max_$($Field)) max_$($Field),
                                                            GETUTCDATE() AS CurrentUTCTime, min(rs.first_execution_time) first_execution_time,
                                                            max(rs.last_execution_time) last_execution_time, q.query_id, qt.query_text_id, p.plan_id, qt.query_sql_text   
											            FROM {{@DatabaseName}}.sys.query_store_query_text AS qt   
												            JOIN {{@DatabaseName}}.sys.query_store_query AS q   
													            ON qt.query_text_id = q.query_text_id   
												            JOIN {{@DatabaseName}}.sys.query_store_plan AS p   
													            ON q.query_id = p.query_id   
												            JOIN {{@DatabaseName}}.sys.query_store_runtime_stats AS rs   
													            ON p.plan_id = rs.plan_id  
											            WHERE rs.last_execution_time > DATEADD(hour, -$($Hours), GETUTCDATE())  
                                                        GROUP BY q.query_id, qt.query_text_id, p.plan_id,  qt.query_sql_text
                                                        HAVING sum(rs.count_executions) > $($MinExecutionCount)
                                                        ORDER BY sum(rs.avg_$($Field) * rs.count_executions) / sum(rs.count_executions) DESC) t
                                        '
				            , '{{@DatabaseName}}', name)
				            , '"', '''')
			            FROM sys.databases
WHERE name like 'A%' 
			            ;

            SET @SQL = STUFF(@SQL, 1, 9, '')
            SET @SQL += 'ORDER BY avg_$($Field) DESC'

            EXECUTE (@SQL) 
"@
    }

    $dt = Invoke-SQLCMD -Query $sql -ServerInstance $Server -database "master" -QueryTimeout 0

    $dt | Out-GridView
}

Related Posts

One thought on “PowerShell Function to Get Query Store Data Across Multiple Databases

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.