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)
- 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.
- 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:
- -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.