Generating a Jupyter Notebook for Glenn Berry’s Diagnostic Queries with PowerShell

The March release of Azure Data Studio now supports Jupyter Notebooks with SQL kernels. This is a very interesting feature that opens new possibilities, especially for presentations and for troubleshooting scenarios.

For presentations, it is fairly obvious what the use case is: you can prepare notebooks to show in your presentations, with code and results combined in a convenient way. It helps when you have to establish a workflow in your demos that the attendees can repeat at home when they download the demos for your presentation.

For troubleshooting scenarios, the interesting feature is the ability to include results inside a Notebook file, so that you can create an empty Notebook, send it to your client and make them run the queries and send it back to you with the results populated. For this particular usage scenario, the first thing that came to my mind is running the diagnostic queries by Glenn Berry in a Notebook.

Obviously, I don’t want to create such a Notebook manually by adding all the code cells one by one. Fortunately, PowerShell is my friend and can do the heavy lifting for me.

Unsurprisingly, dbatools comes to the rescue: André Kamman added a cmdlet that  downloads, parses and executes Glenn Berry’s diagnostic queries and added the cmdlet to dbatools. The part that can help me is not a public function available to the user, but I can still go to GitHub and download the internal function Invoke-DbaDiagnosticQueryScriptParser for my needs.
The function returns a list of queries that I can use to generate the Jupyter Notebook:

