Blog: Using Power BI to analyze Windows Perfmon data for SQL Server

In this second blog post for my series, Power BI for the SQL Server DBA, I’m going to show how you can take Windows Perfmon (Performance Monitor) data for SQL Server and analyze it with Power BI. You can find the source code, Power BI reports, and other related files on my GitHub site at https://github.com/db-best-technologies/power-bi-for-sql-dbas.

Here is a summary of what’s coming up in the series. It is subject to change based on feedback and reader demand:

Getting started with Power BI

I’m going to assume that as an SQL Server DBA, you probably have not used Power BI yet. If you have some familiarity with Power BI, feel free to skip over the basics. However, I hope there are gems you can pull out of the blog posts as part of this service.

Downloading the free Power BI Desktop

The preferred way to get Power BI is through the Microsoft Store. When you install via the Store, Microsoft pushes the latest updates to your computer. If you are running on a Windows Server OS without the Microsoft Store, you can go to https://aka.ms/pbiSingleInstaller to get the Power BI desktop.

Getting the PerfMon data into Power BI

Power BI makes loading data really easy when you first launch it.

Getting Data Startup Dialog

Click on “Get data”.

I’m assuming that you have downloaded the example for the blog series GitHub site at Task-Blog-B1-PerfMon-Capture.csv to the C:\Temp directory on your development computer.

Select Text CSV In List

Select the Task-Blog-B1-PerfMon-Capture.csv file from the C:\Temp directory.

Select Perfmon Capture CSV File

Power BI uses its CSV processing engine to provide a preview of the data to detect data types for the CSV file.

Preview Of CSV File Transform Data

Click “Transform Data” because there is one transformation to make to the data file to get started.

Power BI launches a separate tool window called the Power Query Editor.

Initial Power Query Editor

Using Replace Values to transform data

As you can see, each Performance Counter value has the name of the server. I’m going to assume that you have data associating the Task ID with the server name so there is no need to show the value. In addition, having a shorter Performance Counter value improves readability in the report.

Click on the “Performance Counter” cell in the first row. Then, right-click on the cell and click on the “Replace Values”.

Using Replace Values Command

Delete the text after the server name \\ec2amaz-2k3pvvl\ within Value to Find text box and leave the Replace With text box empty. Click “OK”.

Specify Replacement of Text with Blank Value

You’ll see that Power BI removed the server name from the Performance Counter. If you look in the formula area of the query editor, to can see the Power Query M function called Table.ReplaceValue to perform this action. There are some very cool things you can for with Power Query M functions. However, there comes a point with lots of CSV files loaded into Power BI where performance becomes intolerable. Loading the CSV files into SQL Server as part of an ETL process is the ultimate solution for Power BI. If you are still in “quick-and-dirty” mode, Power Query is ok.

Now it’s time to use the “Close & Apply” command to load the data into Power BI.

Click the “Close & Apply” command in the Power Query Editor toolbar.

Close And Apply Command

Using PowerShell to perform the text replacement

Here is an example of using PowerShell to do the text replacement that you can add this code to the bottom of the original PowerShell script from the first blog post:

# Remove the server name from the result set so that it's easier to work with in Power BI
# Regex Quick Reference:
# https://docs.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-language-quick-reference

# Example: "Task ID","Event Date Time (UTC)","Performance Counter","Value"
#          "48808","12/10/2019 6:06:36 AM","\\ec2amaz-u8hdq2f\memory\page faults/sec","129.892176504284"

