I would like to split tempdb
into two files with same size, to improve performance of the database.
I wonder if these two data files can be on the same disk, separately from the logs and users data files?
sql serversql-server-2012tempdb
I would like to split tempdb
into two files with same size, to improve performance of the database.
I wonder if these two data files can be on the same disk, separately from the logs and users data files?
Best Answer
We have various installations at our shop and we tend to start low with four tempdb files and then add additional files if there is tempdb contention as pointed out by @Kevin3nfs comment, where he references an SQLSkills.com search.
Microsoft has a knowledge base article: Recommendations to reduce allocation contention in SQL Server tempdb database
SQLSKills.com has a good article The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention which has some general information about tempdb contention and a bit further down a script which will retrieve information about tempdb contention from some DMVs.
Below is a script we used for very large database server configurations. It creates a tempdb with 8 files of 11GB size with no autogrowth and a tempdb transaction log file of 11GB. This script can be adapted to your requirements.
As you can see these files were all located on the same drive.
If you still notice contention in the
tempdb
database, then splitting thetempdb
database files over various disks might be a possible solution, but than depends on your configuration (SAN, SSD, ...) and would have to be verified with testing.