Often SQL Server works just fine with the configuration that comes right out of the box. But when it doesn’t, adjustments to Temp DB are a great place to start when performance is not what is expected. There are a few things you can do.
First it helps to isolate the tempDB database files onto separate drives…the fastest ones available. It even helps if you can separate the transaction log from the data files.
Second, it helps to have as many data files as you have cores up to a point. The maximum number of files lays somewhere between 8 and 16, but no more than the total number of cores you have available. This is an area that you need to test to see what works best for you. The tempdb data files should be the same size.
Third, use the optimal block size on the windows drives. SQL Server reads 64k bytes, or one extent, for each disk access. So, it helps if your disk is formatted using 64k blocks, since the operating system reads in blocks. By formatting the disk drives in 64k blocks you can synchronize the SQL reads with the operating system.
Fourth, reduce disk fragmentation. You can’t always isolate the SQL Server files on a separate drive. Make sure that the data files are not fragmented on the disk by allocating all space needed by pre-allocating disk space for the tempdb files so that do not need to auto expand.
Share your experience in tuning tempdb. Get into the conversation with your comment here, or drop an email to btaylor@sswug.org.
Cheers,
Ben