Whether you're a DBA/administrator looking to tune a server, planning for hardware updates, or looking to make a move to the cloud there are a few key performance metrics you're going to want to collect. How do you know if the bottleneck is memory, disk or CPU? How do you know which of the Azure VM sizes to choose? The answer to these questions and other lies in the performance metrics on your existing servers.
Sure, you could just take the number of cores and memory, go to the Azure portal and choose a VM that matches. Is that really the best way to go about a cloud migration? Not really. Are you using all the memory that's on the server? Is the CPU constantly being maxed out? How do you even know what size disk to select?
There are a few key performance metrics that you'll want to gather to answer these questions. Today we are going to look at collecting a few of those metrics using a free tool built into Windows called Performance Monitor (also referred to as Perfmon).
Ultimately we are going to collect metrics on three main areas: CPU, memory and disk (then add network bandwidth for good measure).
CPU
On the CPU we will be looking to get information about the compute capacity, such as the number of processors and cores as well as the utilization.
Relevant CPU counters collected at the total level:
- Processor - % Processor Time - Total CPU usage percentage
Memory
Like the CPU, this is pretty straightforward. How much do we have and how much are we using. Also important here is how often does data page to disk.
Relevant memory counters collected at the total level:
- Memory - Available MBytes - Amount of free memory (careful reading into this SQL takes and holds memory)
- Paging File - % Usage - Memory swap to disk
Disk
This is likely the area that is most complex and will generate the most discussion. Everything from "IOPS aren't a real thing" to "IOPS are the best way to measure disk performance". We are going to gather a few different pieces of information on the disk side. IOPS, latency, and throughput are all important in different ways for sizing a new system and for tuning an existing system. So let's go ahead and get some of the basics gathered. Depending on what this data will be used for, you can collect individual disk metrics or just choose the total for all disks. Collecting for individual disks makes analyzing more work, but also gives more detail.
Relevant disk counters collected for each logical disk:
- Logical Disk - Avg. Disk Bytes/Read - Size of reads
- Logical Disk - Avg. Disk Bytes/Write - Size of writes
- Logical Disk - Avg. Disk sec/Read - Latency for reads
- Logical Disk - Avg. Disk sec/Write - Latency for writes
- Logical Disk - Disk Read Bytes/sec - Throughput for reads
- Logical Disk - Disk Write Bytes/sec - Throughput for writes
- Logical Disk - Disk Reads/sec - IOPS for reads
- Logical Disk - Disk Writes/sec - IOPS for writes
Network Bandwidth
For a migration to Azure VMs this can be useful just to do some sanity checks on the new configuration and to help identify if slowdowns are on the Azure side or your connection from on-prem to Azure.
Relevant network counters collected at the server level:
- Network Interface - Bytes Total/sec - Total network in and out
Let's get started.
Launching Performance Monitor
To access the Windows Performance Monitor simply click the Start button and start typing "Performance Monitor" until it shows up in the result list. If you happen to be running an older version of Windows without search functionality or can't seem to find Performance Monitor in the results go to Run and simply type "perfmon". It is also available in the Computer Management console under System Tools, then Performance.
This utility will allow live viewing of metrics and background collection. We will want to setup a background collection that runs for a long enough time to get a solid representative sample of the workloads on the server. In an ideal world this would run for a few days to cover the major events like changes in daily user load and nightly batch processing.
Setting Up the Data Collector
There are a few ways to setup the collection of performance counters. I will outline one way in this post.
In the Performance Monitor navigation tree, expand the Data Collector Sets node. Then, right-click on the User Defined node and select New > Data Collector Set.
Name the data collector set and select the option labeled Create manually (Advanced).
When asked for the type of data to include make sure to check the box next to Performance counter otherwise there are going to be a few extra steps to get the counters into the collector and the next few steps won't match up at all.
Now we are at the good part. Which performance counters would you like to log? Click the add button and refer to the list above for which counters to add. Let's keep the sample interval at 15 seconds. Click OK when all the counters are added. Depending on the number of disks there could be quite a large list.
After being returned to the counter summary, click Next.
Tell Performance Monitor where to save the results. Click Next.
On the final page you can change the account that will be used for data collection. Be sure that this account has access to gather the metrics from this computer or on the remote computer if you are setting this up to collection from another location. Keep the radio button on save and close, then click Finish.
You will be returned to the Performance Monitor console. We need to make a small change to the way the data is stored, as we will want it in a CSV format. Let's navigate to the collector. On the navigation tree expand the User Defined node and select the collector with the name specified in the earlier steps. There should be one collector there, likely named DataCollector01 because Performance Monitor is as creative as I am. Right-click on that collector and open the properties.
Note: If you want to control the name of the collector, rather than telling the wizard we wanted to add performance counters, you would leave that option blank. The wizard would end rather than allowing you to add performance counters. Then you'd right-click the data collector set and select New > Data Collector. The first page of that wizard allows for a custom name.
On the Performance Counters tab there is an option for Log format. The default is going to be Binary. Change this to tab or comma separated so the data is more easily consumed. You can also switch over to the File tab and change the name of this collector's file so it's more meaningful. Finally, click OK.
Now we are ready to start collecting!
Right-click the data collector set that was just created and select Start. The counters will be written every 15 seconds into a file in the directory specified in the collector wizard.
Viewing the Results
When you are ready to take a look at the collection results it's time to dust off your Excel or Power BI skills.
I like to bring this data into Power BI because it's easy to clean up the results and it's easy to build some quick visuals. A few clicks will help remove the first row in the results that are blank, unpivot all the columns into rows, remove my computer name from the counter text, add a few category labels, and change some column headers.
I can then go build a few visualizations. I have a summary showing the total, median, average, minimum and maximum for each counter, then the same calculations for each counter group.
I also have a page for each of the categories with some visualizations. Because who doesn't love a good line chart?
A table of data is great, and the details are absolutely necessary. However, it can be difficult to see the peaks and valleys in a list of counter results, especially when looking over a day or two. A simple line chart can really help with that. I generally want to see how the different data points trend over the entire collection period. I want to see how often the system runs close to the maximum. I want to see if the average is because there are sustained periods of maximum activity followed by sustained periods of no activity or if it's a constant amount of activity with some periodic peaks. All of these charts will look different based on if the server is running a transactional system or a data warehouse.
What do I do with this information?
I'm going to fall back on my consulting answer. It depends. If you're looking to tune the system, then this will help you know where to look. There are other pieces of information in SQL Server these days that will help point you in the right direction better than these counters.
For me, I generally use this when I'm working with customers who are looking to move servers into Azure. It's great to look at the existing server specs and just use that but it only gives part of the picture. We still need to understand the disk requirements as throughput and IOPS in Azure are not fixed values. They vary based on the VM size, disk type chosen, and the number of disks. We don't want to throw 24 cores at a server just because it has 24 today because the existing server may have changed over time. It may be that a consolidation was done and the server is now underpowered. It may be that services were moved off the server and now the CPU doesn't go above 26% even at peak times. We don't want to overpower VMs because then you're wasting money. We don't want to underpower VMs because then you're having a terrible experience.
In a future post I'll go through how to match this information up to Azure VM sizes. In the meantime, now you have a good way to gather some baseline performance metrics from your existing servers.
