SentryOne : Multiple Baselines & Power BI
Published On: August 23, 2017
Categories: SQL Sentry, Baselines, PowerBI, SentryOne 0
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 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
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
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?
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.
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.
Melissa is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.