Sometimes graphs can be confusing or simply unhelpful. Such is the case for me when I use Perfmon data to show a customer how their subsequent release builds may contain performance problems. Below is a snapshot of a Perfmon log for a server running IIS which hosts over 60 ASP.NET applications. And keep in mind, the graph displayed in this Perfmon log is for only one Counter (ASP.NET Applications/Request Execution Time) over the course of one day!

Exhibit A: Sample Perfmon graph

The goal for me here was to compare these trends to the same diagnostic information from an earlier build, after a new build of the code was released. This would entail comparing these line graphs to another set of confusing line graphs. And this is for just one counter, imagine doing this for four or five counters. Talk about a migraine-inducing exercise!

Rather than present a series of graphs for several ASP.NET applications, covering several hours and include counters to show cpu usage, memory usage, request execution time, etc., I decided it might be easier for the customer to consume the data if was presented in a spreadsheet format. One column could contain the average cpu usage in a day and subsequent columns could contain counter data such as the maximum cpu usage, average or maximum committed memory, etc. I found customers prefer this layout and find the data in this format easier to identify trends. Here’s an example of what the data looks like in a spreadsheet format:

Exhibit B: Perfmon data in a spreadsheet

Exhibit B displays how much easier it is to identify trends amidst all this data. Simply compare spreadsheets which contain the data from releases over time, and the application with a performance problem will reveal itself. With Excel’s sorting and filtering features the customer can customize the view of data to their liking.

There’s just one problem in going from Exhibit A to Exhibit B – how do you extract the data from Perfmon into the spreadsheet? You could manually type the names of the ASP.NET applications and the data points into Excel, but that is an error prone, time-consuming process – even if you’re monitoring a small set of applications.

Brian Mallon, a colleague at Microsoft, showed me an invaluable tip to achieve my goal with less manual effort. Many thanks to Brian for sharing this tip that saved me literally hours for every time I need to do this going forward. You can find out more about Brian by viewing his LinkedIn profile.

Step1: Saved the data to a .tsv file

The first step in moving data from Perfmon to a spreadsheet display is to make sure all the counters of interest are added in the graph. Then right-click anywhere in the graph area and select Save Settings As…

Exhibit C: Saving out Perfmon data points

In the Save As dialog, select “Report (*.tsv)” in the Save as type dropdown. Give the file a name and save it wherever you like.

Step2: Open the data in Excel and format to your liking.

Once you’ve saved the tsv file from Perfmon, open it in Excel. You will notice that counters are listed in rows, and instances are listed in columns (if in fact your counter has instances). Some objects, such as Memory and ASP.NET, do not have instances while other objects, such as ASP.NET Applications and Process, do.

In my example in Exhibit C, I only had one counter from one object displayed in Perfmon – ASP.NET ApplicationsRequest Execution Time. Because the instance names for this object are all pre-pended with the text “_LM_W3SVC__ROOT”, they are all quite long. You need to expand the Instance column to see the names of each ASP.NET application.

Exhibit D: Viewing data points (average values) in Excel

Depending on how you like to view the data, you may or may not like the counter names in rows and the instances (in this case, it’s the ASP.NET application names for the ASP.NETRequest Execution Time counter) in columns. If you want to transpose the layout such that the instance names are in rows & the counter names are in columns, follow these simple steps.

1. Select all the data you want transposed, including the column & row headers.

2. Copy it to the clipboard.

3. Right-click inside an empty cell that has plenty of space below & to the right of it for the transposed data. Select the Paste Option for Transpose.

Exhibit E: Transposing data in Excel

You may notice these numbers are the averages over the time span displayed in Perfmon. But what if you’re tracking a metric whose maximum (or minimum) value is of interest to you – such as cpu usage or Requests/sec? How do you extract those data points from Perfmon?

All you need to do is go back to Perfmon and extract the data again (see Exhibit C), but first make one minor modification within Perfmon. Before you select ‘Save Settings As’ in Perfmon’s context menu, right click anywhere on the graph in Perfmon and select Properties. Go to the General tab and in the ‘Report and histogram data’ section and select the option (minimum, maximum, etc.) which represents the data points you wish to see in Excel. Now repeat Steps 1 & 2 above.

Exhibit F: You can export minimum, maximum, current, or average values for desired counters

Format, sort, filter, and massage the data in Excel to your preference. Sometimes this process of finding the “right” display of data can take several attempts. And what works for one person may not work for another. For the Microsoft customers I work with, who have large scale ASP.NET web sites, a common set of metrics are cpu usage, memory (ProcessPrivate Bytes as well as .NET CLR Memory#Total Committed Bytes), Requests/sec, Requests Executing, and a few other assorted counters.

When the customer has many ASP.NET applications (e.g., more than 15) and is trying to view trends over time to see if they are introducing regressions that result in increased resource usage (cpu, memory), increased load, or something else that adversely affects the performance of their web site, presenting this data in a spreadsheet style for comparison across releases can provide great value. Some of the more common counters I’ve used for are in an old Patterns & Practices book at https://msdn.microsoft.com/en-us/library/ms998530.aspx. Old paper, but many of the counters are still quite helpful in diagnosing & defining .NET application performance & stability issues.

This small step that essentially allows us to move data points from Perfmon’s graph style to Excel’s spreadsheet style can save a large amount of time and decrease the number of mistakes that occur from manually copying data.