Using PowerShell to Automate Glenn Berry's SQL DMV Queries

I frequently use Glenn Berry’s SQL Server DMV queries for performance tuning and diagnostics. Because I run these queries often, I needed a way to quickly connect to a server, collect the necessary data, and review it later when time permits. In my role, efficiency and accuracy are essential, so I automate repetitive tasks whenever possible.

While Glenn Berry’s scripts are well-documented—and I recommend running them manually to understand their context and importance—I wanted to streamline the process. I developed a PowerShell script that automates these diagnostic queries, enabling faster data collection that can be reviewed when time allows. This approach is also valuable for real-time troubleshooting.

What Is dbatools?

Before diving into the script, I want to introduce dbatools, a comprehensive PowerShell module for DBAs and data professionals. This module includes numerous functions for monitoring, scripting, and management tasks. While I plan to cover dbatools in more detail in future posts, I encourage you to explore the dbatools documentation and GitHub repository to get started.

The Script

Credit goes to the maintainer of the core functionality, the Invoke-DbaDiagnosticQuery command in the dbatools repository. The PowerShell script I developed automates SQL Server diagnostic data collection using Glenn Berry’s DMV queries via Invoke-DbaDiagnosticQuery. It connects to a specified SQL Server instance, runs instance-level and optional database-specific queries, exports results to CSV files, and compiles them into a single Excel workbook for analysis.

Accessing the Script

The script is available in my GitHub repository. You can download it directly, and updates will be maintained there.

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 attempt to automatically install the necessary modules (dbatools and ImportExcel), but it’s good practice to install them manually to avoid 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 welcome feedback on this script. If you have suggestions for improvements, please comment below so I can consider adding them.