Sql Server 2005 - Optimizing Stored Procedures

I have 2 stored procedures (sp1 and sp2) in SQL Server 2005 with the same sql script (no diff at all but the name). When I try to execute sp1 it returns more than 3000 rows as resu

Solution 1:

SQL Server saves "plans" of how to execute code.

If a stored procedure is run against a small subset of data it will optimize for a small dataset. The opposite is true for a large dataset.

Theres a nice OPTIMIZE FOR feature in 2008 but in 2005 you are stuck with WITH RECOMPILE. This means it will be recompiled each time it is run, which, in some cases is most optimal!