How do I increase my tempdb performance?

Question

SQL SERVER – 5 Performance Optimizations Must Do for TempDB 1) Pre-Size TempDB. There is no absolutely science how big your TempDB should be. . 2) Faster Drives – SSD. . 3) Separate Data and Log Files for TempDB. . 4) Multiple Data and Single Log Files. . 5) Set FileGrowth to a Large Fixed Value.

How do I speed up TempDB?

You can further increase tempdb performance by disabling the auto update stats, which will save your tempdb some work. Usually objects created in the tempdb are fairly small and, as such, the statistics will not reach the threshold that causes the statistics to update automatically.

What is the best practice for TempDB?

The recommended practice is to keep files and the initial space required for each file to be roughly 80 to 90% of the volume on which the TempDB is stored. The 10 to 20% disk space is left for OS-based virtual memory.

What can cause TempDB to grow?

There are many reasons for uncontrolled TempDB growth events. Much like your operating system has a page file to handle memory overflows, SQL Server uses TempDB like a page file. The most common occurrence of this is when a query “spills” to TempDB.

Does TempDB affect performance?

Although this may be true on smaller, less active SQL Server instances, tempdb can significantly affect SQL Server's performance. DBAs can act to ensure that tempdb performance is optimized and to optimize the overall performance of SQL Server.

Expert · Answer