Splitting Tempdb - MAX DOP

Question

Splitting Tempdb - MAX DOP

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
August 3, 2010 at 1:21 pm

#144392

Hello,
I have two questions mentioned below, please advice.
i) We have MAX DOP enabled, if i split tempdb into multiple files across different drives will it make any difference or enabling maxdop will hinder the impact of splitting tempdb into multiple files?
thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Placing tempdb files on different drives is beneficial. Configuring the MAXPDOP/ using the MAXDOP hint depends on the requirement and the hardware capabilities.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

MAX DOP will not affect anything it is still dynamic and intend to use max available processor; it may also use fewer processor depnding on system workload.

see the extract from BOL:

==========

MAXDOP

Sets the maximum number of processors the query processor can use to execute a single index statement. Fewer processors may be used depending on the current system workload.

Note:

Parallel index operations are available only in SQL Server 2005 Enterprise Edition and later.

==========

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

The max degree of parallelism and having multiple files for your tempdb are not directly related. One has to do with file storage, the other has to do with how many of the available CPU's will be used by processes within SQL Server.

No, I wouldn't worry about MAXDOP messing with your tempdb file split.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Grant Fritchey (8/4/2010)
The max degree of parallelism and having multiple files for your tempdb are not directly related. One has to do with file storage, the other has to do with how many of the available CPU's will be used by processes within SQL Server.
No, I wouldn't worry about MAXDOP messing with your tempdb file split.

Heh... I wondered if anyone was actually going to get to the question that was asked. I was getting ready to hit the "post quoted reply" button and said, "Lemme see if Grant beat me first." 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Jeff Moden (8/4/2010)
Grant Fritchey (8/4/2010)
The max degree of parallelism and having multiple files for your tempdb are not directly related. One has to do with file storage, the other has to do with how many of the available CPU's will be used by processes within SQL Server.
No, I wouldn't worry about MAXDOP messing with your tempdb file split.
Heh... I wondered if anyone was actually going to get to the question that was asked. I was getting ready to hit the "post quoted reply" button and said, "Lemme see if Grant beat me first." 🙂

Yeah, it's my once a week allocation. See you next week.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

curious_sqldba

SSC-Dedicated

Points: 36494

August 4, 2010 at 7:51 am

#1202093

thanks guys.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

can someone point me to an article which states this? thanks

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

I doubt there's an article out there comparing the two because they aren't really something you compare. Here's the BOL entry for max degree of parallelism and degree of parallelism. That will get you started there. Here's a BOL entry on optimizing tempdb. You'll notice that parallelism is not mentioned.

That's the best I can do.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 9

curious_sqldba

SSC-Dedicated

Points: 36494

August 4, 2010 at 10:06 am

#1202183

sounds good..

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

iqtedar (8/4/2010)
can someone point me to an article which states this? thanks

Grant Fritchey is "article enough" for me, and he should be for you too. And if not, Jeff Moden also chimed in and I will add another +1 to the "MAXDOP doesn't matter for tempdb multi-files" answer. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service