Thanks for the explanation Hugo. The questio happened to fall while I was on vacation and traveling. In addition to your explanation, each database should have a good maintenance plan. Within that plan should be the updating of statistics on the various indexes as well as rebuilding indexes that need it. I also use a DBCC UPDATEUSAGE as well. After all of my maintenance is completed, I want to ensure that the various stroed procedures are using an optimal QueryPlan. So, the last thing I do is to perform a recompile of all stored procedures and triggers. Ultimately, this will force the procedures to reaquire a new queryplan the first time that they are run.

Additionally, within your explanation, you should go a bit further. It slips me right this moment which hint it is, but for the scenario you describe, there is a hint that can be used in order to have the procedure use the queryplan for the longest running parameter. this takes some effort to identify, but works. I found it while trying to quell an attempted developer revolt, in that the developers wanted to recompile procedures each time that they ran. In a 1200 user high transaction database, where some of these procedures are called literally a hundred times an hour, could be a problem. I will lookup the hint and post it later.