Thursday, October 2, 2008

How to Performance Tune the Microsoft SQL Server tempdb Database

Heavy activity in the tempdb database can drag down your application's performance. This is especially true if you create one or more large temp tables and then query or join them.

To help speed queries or joins on large temp tables, be sure the AUTOSTATS database option is turned on for tempdb, and then create one or more indexes on these temp tables that can be used by your query or joins. This means that you will need to create the temp table, and then add the appropriate index(s), for the temporary table(s) you create.

In many cases, you will find that this can substantially speed up your application. But like many performance tips, be sure you test this one to see if it actually helps in your particular situation. In some cases, the overhead of creating the index(s) is greater than the time saved by using them. Only through testing will you know which option is best in your situation. [7.0, 2000, 2005]

No comments: