Week Three: Baselining Part 1 - Glenn Berry’s Diagnostic

Week three goal was pretty starightforward: Baselining a critical task to ensure our SQL Server instances are running smoothly. To gather as much information as possible about our SQL Servers in one easy-to-access file. By doing so, I could track settings, performance and health over time. This post kicks off the baselining process with a look at Glenn Berry’s Diagnostic Queries through the Invoke-DbaDiagnosticQuery cmdlet from the dbatools module.

Why Baselining Matters

Baselining is essential when you’re managing multiple SQL Servers. It helps you understand where your systems stand at a given point in time. Over time, you’ll be able to compare this baseline against future states to spot potential issues, performance degradation or misconfigurations. The better your baseline, the easier it is to diagnose and resolve problems.

Glenn Berry’s Diagnostic Queries

Glenn Berry’s diagnostic queries are an industry-standard set of scripts designed to provide a detailed picture of your SQL Server configuration, health and performance. When run through the Invoke-DbaDiagnosticQuery cmdlet in dbatools, these queries pull data on things like:

• Configuration settings

• Missing indexes

• Potential performance bottlenecks

• Disk and memory usage

Using Invoke-DbaDiagnosticQuery, you can run these checks on any SQL instance and get a comprehensive set of results that can be exported for easy sharing and documentation.

Berry has detailed documentation and even a video on this page on his website.

My Process

Here’s the PowerShell script I used to automate the process. This runs the diagnostic queries and exports the results to an Excel file:

$server = New-DbaConnectionString -SqlInstance PROD-DB1 -EncryptConnection:$False -TrustServerCertificate:$True
Invoke-DbaDiagnosticQuery -SqlInstance PRO-DB1 -InstanceOnly | ForEach Object {
    if($psitem.Result) {
  		$excelSplat = @{
			Path            = ('C:\Temp\{0}.xlsx' -f $psitem.sqlinstance)
            	WorksheetName   = $psitem.Name
            	InputObject     = $psitem.Result
            	TableName       = $psitem.Name.replace(' ','')
			AutoSize        = $true
	 }
	 Export-Excel @excelSplat
	}
}

How It Works:

• Connection: The script connects to a specific SQL Server instance (PROD-DB1 in this case).

• Diagnostic Queries: It runs Glenn Berry’s diagnostic queries on the instance and returns the results.

• Export to Excel: Each result is exported as a separate worksheet in an Excel file, with the file name being the SQL instance name.

  
  

After exporting the data, I uploaded the Excel file to Google Drive (we use Google Workspace in our organization), and linked the file in SQL Server Settings Confluence page. This way, all team members can access the latest configuration and health data for our SQL Servers.

In the next part of this baselining, I’ll cover Invoke-SqlAssessment, another key tool for gathering and assessing SQL Server health. While Invoke-DbaDiagnosticQuery focuses on collecting detailed server information, Invoke-SqlAssessment goes a step further by providing automated assessments against best practices for SQL Server.

Stay tuned for the next post, where I’ll walk you through using Invoke-SqlAssessment to continue our baselining process!