We help IT Professionals succeed at work.

Query Tuning an odd execution plan

210 Views
Last Modified: 2014-08-22
I have done everything I can think of to get this query to perform well and it is just not getting there.

The oddness of the execution plan is that (early?) in the plan tree the query as written (not by me) does a merge join which drastically increases the number of rows. It carries these millions of rows all the way up the tree until right before the select there is a hash match join and the rows are filtered to about 50K. I have not been looking at a lot of execution plans but I have seen a few and this merge join is very stark in Sentry Plan Explorer where the number of rows suddenly increases.

So as is this query runs for hours (up to 7 so far) I did manage to nab an actual execution plan which shows pretty much the same thing only there is a lot of complaining about how much estimated and actual varied. I thought maybe statistics. Ran a query the stats were pretty well out of date. There were some that have never had the stats updated and some were it had been several years and then some which were recent which looks like because of my recent defragmentation efforts. Still no change.

Now, if I hint one of the joins (looks like I may have been rather random about it)  as an INNER LOOP JOIN. The query runs in under a minute. The row count is the same and I even have the data from the original in case I need to compare it.

Finally, I have it down to the particular where clause when things go south. It's the last one that would complete the columns in all of the clustered indexes (did I mention there are 6 joined tables?).

I am baffled, I have a solution that is not a solution and I a not sure where to go.

Thoughts? Suggestions?
Comment
Watch Question

Author

Commented:
Oh and when it executes I get constant CXPACKET waits.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
Can you attach the .sqlplan file so we can inspect too?
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
this sounds like a problem on its own

Ran a query the stats were pretty well out of date. There were some that have never had the stats updated and some were it had been several years and then some which were recent which looks like because of my recent defragmentation efforts.
There are some really good solutions for this maintenance e.g. http://ola.hallengren.com/

Author

Commented:
Would the anonymized versions from SQL Sentry explorer do? It won't let me upload them unless I use a different extension.

Author

Commented:
Yes I have come across Ola's scripts. Very nice. I have them installed.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
.sqlplan is an allowed extension at EE (or should be, it has been in the past). If not use a .xml extension perhaps.

but any version is better than none

Author

Commented:
I see. I thought it to only be a proprietary format after the anonymizing process. It will save as several other file types as well.

AD1 is the original actual (7 hours) and AD2 is with the query hint (45 seconds)
AD1.sqlplan
AD2.sqlplan

Author

Commented:
Seems like maybe this is not so uncommon where the predicates do not get "pushed down" the plan tree?

Author

Commented:
Oh and you probably should know that this is part of a several of these types of selects in a union all that is inserted into a table. The last union is the one with the issue.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
The more I read and hear about this thing the more I think this has something to do with predicate "push down".  Joe Sack at SQLskills.com describes the problem I am seeing pretty closely. Though he does not have an example execution plan or any potential solutions.
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Would help if we can see your T-SQL statements.
How often the records in those tables are changed (INSERT, DELETE and UPDATE operations)? And of often do you reindex them?
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
As Bitsqueezer said, if you have already found what needs to be done in order to have the query run as expected, then what is the problem you want help with?

If you are dissatisfied by the fact that sql server did not do it on its own, then create an account on Microsoft Connect, see if there's active SQL Server program, and submit bug report (ideally, verify if it's the same in the newest release). I did that many times in the past, also on issues similar to yours, and had them confirmed by their team, with the promise to visit that in the future. It's not unusual, and this is why the hints are there for you to use.

Author

Commented:
I am sorry to all. There was an 8 day lag and in the meantime I did follow the advice of using the query hint.

The problem is that the query is "certified" from a vendor and changes needed to be avoided if possible. We also know how using query hints can lead to performance problems down the road and without clear documentation that it is there and why can make these problems difficult to find.

Thanks
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Over all this is/was a really poorly maintained database and I have done what I could within the scope of my work to optimize it and added jobs to keep it that way. The server was also under-provisioned and configured incorrectly on the vm host. In addition I am finding that the vendors stored procedures need improvement. Over all this was a strain on my skill set and I love to have this forum to seek help and get competent advice.

I am going to try and divvy up the points as best as I can. If you all have any objections please let me know.

Thanks

Author

Commented:
portletpaul has always been johnny on the spot to lend a hand. I appreciate his efforts and appreciate that he is respectful and honest.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.