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.
asked Apr 05, 2011 at 10:29 AM in Default
@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.
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.
For the most part I do not split the tempdb unless performance issues point to needing.
Paul Randall did a good write-up on it: A SQL Server DBA myth a day (1230)-tempdb should always have one data file per processor core
answered Apr 05, 2011 at 10:38 AM