Aaron,

With regard to #2, the workaround is to use the sys.dm_os_waiting_tasks DMV instead of sys.dm_exec_requests.  This does seem to work in all of my tests so far, and I should add that the next version of my Who is Active? script--to be released very shortly--uses the waiting tasks DMV in order to show correct blocker information.

Hi

re 1 - actually the sp_configure setting does not "force" MaxDoP to 1, query hints take precedence over the sp_configure setting - the sp_configure setting only adjusts the default (not specified behaviour).

re 4 - At the time Lars was a colleague so I can definitely say that the error could be reproduced consistently.

In certain circumstances any hint is valid within a production environment. I don't like putting in hints but I have and will do in production systems when needs arise. Whilst I accept not everything is always rosy in the garden I take a pretty dim view of articles/posts which encourage users to turn off paralleism on the server - which is what tends to happen - no doubt I will now find this post being championed as a reason to disable parallelism on SQL Server. btw. I have done some extensive tests with parallelism on 16 core boxes showing ( in my tests ) up to 600% performance degredation by turning off parallelism.

Thanks, Aaron!  #1 is of particular concern for me, so I appreciate the head's up.  :)

Oh, and FWIW, I do have some procs that use MAXDOP <> 1 restrictions, i.e. MAXDOP 2.  

Good post Aaron, parallelism has all sorts of means just within SQL. There's processor parallism, I/O affinity, parallel query plans, plus just what happnes under the covers with more than one core. People are often under the impression if they set MAX DOP =1 on some troublesome query (happened a lot in 7.0 and 2000, which still have an install base as you mention - it's happening right now), an 8 core server is wasting 7 processors. Not true. Whatever SQL passes down to the core might still use more than one processor.

And, to make things more complicated, there's thread programming and creating your own parallelism. I recently saw an web app that had multiple threads running different queries to draw a page. 3 threads would finish in 2 seconds, one in 30. The end result - 30 seconds for the page to render.

More processors, parallelism, advanced features - I find all of them detrimental if you don't take the time to understand them. Your list, imho, should be common knowledge to anyone who acts as a DBA or dedicated database developer.

More post like this!

Using IDENT_CURRENT might be a good work-around for #1.

If I see high CXPACKET waits with an OLTP workload, I am still going to recommend setting MAXDOP to 1 at the server level, and seeing what the results are. Its not like these settings are permanent...

Glenn,

In high concurrency situations IDENT_CURRENT can return a value inserted from another connection.

GrumpyOldDBA

I disagree with your assertion that Parallelism shouldn't be turned "off" by default.

The following question should always be considered when assessing whether parallelism should be enabled or disabled - does the DBA want individual queries consuming multiple CPUs or, alternatively, does the DBA want multiple queries running concurrently on multiple CPUs?

In OLTP scenarios, I think most would answer that multiple CPUs are installed to support multiple concurrent queries, not individual query parallelism. These DBAs should clearly turn parallelism off & use hints for the occassions they actually want parallelism (for the few big reports that run accross an OLTP system for example)

There's little doubt that DSS work loads can benefit from parallelism, but this doesn't mean OLTP workloads do & by a very large margin, most SQL Server systems are OLTP systems.

You said that you've seen 600% degradation in performance from turning off parallelism, but you didn't state whether this was OLTP or DSS based!?!? Leaving this crucial information out of your statement is a bad omission.

Note that Microsoft's current top TPC-E OLTP benchmark has Max Degree of Parallelism turned OFF, whilst the top SQL Server TPC-H 10Tb+ Benchmark has it turned ON. Obviously Microsoft's TPC benchmarking team disagrees with your opinion.

TPC-E (note MAXDOP config on page 30 of 60):

http://www.tpc.org/results/FDR/tpce/nec.express5800.A1160.e1.7.0.090318.fdr.pdf

TPC-H (note MAXDOP config on page 44 of 305):

http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=109021801

It should also be noted that Oracle takes the opposite approach to SQL Server by leaving parallelism turned OFF by default. This is obviously because more Oracle databases are also deployed for OLTP than DSS. In my opinion, SQL Server *should* be configured the same way by default & this would benefit most users (the relative few who build DSS systems could simply turn it on).

As for personal experiences, I have professionally measured the performance of thousands of SQL Server systems over 16 years & have NEVER seen an OLTP system that has benefitted more in its overall workload from having parallelism turned on than off. Furthermore, I have seen MANY instances where parallelism has brought even large SQL Server systems down, as I'm sure many others have as well.