((Get-Content -path "$($perfmon_outfile)" -Raw) -replace "\\{2}\w+\-\w+\",'') | `
Set-Content -Path "$($perfmon_outfile)"

# Result:  "48808","12/10/2019 6:06:36 AM","memory\page faults/sec","129.892176504284"

I’ve included a version of the revised script into the Blog 2 folder in the GitHub repository — blog-2-using-power-bi\Blog Post 2 – Capturing Disk Based Performance Counters for SQL Server.ps1

By using this new version of the script, you can skip the Power BI Replace Values function.

Using Power BI to visualize IOPS counters

I’m going to show you how to create a simple line chart that shows total IOPS for the data and log volumes that I presented at the end of the prior blog post — Capturing Performance Counters To Optimize SQL Server Performance On AWS EC2 Instances.

If you are following along, you should now have an empty Power BI Desktop with a populated Fields pane at the right side of the window.

Starting with an Empty report

To get started, click on the “Line Chart” icon within the Visualizations pane to add the chart to the canvas area for the report.

Start with a line chart

Resize the chart to match the width of the canvas. Just click on the lower right corner of the line chart and drag it to the right and down a little so it looks like the image below:

Resizing the chart

Next, it’s time to add the fields to the visual. You are going to drag-drop the following fields into the Visualizations pane fields section as follows:

  • Value into the Values field
  • Performance Counter into the Legend field
  • Event Date Time (UTC) into the Axis field

Selecting fields for line chart

Ok, this was not quite what I expected the first time I did this. What Power BI does for fields that use a DateTime datatype is think you want to use a date hierarchy. While this is a great idea for financial trends over a period of years, it doesn’t work as well with data that occurs every minute over a period of a couple of hours. What Power BI did was to place a single data point aggregated over the year.

First, we need to fix the Axis to use the actual value instead of the hierarchy. To do that, click on the drop-down to the right of the Event Date Time (UTC) field and click “Event Date Time (UTC)” in the menu.

Use field value for date time fields

Ok, the resulting chart still is a little strange.

Getting closer with line chart

Power BI created a line chart with all the Performance Counters as a series in the chart. The top line shows the memory\committed bytes for the EC2 instance. All the other values are magnitudes smaller. What we need to do is filter the Performance Counter values to no more than a handful to provide meaningful data on the same scale. For example, you don’t want to mix values that represent percentage values with values that represent other data.

Now, the trick is to use the Filters pane to select the relevant counters. I’ll start with the PhysicalDisk counters for Disk Transfers/sec for the data files E: drive and the log files L: drive.

First, click anywhere in the Performance Counter box within the Filters pane to display the list of Performance Counters to select from.

Displaying Available Performance Counters

You can resize the width of the Filters pane to show the full name of the Performance Counters to look like this.

Resize Filter Pane

In the search box underneath Basic filtering, type transfers/ to see the counters for the LogicalDisk and PhysicalDisk counters. Then, select the counters for (2 e:) and (3 l:) drives.

Selecting Counters For Chart

Now, we have something interesting to look at. Time for a quick recap of what we are looking at. I used an r5d.xlarge EC2 instance with a 300 GB EBS volume (E:) for the 250 GB data file, a 25 GB EBS volume (L:) for the 10 GB log file, and the local SSD (T:) for the tempdb files. The EC2 EBS Optimized instance has 4 vCPUs, 32 GB of RAM, with a maximum IOPS of 18,750 and maximum throughput of 593.75 MB/s. The r5d.xlarge instance uses I/O Credits and Burst Performance to go beyond the 3 IOPS/GB in this case. But the data and log drives can support 3,000 IOPS using the GP2 burst bucket.

You’ll notice that the performance of the E: drive is being limited to approximately 3k in the chart above. The log drive which supports the transaction write operations is well under the 3k limit, but that’s mainly because transactions got throttled on the E: drive. Based on prior experience, SQL Server should use more IOPS for this benchmark.

Analyzing CPU performance counters

Let’s start with the Line chart we just created to save a little time.

Click on any empty space within the existing line chart and then press Ctrl+C to copy the visual to the clipboard and then Ctrl+V to paste the copy.

Create Copy Visual Paste

You’ll see that the paste created the copy just under the original visual. Just click inside any white space on the new Line chart and then drag it below the top one. Then click on the Eraser icon that executes the Clear filter command for the Performance Counter.

Move visual clear filter

Next, in the search textbox for Basic Filtering, type % p and click on the processor(_total)\% processor time and % privileged time performance counters.

Select Processor Time

You can see a very jagged set of data points that roughly aligns with the IOPS for the log drive. Also, CPU usage never exceeded 50%.

What’s interesting here is the relatively high percentage of privileged time shown in red. Privileged time represents the time in the Windows kernel. If you aren’t familiar with the Windows Storage Stack, check out the blog post Measuring Disk Latency with Windows Performance Monitor (Perfmon) by Flavio Muratore. There should be a correlation between disk latency in this scenario.

Analyzing disk latency using the Avg. Disk sec/transfer counters

There is just enough room for one more chart on this page. Copy and paste the Line chart with the processor counters and then drag it to the bottom of the page. Next, click on the Clear Filter eraser for Performance Counter and type sec/tr in the search box. Click on the avg.disk sec/transfer counters for the (2 e:) and (3 l:) drives.

Adding Disk Latency Counters

In general, disk latency for EBS gp2 drive with optimized storage should average around 1 millisecond for OLTP workloads. In the chart above, you can see large values ranging from 0.4 seconds in the beginning to a high of 3.3 seconds at 8:31 PM. Clearly, there is an IO bottleneck. This is not due to gp2 storage performance with EBS volumes. In this case, it’s due to a poor configuration of the EBS volumes!

There is one very important set of data points that we are missing that I call “business-critical transactions” (BCT). These are queries that drive revenue or customer satisfaction in responsiveness that allow you to measure your overall throughput for the system. In my next blog post, I’ll discuss what makes a BCT and incorporate the data into the Power BI report so that we can optimize the EC2 deployment of SQL Server on AWS.

Share this...
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin