Once you install SQL Trace Analyzer, you can quickly identify the most frequently executed and longest running stored procedures that are likely contributing to poor performance.

Step 1 of 4:  When you first open a trace, the trace data is displayed in the Data and Filters tab.

 

 

Step 2 of 4: Click on the “Start Trace Analysis” button to begin analysis. In the Analysis Report tab, you will see detailed performance statistics aggregated by stored procedure or function name.

 

 

 

Step 3 of 4:   To find the most frequently executed stored procedures, click on the “No. of Calls” column header. This will sort the list of stored procedures by total execution count.

 

               

Here, we see the top three most frequently executed stored procedures:

  

Rank	Stored Procedure	No. of Calls
1	usp_GetPayables	113
2	usp_GetOrderDetail	87
3	usp_GetOrderSummary	85

 

 Performance can be significantly improved by limiting the overall number of calls to a stored procedure whenever possible. For example, to retrieve all required information, poorly written application code may execute a stored procedure several times within a loop.  Such code can be optimized by rewriting the stored procedure so that it retrieves all necessary data in a single execution.  Reducing the number of calls for a frequently executed stored reduces resource utilization and helps improve overall application performance.

 

Step 4 of 4: To find the longest running stored procedures, click on the “Duration” column header. This will sort the list of stored procedures by total execution time.               

 

Identifying stored procedures with the highest total execution time tells you which stored procedures are consuming your database resources the longest. These stored procedures are good candidates for tuning and optimization.

 

 

Here, we see the top three longest running stored procedures:

Rank	Stored Procedure	Total Duration (ms)
1	usp_GetOrderReport	4699288.00
2	usp_GetProductReport	4065646.00
3	usp_GetPayables	2703153.00

 

Tuning and optimizing stored procedures with the highest total execution time will have the highest impact on the improvement of your database application’s overall performance.

 We can also sort the stored procedures by average duration:

 

Rank	Stored Procedure	Average Duration (ms)
1	usp_GetPayables	54063.06
2	usp_GetProductReport	48400.55
3	usp_GetOrderReport	41586.62

 

 Information about highest average duration is useful in identifying which application events may take the longest, translating into the most significant wait time for application users.

 

SQL Trace Analyzer also allows you to visually inspect the distribution of a stored procedure’s duration over a period of time. The Procedure Function Calls Distribution Graph helps you determine the regularity of a stored procedure’s execution time over the given period, as well as identify when a stored procedure had unusually long execution duration.

 

 

For example, in the distribution graph above, we can see that usp_GetProductReport stored procedure’s executions are notably longer (>50000ms) beginning at approximately 4:56 AM.

This behavior brings the following questions to mind: How much activity and load did the database have at that time? What other events occurred around that time to slow down the stored procedure’s execution?

 

SQL Trace Analyzer can easily answer these relevant questions. Returning to “Data and Filters” tab and applying a time filter, you can isolate the events that occurred during the time period in question.