Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions. Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs. Make each data file the same size; this allows for optimal proportional-fill performance. Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks. Put the tempdb database on disks that differ from those that are used by user databases.
7. Figure out the number of logical processor cores you have (e.g. two CPUS, with 4 physical cores each, plus hyperthreading enabled = 2 (cpus) x 4 (cores) x 2 (hyperthreading) = 16 logical cores
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15Optimizing tempdb Performance
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175527(v=sql.105)?redirectedfrom=MSDNA SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
