Kirk,

SQL creates a performance plan on the SP when it compiles it, it does this based on the sizes of the tables and the state of the indexes at compile time. As tables grow the execution plan may no longer be the optimal plan. In addition to Jon's suggestion you might also check the frequency of reindexes on these tables as well at statistics updates.

You should look at the query plan to see how many SCANs vs SEEKs are being done. SEEKs are good SCANs are bad. Table SCANs are the worst unless they are on very small tables.


>The code in the stored procedure below normally return a result set to the user in about 1 second. But it seems like after some time (1 - 2 months) the time goes up to 10-12 seconds. I open the stored procedure and add a blank line, or remove one, save it, and it runs fast again. I'm sure this recompiles it and ?stores it in the cache? again? I guess I'm wondering if there is a way within my code that I can ensure it gets recompiled every so often, like when I detect that it took over a certain number of seconds. This SP gets called maybe 100-200 times a day. As a side, if somebody knows a better way to code this SP I'd be willing to listen to suggestions.