Automation: Get System Monitor Counters Into LogMan With ReLog

Scenario: System Monitor (née Performance Monitor in NT4.0) also know as “SysMon” is a Windows application we use to measure a computer’s performance. There are literally hundreds of metrics from which to choose. We can use the tool in a real-time mode where we first manually select the appropriate counters, then initiate a real-time trace to capture the performance data.  We use the SysMon GUI to view the real-time performance data.

If we want to be a bit more deliberate, we can forgo the real-time approach and instead record counter data to a log file, or a “Counter Log” in SysMon parlance.

Microsoft gave us the “System Overview” counter log example for our learning and emulation.

But much like default “System Monitor” view, to create a counter log definition, we likewise need to wade through a number of manual steps:

1) Define a counter log

2) Manually select each specific counter or performance object:

This manual approach quickly becomes unwieldy–death by a 1,000 clicks if you will. How do we automate these counter-log definitions?

Solution: To automate counter-log definitions, we need to avail ourselves to two command-prompt tools (listed with their endemic, Microsoft TechNet descriptions)

  1. ReLog: Extracts performance counters from performance counter logs into other formats, such as text-TSV (for tab-delimited text), text-CSV (for comma-delimited text), binary-BIN, or SQL.
  2. LogMan: Manages and schedules performance counter and event trace log collections on local and remote systems.

Or in plain English, with ReLog we can extract to a text file performance counter information from an existing performance log; and, LogMan is the command-prompt equivalent of System Monitor. So, at this point, perhaps you can see where this is going.

Without getting bogged down with Relog’s many options, know that its “-q” option allows us to extract the performance counters names that exist in a given input file. In other words, if we provide ReLog an SysMon input file, having the performance counters we care about, it could extract all the long, hard-to remember, non-intuitive counter names and stuff them into a text file for us. (And if we take this a step further, we could use this approach to build a reference text file or Excel document of all of SysMon’s bazillion counters!)

And without getting bogged down with LogMan’s many options, know that we can use its “-cf” option to tell it to use a text file that lists the performance counters we with to collect. This intelligent feature is our remedy to the “1,000-click death.” As you’ll see in a minute, with a little up-front work, we’ll have a portable, repeatable SysMon automation solution.

So, without further ado, synthesizing what we know, here are the steps we’ll take to automate counter-log definitions:

1) Note: This step is one that you will need to do only as often as you change your performance counter choices.

Using the SysMon GUI, create a dummy counter log having those objects and/or counters that you find interesting (see Brent Ozar’s thoughts on this)

2) Now run your SysMon counter log for an interval long enough to capture performance data for your hand-chosen metrics–say 3 minutes.

3) Invoke ReLog, with the aforementioned “-q” option to export our performance counter names to a text file:

We should find a text file named “ThePerformanceCountersIChose.txt” in the C:\perflogs path:

And we should discover a list of the performance counter names we chose:

Now we have our counter name list that we’ll feed into LogMan.

4) If I want to programmatically invoke SysMon, by way of LogMan, I’d issue a command like the following:

Where:

  • -si: Sample interval for performance counter data collectors
  • -b: Begin the data collector at specified time
  • -e: End the data collector at specified time
  • -f: Specifies the log format for the data collector
  • -cf: File listing performance counters to collect, one per line
  • -o: Path of the output log file

5) After running this command, we see the new “MyPerfCheck” counter-log definition:

6) We can refine and generalize this automation attempt by putting the above LogMan command in a batch script with a few parameters:

Note: I store these scripts on Box.net. Click on this link to download the below script.

Now, armed with our above batch script and the text file having performance-counter names, we have a portable, repeatable SysMon automation solution.

Conclusion: DBAs need to understand their SQL Servers in a holistic sense. And we use System Monitor to gain visibility into a server’s performance. It is one inexpensive way we can discover if our server is under duress from memory, I/O, or CPU pressure. We can also use SysMon glean many SQL-Server specific performance counters. With this article, I’ve demonstrated my SysMon-automation technique. In a future article, I’ll share further automation steps and make a plug for the free PAL analysis/reporting tool.

This entry was posted in Alphabetical, Automation, DBA Best Practice, Windows Server 2003 and tagged , , , , . Bookmark the permalink.