T-SQL code in script run faster than it does as a stored procedure. Why?

Question

I have some Transact-SQL code that I run as a scheduled job in SQL Server, and it seems to run much longer than I would expect. If I run the same code in Query Analyzer (as a script) it takes about 20 seconds to run. If I put the code in a stored procedure, it takes about 10 minutes to run. I have added "set nocount on" in the code and have even recompiled the stored procedure, but it doesn't help. When I check the query plan produced by the script, and check the query plan produced by the stored procedure, they are different. The script's query plan shows that an index is used, but the query plan for the stored procedure does not use the index. What is going on?

Answer

If you see different query plans produced by what is essentially the same code, as you have in your script and stored procedure, my guess is that the Query Optimizer is not able to come up with consistent query plans because the index statistics for the table or tables affected by your code are not up to date.

What you need to try is to reindex the tables in your database, and also ensure that the database settings, "Auto Create Statistics" and "Auto Update Statistics" options are on. By doing this, you can ensure that your indexes statistics are up-to-date, and this will help ensure that the Query Analyzer can work properly to select the optimum query plan to make your code perform as fast as it can.

Follow-Up From Question Writer

"Thanks a lot! Your suggestions worked. The stored procedure now only takes just 5 seconds to run. You're great!"