Migrating perfmon CSV files into SQL Server for analysis

Up to a certain size and for quick sampling using perfmon data in a CSV file makes life easy. Easily pulled into Excel, dumped into a pivot table and then analysed and distributed.

But once they get up to a decent size, or you’re working with multiple files or you want to correlate multiple logs from different servers, then it makes sense to migrate them into SQL Server. Windows comes with a handy utility to do this, relog, which can be used to convert perfmon output to other formats. Including SQL Server.

First off you’ll need to set up an ODBC DSN on the machine you’re running the import from. Nothing strange here, but you need to make sure you use the standard SQL Server driver.

Make sure you use the standard SQL Driver when setting up an ODBC connection for relog to load perflog data into SQL Server

If you use the SQL Server Native Client you’re liable to run into this uninformative error:

0.00%Error: A SQL failure occurred. Check the application event log for any errors.

and this also unhelpful 3402 error in the Application Event log:

The informative 3402 message relog returns if you're using the wrong ODBC driver

Once you’ve got that setup it’s just the syntax of the relog command to get through. The documentation says you need to use:

-o { output file | DSN!counter_log }

What it doesn’t say is that counter_log is the name of the database you want to record the data into. So assuming you want to migrate your CSV file, c:\perfmon\log.csv, into the database perfmon_data using the DSN perfmon_obdbc, you’d want to use:

relog "c:\perfmon\log.csv" -f SQL -o SQL:perfmon_odbc!perfmon_data

Running that you should get a nice whirling % count as your records are loaded up to SQL Server.

Tagged , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *