Stored Procedure Performance 2

[theusualsuspect]

Recently, I've seen a stored procedure that is executed fairly regulary take several times as long to run as it once did. What's strange is that when I copy the proc and the associated tables to another database it runs fine. Any thoughts?

 

[hmckillop]

Rebuild any indexes, or at least reindex them using DBCC DBREINDEX on queries that would be used in the proc. if this still doesnt sort things out try using sp_recompile (sp_recompile 'MyProc') to recompile the procedures query plan. You can also do this by using DBCC FREEPROCCACHE but this will also remove all other query plans of all procs.
This should sort out any dodgy query plans which may be slowing down the proc.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[vongrunt]

Probably lock/concurrency issues or missing/inaccurate column statistics (some queries in SP are slow).

Lotsa things depend on SP code and database itself, so it is hard to guess without extra nfo.