July 23, 2008 at 10:44 am
I have a sql server 2005 Enterprise Edition x64 bit on a Windows Enterprise Edition 2003 server. The max degree of Parallelism for sql server is configured to '1'.
Question is related to TEMPDB. Currently TEMPDB has only 1 file (1 .mdf file). One of the article mentioned that to optimize the performance of TEMPDB, create more than 1 file , 1 for each cpu processor on your system.
I the Parallelism for sql server is configured to 1, would configuring TEMPDB with multiple files be any use at all?
July 23, 2008 at 11:11 am
MAX DOP refers to a single executing process. If you have two different connections to your database, they will still get their own threads. So, splitting TempDB or any other database onto multiple drive arrays could allow individual processes to have completely independant resources (their own processor and their own drives).
July 23, 2008 at 12:28 pm
I'm not sure you can control which objects go where in tempdb. I've not heard that splitting it helps. If you can get multiple drives under it, you can improve IO performance, which is what you really want to do.
July 23, 2008 at 9:32 pm
I dont think Parallelism and Tempdb disk contention are related.
I have first hand experience. We were given 4 dedicated RAID 10 spindles and created a tempdb file on each mount. We have 8 logical cpu, but only created 4 files since we could not afford the 16 some odd drives needed for RAID 10 for an 8 way. We had heavy Tempdb usage from poor architecture. I quantified a 15% increase in overall DB performance in the OLTP instance, which is very significant, albeit expensive.
IMHO, IO contention in TempDB (especially in the GAM), is a problem in many environments. Mileage may very, but the best gains from this approach are in data tiers with less than optimal DACs.
Good luck!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply