SQL Trace Analyzer allows you to graph and easily compare resource utilization by each stored  procedure between different periods or over a period of time.

Step 1 of 3: Open all traces you wish to compare and perform an analysis on each trace by clicking on the “Start Trace Analysis” button.

               

             

 

Step 2 of 3: In the Analysis Report tab of any trace, under SQL Calls, click on       “Procedure/Function calls” then click on the “Compare Traces” toolbar button.

                                                               

              

               

Step 3 of 3: In the Compare Traces Window that opens, for each stored procedure that you wish to add to the comparison graph, highlight its name under “Procedure Calls” then click on the “Add to Diagram” toolbar button.

 A bar for each trace is displayed side by side with the rest of the traces in the graph, ordered by date.    

               

              

 

 Here, we can see the following performance comparison in total execution duration (ms) for the usp_GetOrderReport and usp_GetProductReport stored procedures:

	Traces.dbo. 2007.07.01	Traces.dbo. 2007.07.02	Traces.dbo. 2007.07.03	Traces.dbo. 2007.07.04	Traces.dbo. 2007.07.05
usp_GetOrderReport	7820477	6881446	5573400	3956533	2294217
usp_GetProductReport	1438240	1265079	1024141	726559	420823

 

By looking at the graph alone, you can immediately see a trend in performance improvement over the period of 5 days.

                                               

You can change the performance counter displayed in the comparison graph by clicking on the “Chart Measurement” toolbar button and selecting the counter by which you wish to compare stored procedure performance.

                                                               

              

 SQL Trace Analyzer allows you to graph and compare multiple traces to help you quickly determine code level performance patterns, improvements or degradation at different periods of database activity or over a period of time. This is useful, for example, when benchmarking code optimization results against a baseline performance. This is also helpful when determining specific days or times at which stored procedures, SELECT or DML statements are performing poorly and affecting your database application’s performance.