SQL University: Parallelism Week - Introduction

re: SQL University: Parallelism Week - Introduction

Jimmy May, Aspiring Geek — Mon, 24 May 2010 12:27:56 GMT

This community could benefit from some thoughtful prose on parallelism. Great intro. Look forward to the rest of the series. The graphic is a nice touch, BTW.

Jimmy

re: SQL University: Parallelism Week - Introduction

Erin Stellato — Mon, 24 May 2010 13:43:11 GMT

Great article, especially the Background section. One of the most clear explanations I have read about "how it works". Thanks!

Erin

re: SQL University: Parallelism Week - Introduction

Adam Machanic — Mon, 24 May 2010 14:07:44 GMT

Thank you, Jimmy and Erin, for the kind words!

re: SQL University: Parallelism Week - Introduction

Mike A — Mon, 24 May 2010 14:31:21 GMT

Hey Adam,

Great write up~ I have a couple of questions about applying this knowlege to my environment and I wish I could find more information on this in the form of best practices.

How should DBAs manage their parellelism settings in an environment where they have multiple instances of SQL Server running on the same physical server? As far as I know each SQL Server instance is blind to the schedulers of the other instances so my assumption is that turning on parallelism would cause a big increase in context switches and CX related waits.

Is there any good documentation on how to set the cost threshold of parallelism? The cost threshold of parallelism is like a microsoft mystery knob to me. All I've read about it is that when the optimizer is looking to generate a plan if I turn that knob up the optimizer will prefer non parallel plans over parallel ones. I haven't come across documentation to tell me if I should be turning it up by 1's by 10's or by 100's or anywhere to say look at this statistic and turn it up until that goes below a threshold. All we know about the cost threshold for parallelism option is that it can be set to any value from 0 through 32767. The default value is 5. and cryptically the cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. That last part sounds like the estimated cost of execution when looking at an execution plan but the documentation doesn't explicitly state that. How would someone set the CTOP to an optimal value?

Thanks for your contributions to the SQL Community Adam! Keep up the great work!

-Mike

re: SQL University: Parallelism Week - Introduction

Adam Machanic — Mon, 24 May 2010 14:35:47 GMT

Hi Mike,

Great questions! But answering them would require a big chunk of a blog post and... There is one on those very topics (and related issues) scheduled for Friday. So hold those thoughts, and let me know if Friday's post doesn't get you where you need to be.

Adam Machanic : SQL University: Parallelism Week - Introduction

Adam Machanic : SQL University: Parallelism Week - Introduction — Mon, 24 May 2010 19:29:48 GMT

PingBack from http://sqlblog.com/blogs/adam_machanic/archive/2010/05/24/sql-university-parallelism-week-introduction.aspx

re: SQL University: Parallelism Week - Introduction

Robert L Davis — Mon, 24 May 2010 19:36:12 GMT

Seems like you should have posted all 3 parts at the same time?

When we had Conor Cunningham talking to us in our MCM class, he stated that the optimizer always creates a parallel plan and a non-parallel plan. But then the subject of cost threshold never came up, so perhaps he was giving us a simplified description.

re: SQL University: Parallelism Week - Introduction

Adam Machanic — Mon, 24 May 2010 19:44:40 GMT

Hi Robert,

SQL University topics are supposed to span a week. Had I posted all three at once, it would have only spanned a single day.

What I understood from Conor is not that the optimizer always creates both plans but rather that it -considers- both plans. Which makes a lot of sense, and from what I can see in my server's plan cache it's correct. But I'll ping Conor for the decisive answer.

re: SQL University: Parallelism Week - Introduction

RichB — Wed, 26 May 2010 07:03:59 GMT

Really good article, succinct and informative, thanks.

I have found that on my OLTP systems, the best way is just to set maxdop to 1. Even with a high threshold it often - 2008 more than 2000 - seems to choose parallelism when the equivalent non parallel query is both dramatically faster, and 10's or 100's times less intensive on the CPU.

Might not be the most efficient at all times, but it sure is safest on a high throuput OLTP system!

Rich

re: SQL University: Parallelism Week - Introduction

Adam Machanic — Wed, 26 May 2010 13:46:02 GMT

Rich: I tend to agree with you that for most OLTP workloads a MAXDOP of 1 is appropriate. But we'll get into that just a bit more in Friday's post :-)

re: SQL University: Parallelism Week - Introduction

AdamP — Wed, 26 May 2010 16:07:29 GMT

Great article, made a complex subject make sense to someone with little experience in the topic.

A quick note on the parallelism of the brain though... The human brain has tons of parallelism. When you look at an image different parts of the brain will identify movement, color and contrast, then combine them into one image. Different parts of the brain run different 'processes' independently all the time. Your analogy makes sense in that we don't consciously assign different parts of a math problem to different parts of our brain, but when doing a math problem, another part of our brain is keeping our heart pumping and another part is managing your digestive system and another part of your conscious brain can be writing the problem down all while not interfering significantly with the conscious math problem.

The parallelism of the brain and our ability to make us run hundreds or even thousands of 'processes' simultaneously is part of what allows us to excel over a computer in certain tasks, such as image recognition, and picking out the important part of an image instantly; while a computer may struggle to analyze the meaning and individual parts of an image.

Again, I know you were just using an analogy and it makes sense in how we don't consciously divide tasks, but I just thought this would be interesting information for a few of your readers.

re: SQL University: Parallelism Week - Introduction

Adam Machanic — Wed, 26 May 2010 16:56:58 GMT

AdamP: A very good point indeed. I guess we can extend the analogy by aligning with SQL Server system vs user processes. We could say that keeping the heart beating, etc, is much like a system process, and that our brains can do lots of these in parallel. But something like a math problem is more of a user process, and although our brains can perhaps turn on a form of intra-query parallelism to help solve the math problem, we can't really multi-task and do many math problems concurrently. At least, most of us can't. I've met a few people along the way who break the mold. Proving once again that nature is a lot more powerful than technology in many ways.

SQL University: Parallelism Week - Part 2, Query Processing

Adam Machanic — Wed, 26 May 2010 20:31:08 GMT

Welcome back for the second part of Parallelism Week here at SQL University . Get your pencils ready,

re: SQL University: Parallelism Week - Introduction

Mark Broadbent — Fri, 28 May 2010 07:29:52 GMT

Great article, concise descriptive and a good entry point even for those that haven't had an exposure to parallelism.

SQL University: Parallelism Week - Part 3, Settings and Options

Adam Machanic — Fri, 28 May 2010 13:33:52 GMT

Congratulations! You've made it back for the the third and final installment of Parallelism Week here

re: SQL University: Parallelism Week - Introduction

David — Sun, 27 Jun 2010 02:00:37 GMT

Great article. Indeed, we are only beginning to understand the parallel processing capabilities of the human brain. However, at this time, it is generally acknowledged that uniprocessing is a limitation peculiar to the male brain.

How Queries Are Processed (A Month of Activity Monitoring, Part 13 of 30)

Adam Machanic — Wed, 13 Apr 2011 14:44:22 GMT

This post is part 13 of a 30-part series about the Who is Active stored procedure. A new post will run