SQL Server - Option (MAXDOP 1)

Asked By Ken on 12-Apr-07 06:51 PM
On more than one occassion I have put this hint "Option (MAXDOP 1) in
my queries to get them to run faster, dramatically faster. And this is
after optimizing the query for everything else. I've done a lot of
searches on google and most of them just say "Do this" but I haven't
found much telling me "Why" this is necessary. The server (SQL 2000)
configurations for maxdop is set to the number of physical processors,
i.e, 4 and default cost threshold is 5 seconds. You would think sql
server would be smart enough to know when to execute using parallelism
or not? And when it does decide to use parallelism for certain
queries, it runs slower. In some cases, 100 times slower. The engine
seems to be "unreliable" on these occasions when it decides to use
parallelism?
Is this fixed in SQL 2005 because we are upgrading this year?




Greg Linwood replied on 12-Apr-07 07:07 PM
Hi Ken

I agree with your thoughts.

I nearly always turn parallelism off for OLTP systems for this reason. (Data
Warehouses are a totally different ballgame). With OLTPs, it's generally
better to use multiple CPUs to allow concurrent processing of multiple
queries rather than allow individual queries consume all CPUs at once.

You can use either individual query hints using the OPTION clause as you've
already described or just turn DOP down to 1 at the server level..

This isn't *fixed* in SQL 2005 b/c ultimately, SQL Server can't know whether
you're installing it for Data Warehousing use or OLTP use - you basically
need to configure this on a case by case basis..

Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Ken replied on 13-Apr-07 10:20 AM
Hi Greg,

Thanks for your response. Unfortunately we have both reporting and
oltp going in the same databases. A problem, like many others, I
inherited from my predecessors. My other thought is if I was running a
read-only reporting database and hit it simultaneously with many
queries (not all optimized) and index and statistics not routinely
built or up-to-date that would over utilize processors and cause a
similiar situation?

A collegue of mine pulled this from SQL 2005 books online "If the SQL
Server 2005 Database Engine detects that the system is busy, the
degree of parallelism of the index operation is automatically reduced
before statement execution starts."   However a closer look at this
showed me this statement was under "Configuring Parallel Index
Operations".  According to this if you were configured to use all
processors, SQL would be smart enough use the available processors if
it detects that other processors are busy for performing index
operations. So why not everything else? However, according to SQL
2000, I've read that if you have a high MAXDOP number doesn't mean
that SQL Server will always use all processors. So I can't even rely
on what I read? What I'm gathering here is regardless of what I may
read or am suggested or sold, a High MAXDOP setting just cant be
relied upon and and MAXDOP setting of 1 is always guaranteed granted
that MAXDOP 1 may not be necessarily what you want cases when you
could take advantage of parallelism?
Chris Wood replied on 13-Apr-07 10:57 AM
Ken,

Are you using hyperthreaded CPU's? We are and using all CPU's on SQL2000 SP3
and are not really sure if we should switch hyperthreading off.

Chris
Ken replied on 13-Apr-07 11:07 AM
Yes, we are using hyperthreading. But we set the maxdop on the server
setting to the number of physical processors not logical processors.
Chris Wood replied on 13-Apr-07 11:20 AM
Ken,

Have you set the affinity mask as well so you only use the physical CPU's?

Chris
Ken replied on 13-Apr-07 12:12 PM
no, should I?
Chris Wood replied on 13-Apr-07 01:24 PM
Have you read this Ken?

http://support.microsoft.com/kb/322385

Chris
Ken replied on 13-Apr-07 01:48 PM
Okay, I have read it. Our afffinity mask settings are set to default. So
we use all logical (8) processors. Why would I want to change this
setting?
Chris Wood replied on 13-Apr-07 03:10 PM
Here is an old Blog.
http://news.zdnet.co.uk/hardware/0,1000000091,39237341,00.htm

I am unsure if SQL2000 works best on hyperthreaded CPU's and hoped to get
some feedback.

Chris
Ken replied on 13-Apr-07 03:36 PM
Hi Chris,

I cannot either confirm or deny. Sorry. My guess its not something to
worry about. There would most likely be more posts if it really was an
issue. However, if you get any feedback on the contrary I would sure
like know.
Chris Wood replied on 13-Apr-07 04:14 PM
Ken,

Maybe Greg or Tibor would like to comment?

Chris
Andrew J. Kelly replied on 13-Apr-07 05:17 PM
That definitely helps but is not a cure all. You can still get into
situations where you perform a similar operation on both the logical and
physical processor pair. If this happens you can almost be assured of
decreased performance. That is because they both want to use the same parts
of the processor at the same time but they think they each have their own.
HT works best when the operations use parts that the other does not need.
For instance if they are both trying to do I/O they conflict. If one needed
to do floating point calcs and the other I/O they can potentially do more of
a concurrent operation.  That's greatly simplified but is close enough:).
Even if you have access to all physical processors in parallel that does not
mean you can't have issues. Parallel operations always use more resources
than a single threaded operation. Under the right conditions the parallel
operation will return the results faster than the single threaded but it
will decrease concurrency and use more resources to do so. Sometimes that is
goo sometimes it is not. In a heavy OLTP system parallelism is usually not
desirable except for maintenance type operations such as rebuilding indexes,
DBCC's etc. If you have a parallel operation and one thread takes a long
time to finish the others can sit idle waiting for it so they can coordinate
their actions. This usually results in CXPacket waits and usually are
detrimental to performance overall. I am not trying to say that you can't or
shouldn't use parallelism but you can usually get by with less than you
think. SQL Server does have smarts so as to not use all procs when the
system is busy etc. but you may want fine tune the system as well. Try
setting MAXDOP to 2 and see how that goes.

--
Andrew J. Kelly SQL MVP