SQL Server Performance Forum – Threads Archive
Slow running QueryHi I Have a Stored Procedure which joins 2 Tables( which are not very large , but it still has appropriate indexes )
and it used to run in no time ..Suddenly our web pages started giving time out errors. After checkiing the Execution Plan .. its doing an Index Seek and even I checked the Fragmentation on the table using show contig
which showed almost 100%. After checking all these .. I dropped and recreated the SP and its started working fine ..giving the result set back
in no Time . What could be the reason for these time out errors and why it is working fine after I dropped and recreated the SP with out making any changes. Thanks A Million
It can be the compiled plan. If the sp is reading data from frequently updated/inserted tables, then you have to recompile it from time to time. A good way to do it is to use sp_recompile with input parameter one of the join tables – this will recompile any stored source that accesses the table.
Another possibility is that the actual sp was attempting to recompile at each query – this is possible if you have recursion or looped calls between stored procedures/triggers.
"How do you expect to beat me when I am forever?"
The advice you have received so far is good. In addition, consider using Profiler to capture details of the stored procedure in question, tracking the execution plan, recompiles, and any other data you want to track, so that you can see what is going on in more detail. The more data you have, the easier it will for you to identify the exact problem. If it is not a recompile issue, also consider the prospect of out of date statistics. Although statistics should update automatically (if the default database statistics settings are used), they don’t always do so often enough, which sometimes can through off the query optimizer. Only consider this after if you eliminate recompile as an issue.
Brad M. McGehee