Using PowerShell to Automate Glenn Berry's SQL DMV Queries

Invoke-DbaDiagnosticQuery runs the whole set of Glenn Berry’s SQL Server DMV queries against an instance in one command, and I wrapped it in a script that dumps everything to an Excel workbook I can read later.

I use those queries more than any other diagnostic tool, and running them by hand means sitting there pasting one query at a time while whoever reported the problem waits. What I wanted was to connect, collect everything, and get out, so I can look at it properly once the immediate thing is dealt with.

Glenn Berry’s scripts are already well documented, and I’d recommend running them manually at least once to understand what each one is telling you, but I wanted to streamline the repeat runs. I developed a PowerShell script that automates the execution of these diagnostic queries, allowing for faster data collection that can be reviewed when I actually have time. This script is also invaluable when troubleshooting issues in real-time.

What Is dbatools?

Before diving into the script, I want to introduce dbatools, arguably the most powerful PowerShell module for DBAs and data professionals. This module is chock-full of incredible functions ranging from monitoring and scripting to management tasks. While I plan to dedicate an entire series of posts to dbatools in the future, I encourage you to explore the dbatools documentation and GitHub repository to get started.

The Script

I have to give major credit to the person that maintains the “meat and potatoes” part of the script, which is the Invoke-DbaDiagnosticQuery command within the dbatools repo. That person’s site is linked on the command documentation. The PowerShell script I developed automates the collection of SQL Server diagnostic data using Glenn Berry’s DMV queries via Invoke-DbaDiagnosticQuery. It then connects to a specified SQL Server instance, runs both instance-level and optional database-specific queries, exports the results to CSV files, and then compiles them into a single Excel workbook for easy analysis.

Accessing the Script

The script is available on my GitHub repository. You can download it directly from there, and any future updates or improvements will be maintained in that location.

Prerequisites

• PowerShell Execution Policy: Ensure that your system’s execution policy allows the running of scripts. You can check your current policy by running Get-ExecutionPolicy in PowerShell. For more information on execution policies and how to modify them, refer to Microsoft’s official documentation.

Required Modules: The script will try to install dbatools and ImportExcel on its own, but it’s good practice to install them manually first to avoid any potential issues.

Install-Module -Name dbatools -Scope CurrentUser
Install-Module -Name ImportExcel -Scope CurrentUser

How to Use the Script

Here’s a step-by-step guide to using the script:

• Download the Script:

  • Visit the GitHub repository

  • Download the Export-SQLDiagnosticData.ps1 script to a local directory.

• Run the Script:

  • Open PowerShell.
  • Navigate to the directory containing the script.
  • Execute the script:

.\Export-SQLDiagnosticData.ps1

• Respond to Prompts:
  • SQL Server Name: Enter the name of the SQL Server instance you wish to connect to. If you press Enter without typing anything, it defaults to localhost.
  • Export Path: Specify the root directory where you want the export files to be saved. The default is C:\Users\YourUsername\Documents\SQLDiagnosticData.
  • Authentication Method:
    • Type 1 for Windows Authentication or 2 for SQL Server Authentication.
    • If you choose SQL Server Authentication, you’ll be prompted to enter your SQL Server credentials.
  • Run Database-Specific Queries: Type Y to run database-specific diagnostic queries or N to skip. The default is N.
    • If you select Y, a list of databases on the server will be displayed.
    • Enter the numbers corresponding to the databases you want to analyze, separated by commas (e.g., 1,3,5).
• Wait for Completion:
  • The script will connect to the SQL Server instance, execute the diagnostic queries, export the results to CSV files, and merge them into an Excel workbook.
  • Progress and any relevant messages will be displayed in the console.
• Review the Results:
  • Navigate to the export path you specified.
  • Open the SQLDiagnostics.xlsx file to review the collected data.

Pro Tip

You can bypass most of the prompts by simply specifying them in the execution of the script. For example:

.\Export-SQLDiagnosticData.ps1 -SqlInstance "YourServerName" -ExportRootPath "C:\Your\Export\Path" -RunDatabaseQueries -DatabaseNames "DB1","DB2"

One thing I’d change if I used this more heavily is that it captures a moment and nothing else. Wait stats, plan cache contents and index usage are all cumulative since the last restart, so a single capture tells you where the server has been rather than what it’s doing right now, and comparing two captures taken an hour apart would be far more useful than either one alone. The script doesn’t do that and I haven’t written it.

As always, I would love to receive feedback on this script. If you have any suggestions for how to make it better, please comment below so I can try to add it.