The First Responder Kit’s free Power BI Dashboard gives you a fast overview of how your SQL Server is performing. You can trend your throughput by time, see how hard SQL Server was working during those times, and see what sp_BlitzFirst recorded about various performance issues like blocking, backups, and poison waits.
To use it, we need to:
- Install & configure sp_BlitzFirst to collect data
- Point the First Responder Kit Power BI file at your SQL Server
- Analyze your SQL Server’s performance bottlenecks
- Drill down into a single query’s history
- Learn more advanced tips & tricks
Let’s get started.
1. Install & configure sp_BlitzFirst to collect data.
Download our free First Responder Kit for SQL Server, then install the scripts. Open “Install-Core-Blitz-No-Query-Store.sql” and run it – that’ll install sp_Blitz, sp_BlitzFirst, sp_BlitzCache, and friends.
They’ll install in whatever database you’re in – master is fine, or you can pick another database if you like. (Be aware that if you install them in another database, you’ll need to fully qualify ’em when you run ’em.)
Create a database to hold your First Responder Kit data, like say FirstResponderKit or DBAtools.
Then, set up a SQL Server Agent job to run sp_BlitzFirst every 15 minutes, like this:
@OutputDatabaseName = 'DBAtools',
@OutputSchemaName = 'dbo',
@OutputTableName = 'BlitzFirst',
@OutputTableNameFileStats = 'BlitzFirst_FileStats',
@OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
@OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
@OutputTableNameBlitzCache = 'BlitzCache';
The @OutputTableRetentionDays parameter may also come in handy here – it defaults to 7 (since the 2017/11 release.)
2. Point the First Responder Kit Power BI file at your SQL Server.
Install Microsoft’s Power BI Desktop, which is totally free for Windows users. (No love for Mac or Linux folks, sadly.) Go get you some of that.
Then open the FirstResponderKit Power BI file from your First Responder Kit download. (It’s in the Power BI folder.) When you open it, it’ll default to a set of training data, which is cool when you’re learning how to use it.
In the toolbar, click the down arrow under Edit Queries, then Data Source Settings, then Change Source. Point it at your own SQL Server – in this case, SQL2017A:
Click OK, Close, and then the Refresh button in your toolbar. The Refresh tells Power BI to go fetch the data from your newly configured SQL Server.
3. Analyze your SQL Server’s performance bottlenecks.
At the top left, use the CheckDate and CheckDateHour to zoom in on performance statistics for a specific time range:
(Yes, that’s actually how Power BI wants you to pick time ranges. Yes, I’m surprised too. No, I’m not happy about that. Yes, I’ll switch it to something better as soon as Microsoft offers it.)
In the graph shown above:
- The line = Batch Requests per Second. This is like your SQL Server’s speedometer showing how many queries it’s handling.
- The bars = Wait Time Ratio. For every minute on the clock, how many minutes does SQL Server spend waiting on stuff? If Wait Time Ratio = 10, that means SQL Server spent 10 minutes of time waiting on stuff in every minute. The lower, the better/faster. When this ratio is under 1, that means your SQL Server isn’t really waiting on anything, and it’s just not working all that hard.
You can click on a single wait category bar as shown at right.
When you click on a single bar, the top right table shows the wait types in that wait category – in this case, just SOS_SCHEDULER_YIELD.
The rest of the page filters by time (not wait type):
- The Quick Diagnosis section shows only the warnings at that time
- The Resource-Intensive Queries section shows only the queries at that time (although not just for that wait type – it shows all of the queries logged at that time)
This can be a little confusing. Over time, we’d love to refine this a little more to be more intuitive, but we’ll see what you come up with first, dear reader.
4. Drill down into a single query’s history.
After a few collections, once you’ve got some query data, refresh your Power BI data. In the Resource-Intensive Queries section, right-click on a query of interest, and click Drillthrough, Query Details.
The “Query Details” tab of the Power BI doc will open showing the query’s performance over time, with the time filters honoring the same ones you used for the overview page.
From top to bottom:
- sp_BlitzCache commands to show you more details about the query right now in your own plan cache. (Man, I really wish Power BI made it easy to copy/paste this command out into SSMS.)
- Query Performance Over Time – check to see if reads/CPU/duration suddenly dropped, indicating that you’ve got a better plan in the cache.
- Warnings – same as if you ran sp_BlitzCache right now looking at the query, but you can see its warnings over time to know if the plan had a problem in the past.
We’re thinking about adding graphs on this page, but we’re not sure which metrics we want to show on the graph by default. As a query tuner, I look at very different metrics for different wait types – for example, when I’m tuning high SOS_SCHEDULER_YIELD due to CPU-consuming queries, then I want to graph total worker time.
The full query & query plan data are also available inside the data, but it’s a question of how we surface that data in Power BI. Power BI doesn’t exactly have an execution plan viewer built in. 😉
5. Learn more advanced tips & tricks.
Additional advanced functionality you can grow to use later:
To learn more:
- Read our most recent First Responder Kit blog posts
- Talk with us in the #FirstResponderKit Slack channel (if you need a free invite, hit SQLslack.com)
- To make code improvements or report a bug, visit the Github issues list and read the Contributing Guide
- Join our training classes where we teach you how to use the First Responder Kit to make your SQL Server go faster