Partilhar via

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

  • Artigo

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

image

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)

image_thumb_11 

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.

Comments

  • Anonymous
    September 19, 2013
    getting this "Attempting to run utility under WOW64 environment which can have negative performance ramifications.  Use native utility version instead." I ran: Readtrace.exe -S"MSSQLSERVER" -E -d"mydb" -I"RSCPC1_11AM_12PM.trc"

  • Anonymous
    September 18, 2014
    Just for the benefit for all who stumble into this blog... (know this is pretty old post :-) ), I got rid of the error by installing amd64 version of RML utilities. Don't forget to uninstall the 32 bit before installing the new one.