Resolving Query Plan Differences

The system administrator or user cannot know or control which query plan is used for a given execution. This can explain unexpectedly different execution times for the same procedure given the same data and parameters. If this occurs, dropping and re-creating the procedure will cause all existing plans to be flushed from the cache.

To ensure that you always get your own plan, you can use either the EXECUTE statement with the WITH RECOMPILE option or the CREATE PROCEDURE statement with the WITH RECOMPILE option. Remember, however, that creating a procedure by using the WITH RECOMPILE option decreases performance because every execution requires compilation.