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:

 

StoredProcedureExecutionChangingOverTime.png

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.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.