question

erlokeshsharma08 avatar image
erlokeshsharma08 asked

Stored Procedure : Inconsistent times of Execution

Hi Guys I have been facing a scenario where one of the stored procedure is taking more time to execute on certain days than the other. The maximum time it takes is arround 10 minutes and the least is 3 minutes. Stored procedure is part of one of the steps in a sql job. The volume of data and the code remains the same. How would I troubleshoot this case. Thanks Lokesh
stored-proceduresperformancesql-agent
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
There are a number of reasons your stored procedure would perform differently on different Days, including (but not limited to) - Other activity, either in the database instance, on the server or on any of the subsystems related (disk subsystem if you use a shared storage lika SAN, other VMs in the cluster if you use a virtual server etc). If stuff is happening inside the server you would probably know about it, or at least find out. If it's a related subsystem, it's harder to know. You could measure the I/O latency while running the stored procedure. See this script by Paul Randal, it has helped me out a lot in troubleshooting: https://www.sqlskills.com/blogs/paul/capturing-io-latencies-period-time/ - Execution plan not suited for the parameters. When a stored procedure (and the statements inside the stored procedure) is first run, Sql Server will compile an execution plan. This execution plan is likely to be used on the next execution, even if the parameters passed on the second execution would have produced a different execution plan. You could fiddle with OPTION(Recompile) on the statements inside the Stored Procedure to see if that helps, alternatively create the stored procedure using WITH RECOMPILE. Be aware though that recompilation also comes with a cost. - Bad statistics and/or fragmented indexes. If the statistics is not updated recently and you have new rows in the table with new values for an index or statistics object, the execution plan will be off. Same goes if you have deleted rows which has changed the distribution of data within a column. Sql Server will expect too few or too many rows meeting certain WHERE-predicates in this case and therefore produce a bad execution plan. This can be spotted by comparing Estimated Number of Rows and Actual Number of Rows in the execution plan. Fragmented indexes are rarely a performance problem, unless you have an execution plan where Sql Server COULD have used read-ahead reads (meaning Sql Server fetches more pages than each read asks for). If the index is fragmented, Sql Server won't be as likely to use use read-ahead read, giving more physical I/O operations.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Magnus #1. I had dba monitor the execution of sp today. He reported one of the queries that was part of the sp took longer to execute and was in suspended state. #2 sp does not have any input or output parameters. #3 we have a job that updates stastics on daily basis. So I guess I need to fine tune the sp a bit, right?
0 Likes 0 ·
Hi, nice to hear that you have spotted which individual Query is slow, that's a great start. A long running Query will be suspended from time to time, due to many reasons. It could be that there are other queries using the CPUs in the machine, and then the queries will have to take turn running. It could be that the Query is waiting for information from the buffer pool. It could be that the Query is waiting for a lock to be released on a resource that the Query needs to use. Finding out that the Query is suspended longer times is good, it means you can now look to see what it's waiting for, and if possible take care of that so the waiting gets shorter. Regarding parameters: Even if there are no input parameters to the stored procedure, individual queries within the procedure could still use parameters - if you define variables in the SP and use them in queries within the procedure, the variable is a parameter to the individual queries. A Query suffering from a poor execution plan is likely to get suspended on and off during execution, while waiting for information from disk or the buffer pool (many key lookups means many individual I/O operations eg).
0 Likes 0 ·
Thank you Magnus. Helping me for a starting point for my investigation
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.