Tempdb Affects Query Performance During Autogrow

The execution time for a particular stored procedure increases 500 percent when we restart SQL Server. Our tests account for the effect of data caching, and the stored procedure's execution plan doesn't change. We don't see drops in performance from our other stored procedures, and the slow procedure's performance returns to normal eventually. How can we tune the slow procedure's performance?

One thing many people forget about in this situation is tempdb's effect on query performance. People also neglect to manually set tempdb to an adequate size, so tempdb autogrows to whatever size it needs as the application runs. Unfortunately, the default size for tempdb is 8MB, and it shrinks to this size when you restart the server. Imagine a situation in which your application forces tempdb to grow to 500MB. Restarting SQL Server shrinks tempdb to 8MB, then when SQL Server restarts, it autogrows back to 500MB again. The autogrow process can be slow and degrade the performance of queries that use tempdb during the autogrow process. I can't say that this is definitely the cause of your performance problems, but it's worth checking if you've investigated other, more obvious possibilities. Run Listing 1 to see what size tempdb was when the server was last started. If tempdb is autogrowing, use the ALTER DATABASE command to set tempdb's size to the maximum size it grows to during normal processing. Remember, setting tempdb to 1MB larger than its current size will force SQL Server to recreate tempdb at that size when it restarts. That's not necessarily what you want, but you want to avoid growing or shrinking databases, including tempdb, during typical production cycles so it's best to plan ahead.