SQLStarters

Figuring out possible causes for performance issues is one of the core skills for any DBA. There’s a whole bunch of tools for it, but this time I’ll be writing about one of my favorites. The Performance Monitor (PerfMon), which is included in every version and edition of Windows Servers and workstations. PerfMon and I go back a long way and we have had a most satisfying relationship so far. Naturally there have been some rocky spots over the years, but we have never drifted apart.

If you’re not familiar with Performance Monitor it might, at first, appear slightly un-helpful. It can and will, however, provide you with a wealth of information about everything happening on your server. The challenge is not in collecting the performance data, but properly analyzing the results. In this post we’ll look at some of the PerfMon features and some quirks you need to be familiar with.

Getting started

Over the years Performance Monitor GUI itself has changed very little, though there’s a few very nice changes and additions made to the tool itself. This has some benefits, as with the new versions of Windows you still have a familiar and extremely powerful tool at your disposal. Performance Monitor can be started in couple different ways, my favorite is to open up the Run window (or home screen in Windows 2012) and just type “perfmon” or “perfmon /sys”.

It has also couple other startup options, but I’ll get to them later. The first one opens up a regular PerfMon, while the latter one will open just the Performance Monitor without any of the other features available.

The one on the left is the regular view of the PerfMon with Data Collector Sets, Reports etc. The one on the right is PerfMon started with /SYS option. It also has some Counters in it I had previously added.

There are also two other important troubleshooting tools that can be accessed from Performance Monitor if you start it without the /SYS switch. The System Reliability Report and the Resource Monitor. These are pretty cool features to know about, so cool in fact, that they’ll deserve their own blog post.

Now while I like most of the changes made to PerfMon over the years, there is one that I simply dislike. Back in the Windows 2003 days you could add whatever counters you wanted to PerfMon, then save it on your desktop to have your favorite counters available when ever you needed them. For some strange reason in Windows 2008 this was made bit more difficult. If you want to save your counter set you need to either start it with Microsoft Management Console by adding the Performance Monitor snap-in into it, or start PerfMon with /SYS option.

After starting PerfMon using either one of these options all you need to do is to just add the counters you want and save it to location of your choosing. Benefit for using Microsoft Management Console (mmc) is that you’ll still have access to other Performance Monitor tools as well after clicking open your .msc file. If you started PerfMon using /SYS option, you’ll have just the counters you added when you click open the created .PerfMonCfg file. Now maybe there’s logic behind the change that made simple thing that much more complicated, but whatever it is, I don’t get it.

There’s also another way to achieve this. When you run the Performance Monitor, there’s a button in there that allows you to copy the properties of your counters into clipboard.

After that you can run your favorite text editor, or Notepad, paste the results there and save the file. If you save the file as a text-file, you can later open it up, copy the contents and paste (either using Paste Counter List button located next to copy one or by pressing CTRL+V) them into your Performance Monitor. Yes, you read that right, copy and paste works just fine with Performance Monitor. On top of that, if you saved the counters you pasted into Notepad as an HTML file you can open it up on your web browser and run your Performance Monitor from IE (if your settings allow it)!

I’m not sure if I think that to be cool, or just plain crazy 🙂

System Diagnostics Report

Now we’ll get to one of the features I do like in PerfMon, and I like it a lot. It’s called the System Diagnostics Report. This can be accessed by running the following command:

perfmon /report

What this does is that it starts a 60 second data collection about your system. It’ll also collect information about failed services, Security Center settings, drivers etc and creates a nice little report out of it. It also shows some other interesting items, such as “hot” files on your disks and information about network traffic. When ever troubleshooting performance issues, this is one of the first things I’ll do. It only takes a minute to run and you can let it do it’s thing while getting all your diagnostic scripts and such in place.

And most importantly, it’ll also gives you a wealth of information to assist you in your troubleshooting actions.

Resource Monitor

For some reason people often seem to overlook the tools provided by Windows when troubleshooting performance issues. One of these overlooked tools, in my opinion anyway, is the Resource Monitor that was introduced in Windows 2008 release. Resource Monitor is an excellent addition to regular old PerfMon.

Resource Monitor can be started in many different ways. If you’ve started up a regular perfmon without any switches, you can right click Monitoring Tools and choose Resource Monitor.

Or you can fire it up by starting PerfMon with /RES switch, or finally you can find it from the Performance Tab of the Task Manager. Resource Monitor gives you a good overview of the current situation by CPU, DISK, NETWORK and MEMORY usage. And it does this quickly, which is a huge bonus.

Working with the GUI

There are few things that’ll help you work with the counters and results in the PerfMon GUI. One is the ability to scale the counter graphics to fit your screen, the other is the ability to zoom into certain timeframe for more accurate diagnostics. The line graph in PerfMon can be deceiving, which I’ll demo in the quirks part, but it allows you to eyeball certain trends apparent in memory leaks, for example. However these values are often “out of the chart” that scales from 0 to 100 so your actual line is not even visible on the screen.

As you can see the image on the left is not all that useful, it’s drawn somewhere way above the visible area of your monitor. If you have a decent imagination you can probably see it, if you squint your eyes really hard.. Or you could right-click the Counter and select Scale Selected Counters option to see it on the screen. If you look at the Scale, you can see it going from 1,0 to 0,001.

The Quirk

Like I said previously me and PerfMon, we have a lot of history behind us. There are admittedly few quirks in PerfMon, but only one that really, really drives me nuts. Now the tool itself is devilishly accurate on providing performance data, however the part that draws up the line charts isn’t, especially when you have gathered data for several hours.

So what you have in the graph is the line jumping between 20% and 60%, however the Maximum is at 97%. While it’s okay to look at some trends from this, don’t ever forget to look at the actual values as the graphs will not be accurate after a while (from my testing, if you collect more than 1 hour worth of data). This is also one of the reasons that when ever I do reporting to customers, I’ll relog the counters out from the BLG file and use Excel for the graphs.

This post did come up longer than I had originally intended, but I felt that PerfMon is just so cool tool that it deserved it! And there are few more things I didn’t even touch at yet, so expect to hear more about.. The PerfMon!