March 15, 2010 at 8:25 am
I have always seen a query plan that included parallelism in a transactional database as a bad thing. These queries generally end up taking a huge amount of time to complete and handicap the number of resources that are available to other operations. So, I used finding parallelized plans as an opportunity to optimize the query, normally by simplifying the individual operations, with usually huge (magnitudes) gains in performance (yay me!!). However, it seems that similar gains can be made on these queries simply by adding the MAXDOP 1 query hint, which we have done on several queries. Have other people seen similar results with regards to parallelization? I do a pretty good job of keeping things running smoothly in my table and procedure design. But when these issues come up, they have the feel of a feature which is more of a nuisance. I can certainly see how in an OLAP environment, having several threads banging at a problem is beneficial, but in OLTP it seems that nothing good comes from it. So, here is the crux of this thread. My experience tells me that changing the max degree of parallelism option in my OLTP databases would be a good thing, but I feel like well, maybe Microsoft knows best... . Are there any opinions on this matter?
Tim Januario
March 15, 2010 at 3:40 pm
I guess it depends on what you mean by OLTP. If it's pure OLTP then there probably aren't many (any?) queries that would generate parallel query plans (assuming it is well-designed), but most databases have some degree of reporting use in one form or another. It probably wouldn't hurt you to set maxdop to 1 for the server, but you would certainly want to test first.
You said that you had tuned the parallel queries, which is the right way to address this IMO. I believe that the logic behind parallelism is generally sound - the optimiser may not be able to identify a better plan given the available information. More information in the form of constraints, indexes, stats etc may allow it to find a non-parallel plan on its own merits.
Matt.
March 15, 2010 at 5:07 pm
you may also find benefit in increasing the value of 'cost threshold for parallelism', but like everything else, test it.
I certainly find parallel queries to be resource hogs and they seem to spend as much time on CXPACKET waits as processing.
Another thing I do, certainly on new builds is turn off hyperthreading. Interested on others views on hyperthreading as we are now in the era of quad core processors as standard.
---------------------------------------------------------------------
March 16, 2010 at 6:39 am
Don't get hung on OLTP = no paralellism. I've seen it go both ways. I agree with George (stop banging your head). Most of the time the cost setting is way too low. The default is ridiculously low. Setting that higher usually helps. But don't throw out the baby with the bathwater. Depending on the query, parallelism can make for much faster execution. There is a reason we have it. It's frequently down to an individual basis whether or not it helps or hurts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2010 at 6:47 am
feel better now, head banging stopped.
(wasn't because of this thread BTW)
---------------------------------------------------------------------
March 16, 2010 at 8:21 am
But the issue as I see it isn't necessarily that parallelism is happening, but that when it does, it actually performs worse. Most of the time that I see parallelism, simply setting "option (maxdop 1)" seems to affect performance beneficially. I do believe that the optimizing is the way to go. I find that unless you are doing an aggregate of all of the data in a table or across many tables, a parallel plan is an indicator that something isn't quite what you expect and a little guidance to the engine or a change in the query design can affect huge performance gains. In fact, this is one of the the things that worries me about setting maxdop to 1 at the server level, is that I won't be able to find the possibly worst performing queries using that criteria. However, with that said, if I have an urgent need to improve server performance and I know that many of my queries are parallelizing and under-performing, making this change is an attractive solution. But why does it perform so badly? I've seen this in multiple database, industries, environments and it seems to always be the case (except when processing OLAP data). I've seen query times go from 3 minutes to 30 seconds just by telling it not to parallelize the query. Understanding that this is still probably not the best performance (again in a transactional database- 30 seconds), but the point being, that simply turning off the "feature" would give my server better performance. Is the query analyzer not yet mature enough to make decisions that affect performance by, at times, magnitudes? Has nobody else seen this and wondered what if I just turned it off?
It also seems to me that a single process in a multi-user environment should not be given the ability to affect performance so heavily on a server, which was my point regarding the distinction between OLAP/OLTP. It probably should have been [OLAP/Reporting]/OLTP because both of those services have a certain amount of expected processing time. Often, a transactional database is active 24/7 and a job coming along that is supposed to do maintenance on a table and taking up several processors to do it doesn't seem like a good idea. Since as far as I can tell, there are more transactional databases than OLAP/reporting, shouldn't the default be to have a high threshold? Or at least make it something in Best Practices.
Tim Januario
March 16, 2010 at 8:32 am
It's all based on the estimated costs of the query as determined by the optimizer. If that estimate is higher than the cost threshold for parallelism, then you have the chance of getting a parallel query. That's why we've suggested setting the threshold higher. The default is way too low. You do this instead of setting MAXDOP=1 because you might still have queries that will actually benefit from parallelism.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2010 at 9:54 am
Is there a sound way of determining what the threshold should be? I have no idea what it would mean to the planner, for example, to double the default value.
Tim Januario
March 16, 2010 at 10:29 am
I'm not aware of a precise method for calculating your number, but if I wanted to make an educated guess, I'd query the procedures that are currently in cache to see what the average cost estimate is, especially for any queries that I knew were running in parallel and then set the number a little above that.
As a starting point, I'd suggest going quite a bit higher than merely doubling. The default is 5. I'd bump it up to 25 and see what that does for, or to, performance and work from there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2010 at 10:49 am
I think I'll suggest we go the cost threshold route rather than just killing the entire thing. I appreciate everyone's input. Thanks Grant especially for yours.
Tim Januario
March 16, 2010 at 10:56 am
At the risk of complicating things again if you have hyperthreading turned on it is worth trying turning it off. If you do please post back the results.
Experimenting with the actual value of maxdop also worthwhile (values other than 0 or 1), say half the no of physical cores
---------------------------------------------------------------------
March 16, 2010 at 11:01 am
george sibbald (3/16/2010)
At the risk of complicating things again if you have hyperthreading turned on it is worth trying turning it off. If you do please post back the results.Experimenting with the actual value of maxdop also worthwhile (values other than 0 or 1), say half the no of physical cores
Yeah, absolutely great advice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2010 at 4:38 pm
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply