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.