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. Due to the frequency with which I execute these queries, I needed a way to quickly connect to a server, collect the necessary data, and move on until I have time to review the information thoroughly. In my role, speed and accuracy are important, so any opportunity I have to automate something, I take it.

While Glenn Berry’s scripts are already well-documented—and I highly recommend running them manually to understand their context and importance—I wanted to streamline the process further. 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: While the script will automatically attempt to install the necessary modules—dbatools and ImportExcel—it’s good practice to install them manually 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"

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.