Recently, my team was working on an assessment of a client's SQL Server environment for migration to Azure. A part of the assessment we had to run perfmon traces against their SQL Server instances. We pushed the perfmon counter sets out using code modified from this post.
Once the counter sets were created and started, they ran for 24 hours. The counter sets were stopped and the log files copied to a central location. Once we had the log files, the prospect of churning through 24 hours worth of data collected at 15-second intervals from multiple servers was daunting.
Enter relog. relog is a Windows utility that processes perfmon logs. I used it to import the log data into a SQL Server database to allow aggregation. The relog command syntax is:
relog logfilepath -f SQL -o SQL:databasename!sqlserverinstance
The logfilepath is the path to the perfmon log file. SQL tells relog to format the output for SQL Server. The -o parm contains the database and instance name separated by an exclamation point. After writing a foreach loop in PowerShell the process of importing several hundred log files into SQL Server was ridicualously easy. Here is the loop code:
$path = "C:\temp\perfmonfiles"
$perfmonfiles = Get-ChildItem -Path $path;
foreach($perfmonfile in $perfmonfiles)
{
relog "$path\$perfmonfile" -f SQL -o SQL:relog!localhost
}
relog creates a database containing three tables:
- CounterDetails - contains infomration about the perfmon counters collected
- CounterData - contains the perfmon data collected by the counter set
- DisplayToID - contains metadata about the perfmon run
This simple schema allowed aggregation of individual counter data using T-SQL queries. If you collect perfmon logs and use SQL Server, I highly recommend becoming familiar with the relog utility. It will save you time and effort.