• sqldba_newbie (11/10/2012)


    i have noticed tempdb contention on our tempdb, so i want to split the data file to multiple files.We have 24 cores, i know that i shouldn't be splitting more than 8 files. Our drive capacity is 100gb, how much should i assign size to each of these file.? Lets say if i assign 2 gb to each file, when will the transaction switch to next file?

    While there is no one right answer to the question of how many tempdb data files, there are some best practices to follow. First, I would recommend reading Paul Randal's blog on tempdb file allocation myths:

    http://www.sqlskills.com/blogs/paul/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    Creating one file per processor core is a myth perpetuated by many who do not fully understand how allocations in tempdb work. We typically allocate files based on the number of NUMA nodes in the server and either adjust it up or down depending on performance outcomes. Our primary SQL Server has 32 cores, but only 4 NUMA nodes. We therefore started with 4 data files and increased that number to 8 after tests showed we got better performance.

    Make sure you have a tempdb allocation problem first by confirming that you are seeing file I/O latch waits in tempdb (database = 2).