imo, you're up the wrong tree with this one..

Cheers,

Greg Linwood

Great post Aaron btw, this list contains some important & very useful pieces of information..

Cheers,

Greg Linwood

Configuration choices, aside, my concern is a simple one that I've seen voiced elsewhere. These are functionality breakdowns. Some of these are things folks have depended on because they're supposed to work. The @@IDENTITY and SCOPE_IDENTITY() bug is a perfect example.

I think Aaron's point of "2011 is a long time away, and if some extra votes create an opportunity to raise the priority, then by all means, vote away," is right on the mark. And this reminds me of the whole SQL Server 2005 SP3 argument. We kept arguing for and it seemed to take a lot for MS to finally agree to put it together and release. If there's functionality issues in a product that's still under Mainstream support (and both SQL Server 2005 and 2008 are), we should be able to expect that these kinds of issues get fixed and not deferred to the next major version.

I agree that OTLP doesn't benefit from parallelism but if we are seeing cxpacket waits, we probably have missing indexes or need query tuning. Alot of OLTP system do have DSS type of queries that run either overnight or because the business requires real time data. Index maint also benefits from parallelism. The arguement could be made to leave parallelism on globally and turn it off on problem queries.

Well I guess I could say that it proves my point about disabling parallelism. Many applications do not necessarily fall into either the oltp or dss camp; but the databases I work with would not be described as being the back end for a data warehouse. I suppose you might say they were a type of order processing. My tests were not on what you call a DSS system and were extracted from systems with parallelism turned off where there were performance problems - I was making the point that many of this issues could be resolved by turning parallel processing back on. It's worth pointing out that there is a vast difference ( in the applications I have supported ) in how parallelism is handled from sql 2000 to sql 2005/8.

Whilst I agree about using individual threads with increasing number of cores how many actual concurrent active processes do you really have on a system - probably far less than you might think, even on a 1000 user system.

At least it generated a discussion !

Grumpy - what is a "back end for a data warehouse"?

Generally speaking, the right thing to do with MAXDOP is turn it off globally for OLTP & use DOP hints for the few reports or big (eg search) queries that actually benefit from parallelism in an OLTP. Even then, you probably don't want individual queries going parallel over ALL CPUs.

This approach makes far more sense than leaving DOP on globally / unrestricted (0) & having using OPTION (MAXDOP 1) in a large number of queries to control the system (I've never seen anyone actually do this - generally most DBAs just turn parallelism off).

IMO, it would be far better for SQL Server to be configured with MAXDOP off by default b/c the cases where things go wrong on OLTPs is fairly common. A lot less harm would come from requiring those who really need parallelism to turn it on.

Along with the line many have already put forward, I'd argue that (1)more and more we are seeing mixed workloads on servers, (2) SQL Server query parallelism used to be terrible, but has got a lot better, and (3) keeping parallelism on but restricting it to a low number appears to be a good compromise in many cases.

Ultimately, this is a problem with the query optimizer. From the their respective default setting, one could argue that Microsoft is probably more confident with their query optimizer w.r.t. parallelism than is Oracle with theirs :-)

Ideally, SQL Server should allow more granular control of query parallelism than it currently allows (e.g. to be able to control parallelism for maintnenance operations only or for some databases only).

Linchi - why do you think DOP is turned off (MAXDOP = 1) in high end TPC-E benchmarking then? If it were better on OLTP to have DOP restricted to a "low number", why wouldn't Microsoft set DOP to something like 2 or 3 in their big TPC-E benchmarks?

Greg;

I'm not arguing with you on TPC-E's MAXDOP setting. I'd turn it off if I were to configure it. But then it is a typical OLTP workload, whereas Iwas talking about making a compromise in real-world non-OLTP-pure workloads.

By "real-world non-OLTP-pure workloads" do you mean OLTPs that have a few search & report queries? In my experience managing a wide variety of business apps, this is pretty much what you're dealing with in most cases. It is FAR easier to manage these type of apps by turning MAXDOP off globally & enabling where necessary on the few queries that actually benefit from parallelism. Configuring MAXDOP to something low like (2) or (4) still leaves the majority of regular OLTP queries susecptible to parallelism problems, so I disagree with this as being a good approach.

Again, I don't think there is any argument here. I wasn't precise, I guess. If the workload is predominately OLTP, set MAXDOP = 1.

