How many tempdb files should I have in SQL Server?

Question

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. 2 июл. 2019 г.

Do I need more TempDB files?

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.

How many TempDB files we can create in SQL Server?

It is recommended to create a data file per logical processor for the tempdb database, until a maximum of 8 data files, meaning that if your server has more than 8 processors, you should not create more than 8 data files. This is only a best practice recommendation, though, and can be tweaked if necessary.

How does SQL Server calculate TempDB files?

Open SQL Server Management Studio (SSMS) and go to Object Explorer. Go to databases (System Databases) and expand TempDB. Next, right-click on it and check the properties for it. It will bring up the following screen where you can find the number of the database files.

What is the best practice to configure TempDB SQL Server?

What are some of the best practices for TempDB?Do not change collation from the SQL Server instance collation.Do not change the database owner from sa.Do not drop the TempDB database.Do not drop the guest user from the database.Do not change the recovery model from SIMPLE.Tempdb Configuration Best Practices in SQL Server - MS SQL Tips

Expert · Answer