# Purpose: take the diagnostic queries from Glenn Berry
# and generate a Jupyter Notebook to run in Azure Data Studio
# Example usage:
# create-diagnostic-notebook.ps1 -diagnosticScriptPath "C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.777\bin\diagnosticquery\SQLServerDiagnosticQueries_2019_201901.sql" -notebookOutputPath "diagnostic-notebook.ipynb"
# Function taken from dbatools
# Parses the diagnostic script and breaks it into individual queries,
# with text and description
function Invoke-DbaDiagnosticQueryScriptParser {
[CmdletBinding(DefaultParameterSetName = "Default")]
[ValidateScript( {Test-Path $_})]
$out = "Parsing file {0}" -f $filename
write-verbose Message $out
$ParsedScript = @()
[string]$scriptpart = ""
$fullscript = Get-Content Path $filename
$start = $false
$querynr = 0
$DBSpecific = $false
if ($ExcludeQueryTextColumn) {$QueryTextColumn = ""} else {$QueryTextColumn = ", t.[text] AS [Complete Query Text]"}
if ($ExcludePlanColumn) {$PlanTextColumn = ""} else {$PlanTextColumn = ", qp.query_plan AS [Query Plan]"}
foreach ($line in $fullscript) {
if ($start -eq $false) {
if (($line -match "You have the correct major version of SQL Server for this diagnostic information script") -or ($line.StartsWith("— Server level queries ***"))) {
$start = $true
if ($line.StartsWith("— Database specific queries ***") -or ($line.StartsWith("— Switch to user database **"))) {
$DBSpecific = $true
if (!$NoColumnParsing) {
if (($line -match "— uncomment out these columns if not copying results to Excel") -or ($line -match "— comment out this column if copying results to Excel")) {
$line = $QueryTextColumn + $PlanTextColumn
if ($line -match "-{2,}\s{1,}(.*) \(Query (\d*)\) \((\D*)\)") {
$prev_querydescription = $Matches[1]
$prev_querynr = $Matches[2]
$prev_queryname = $Matches[3]
if ($querynr -gt 0) {
$properties = @{QueryNr = $querynr; QueryName = $queryname; DBSpecific = $DBSpecific; Description = $queryDescription; Text = $scriptpart}
$newscript = New-Object TypeName PSObject Property $properties
$ParsedScript += $newscript
$scriptpart = ""
$querydescription = $prev_querydescription
$querynr = $prev_querynr
$queryname = $prev_queryname
} else {
if (!$line.startswith("") -and ($line.trim() -ne "") -and ($null -ne $line) -and ($line -ne "\n")) {
$scriptpart += $line + "`n"
$properties = @{QueryNr = $querynr; QueryName = $queryname; DBSpecific = $DBSpecific; Description = $queryDescription; Text = $scriptpart}
$newscript = New-Object TypeName PSObject Property $properties
$ParsedScript += $newscript
$cells = @()
Invoke-DbaDiagnosticQueryScriptParser $diagnosticScriptPath |
Where-Object { -not $_.DBSpecific } |
ForEach-Object {
$cells += [pscustomobject]@{cell_type = "markdown"; source = "## $($_.QueryName)`n`n$($_.Description)" }
$cells += [pscustomobject]@{cell_type = "code"; source = $_.Text }
$preamble = @"
"metadata": {
"kernelspec": {
"name": "SQL",
"display_name": "SQL",
"language": "sql"
"language_info": {
"name": "sql",
"version": ""
"nbformat_minor": 2,
"nbformat": 4,
$preamble | Out-File $notebookOutputPath
$cells | ConvertTo-Json | Out-File FilePath $notebookOutputPath Append
"}}" | Out-File FilePath $notebookOutputPath Append

In order to use the script, you need to provide the path to the file that contains the diagnostic queries and the path where the new Jupyter Notebook should be generated. Dbatools includes the latest version of the diagnostic scripts already, so you just need to choose which flavor you want to use. You will find all available scripts in the module directory of dbatools:

$dbatoolsPath = Split-Path -parent (Get-Module -ListAvailable dbatools).path
Get-ChildItem "$dbatoolsPath\bin\diagnosticquery" | Select-Object Name

The script above produces this output:

C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.777


Once you decide which file to use, you can pass it to the script:

create-diagnostic-notebook.ps1 `
    -diagnosticScriptPath "C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.777\bin\diagnosticquery\SQLServerDiagnosticQueries_2019_201901.sql" `
    -notebookOutputPath "diagnostic-notebook.ipynb"

What you obtain is a Jupyter Notebook that you can open in Azure Data Studio:


This is nice way to incorporate the code and results in a single file, that you can review offline later.  This also allows you to send the empty notebook to a remote client, ask to run one or more queries and send back the notebook including the results for you to review.

Happy Notebooking!

Posted on March 20, 2019, in PowerShell, SQL Server, Uncategorized and tagged , , , , . Bookmark the permalink. 11 Comments.

  1. Michael Kirkpatrick

    I get an error saying “Illegal characters in path”
    “C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.777\bin\diagnosticquery\SQLServerDiagnosticQueries_2019_201901.sql”

    Is it the space in Program Files?

    • Excellent. Thank you very much.

      It works perfectly with the 2012, 2014, 2016 and 2017 scripts. Unfortunately this is all that comes out of the 2016 SP2 script:

      “metadata”: {
      “kernelspec”: {
      “name”: “SQL”,
      “display_name”: “SQL”,
      “language”: “sql”
      “language_info”: {
      “name”: “sql”,
      “version”: “”
      “nbformat_minor”: 2,
      “nbformat”: 4,
      “cell_type”: “markdown”,
      “source”: “## \n\n”
      “cell_type”: “code”,
      “source”: “”

      • There seems to be a bug in the format of the 2016 SP2 queries. My code relies on a function in dbatools that parses the diagnostic queries based on some placeholders inside the file. The placeholders are not correct in the 2016 SP2 file.
        I will contact Glenn and let him know. Thanks!

  2. Michael:
    Check the path to the diagnostic queries. Either replace the version number in …\dbatools\\diagnosticquery\….
    or download later versions of the scripts and use the path to where you save the downloaded files. As long as you use quotation marks around \ spaces is no problem.

  3. Worked perfectly. Very useful and timely. Nifty scripting! Kudos.

  1. Pingback: Building a DMV Diagnostic Queries Notebook – Curated SQL

  2. Pingback: Last Week Reading (2019-03-24) | SQLPlayer

  3. Pingback: Azure Data Studio - Wird alles zum SQL Notebook ?

  4. Pingback: Ms SQL Girl | SQL Notebooks for Data Analysts

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: