Splitting tempDB files across CPU cores (multi-instance server?)

Hi all, I was wondering how you all approach splitting tempDB files across CPU cores in a multi-instance server environment. I have typically split tempdb to each CPU available (not core). Thus in a 2 chip dual-core environment i would have 4 tempdb database files.

But now looking at a multi-instance server environment where both instances are allocated all available CPUs I am not sure if giving each instance 4 tempdb database files is the best practice.

Thanks, Vin

asked Apr 05, 2011 at 10:29 AM in Default

vinman2
217 ● 2 ● 4 ● 9

Thanks for the input guys, I will check on those DMVs and see what contention (if any) we are experiencing. Here's something that I had read that was interesting also:

Denny Cherry wrote this article: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1307255,00.html

Apr 05, 2011 at 11:48 AM vinman2

(comments are locked)

2 answers:

@vinman2, Great question and there is no completely right answer. It all depends on your I/O and contention on your system. There are DMV's that can assist you with looking for contention in your tempdb. I will tell you that on multi slot quad core systems 4x4's, I typically start with 8 data files. I do have a few servers where that just isn't enough, so I had to increase a bit more. On my servers with dual quad cores, I start with 4 files, some of those have been moved up to 8. I have heard of extremes where folks have very large number of data files but those are the exceptions. I will look for my blog links to some queries for you for determining tempdb contention.

Robert David

List of Wait Stats

Additional information. I really only addressed your literal question. I should have also spent the time to make sure you are aware of all the best practices regarding tempdb. I assumed that by you asking about splitting tempdb into multiple data files that you are already aware of those best practices, but just in case you are not or someone else is reading this post then I want to make sure they have all the resources they need. I am sure others can pile onto this list and I would appreciate it as well.
1) Size the tempdb correctly. tempdb is recreated each time SQL Server Service is restarted. You don't want this thing growing constantly each time your service is restarted. You want to size it where it doesn't have to keep auto growing, but you do want to keep auto grow turned on for those just in case moments. 2) Split tempdb to its own physical set of disks (as fast as possible 15k RAID10). If possible put the data file on its own set of disks and the log file on its own set of disk (expensive right?), if you really have lots of contention and IO on tempdb is your bottleneck spring for a FUSION IO card. approx $7500 for a 320 GB card. Can push 100,000 IOPS.
3) As your questions mentions, split the tempdb data file into multiple equal sized files depending on your SGAM, GAM, and PFS contention in tempdb. There are lots of resources regarding this online. Check here and HERE