x

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
more ▼

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

vinman2 gravatar image

vinman2
217 2 2 4

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)
10|1200 characters needed characters left

2 answers: sort voted first

@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
more ▼

answered Apr 05, 2011 at 10:37 AM

Tim gravatar image

Tim
36.4k 36 41 139

Ah, you type quicker than I do :)
Apr 05, 2011 at 10:39 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

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][1]

[1]: 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
more ▼

answered Apr 05, 2011 at 10:38 AM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 19 21 29

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x66
x50
x22
x13

asked: Apr 05, 2011 at 10:29 AM

Seen: 8461 times

Last Updated: Apr 05, 2011 at 10:29 AM