SQL Server profiler trace parsing tool – Build performance reports based on profiler traces only

Imagine the scenario that you application has just been deployed into production environment and SQL Server is extremely slow consuming too many resources. Sometimes a single store procedure can be easily identified by looking into DMV’s or just profiler trace. But how you can summarize entire profiler trace in simple reports that aggregate all store procedures executions, many scenarios performance problems of store procedures are not related with single execution but with aggregation of execution per minute. A store procedure that takes 1 second to execute could be a problem if is running more than 60 times per minute… how to figure out that…simple… use ReadTrace utility to parse profiler traces:

1) Download and install RML Utilities (usually referred as Read Trace) from https://www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en (works on SQL Server 2000, 2005 and 2008)

2) Open RML Cmd Prompt and use the following syntax to start parsing your profiler trace file(s):

Readtrace.exe –S"server_name" –E -d"database_name" –I"profiler_trace.trc"

Note: ReadTrace will import profiler trace into a database in order to aggregate and normalize all statements, you can use a remote or local SQL Server instance for this purpose.

3) After previous point execution Reporter should open automatically displaying main report based profiler trace analysis, you can export all reports and sub-reports to excel files

4)

I typically use the tool to summarize performance by SQL statement.  ReadTrace will "normalize" the SQL statements by replacing constants with placeholders.  This lets you identify which SQL statements are using the most resources. Report below is the sub-report that shows top 10 resource consuming statements. On this specific case is possible to notice that top 1 statement is responsible for more than 30% total SQL Server cpu utilization. Is possible to drill down even more each statement to find summary of all executions along the time for same statement, executions plans being used (if profiler trace has captured these events)

 

5) One great functionality is that ReadTrace normalizes SP/statements so even same store procedure is being called with different parameters will be aggregate on same group since it’s ‘skeleton’ will be the same.