Drop in performance after archiving database

Question

Drop in performance after archiving database

deleios

SSC Journeyman

Points: 95
More actions
March 16, 2017 at 11:24 am

#341615

Hello,
after having made an archive on a database (pruge outdated rows in tables), I have a query that dropped significantly in performance (from instance to 20 mins).
I've tried reindexing, updating statistics with no result and I'm short on ideas 🙁
I'm trying them on the same machine so it's not a configuration issue. (I have before/after archiving backups)
See execution plans, I hope someone will have a clue, because I'm banging my head on walls.
(the file "court" is the one that goes fast, the "long" is the one that is long)
Thank you for your insight.

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

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 1

I have had this problem before, as you can see in the plan it is processing way more records than needed. In my case i had to redo my index after purging the data. If you can send the 2 actual plans we can provide some more recommendations.

RandyOM SSC Veteran Points: 211 More actions · Answer 2

RandyOM

SSC Veteran

Points: 211

March 16, 2017 at 12:03 pm

#1933599

Drop the existing execution plan

Alex Chamchourine SSC Eights! Points: 912 More actions · Answer 3

You can try to arrange your tables in specific order (to mimic the successful plan, that is the one where TABLE8 gets processed first) and enforce the order using "option(force order)" with your query. It seems that after you purged your tables optimizer does not want to scan TABLE8 anymore and that is the problem as it repeats the index search 1,5M times retrieving just a handful of records.

deleios SSC Journeyman Points: 95 More actions · Answer 4

curious_sqldba - Thursday, March 16, 2017 11:59 AM
I have had this problem before, as you can see in the plan it is processing way more records than needed. In my case i had to redo my index after purging the data. If you can send the 2 actual plans we can provide some more recommendations.

As said in my first post, I tried this (redid the index + statistics on all tables) but it didn't help.

I think I sent you the actual plans (I'm not an expert so I might be wrong but...)

RandyOM - Thursday, March 16, 2017 12:03 PM
Drop the existing execution plan

How?

Alex Chamchourine - Thursday, March 16, 2017 12:37 PM
You can try to arrange your tables in specific order (to mimic the successful plan, that is the one where TABLE8 gets processed first) and enforce the order using "option(force order)" with your query. It seems that after you purged your tables optimizer does not want to scan TABLE8 anymore and that is the problem as it repeats the index search 1,5M times retrieving just a handful of records.

I will give this a try.

Thanks for your answers everyone 🙂

RandyOM SSC Veteran Points: 211 More actions · Answer 5

http://serverfault.com/questions/91285/how-do-i-remove-a-specific-bad-plan-from-the-sql-server-query-cache

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

deleios - Thursday, March 16, 2017 3:19 PM
curious_sqldba - Thursday, March 16, 2017 11:59 AM
I have had this problem before, as you can see in the plan it is processing way more records than needed. In my case i had to redo my index after purging the data. If you can send the 2 actual plans we can provide some more recommendations.
As said in my first post, I tried this (redid the index + statistics on all tables) but it didn't help.
I think I sent you the actual plans (I'm not an expert so I might be wrong but...)

We would need sqlplan files instead of simple images. A lot of information is lost when sharing just images.
Check the following article to get better help: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

deleios SSC Journeyman Points: 95 More actions · Answer 7

Oh sorry I misunderstood... It was kind of late for me and my English failed me...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

At a guess, you've got an optimiser timeout. You can verify this by looking at the property sheet of the result operator, the SELECT.
SQL Server hasn't had enough time to guarantee that the chosen plan is a good one. Sometimes it's awful.
If this is the case, then you don't necessarily have to change much to get around it. A query hint might be sufficient. Using the fast plan as a reference, tables 9 and 11 are inner joined by a nested loops operator. So, in your query FROM list, you want to see either this:

FROM Table 9
INNER loop JOIN Table 11
[join criteria here to join T11 to T9]

or this:

INNER JOIN Table 9
ON [whatever it is already]
INNER loop JOIN Table 11
ON [join criteria here to join T11 to T9]

The second option is likely to be easiest to implement. Good luck because this won't work if the query is too complex.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

deleios SSC Journeyman Points: 95 More actions · Answer 9

Okay so I think I found what was wrong.

My query uses two variables (start/end date) and after archiving we had some data that's been put by the customer for 2100 (go figure why) that was representing 35/40 % of data of a table instead of 5% before.
After deleting those and rebuilding indexes/recalculating statistics on this table the execution plan didn't change (I'm wondering why), but using the option OPTIMIZE for the query does the trick. (specifying a week time frame for my variables).
So while it's not a long term solution, at least is solves the issue for now.

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 10

Looking at the PNGs previously posted, I'd do the Divide'n'Conquer thing of quickly isolating the "root" rows to be looked up in a temp table and then join to the temp table so that the optimizer doesn't bless you with further "favors".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)