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
Baseline created. It's that simple!
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:
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 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.
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
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:
Connect the Power BI to a SentryOne Database
To connect it to your SentryOne database, delete the Baselines data:
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
*You may need to select a Baseline and Metric from the drop-downs to populate the charts.
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.