This PowerShell script allows users to export data from SQL Server Query Store to a PivotTable. The script utilizes the sp_QuickieStore stored procedure from Erik Darling ( https://erikdarling.com/sp_quickiestore/ ) to retrieve performance data from the Query Store and then formats it into a PivotTable for additional analysis and visualization.

QueryStore Export to PivotTable using sp_QuickieStore.ps1

# create an array of SQL Server instances
$allInstances = @('server1', 'server2', 'server3')

# Loop through each SQL server instance
foreach($sqlInstance in $allInstances) { 

    # Output the current SQL server instance
    Write-Output "`$sqlInstance: $sqlInstance"

    # Execute a query on the current SQL server instance and store the results
    $results =  Invoke-DbaQuery -SqlInstance $sqlInstance -Query "exec sp_QuickieStore 
        @database_name = 'WideWorldImporters'
      , @top = 20
      , @sort_order = 'executions' --pick one 'cpu, logical reads, physical reads, writes, duration, memory, tempdb, executions, recent'
      --, @execution_count = 5
      , @query_text_search = 'SELECT * from Sales.Invoices'
      , @start_date = '2024-01-20'
      --, @include_query_ids = ','
      --, @workdays = 1        /*Use this to filter out weekends and after-hours queries*/
      --, @work_end   = '5pm'  /*Use this to set a specific end of your work days*/
      --, @work_start = '7am'  /*Use this to set a specific start of your work days*/
      --, @duration_ms = 45" -AppendServerInstance -MessagesToOutput

    # Output the count of results
    Write-Output "`$results.Count: $($results.Count)"

    # Define the file description, worksheet name, and filename for the Excel file
    $fileDescription = "$($sqlInstance)" # assign $fileDescription variable (used for the worksheet name and filename)
    $worksheetName = $fileDescription -replace "[-_]", "" # remove dashes and underscores from the worksheet name and file description
    $filenameExcel = ("$env:USERPROFILE\Documents\" + [string](Get-Date -format "yyyy-MM-dd__HHmmss") + "_" + $fileDescription + ".xlsx" ); #assign $filename variable

    # Define parameters for the Excel export
    $paramHash = @{
        Path              = $filenameExcel
        WorksheetName     = $worksheetName
        TableName         = $worksheetName
        TableStyle        = 'Medium27'
        AutoSize          = $True
        IncludePivotTable = $True
        PivotRows         = "query_id"
        PivotData         = @{total_duration_ms = 'sum'}
        AutoFilter        = $True
        PivotFilter       = @("total_cpu_time_ms", "total_logical_io_reads_mb", "plan_forcing_type_desc", "count_executions")
        IncludePivotChart = $True
        ChartType         = "PieExploded3D"
        ShowCategory      = $True
        ShowPercent       = $True
    }

    # Export the results to an Excel file
    $results | Export-Excel @paramHash -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors

    # Check if the Excel file was created and output a message
    if(Test-Path $filenameExcel) { Write-Output "$filenameExcel exported"}
}