In the First Responder Kit’s Power BI Dashboard, there’s a Quick Diagnosis results area where sp_BlitzFirst takes a guess at why your SQL Server was slow – or in this case, why it suddenly got faster:
In this case, wait times were bad, and then all of a sudden the wait times just disappeared, and the line graph for Batch Requests/sec takes off. We’re doing way more queries, but waiting less times. Why?
The answer is shown in the Quick Diagnosis area – look closely at the bottom of that screenshot. Our DBA has been hard at work tuning queries and indexes:
Logging messages directly into the monitoring tables means it’s way easier to understand cause-and-effect changes in performance. This helps you track your effectiveness doing performance tuning.
How to Log SQL Server Changes
First, start logging sp_BlitzFirst data to table to track your performance metrics over time.
Then, whenever you do something that might affect performance, call sp_BlitzFirst with the message you want to log:
sp_BlitzFirst 'Index tuning - added an index on StackOverflow.dbo.Users (Reputation)';
sp_BlitzFirst assumes that you’re logging data in a DBAtools.dbo.BlitzFirst table, or in a dbo.BlitzFirst table in the current database. If you’re using a different database/schema/table name, you’ll need to fully qualify it like this:
sp_BlitzFirst @LogMessage = 'I did something awesome!',
@OutputDatabaseName = 'DBAtools',
@OutputSchemaName = 'dbo',
@OutputTableName = 'BlitzFirst';
You can run this manually, but of course you can also call it from your own utility scripts & tools like:
- A new version deployment tool that tracks when code changes
- A DDL trigger that tracks changes to tables
- A nightly load process that adds a lot of new data to a table
- Stats update jobs that change execution plans
- SQL Server startup scripts that track when an AG failed over or was moved to a faster VM or instance type
Advanced Parameters for Custom Logging
Behold! Magic Extensibility!
- @LogMessagePriority: defaults to 1, but you can use lower priorities like 100-200 if you want to just put in low-priority informational notes, like a data warehouse load finished.
- @LogMessageFindingsGroup: defaults to “Logged Message”
- @LogMessageFinding: defaults to “Logged from sp_BlitzFirst” – but with these two parameters, you could define other sources like your ETL processes
- @LogMessageURL: defaults to “https://www.brentozar.com”, but if the message relates to a deployment, issue, help desk ticket, etc, you can link it here. Same with more info links to Sharepoint.
- @LogMessageCheckDate: defaults to now, but if you need to retroactively log something, you can.
You can start to see where I’m going with this: I wanna give you a framework that we can use together to build a better understanding of what’s happening to our SQL Servers, and let everyone from development to sysadmins to DBAs understand why performance got better or worse.