Relogging Perfmon binary log files to SQL

Grant Holliday’s blog

Senior Service Engineer, Microsoft Visual Studio Team Foundation Service

Relogging Perfmon binary log files to SQL

  • Comments 10

Lately I’ve been doing quite a bit of work with Performance Monitor (or PerfMon for short) to monitor some performance counters on a collection of servers.

PerfMon has the ability to log it’s counter collections directly to a SQL ODBC data store. The downside of this is that if the store is remote and it is unavailable for some reason, then you’ve lost that data. Another alternative is to log the data to a *.blg (binary) format on the local disk of the machine that you’re monitoring, then post-process it later.

That’s what I’m doing. Every 24 hours I start a new .blg logfile (see screenshot below). Then at a convenient time I copy the directory log files onto another machine for post-processing using robocopy.

Data Collector with limit set to 1 day

One of the very useful tools that comes with Windows that I didn’t know about is “relog”.

Microsoft r Relog.exe (6.0.6000.16386)

Relog creates new performance logs from data in existing performance logs by changing the sampling rate and/or
converting the file format. Supports all performance log formats, including Windows NT 4.0 compressed logs.

Usage:
relog <filename [filename ...]> [options]

Parameters:
  <filename [filename ...]>     Performance file to relog.

Options:
  -?                            Displays context sensitive help.
  -a                            Append output to the existing binary file.
  -c <path [path ...]>          Counters to filter from the input log.
  -cf <filename>                File listing performance counters to filter from the input log. Default is all
                                counters in the original log file.
  -f <CSV|TSV|BIN|SQL>          Output file format.
  -t <value>                    Only write every nth record into the output file. Default is to write every record.
  -o                            Output file path or SQL database.
  -b <M/d/yyyy h:mm:ss[AM|PM]>  Begin time for the first record to write into the output file.
  -e <M/d/yyyy h:mm:ss[AM|PM]>  End time for the last record to write into the output file.
  -config <filename>            Settings file containing command options.
  -q                            List performance counters in the input file.
  -y                            Answer yes to all questions without prompting.

Examples:
  relog logfile.csv -c "\Processor(_Total)\% Processor Time" -o logfile.blg
  relog logfile.blg -cf counters.txt -f bin
  relog logfile.blg -f csv -o logfile.csv -t 2
  relog logfile.blg -q -o counters.txt

Relog gives you the ability to extract out the counters you care about and put them into another file. It also allows you to import the binary logfile format into a SQL store. You have to create a SQL Server ODBC System DSN for this to work.

image

One of the things relog can do is summarize the data. If you collect performance counters every 1 minute and you want to summarize that data to the hour, use “-t 60” as a parameter.

The command to relog from a *.blg logfile to a SQL database is:

relog logfile.blg -cf CounterList.txt -f SQL -o SQL:PDB!LogFileIdentifierHere

CounterList.txt is a text file that contains a list of counters that you care about, for example:

\\SERVERNAME\LogicalDisk(C:)\Avg. Disk sec/Transfer
\\SERVERNAME\LogicalDisk(C:)\Disk Bytes/sec
\\SERVERNAME\LogicalDisk(C:)\Disk Transfers/sec
\\SERVERNAME\Memory\Pages/sec
\\SERVERNAME\Processor(_Total)\% Processor Time
\\SERVERNAME\System\Processor Queue Length

And here’s my latest one-liner. This will take a directory of *.blg files, check to see if they’ve been relogged into the SQL database and if not, it will relog them. It uses the filename as the key for the log file identifier.

for %a in (*.blg) DO osql -o NUL -b -E -S (LOCAL) -d PDB -Q "EXIT(SELECT COUNT([GUID]) FROM DisplayToID WHERE DisplayString = '%a')" & IF NOT ERRORLEVEL 1 relog %a -cf CounterList.txt -f SQL -o SQL:PDB!%a

It makes use of FOR, osql.exe, ERRORLEVEL and relog. All of which are available from the standard windows command prompt.

  • Very cool Grant. I've just set mine up now.

  • In a previous post I talked about Relogging Perfmon binary log files to SQL . The next obvious step is

  • howdy there.. you mention you are able to log directly to an sql db... i've found it pretty easy under w2k3 to do this, but where is this same functionality under w2k8 server??

  • In Windows 2008:

    Open PerfMon >> Data Collector Sets >> User Defined >> New Data Collector Set >> Data Collector Properties

    You can set the log format as SQL and define a data source to log to.

    You can also do it via the command line:

    http://technet.microsoft.com/en-us/library/cc788038.aspx

  • thanks Grant.. i actually found it after asking the question... it was a bit hard to find since you can only choose a file location when setting up the data collector set, but you can go in and edit the output file to use an SQL datasource later.

    also, i see that you have some pretty RS graphs that use permon data. Are these publically available somewhere?

  • Yep - I've been away for a bit, but publishing the reports is on my backlog.

  • Is there anything like this that will work with Server 2000?  I need to get my results into SQL 2000.  Yes, I know it's 2008 - this 2000 thing isn't my idea.  Thanks.

  • Since joining Microsoft, I’ve become intimately familiar with running a TFS server for ~3,500 users in

  • hmmm, "summarize" would be nice, but the doc says -t N simply throws away all the in between values. that's unfortunate, but i guess there are other ways.

  • Summarize - perhaps you could reprocess a CSV file using PowerShell...

    For me, I'm going to be using this - taking a slice every hour of the perf logs.

Page 1 of 1 (10 items)