Do I need more tempdb files?

Question

You may have read that you need to have more than one data file in SQL Server's tempdb. This can happen even if you're using blazing fast storage. If you create a lot of tiny objects in tempdb you may hit a bottleneck on special pages that SQL Server uses internally to allocate all those objects. 4 нояб. 2020 г.

How many tempdb files should you have?

According to Microsoft Support, the best approach is to create one tempdb data file per logical processor up to 8 data files. If your system has more than 8 logical processors, start with 8 data files and monitor your server's workload to determine if more data files would be beneficial.

Why is it recommended to have multiple files for tempdb?

Microsoft recommends that you create multiple files to reduce contention issues within the TempDB. It is recommended that you create one file per CPU core- a server with (2) 4 core processors would need 8 data files. There is no need to create multiple Log files, one is sufficient.

Can I delete tempdb files?

All tempdb files are re-created during startup. However, they are empty and can be removed. To remove additional files in tempdb, use the ALTER DATABASE command by using the REMOVE FILE option. Use the DBCC SHRINKDATABASE command to shrink the tempdb database.

What happens if tempdb is full?

The TempDB database is special in many ways, but an interesting aspect is that when its files automatically grow when they become full, this growth is not persisted and will be undone on the next restart of the SQL Server service.

Expert · Answer