SQLStarters

PerfMon is a great tool for collecting performance data from your servers, but it has a few shortcomings when it comes to reporting these results. One of the biggest issues that I also mention in one of my older posts here is, that the graphical presentation becomes hugely inaccurate when you collect data over a long period. While this might not bother you personally, if you’re writing that report to your manager or a customer, it makes sense to show information that is correct.

Let’s look at a simple example.

This is a CPU line graph for a single core with about 4 hours of collected data. PerfMon reports that the maximum value during that time for this specific counter has been 98.752, but that’s not what the graph is showing, is it? The line only seems to hardly go up to 60%.

About the Only way to get more accurate graphical presentation is to zoom into shorter time period on the collected data (less than 1 hour). And even then finding the actual spot where the highest value is can be troublesome. For example in the line graph below the highest CPU usage is not around 12:58 PM where you might expect it to be, but its actually found around 2:28 PM.

While annoying there’s a simple fix for this, get a better a tool. My personal favorite one for turning numbers into graphics is the good old Excel. All you need to do is to get the results from the BLG file into Excel and this is exactly what you can do with a Windows command line utility called ReLog.

Let’s have a look at how it’s done.

What we’ll be doing is that we’ll take the counter values from the BLG and turn them into CVS file first. After that is done we can then load these results into Excel. First of all, Relog is a Command Line utility so you need to fire up a Command Prompt. Then we need to figure out what are the counters that exist in our BLG. This we can do by giving the command:

relog -q perfmon_datafile.blg >> perfmon_counters.txt

Depending on how many counters you have, it usually makes more sense to direct this listing to a text-file by adding the following to the command:

>> perfmon_counters.txt

With this data collection I got little over 2754 rows, one for each counter instance. You can also search for specific counters from the BLG file, lets say for example that you want to look at what Processor counters you have in there. You can do this by giving the following command:

relog -q perfmon_datafile.blg | find “Processor(” >> processor_counters.txt

Note: I added “(” to the end of the Processor, because otherwise it’ll also show all the Process object % Processor Time counters, not just the Processor. In my case there are several Processor counters for Processor time, Privileged Time and User Time. The graph above in the PerfMon window is for the Processor(11)\% Processor Time counter, let’s extract that from the BLG.

relog perfmon_datafile.blg -C “\Processor(11)\% Processor Time” -F CSV -O processor11.csv -T 2

This command takes every 2nd record (I used very short monitoring frequency and I know the problem will be visible even with showing just half of the results) for the Processor(11) counter instance from the BLG file and writes it to CSV file called processor11.csv. The -F parameter is used for determining the output format and the -O is for the output file/path. Normally I wouldn’t take just a single counter instance if there is number of them available, but it’ll make this example bit more easier to follow.

If you open the file with Excel, it’ll look like this.

Not very good yet, but you have all that is required to make a nice chart out of it. The next steps are simple, you need to clean up and separate the date/time data from the counters. Lets start with separating the data, in Excel you can do this at least in two different ways. Easiest way is to go to DATA tab on Excel and choose Text to Columns.

That’ll fire up the Convert Text to Columns Wizard. You have nice set of delimeted data, choose Delimeted option and click Next to move on.

Choose only the Comma as your Delimeter, click Finish and you’re done! Now you’re date/time information is firmly separated from the actual counter values. You’ll probably have bit different looking results yourself, especially for the date and time information, based on what your locale and display settings are.

The results are however now in better looking state for creating an Excel graph!

I usually get rid of the date information, this is simply to do by choosing the Column (in this case Column A) and changing it’s type to Time. As for the Processor time, I usually go with Number format and then remove decimal values.

In HOME tab there’s a button that you can use to decrease or increase the decimal, it might not work though if you’re computer doesn’t recognize the decimal mark. In Finland for example the decimal mark is comma and I have a dot in the column.

There’s a simple trick for this, press Control+H to open up the Find and Replace dialog. To Find what box enter .* and leave the Replace with box empty, then click Replace All. It doesn’t actually remove them but it’ll convert them to “proper” decimals after which you can use the increase/decrease decimal button.

You should now have cleaned up results that you can whip out into graphics to your report. Here’s an example of how mine turned out (this is just the default line graph with nothing changed). Much more accurate than what we started out with and with little extra work I can also make it a way better looking than the original one!

Hopefully you enjoyed this tip about how to make most out of PerfMon.