Quick Tip: TempDB on a multi-core SQL Server box

Just learned this today from one of the Microsoft support reps reviewing our SQL Server 2008 box for performance optimizations: if you are running SQL Server on a multi-core box, you should set up tempdb to have as many data files as there are physical CPU’s (up to a reasonable limit).  These files should also be equally sized.  In other words, if you need a 6GB tempdb, and you have 2 cores, you’d want to have 2 3GB files.  If you have 16 cores, you can probably limit it to 8 files of 750 MB.  In a 32 core box, you may benefit from additional files, but you probably want to test to see.

The reason for this (as it was explained to me) is that on a high-transaction server, there is the possibility that the file I/O associated with the creation and destruction of temporary objects may cause blocking on parallel CPU operations. For example, CPU0 may run a stored procedure that uses temporary tables; CPU1 may also run that same procedure simultaneously.  If there is a single tempdb data file, the I/O associated with the creation of the local temp object with the stored procedure being executed on CPU0 may block the creation of the files associated with CPU1, even though the tables themselves are locally scoped. 

Sizing the files equally also assists the optimizer, as space availability per file is used in considering where to store the local objects.   If you have 1 file that is extremely larger than the second file, most of your objects will be created in the larger file, which could also contribute to blocking concerns.  In short:

• Create the same number of tempdb data files as there are physical processors on the server (up to 8), and
• Make sure that the data files are equivalently sized.

NOTE: Immediately after posting this, I read the following from Kevin Kline.  Please note that I am not talking about multiple log files, but rather multiple data files.  http://sqlblog.com/blogs/kevin_kline/archive/2009/06/23/old-performance-tuning-recommendations-die-hard.aspx

NOTE2: @kbriankelley tweeted me shortly after this post, and recommended the following post: http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx

Apparently, there may be some disagreement about the requirement for the 1-to-1 ratio of data files to cores.   Paul Randall indicates that it may be a 1-4 or 1-2 ratio. I don’t know; I had a Microsoftie in the office telling our DBA team that we needed at least 8-10 data files for our 16 core box (and he did specify the 1-1 ratio) .  As a database developer, I don’t think about file I/O enough to argue with him.  I do think that this is a tip worth investigating and testing, however, which is the reason I posted it.