|
I need help on configuring multiple files for TempDB. We have 2 instance on a physical server with 2 Numa (8 processors) CPU. These instances are high performance OLTP databases. I didn’t think it would be right to configure by creating 8 multiple files for TempDB on each instances, but I might be wrong. Any advice? I would highly appreciate it.
(comments are locked)
|
|
Do you have a tempdb issue to begin with? Typical wisdom (once you've established that you have a tempdb bottleneck) is to start slowly - try # of files = half the number of cores the instance can see. Make sure they are all exactly the same size.
(comments are locked)
|
|
Take a look on Paul Randal's posts: A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core You will find very useful information here related to tempdb.
(comments are locked)
|
|
From my experience I would start out with 4 files on both instances and would have a dedicated RAID group for TEMPDB. With multiple instances and both having high performance OLTP databases you are bound to have contention with TEMPDB if it is configured by default out of the box. If this system has been up and running for awhile you are in much better luck because you have some data to go on with the size of TEMPDB that you need and possible work load that has been put on it. Just make sure you are following as many of the best practices as possible. Things like moving the ldf and mdf to dedicated drives, sizing it appropriately, splitting the data file into multiple equal size files, turning off auto close, etc. There are lots of resources about TEMPDB out there. I agree with Tim here. Start with four and evaluate your workload/look for contention problems.
Jul 01 '11 at 07:59 AM
SirSQL
(comments are locked)
|
