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
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.