Created
January 31, 2024 01:14
-
-
Save nanoDBA/d8eef910958f5145396bdfb7dbf7c48c to your computer and use it in GitHub Desktop.
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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"} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment