Our query used to take 30 seconds, and now it takes over 30 minutes. Why?

Question

We have a SQL Server stored procedure that normally runs in less than 30 seconds. It has been running fine for over 6 months. But today, at about 11:00 AM, it started to take over 30 minutes to run. I took a Profiler trace of the stored procedure when it ran, and it performed over 19 million page reads to complete. We reindex our database once a week, and have our database set to "Auto Update Statistics". What might be causing this problem?

Answer

Most likely, even though you rebuild your indexes weekly and have "Auto Update Statistics" on in the database, the statistics have not been updated correctly. Because of this, the SQL Server query optimizer has determined that an index, which I imagine is normally used for this stored procedure, should not be used, which is forcing a table scan of what appears to be a very large table.

I recommend that you update the statistics on the tables affected by the stored procedure and see what happens.

Reply to Above Answer

Once I updated the relevant tables, the stored procedure immediately began to run like normal, running in under 30 seconds. What can I do to prevent this from happening again?

Answer

For whatever reason, it appears that SQL Server is not updating statistics like it should. You might want to consider updating the statistics every night, assuming you have a slow period when you can perform this procedure.

Another option is to use an Index Hint in your stored procedure to specify that the SQL Server Optimizer always use the appropriate index.