Sql-server – Is splitting tempdb files across drives necessary

sql servertempdb

If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful?

I want to persuade my manager to put all 4 tempdb files on one drive.
If one of those tempdb files became corrupt, e.g. file D on drive B, would file A and B on drive A and file C on drive B continue to function, would tempdb 'break'? I assume SQL would create another tempdb file, based on the temporary nature of tempdb.

Thanks

Best Answer

If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful?

I suggest you to understand why we use multiple files for tempdb. Once you understand that clearly and know your underlying storage system (as suggested by comments) you will be able to answer the question. Things might vary depending on your SQL Server Version and major TempDB enhancements are done in SQL Server 2016, 2017 and 2019.

Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

Read more details about this here by Paul Randal.

Microsoft's official recommendation about reducing tempdb allocation contention is here:

Recommendations to reduce allocation contention in SQL Server tempdb database

If one of those tempdb files became corrupt, e.g. file D on drive B, would file A and B on drive A and file C on drive B continue to function, would tempdb 'break'?

I am sure the answer can be depends on the type of corruption. But I do not see any relevance with this question and splitting your files into two LUNs.

I assume SQL would create another tempdb file, based on the temporary nature of tempdb.

SQL Server will NOT automatically create new files, but you can do it yourself. Will it cause downtime? It depends. Read this question and answer.

Tempdb Add files require Restart Correctly adding data files to tempdb by Paul Randal

Related Question