Tempdb configuration survey results and advice

A few weeks ago I kicked off a survey about tempdb configuration – see here for the survey. I received results for more than 600 systems! Here they are:

These are very interesting results, for several reasons:

• It shows the relative distribution of core-count for SQL Servers, with a pronounced shift to 8+ cores (55%), but still with a quarter of respondents using 4-core machines – they've got a lot of life left in them.
• It shows a result from someone with 128 cores on their server (at time of writing I didn't know what kind of machine, but the machine owner was kind enough to add a comment – it's an 8×8 with hyperthreading enabled and 2TB memory!).
• It shows that across the board, just over half of all servers are configured to have a single tempdb data file, regardless of the number of cores.
• For the 7 systems with 48 or more cores, none had a single tempdb data file.

One of the hidden causes of poor performance can be incorrect tempdb configuration. There has been a lot of info posted about tempdb, so I don't want to repeat it here, but instead give you some pointers.

Is tempdb I/O a bottleneck? Use the sys.dm_io_virtual_file_stats DMV (one of my favorites) to see the read/write latencies. Jimmy May (blog|twitter) has a great script using this DMV here that calculates per-I/O latency for all files on a SQL Server instance. If you see latencies higher than you'd expect from your I/O subsystem (say 15ms or more), then you need to take action. Adding more files or moving tempdb to a faster I/O subsystem is not necessarily the answer. It could be that you have some workload making use of tempdb that shouldn't be – e.g. a large sort or hash join operation that's spilling to tempdb, or someone using snapshot isolation in a database and causing a lot of read/write activity because of the version store. Take a look at the whitepaper Working with tempdb in SQL Server 2005. It also applies to 2008 and will show you how to diagnose tempdb performance issues. If you know what's going on with tempdb and it all looks ok, then you will have to reconfigure tempdb. This could mean adding more files to allow I/O parallelizing (just to be clear – I mean I/Os being serviced by different portions of the I/O subsystems, not multiple outstanding I/O requests from SQL Server – which happens all the time), moving to faster storage, moving away from other database files. The solution will be different for everyone, but a generalization is to separate tempdb from other databases, and sometimes separate tempdb log from tempdb data files.

Is tempdb allocation a bottleneck? This is where in-memory allocation bitmaps become a contention point with a workload that has many concurrent connections creating and dropping small temp tables. I've discussed this many time – most recently in the post A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core. This post has lots of links to scripts where you can see if this is a problem for you, and discusses trace flag 1118 and how to work out the cores:tempdb-data-files ratio for your situation.

Note: if you are going to add more tempdb data files, ensure that all tempdb data files are the same size. There is a known bug (which has been there forever and is in all versions) that if tempdb data files are not the same size, auto-growth will only occur on the largest file. You can work around that using trace flag 1117 (which forces all files to auto-grow at the same time) but that applies to *all* databases, just like trace flag 1118, which may not be the desired behavior.

Bottom line: you've only got one tempdb – take care of it!