I have a stored procedure which usually runs pretty quickly (hardly a few seconds), but then there are odd days where the same proc with the same parameters takes minutes to execute. But if I defrag the indexes at this point, it starts running within seconds again.
Could this be because of bad execution plan or fragmented indexes?
If so, Is there a way I can make this procedure NOT dependant on execution plans or fragmented indexes?
Thanks in advance, Joseph
Well, depending on your SP, the solution might be throught these options:
1/ WITH RECOMPILE could save your day. This increases the total execution time, by recompiling the SP, but it assures you'll have the best execution plan.
2/ KEEPFIXED PLAN could be also an option.
3/ It worths to give a try with OPTIMIZE FOR in case you have a set of parameters that are "representative" from statistics point of view.
4/ Monitor the level of fragmentation on involved tables and indexes. Check if there are statements that heavily updates the tables used by your SP. If yes, update statistics (UPDATE STATISTICS <tablename>;)
5/ Parameter sniffing could be also a root cause.
You can go further into details and see a list of causes of recompilations.
Short answer: no. SQL Server relies on execution plans and indexes to perform well.
Longer answer: Maybe. If your performance improves immediately after defragging your indexes, then my first question would be: which indexes, and why are they fragmenting? Are you clustering on a uniqueidentifier? Are your statistics up-to-date? What's the execution plan look like?
