I have a stored procedure that normally takes 1-2 seconds to run. It is just a Select, no action.

However, after a couple of days, it then takes 7-8 minutes to run.

The tables are not being heavily modified.

If I stop the SQL server and its service then start it again and run the same stored procedure, it runs again in under 2 seconds.

I am doing a nightly "Reorginize data and index pages" with the option checked for "Change free space per page percentage to" set to 10%.

Any thoughts?

Mitch

__________________
Mitch

I just did a sp_recompile on it and that did not work either.

Mitch

Use CHECKPOINT Regularly and use DBCC FREEPROCCACHE. Its an issue related to Procedure Cache, You need to keep it clean may be with a frequent SQL JOB. please refer other DBCC commands and SPs for buffers and Cache.

Hope this works perfectly.

B. Anant