Query won't finish! Amzaingly large query plan

Question

Query won't finish! Amzaingly large query plan

aaa121

SSCertifiable

Points: 5902
More actions
September 16, 2011 at 2:18 am

#248095

Ok so a merge query which compared 14m records from staging on a daily basis used to run in 40 mins.
We change the way the data is loaded into the staging table although the data is the same.
All of a sudden the query won't complete after 4 hours. The query plan took 3.5 mins to display and is full of table spools, sorts and index inserts (I can't post the plan due to restrictions here).
I have re-created the indexes on staging and recompiled the stored procedure which carries out the merge. ANy ideas??

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Update all the statistics on all the tables involved.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aaa121 SSCertifiable Points: 5902 More actions · Answer 2

Hi sorry, forgot to mention I had already carried out a global update stats on the staging database.

And there was one thing which did change - this was the addition of a composite primary key on the staging table. Do you think this would have such a large impact?

I am now updating stats on the target database, and recompiling the stored proc.

I am also dropping the primary key before attemping another run.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

Is the merge performing a huge number of inserts after that change ? The clustered index could be slowing down the insert. or if the new composite primary key is a clustered index which is not in an order format you would get the same issue.

Jayanth Kurup[/url]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Make sure your stats updates are with fullscan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aaa121 SSCertifiable Points: 5902 More actions · Answer 5

Ok well there shouldn't be any more inserts than normal, the data has simply moved on a day.

I have already kicked off the query and it is running well from what I can see, processor is up around 14%, Database I/O is spiking up to about 50% then down and the query is running with several threads which are all alternating between running/suspended.

Unfortuntely I don't know what it was looking like before as I have only just got involved. But it looks healthy from what I can see.

I didn't include the fullscan though!

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

aaa-322853 (9/16/2011)
Hi sorry, forgot to mention I had already carried out a global update stats on the staging database.
And there was one thing which did change - this was the addition of a composite primary key on the staging table. Do you think this would have such a large impact?
I am now updating stats on the target database, and recompiling the stored proc.
I am also dropping the primary key before attemping another run.

First of all let me say that without seeing the actual execution plan you can only get general advice in the best case, wild guesses in the worst.

However, since you say that the query plan is huge, I suppose you have lots of tables and indexes involved in this query.

Adding a new primary key (clustered, I suppose) changes the rules, or, at least, introduces a new object that the optimizer has to take into consideration while producing the query plan. I have seen many cases where the elevated number of objects in the statement multiplies the effects of inaccurate cardinality estimations, even if the estimation was only slightly off.

Tools such as SQLSentry plan explorer can highlight the operators in a query plan that contain a significant difference between estimated and actual rows. It could be a starting point.

I would suggest you to rollback your changes (in a development/test environment) and save the original query plan to compare it to the new plan and discover what exactly in your changes led the optimizer to produce that monster.

Regarding how to fix it... it would be a whole book alone! Filtered/multicolumn statistics could help, new indexes or indexed views could be other possible fixes. Who knows without seeing the plan?

-- Gianluca Sartori

aaa121 SSCertifiable Points: 5902 More actions · Answer 7

Sure there are several indexes on the target table and they have always been there.

My point is that now everything is structurally the same, the data is the same, the merge statement is the same and the environment is the same.

In that case why isn't the query time the same as it has been for the past few weeks?

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

aaa-322853 (9/16/2011)
Sure there are several indexes on the target table and they have always been there.
My point is that now everything is structurally the same, the data is the same, the merge statement is the same and the environment is the same.
In that case why isn't the query time the same as it has been for the past few weeks?

Sorry, didn't you add a index?

In this case, it's not structurally the same.

-- Gianluca Sartori

aaa121 SSCertifiable Points: 5902 More actions · Answer 9

Yes, I did. But I have removed it, updated the stats, recompiled the sp and have executed again.

Going for 70 mins so far when it usually takes 40

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

Well, things don't change without a reason, check that everything is really back as it was before.

However I would not focus on that ...

Now your plan sucks and it used to be good in the same circumnstances: who cares?

Now it sucks: find the root cause and fix it!

-- Gianluca Sartori