How To: View Changes in Stored Procedure Execution Duration

RockSolid allows you to plot the average execution duration of a stored procedure over time. This allows you to understand if the execution duration of a stored procedure is changing. Increasing duration's may be an indicator of:

System load is increasing, and less resources are available to process the execution

Data volumes are increasing and execution is taking longer because of this. Sometimes this can be resolved through targeted indexing.

To view how stored procedure execution is changing over time:

Go to the instance in RockSolid that you are wishing to view execution details for.

Select the Analysis Tab

Select the Ad-Hoc sub tab

Choose "StoredProc - Elapsed Time per Execution" from the "Metric" drop down

Chose the database and stored procedure name from the "Sub" drop down

Choose the Start and End date range that you are interested in comparison for

Select a "Linear regression" trend analysis to help indicate if execution duration is increasing

You should see a graph similar to that below:

In this example we can see that typically execution duration for this stored procedure is under 34ms. However on occasion this spikes to over 100ms. However as a general trend, no significant change in stored procedure execution is occurring.