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
(comments are locked)
|
@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. Ah, you type quicker than I do :)
Apr 05, 2011 at 10:39 AM
Shawn_Melton
(comments are locked)
|
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 I was quite pleased when Paul wrote this one debunking the myth of 1 file per core.
Apr 05, 2011 at 10:45 AM
Tim
I was pleased with all 30 :) learned a good bit from reading those. Also pleased as peaches that he made a PDF book out of it: http://www.sqlskills.com/BLOGS/PAUL/post/Myths-and-misconceptions-60-page-PDF-of-blog-posts.aspx
Apr 05, 2011 at 10:47 AM
Shawn_Melton
(comments are locked)
|
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