Compare two SQL Server Profiler traces

Question

I want to compare two SQL Server profiler traces, any tool does this?

Thanks, Ramkumar

Answer 1

@Narnian is correct, comparing the traces in a database is usually easiest. If your traces were captured into files, you can use fn_trace_gettable to read those files into a database.

Do note that I said, a database. You're rarely going to want to load them into the same database that you profiled. Typically I use a different instance.

Answer 2

Most answers are about comparing, literally, two trace files.

However, I think the OP was asking how to determine if a change in code or hardware improved/degraded performance.

In that case:

• Database Experimentation Assistant, from Microsoft - painful to use
• DBSophic Qure Optimizer - now owned by "EZ Manage", whom I've never heard of. Doesn't support SQL Server 2016
• Roll your own - http://sqlmag.com/database-performance-tuning/sql-servers-trace-and-replay-tool - be sure to install Itzik Ben-Gan's sqlsig SQL CLR helper for aggregating your data, available here: https://technet.microsoft.com/en-us/library/cc293616.aspx

Answer 3

The first part of Wilco's answer is good (In the profiler you can export the trace files by using File | Export | Extract SQL Server Events | Extract Transact-SQL Events) and I do use Notepad++ from time to time to compare, however using Winmerge is very easy. Install the little component: http://winmerge.org/?lang=en Find the two files you want to compare with Winmerge installed, highlight them, right-click and choose "Winmerge", will then open the editor in compare mode.

Answer 4

In the profiler you can export the trace files by using File | Export | Extract SQL Server Events | Extract Transact-SQL Events. When you do this for both traces, you can compare them for instance with Notepad++ (compare plugin).

Answer 5

Save them to a text file and use a file comparison tool like Beyond Compare?

Answer 6

There is an option to write your results to a database table. If you do that, then you can write queries to compare the aggregate results of both. This would be meaningful, provided that both runs occur at the same time of day and go for the same duration.

I've recently blogged about how to use SQL Profiler to optimize databases.

A Beginner's Guide to SQL Server Database Optimization