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 result, in 0-1 secs. But when I run sp2 it will return those 3000 rows in 10 seconds.

Would like to know is there any tool/command/anything which can set a specific stored procedure to optimize well?

Thanks.

回答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!