> By "real-world non-OLTP-pure workloads" do you mean OLTPs that have a few search & report queries?

No, I meant real mixed workloads. I would characterize OLTPs that have a few search & report queries as OLTPs. Try as hard as you may to separate OLTP from reporting, they pop up mixed everywhere. It could be the case where OLTP queries are mixed with reporting queries in the same database. It may also be the case you have a database that is basically OLTP living with a database that is basically reporting on the same server. There are many combinations.

But back to my original point, I do think the SQL optimizer has got a lot better in dealing with parallelism, far from perfect but better nonetheless.

When you say "better in dealing with parallelism" do you mean that the optimiser chooses parallelism more judiciously or that the query processor's parallelism iterators are actually more efficient? Any specifics would be interesting..

We still see plenty of problems with parallelism on SQL 2005, though our exposure to SQL 2008 is relatively limited.

With SQL2000, I routinely see reporting queries in a test suite I used to use go for 3 hours only to complete in seconds when MAXDOP is set to 1. I don't see the same behavior with the test suite on SQL2005. I also don't hear as many complaints about parallel plans gone wild in the SQL2005 prod environment.

Excellent post Aaron...

Many times I encountered parallalism issues in SQL 7 and 2000...

I wonder whether this is because the user base has learned to switch DOP off by default though. We certainly advise turning it off routinely & still occassionally get caught out when a mis-configuration slips through during an upgrade, so I'm sure it's still a problem.

I wonder whether there's anything published on the technical improvements in SQL 2005's parallelism? Introduction of partitioning in SQL 2005 was potentially something that could have improved parallelism but it's well known that parallelism combined with partitioning actually had a lot of problems that were later fixed in SQL 2008. As for non partitioned parallelism, I'm not sure what technical improvements, if any, were actually made.

Keep in mind that many other improvements were made in SQL 2005, so it might be that these simply masked the problems associated with parallelism. Removing the Std Edn memory restriction in SQL 2005 was a huge advance on its own for example..

I don't want to get into tit for tat comments and I was planning to blog about parallelism anyway; but there is a ms kb which recommends turning parallelism on for oltp databases. http://support.microsoft.com/kb/329204

The spec tests are all very well but hardly relate to real world applications, and as I have experienced tests can be arranged to prove almost anything!

In answer to the 2000 to 2005 Q. I did a lot of testing for several clients on upgrades and I can confirm that 2005 handles parallelism much differently; maybe even far better. I also tend to be working on databases with tables that contain tens and hundreds of millions of rows, maybe that makes a difference?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

Greg

I mostly work with financial systems, I wouldn't say that any of these fall into either the oltp or dss camp. Can't be more specific, sorry.

I have a severly overworked development server that has multiple CPU's that is set to MAXDOP 0. Would switching this to 1 maybe improve thruput for this mostly OLTP server? I do know that a transaction that runs on 8 processors and rolls back will only user 1 processor.

Thanks

Chris

I wrote the above as I have a database recovering for over 2 hours now that says it will take another 6 hours because the process ran on many cores and now is rolling back as a single thread. The joys of poor processing.

Chris

And why does SQL2005 give me a message every 30 seconds telling me how much time is left in the recovery process???

Chris, you'll have to test the effect of the setting on your workload and in your environment.  Any of us could *guess* whether it will help or not, but that won't be very useful to you, I'd suspect.

The output messages are annoying, sure.  But I'd rather get lots of feedback (especially if the estimated completion time changes during the process due to other impeding activity) than sit there wondering, "are we there yet? are we there yet? are we there yet? are we there yet? how about now?"

PingBack from http://www.pythian.com/news/1764/log-buffer-141-a-carnival-of-the-vanities-for-dbas

Paul Randal just highlighted another parallelism problem in his blog.  This one deals with parallel index rebuilds.

http://www.sqlskills.com/BLOGS/PAUL/post/Index-rebuilds-depend-on-stats-which-are-updated-by-index-rebuilds!.aspx

http://blogs.msdn.com/psssql/archive/2009/03/18/be-aware-of-parallel-index-creation-performance-issues.aspx

For the SCOPE_IDENTITY()/@@IDENTITY issue mentioned above, Pinal Dave has posted some workarounds that could be helpful:

http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

It has been only a month and a half since I posted the last version of Who is Active? ( v7.30 ), but

PingBack from http://topsy.com/tb/sqlblog.com/blogs/aaron_bertrand/archive/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism.aspx