SentryOne : Multiple Baselines & Power BI

Much of what I've done at SentryOne has required me to compare performance test results between different SQL Server settings, compression methods, columnstore indexes, delayed durability, etc. I've used a combination of SQL Sentry and Visual Studio for much of my analysis and workload testing. Therefore, it's often necessary for me to compare Tests A, B, C, D, etc. to get a full comparison of different situations. As a result, I've created many charts; too many of them by manually entering or copying data. Clearly, there's a better way. Hence, in this post, I'm going to show you how I use SQL Sentry to create and compare multiple baselines (used as performance and/or workload test results).

The Baselines

The first thing you'll need to do this with your SentryOne database is some baselines. Hopefully you already know all about baselines and love them. If not, there's a great blog post about them right here called "SQL Sentry v8: Baselines from Every Angle". And, if one baseline is good, then multiple baselines are better. (OK, not usually the best logic, but just let me have it here, please.)

Highlight a time period on the Dashboard, right click and select Create Baseline:

How to create a SentryOne Baseline from the Dashboard

Name and save (click OK) the Baseline:
SentryOne Baseline Dialog Screen

Baseline created. It's that simple!

The SentryOne Query

For this example, I've created 5 arbitrary baselines that I've named Test A through Test E.

Executing this query:

USE [SentryOne]

SELECT Baseline = b.Name,

	Metric = c.Name,

	c.Average,

	c.[Min],

	c.[Max],

	c.StandardDeviation,

	b.RangeStartTime

  FROM [dbo].[PerformanceAnalysisBaselineCounterMapping] c

  JOIN [dbo].[PerformanceAnalysisBaselineChartArea] ca ON c.ChartAreaID = ca.ID

  JOIN [dbo].[PerformanceAnalysisBaseline] b ON ca.BaselineID = b.ID

  ORDER BY c.Name, b.Name, b.RangeStartTime;

Yields the following results in my test database:

Example of SentryOne baseline data query

It contains the basics of what one needs to compare some metrics (the average, minimum, maximum, and standard deviation values from the time period used to create the Baseline).

The Power BI

The Power BI chart below visualizes this Baseline data. The top chart displays the average values. It shows that whatever I was doing during Test C used the least CPU on average and the situation during Test D used the most CPU on average.

A Power BI Dashboard Comparing Multiple Baselines in SentryOne

The three charts on the bottom display the Maximum, Minimum, and Standard Deviation values. This helps you see if there are any unusual spikes or widely varying values within the Baseline.

The upper left corner has two drop-down lists where you can select multiple Baselines and the Metric you wish to view. If you select multiple Metrics it will display a sum of the values on the charts.
A Power BI Dashboard comparing multiple SentryOne Baselines

This chart is an example of three Baselines selected with Total Memory Usage (MB) as the Metric:
A Power BI Dashboard comparing multiple SentryOne Baselines

Downloading and Configuring This Power BI

I've uploaded a slightly modified PBIX file that uses the results of the example query in an Excel spreadsheet. You can download these files to test the Power BI. To use the sample Excel file, you'll need to change the Data Source to reflect your file location.

Use the Sample Multiple Baselines Data in Excel

First, click on Edit Queries in the ribbon menu. Then, select Data source settings. Finally, select Change Source to edit the File path as seen below:

Editing the Baselines Data Source

Connect the Power BI to a SentryOne Database

To connect it to your SentryOne database, delete the Baselines data:

Delete the Baselines Data Source

Power BI won't be happy about this, but it's only temporary:
Broken Visualizations are Expected

Next, click on Get Data and select SQL Server database.
Select 'Get Data' to Connect to a SQL Server database

Then, enter your SentryOne repository server and database information. Copy/Paste the query from this post into the SQL statement field. When your screen is completed like this, click OK.
Set the SQL Server Information via Power BI

Once Power BI displays a sample of the data, click on Load to proceed.
Load the Data in Power BI

Finally, rename the Query1 (or whatever it may be named) to Baselines. This reconnects the visualizations to the expected data.
Rename the Data set/Query to Baselines

If you've been successful, then the visualizations fix themselves*.
Visualizations Reconnect with 'Baselines' as the Name

*You may need to select a Baseline and Metric from the drop-downs to populate the charts.

Why use this?

It's Fast

Using Power BI to connect to multiple baselines in SentryOne allows me to make fast comparisons. I previously translated baseline values into charts manually or through some Excel/SQL Server connections. See this post on data compression as an example. I wanted to compare performance between different compression levels (None, Row, and Page), and include the Average, Minimum, Maximum, and Standard Deviation values. Now, I have a standard template that looks better in Power BI. Once I create a baseline in my database, I have access to it in my charts. If you don't have Power BI, you can use the query from this post in Excel or another reporting method for your comparisons.

It's Versatile

If you are testing code changes, VM resource changes/configurations, OS/SQL Server settings, feature utilizations, etc., you can use multiple baselines this way to compare the performance impact of each. Want to compare multiple baselines across multiple servers monitored by SentryOne? You can. Put the server name in the baseline for easy selection in the drop-down.

I do something similar with the Visual Studio Load Testing database for better reporting. I'll share those queries with you. There are some variations to this query that might also be useful. For example, including the target name, description, category, or filters on fields. With this tool, you have the Power to modify it as you see fit. ;-) I hope this at least gave you some ideas and a way to get started.