[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Query Tuning an odd execution plan

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?
0
mike1142
Asked:
mike1142
  • 10
  • 4
  • 2
  • +2
3 Solutions
 
mike1142Author Commented:
Oh and when it executes I get constant CXPACKET waits.
0
 
PortletPaulCommented:
Can you attach the .sqlplan file so we can inspect too?
0
 
PortletPaulCommented:
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/
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mike1142Author Commented:
Would the anonymized versions from SQL Sentry explorer do? It won't let me upload them unless I use a different extension.
0
 
mike1142Author Commented:
Yes I have come across Ola's scripts. Very nice. I have them installed.
0
 
PortletPaulCommented:
.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
0
 
mike1142Author 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
0
 
mike1142Author Commented:
Seems like maybe this is not so uncommon where the predicates do not get "pushed down" the plan tree?
0
 
mike1142Author 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.
0
 
PortletPaulCommented:
well I must thank you for providing the execution plans, but I'm not really much wiser as a result.

That expansion of the data to 307Mb is starkly visible with SQL Sentry Plan Explorer... and you describe it well. The adjust query clearer does it better (whatever that hint was). I take it you don't want to rely on the hint and that you are looking for an alternative to that.

Right now all I can think of is that I had a somewhat similar experience in Oracle once, which I solved* by using a CTE. Maybe taking that subset of the overall query into a CTE to force it down a better path would work here?

--
*I wasn't allowed anything except a query, it's a long story, but this was a reason for using a CTE
0
 
mike1142Author 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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
 
BitsqueezerCommented:
Hi,

if the hint works, why not use that? It's not forbidden to use hints...:-)

The execution plan shows a table scan in both versions, that let me guess that the used table is not indexed correctly or the index usage was killed through bad WHERE methods in the query.

UNION queries are known to kill index usage, if you really need that it could be of help to try to create a view for all the UNIONs together and create an index on the view. That of course has a lot of prerequisites like schemabinding and other such stuff. But the resulting view would be properly indexed and anything which works with this view surely would perform faster.

Then it seems that the resulting command is DELETE and not SELECT. DELETE really has a problem if the used tables have any reference constraints to other tables, the more the bader. I had a similar issue with DELETE on my previous project where a table had a reference to another and this constraint killed the performance of deleting something in the desired table. In this case I decided to remove the reference and to create my own delete procedure which makes sure the referential integrity is OK, that worked without problems but is of course only a workaround. In my current database project (which is much bigger) I prohibit any DELETE command, if anything should get deleted I use a deletion field in the record so the records will only get invisible which has the advantage that the deletion can be undone.

But to say anything more specific to your problem you would need to show the complete SQL construct which is performing 7 hours.

Cheers,

Christian
0
 
Vadim RappCommented:
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.
0
 
mike1142Author 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
0
 
Vadim RappCommented:
Why sql server has chosen this or that plan, can be explained only by sql server team, and it also depends on the release of sql server itself. In one my case, I had a query that took 5 seconds in sql server 2000, but 5 minutes on 2008. So on 2008 it needed a hint in order to perform in the same fast way as on 2000.

Maybe you could try to update statistics on everything involved and execute with recompile.
0
 
mike1142Author 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
0
 
mike1142Author 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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now