topic Re: Time Taken for stored procedures to execute in Getting Data In

Time Taken for stored procedures to execute

mshea — Fri, 06 Nov 2015 19:06:21 GMT

Hi,

I have an application with about 10 stored procedure calls made via Linq. I'd like to track the performance of the stored procedure. There is currently no tracing/logging in place for these calls.

What is the most efficient way to instrument the call so that splunk can report on the avg time taken and show trends in a dashboard as well as send alerts when a threshold has been released.

The two options are, I think are:

1. Start and Finish tracing statements.

       Started processing sproc1
       exec sproc1(param1, param2)
       Finished processing sproc1

2. A single tracing statement with timeTaken when the stored procedure finishes.

i.e. finished executing sproc1 in 1114 ms

Please let me know if you have any thoughts on the best way to accomplish this.

Thanks,

Mike

Re: Time Taken for stored procedures to execute

bmacias84 — Tue, 10 Nov 2015 23:19:36 GMT

Have you considered using Splunk Stream app. If this is a DB Stream can capture request and response time for the stored procedure by grabbing the information on the wire. Works for TDS, mysql, and Postgress.

Re: Time Taken for stored procedures to execute

lguinn2 — Tue, 10 Nov 2015 23:45:09 GMT

I would consider doing this

index=xyz procedure=* ("Started processing" OR "Finished processing")
| transaction procedure startswith="Started processing" endswith="Finished processing" keepevicted=f
| timechart avg(duration) as AvgDuration by procedure span=1h

assuming that "procedure" is the name of the field that contains the names of the stored procedures (sproc1, etc.).

However, if your dataset is very large, the transaction command will consume a lot of memory and may not work well.