Parallel Worlds or slipped reality?
I’ve been planning a post on the subject of parallelism for some considerable time but the inability to be able to present reproducible tests for illustration had been a major concern. Things change however, I picked up on an interesting post http://sqlblog.com/blogs/aaron_bertrand/archive/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism.aspx
I hope you’ll grant me a quick rant here < grin >, I spend much of my time involved in the performance tuning of databases and something I frequently encounter are questionable settings applied to servers and databases based upon a blog post or forum post. The internet is a wonderful source of information but it is also a wonderful source of misinformation and misguided best intentions. It was my view that there was a danger this post could be4 taken as a reason to disable parallelism but it was within the run of comments that surprised me.
Over the last few years I have been involved with extensive testing of migrations of sql 2000 to sql 2005 / 2008 for several clients, this involved parallelism as these boxes ranged from 8 cores / processors to 32 cores, In fact I’ve always used multiple cpu servers since the Pentium Pro. I had commented that my testing had seen performance degradation of up to 600% where parallelism had been disabled, this had produced a criticism that I had not defined if this was an OLTP or DSS system.
OK, so the obvious reaction is “What the *!!!* ! “ What on earth does a classroom definition of databases have to do with anything, performance degradation is performance degradation. There was also mention of the TPC benchmarks, again another classroom exercise that has little to do with real world. I give an example, here in the UK every car has to have fuel consumption figures published, my wife was disappointed to discover her new car could only manage 18 miles to the gallon despite the handbook suggesting at least 26. My own car at best only manages 60% of the manufacturer’s suggestion. Why is this? Because the tests do not reflect real world driving, yes they give give a way to compare car to car, just like the TPC benchmarks allow us to compare a server configuration – but real world – not a chance. ( And yes I have read many of the full disclosure documents for TPC benchmarks, I also read storage equivalents. )
So what type of applications have I worked with, well without being too specific Accounting, CRM, ERP, Treasury Systems, Loan Originations, online loan applications, price comparison, call centre applications, loan broking, Billing systems and a few Data Warehouse and OLAP solutions. Do I find the same “ issues “ with them all – basically yes ( I’m not insinuating all my clients’ systems were problematic btw. ) I can’t say I’ve ever thought “Oh OLTP – can’t do parallelism here”
As you might guess I hate sweeping generalisations and comments like “ I always xxxx on a yyy system “ just indicates a closed mind and tunnel vision to me. I like to think I approach every situation with an open mind to all things SQL Server, I’ve encountered enough interesting aspects of tuning to fill a book, sadly of course you can’t take client databases and data to make examples so the book will never get written.
Now to the serious side of parallelism settings, it’s a server wide setting so it affects every database on a server when you make a change, so altering this can have wide ranging effects. Although I’ve never disabled parallelism and then used maxdop = n hints to enable it for specific queries, I understand that the optimiser doesn’t actually take a lot of notice and will split the query across all available cores, so on a 16 core box it will split a query with a maxdop = 2 across 16, not what you’d actually want. As I say I’ve never tried this and currently i don’t have any 16 core or greater boxes to use for such a test.
One of the points about Servers is that Moore’s Law no longer applies, my 6 year old lap top has a 2.8 GHz cpu, actually faster than many of the multi core servers I use today. True the modern cpu has more cache, bus speed is faster, so is memory, but bottom line we are not getting faster core cpu speed – the future is parallel processing and I understand Microsoft are indeed working on the improvements to in memory parallel processing of queries within SQL Server.
I tend to work with reasonable size databases, I guess that it’s no surprise databases are getting bigger, I’m used to tables with tens and hundreds of millions of rows and I expect to reach billion(s) very soon. Partitioning or no partitioning, even a partitioned billion row table is likely to have hundreds of millions of rows per partition, you need parallel plans to query these types of tables if you want decent performance.
And while I’m having a rant – multiple concurrent connections – yeah – I’ve been there too on moderate thousand user systems, ever actually monitored concurrent running processes on your SQL Server? Well I have – down to 1 second granularity – it can be truly amazing how few queries actually run at the same time. Hyperthreading – how many DBA’s actually know what the problem is/was with HT – I can’t say that in interviews I’ve found many that do ( I’ll not count thread starvation which is a real issue with multiple cores and parallelism or how many workers sql server allocates and how much memory they require on an x64 system, and we have 6 core cpus , now that’s a concern )
CXPacket waits – well any parallel query will tend to show CXPacket as the last wait, but it’s understanding if you have blocking threads, again not many DBAs I’ve interviewed even have a brief working knowledge of waits, not that I claim to have all the answers there!
So what I’m really saying is that I’m worried this post will be picked up as a reason to disable parallelism, because many readers won’t fully understand and some of the comments are quite seriously worrying. A very interesting discussion none the less. Probably earned my Grumpy status today!