Using PowerShell to Automate Glenn Berry's SQL DMV Queries

I find myself using Glenn Berry’s SQL Server DMV queries more than any other performance tuning or diagnostic tool. Because I run them so often, I needed a way to quickly connect to a server, collect the data, and move on until I have time to review it properly. In my role, speed and accuracy are important, so any chance I get to automate something, I take it.

Glenn Berry’s scripts are already well documented, and I recommend running them manually to understand what each one is telling you. I wanted to streamline the process further, so I wrote a PowerShell script that runs the diagnostic queries for me and saves the results to review later. It’s also useful when troubleshooting an issue in real time.

What Is dbatools?

The script is built on dbatools, the PowerShell module that comes up in most of my automation posts. The dbatools documentation and GitHub repository are the places to start, and I have a short post on installing it.

The Script

Most of the real work is done by Invoke-DbaDiagnosticQuery in dbatools, which the command’s documentation credits to Andre Kamman. The same page says the most recent version of Glenn Berry’s queries is included in the dbatools module, so there’s nothing separate to download. My script is a wrapper around it that connects to a SQL Server instance, runs the instance-level queries and optionally the database-specific ones, exports the results to CSV files, and compiles them into a single Excel workbook.

Accessing the Script

The script is available on my GitHub repository. If I change anything, that’s where the update will be.

Prerequisites

• PowerShell Execution Policy: Your execution policy has to allow scripts to run. Get-ExecutionPolicy shows the current one, and the dbatools install post has the setting I use.

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

Running it is mostly a matter of answering prompts:

• Download the Script:

  • Visit the GitHub repository

  • Download the script to a local directory. It’s stored in the repository as Glenn Berry's DMV's using PowerShell.ps1, and the examples below use the name from the script’s own help text, Export-SQLDiagnosticData.ps1, so rename it or adjust the commands.

• 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 (for example 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 skip most of the prompts by passing the answers as parameters:

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

One thing to keep in mind when reading the results is that many of these DMVs, including wait stats and index usage, are cumulative since the last restart. So a single run shows everything since the server started rather than what it’s doing right now.

As always, I’d love to hear feedback on this script. If you have suggestions for making it better, leave a comment and I’ll try to add them.