February 18, 2016 at 6:50 am
I have the weird problem where a query joining to a table in TempDB is coming up with a horrible plan & taking 60 secs to run, the row estimates are WAY out & a Hash Match is taking forever.
If I set MAXDOP to 4 I get a totally different plan & the query takes less than a second.
However, this isn't a great solution for me as I can't change the vendor code so I have to set the whole server to MAXDOP 4 (server has 32 cores)
We tried recreating statistics, sp_recompile on the tables & even DBCC FREEPROCCACHE but none of these things helped.
Can anyone help me to understand what is happening here & how I might find a better solution to the problem?
(plan attached)
Thanks!
February 18, 2016 at 1:44 pm
Thanks for posting a nice execution plan to sink my teeth into! 😉
Just a few loose thoughts, in random order.
* I see estimates that are off, but not way off. The differences are not big enough that they would worry me.
* You say that the MAXDOP 4 plan looks different. The .sqlplan you posted is for MAXDOP 32, and the .png you posted looks the same. Are both for the MAXDOP-32?
-- (also, I would understand a serial plan looking different but a lower DOP changing the plan would surprise me).
* In the plan you posted, I see that there is a lot of skew. Look at the fat arrow running from Merge Join into Hash Match - right-click it, open the properties, then click the arrow in front of "Actual Number of Rows" to see the breakdown per thread. Thread 5 is doing over 70% of the work, while all other threads sit idle. To understand how this happens, you have to understand that SQL Server needs to make sure that rows that join together sit on the same thread. So each branch of the plan that reads from one of the three tables has a parallelism (distribute or repartition stream) operator that assigns rows to the thread based on a hash function that is applied to the join column. Apparently, in you current data distribution the hash function that returns a value from 1-32 returns 5 for over 70% of the data. You already see this in the top branch (Table2), but the final join hugely increases the problem. I will get back to this. Unfortunately, we cannot control what hashing algorithm SQL Server uses, except in this case by changing the DOP (because then a hash function has to be used with a different number of possible results). My guess is that the actual execution plan with MAXDOP 4 will have a much more even distribution of rows among the four threads. I think you understand that a plan where four threads each do 25% of the work is faster than a plan where 1 node does over 70% while the other 31 threads do almost nothing.
* My biggest concern with this query is unrelated to the poarallelism at all, and will almost certainly show up in every plan for the query regardless of DOP - and that is that the left-most join appears to do something quite close to a full cartesian product between two already big streams. Given that the very next step is to aggregate all that back to a much smaller set, I am 95% sure that there has to be a better way to achieve the same result. However, I really need to know more about the tables used and the query text to have any chance of helping you with that - and since you didn't post any of that and even actively masked the statement in the plan, I cannot.
-- This almost many-to-many join is also where the problem for thread 5 really spirals out of control. A full cross join of the actual rows on thread 5 for the two inputs would result in 587 million rows, the actual number is only 5.5 million below that. So every row on the upper input of thread 5 joins with almost 90% of the rows in the lower input.
* Also, both merge joins have the many-to-many property set to true. This makes them slower, makes them write a lot of stuff to tempdb, and may make them read a lot of that stuff back in again. For the right-most join between table2 and table1, looking at the actual rowcounts this might not even be needed - are you perhaps joining on a column that is unique but not constained to be unique so that SQL Server doesn't know it is? Or have you wrapped a join column in an expression so that SQL Server cannot figure out the uniqueness anymore? The other join that does the almost-cartesian product really is joining many-to-many - see above,
You say you cannot change the vendor code. I understand that. But perhaps, if you can explain that this query is giving you huge headaches *and* you can present them a rewrite that works better, they will implement it? (There are actually some vendors out there who do appreciate that kind of feedback - allthough I am all too aware that a lot of vendors are complete *** (expletive deleted) *** about fixing their *** (expletive deleted) *** code).
Finally, as a more short term solution - even when you cannot change vendor code, you can still attach hints to it. The instrument for this is called a "plan guide". It is quite an advanced and little known feature. I will not give you a full rundown of how to do this because if you are going to use this, you need to understand exactly what you are doing. So read up on it, experiment, and then try if it works for you.
(Of course, if you are trying to do this and run into a specific problem, post what you have done and where you are stuck and we will be happy to help).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply