Using Relog Load Perfmon Data Into a SQL Server Database

October 14, 2018

Relog is a command line tool that you use to convert the file output created by Perfmon.  It can convert the files to binary, SQL, text, or csv.  By default, the file will already be in binary format.  However, it’s possible the file could be in text or CSV.

I’m using Relog to convert the binary format to SQL.  This way, I can use t-sql to analysis the Perfmon data i’ve collected.    When using the SQL output Relog loads the data into a specified sql database.  This requires an OBBC driver to be setup to an existing database.  The process will automatically create three tables.  The three tables are:

  • CounterData –  The actual counter data values.
  • CounterDetails – Lists the counters that were collected by machine.  The combination of MachineName, ObjectName, and CounterName should create a unique row in this table.  This makes it possible to load data from more than one machine in a single database.
  • DisplayToID – List the collector sets imported.

Again, these tables are created automatically, the first time Relog is run.  Once these tables are created, subsequent Relog processes will append data to the existing tables.

To Load data into a SQL database follow the following steps:

        1. Create a database.  I usually name the database PerfmonCollection.  There’s no need to create any tables.  Relog will do that for you.
        2. Create an ODBC driver to the database.  I always create System DSN connections.  Also, you have to use the old SQL Server driver.  Relog will error if you try to use the ODBC or Native Client.  I’ve read you need to use a 32-bit driver, but I’m not having any issues with the 64-bit version.
        3. Open a command prompt with elevated privileges. (Run as administrator)
        4. Change directories to the directory that has the Perfmon file.  (You can also specify the entire path in the following command)
        5. Run the Relog command.  There’s two flags needed for this work; -o Output and -f format.  The -f format flag is sql.The -o is SQL:<Name of ODBC driver>!<Name of data collector set> in quotes.  The following is Relog command where the file name is SSASProcessing.blg, the data collector set is SSASProcessing, and the ODBC driver is PerfmonCollection:  relog SSASProcessing.blg -f sql -o "SQL:PerfmonCollection!SSASProcessing"While the command is executing you will see the percentage complete.  When done, you will get some info about the sample too.