rebuild index worsens query performance

Question

rebuild index worsens query performance

Viewing 15 posts - 31 through 45 (of 56 total)

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

jgenovese (11/20/2012)
All:
This was literally a "switch-flip" -- the query run time doubled exactly right after I made the changes noted in my original post
(note: on that day I rebuilt indexes but NOT stats -- update stats came 2 weeks later and made no difference either way)
No other operations took place on either database that day
We need to track down what changed such that the query run time doubled, before we start making SQL changes
Did I do something wrong that day?

No. You did fine. As a sidebar, rebuilding (not reorging) indexes will rebuild the stats anyway.

Depending on the queries, of course, it is possible that a previous query plan could survive through something like this. It may be that you just have a different query plan in cache than what's needed to support the rebuilt indexes. I don't know your system or what the load is on it but it might be worth trying to run the query with a "recompile" built into it or, as a last resort, clearing proc cache altogether. Note that clearing proc cache will cause every query to recompile during first usage so things could slow down quite a bit. I've not been able to check on your actual execution plans so read on before you try any of that.

I've also run into situations where a new query plan is worse than the old one. For example, I found one query that shifted from doing a single table scan on several hundred thousand rows to what looked like a nice index seek. Looking at the properties of the index seek, it turned out that the index seek was being executed thousands of times and that's actually quite a bit slower than a single table scan.

In stark contrast to the above, I've also seen where the optimizer has decided that, because of the reduction in data (you did remove about 50% of the data IIRC), the optimizer suddenly decides that it would be more cost efficient to do scans instead of seeks, sometimes much to the detriment of performance.

This all falls under the umbrella frequently described as "**it happens' 😀 and, at this point, I'd recommend not wasting much more time on trying to figure out precisely what happened and begin making tuning efforts on the troublesome query.

Cosidering all of the OR inequalites in the query, the non-sargable criteria, and the "all-in-one" nature of the query, I'd say that using a bit of "Divide'n'Conquer" technology would not only go a long way in improving the performance of the query but would also make it much more "bullet proof" for such future actions as reducting data and reindexing.

--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)

jgenovese Right there with Babe Points: 791 More actions · Answer 2

thanks Jeff

note that the table I deleted from is not referenced in the query

jgenovese Right there with Babe Points: 791 More actions · Answer 3

DDL attached, we are not able to provide sample data, per my boss's instructions:

"Everything but sample data. Column definition should allow them to generate what they need"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

jgenovese (11/21/2012)
DDL attached, we are not able to provide sample data, per my boss's instructions:
"Everything but sample data. Column definition should allow them to generate what they need"

Just so you know, we don't know your problem domain so for us making up data for testing doesn't really work. Sample data is something you can fabricate and then build the expected results.

We DON'T want live data.

jgenovese Right there with Babe Points: 791 More actions · Answer 5

I dont have time to fabricate sample data

I will see what my boss says about this and get back to you after Thanksgiving break

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

jgenovese (11/21/2012)
I dont have time to fabricate sample data
I will see what my boss says about this and get back to you after Thanksgiving break

On this one, I agree.... way too many tables involved. That does get back to my suggestion, though. You could probably turn this sproc into a real screamer if you did the Divide'n'Conquer thing.

--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)

jgenovese Right there with Babe Points: 791 More actions · Answer 7

anyone?

(re:)

On a related note, I am trying to pass as a query hint the XML from what I believe to be the plan that the query was using before purge

I can see the plan just fine as a sqplan, but when I try to retrive the XML, the "StatementText" gets truncated. If I try to paste into the hint the missing part of the statement from the original query, I get this at run time" "XML parsing: line 104, character 17, well formed check: no '<' in attribute value"

I suspect there is some limit to the length of "StatementText", on both the retrieval and the use in a query option -- is there a way to circumvent that limit? Or, can the complete XML live apart from the query option (for ex. a text file) and still be referenced by the query option?

jgenovese Right there with Babe Points: 791 More actions · Answer 8

what would be the best way to get you sample data? (backup file, etc.)

jgenovese Right there with Babe Points: 791 More actions · Answer 9

no "divide-and-conquer", etc. until we figure out what happened that weekend

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

jgenovese (11/26/2012)
no "divide-and-conquer", etc. until we figure out what happened that weekend

Unless you can resurrect a Before'n'After of the actual Execution Plan, that could be a very long wait. In the meantime, you're stuck with an underperforming sproc. It's your database and you should, of course, do what you think is right for it but I'd make a copy of that proc and start fixing the code because it's a problem now and the code wasn't durable enough to withstandeven a reindexing.

--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)

jgenovese Right there with Babe Points: 791 More actions · Answer 11

jgenovese

Right there with Babe

Points: 791

November 26, 2012 at 12:39 pm

#1562378

look at my post from 4:34:43 PM

Johan Bijnens SSC Guru Points: 135266 More actions · Answer 12

Because estimates and actuals are that much dissimilar, maybe have a look at Daves article "When estimation goes wrong".

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/11/26/when-row-estimation-goes-wrong.aspx

I think trying out his workaround is worth the test.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 13

jgenovese (11/26/2012)
look at my post from 4:34:43 PM

The times change according to each of our local times. So there is no such entry according to what I can see. Use the Post # instead.

--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)

jgenovese Right there with Babe Points: 791 More actions · Answer 14

this is the post:

On a related note, I am trying to pass as a query hint the XML from what I believe to be the plan that the query was using before purge

I can see the plan just fine as a sqplan, but when I try to retrive the XML, the "StatementText" gets truncated. If I try to paste into the hint the missing part of the statement from the original query, I get this at run time" "XML parsing: line 104, character 17, well formed check: no '<' in attribute value"

I suspect there is some limit to the length of "StatementText", on both the retrieval and the use in a query option -- is there a way to circumvent that limit? Or, can the complete XML live apart from the query option (for ex. a text file) and still be referenced by the query option?

jgenovese Right there with Babe Points: 791 More actions · Answer 15

this is the post:

On a related note, I am trying to pass as a query hint the XML from what I believe to be the plan that the query was using before purge

I can see the plan just fine as a sqplan, but when I try to retrive the XML, the "StatementText" gets truncated. If I try to paste into the hint the missing part of the statement from the original query, I get this at run time" "XML parsing: line 104, character 17, well formed check: no '<' in attribute value"

I suspect there is some limit to the length of "StatementText", on both the retrieval and the use in a query option -- is there a way to circumvent that limit? Or, can the complete XML live apart from the query option (for ex. a text file) and still be referenced by the query option?