Understanding Max DOP Settings

I am back again folks with one more article. Donno now a days I am enjoying writing in english rather than tamil. Did I start thinking in english by any chance? (enof enof frst start thinking dude kind of thoughts).

Today we are going to talk abt MAX DOP and a interesting fact about it.There is a lot of wrong thinking that max DOP is to determine the no.of processors used by SQL Server. Its not basically affinity mask is the one to do that. This one jst to determine the maximum number of threads an operator (or an operation) in a query can use. Even the explanation in BOL is not quite upto the mark :(. Quite confusing isn't then check this out page then :).

This time again with a peculiar problem. I have set max DOP as 4 in one of my server interestingly I am seeing alteast 20-30 parallel threads. Believe me that scared me a lot. After lots of googling, talking to PSS engineer nothing worked out.

Atlast I went to my last resource social.msdn.com(Believe me folks one of the best place to hang around for sql server techies) a couple of MVPs replied back telling dude MAX DOP is not for query rather its applicable for each operator ooch..Linky dinky of this question.so no wonder my queries are executing in 30+ threads. So my recommended recipe to set up max DOP is to set a one for the server level and if you think a couple of your queries are performing worst due to this then set a one in the query level.

Thats it for the day folks. Hope to see you all soon with some interesting problem.Until then its me signing off with one full & one broken leg :)

With Smiles

Santhosh