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, 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.
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
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]: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
answered Apr 05, 2011 at 10:38 AM