Using Powershell to read trace files

We know that trace files can be opened in Profiler and manually examined. They can also be imported into SQL Server database tables and queried using fn_trace_gettable. The problem with these approaches is that they are time consuming and, for fn_trace_gettable, require database storage before queries can be run.

An alternative is to use Powershell. Credit for this approach goes to the authors of this post:

http://www.solidq.com/sqj/Documents/2010_December_Issue/sqj_006_pag._33-38.pdf

As mentioned in this post, the trace objects are not supported on 64 bit platforms. To avoid run time errors you need to run the commands or script in a Powershell x86 command line shell.

Here is an example of using Powershell to count the number of occurrences of a SQL statement within a set of trace files. The count is returned in terms of number of occurrences per minute. A possible use-case would be where you have a very frequently running SQL statement and you wish to trend its execution:


# Credit: http://www.solidq.com/sqj/Documents/2010_December_Issue/sqj_006_pag._33-38.pdf

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfoExtended") | Out-Null;

$InputTraceDirectory = "D:\traces-in";
$TraceFilePattern = "*.trc";

# Hash to store occurrences per minute
$Occurrences = @{};

Get-ChildItem $InputTraceDirectory -Filter $TraceFilePattern | ForEach-Object {

$InputFilename = $_.FullName;

Write-Host ("Processing file " + $InputFilename);

$InputTraceFile = New-Object "Microsoft.SqlServer.Management.Trace.Tracefile";
$InputTraceFile.InitializeAsReader($InputFilename);

While ($InputTraceFile.Read()) {

If ($InputTraceFile.GetValue($InputTraceFile.GetOrdinal("EventClass")) -eq "SQL:BatchCompleted") {

If ($InputTraceFile.GetValue($InputTraceFile.GetOrdinal("TextData")) -match "select columns from dbo.mytable") {

$EndTime = $InputTraceFile.GetValue($InputTraceFile.GetOrdinal("EndTime"));

# Date doesn't matter. Start time set to 00:00 as we're measuring minutes since midnight
$TimeDiff = New-TimeSpan -start "1 April 2012 00:00" -end $EndTime;

# The hash key is the minute value. The hash value is the count.
$Occurrences[($TimeDiff.Hours)*60 + $TimeDiff.Minutes]++;
};
};
};

$InputTraceFile.Close();
};

$Occurrences.GetEnumerator() | Sort-Object Key | Select-Object @{Label="Time";Expression={([System.DateTime]"1 April 2012 00:00").AddMinutes($_.Key)}},Value | ConvertTo-CSV;

As commented in the source code, the date part is irrelevant. This would be appropriate where you are processing, for example, an afternoon’s worth of trace files. The CSV output can be saved as a file, opened in Excel and charted.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: