An Orders of Magnitude Problem (T-SQL Tuning)

Question

Post reply

An Orders of Magnitude Problem (T-SQL Tuning)

Jerry_Manioci

SSC Journeyman

Points: 78
More actions
March 12, 2013 at 9:52 pm

#290799

Comments posted to this topic are about the item An Orders of Magnitude Problem (T-SQL Tuning)

Viewing 15 posts - 1 through 14 (of 14 total)

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

okbangas SSChampion Points: 11773 More actions · Answer 1

I've experienced somewhat the same as described, and I have two books I would recommend on this subject:

SQL Server Execution Plans

Inside the SQL Server Query Optimizer

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 2

What a great article! It shows the incredible thought process and the tenacity a true professional has in the world of SQL Server. Nice job and congrats on your success!

As a bit of sidebar, was this an all-in-one query? If it was, I've had a great deal of success using the "Divide'n'Conquer" method of dividing the problem up using a Temp Table here and there to hold smaller interim results and then join to those. It's a way of doing a manual and guaranteed "force order". The advantage there is for future troubleshooting because you can execute and measure one section of the code at a time for tuning purposes. It's not often that a simple hint such as FORCE ORDER will actually solve the problem either initially or permanently simply because "things change".

That, notwithstanding in this case, very nice article. It should be required reading for anyone who says, "Well, of course it's slow. There's a lot of data." and then walks away from the problem (which I've seen happen way too many times).

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

sknox SSChampion Points: 12388 More actions · Answer 3

Great article!

This is definitely one of those instances where the journey is more valuable than the destination (for me, at least).

The methodical testing of the issue and possible solutions led to a counter-intuitive, situation-specific solution. So while the end result [OPTION (FORCE ORDER)] is not a good takeaway, more knowledge and understanding of the value of a systemic query tuning process (the journey) is something I definitely benefitted from.

Thanks!

Jerry_Manioci SSC Journeyman Points: 78 More actions · Answer 4

Jeff,

Divide/conquer is great advice and I expect it would have worked in this situation as well. I absolutely agree FORCE ORDER typically won't be the right approach for solving performance problems. This was an odd one which I believe hit a bug in the particular version of the optimizer...it got confused by too many joins.

Tom_Sacramento SSCertifiable Points: 6304 More actions · Answer 5

Hi Jerry,

I appreciate this article not just for the technical elements but for the deductive reasoning that identifies the problem from the noise. This is a very good guide on the why and where and I agree with the other poster - this should be required reading. Jeff's divide and conquer is a familiar and very useful method also. Jeff has and will be a great resource in things SQL.

I've been consulting for 30 years and have walked into any number of problem scenarios, including the one you describe. This is a more common problem than I originally thought. I appreciate your approach and solution, and congratulate you for solving the problem - I know the customer must have been very happy and that's a very good thing :-D.

Thanks for sharing

Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/

DavidL Hall of Fame Points: 3582 More actions · Answer 6

The only way I could even think of approaching a problem like this is 'divide and conquer', so I am very very appreciative of an exhaustive play by play of how you broke this down. This has gone in my briefcase for further study! Thank you.

george sibbald SSC Guru Points: 104210 More actions · Answer 7

interesting article and good timing.

we hit a problem only yesterday where an ETL process was blowing tempdb (940GB then it ran out of space). This was fixed with a query hint, changing a merge join to a hash join. This was done as this was the option chosen by the optimiser in the dev environment (same SQL version but different spec server).

I would have to wonder what would have happened if we could have put an update stats at a high sampling rate in there somewhere.

---------------------------------------------------------------------

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

Jerry_Manioci (3/12/2013)...and simply added OPTION (FORCE ORDER) at the end of the query. It ran in record time in both test and production...

Yep, the joy of cost based optimizers. I remember in my Oracle days a simmilar query hint ORERED was added in version 9 that became my favorite hint there when the cost based optimizer just couldn't get a clue and pick the right driving table of the query. I was already writing queries joining in what my mind was the natural order to execute it based on my knowledge of the system and data. I remember in the Oracle 8 days there were some third party companies that even required you to use Oracle's rule based optimizer instead of cost based and woudn't support performance issues otherwise.

Even if SQL Server had perfect statistics to work with every time, there'd probably still be some cases it picks the wrong driving table, and we don't have any rule based optimizer to fall back on here.

pdanes SSCrazy Eights Points: 8497 More actions · Answer 9

Can't think of anything to add to this one, just congratulations. Pulling off a stunt like that has got to be a pretty major ego boost.

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 10

Great "war" story (and very educatonal)! Thank you for sharing.

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 11

Jerry_Manioci (3/13/2013)
Jeff,
Divide/conquer is great advice and I expect it would have worked in this situation as well. I absolutely agree FORCE ORDER typically won't be the right approach for solving performance problems. This was an odd one which I believe hit a bug in the particular version of the optimizer...it got confused by too many joins.

Just to be sure.... I wasn't trying to take anything away from you or your article. You even mentioned that such hints might not be the right thing to do in your article. Turned out to be fantastic in this case. That's one of things I hope people take away from your article... not letting preconceived notions deter you from finding a solution.

Again, very well done. Thanks for taking time to write it.

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

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 12

Tom_Sacramento (3/13/2013)
Hi Jerry,
I appreciate this article not just for the technical elements but for the deductive reasoning that identifies the problem from the noise. This is a very good guide on the why and where and I agree with the other poster - this should be required reading. Jeff's divide and conquer is a familiar and very useful method also. Jeff has and will be a great resource in things SQL.
I've been consulting for 30 years and have walked into any number of problem scenarios, including the one you describe. This is a more common problem than I originally thought. I appreciate your approach and solution, and congratulate you for solving the problem - I know the customer must have been very happy and that's a very good thing :-D.
Thanks for sharing

Thanks for the kudo, Tom. :blush:

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

peter-757102 SSCertifiable Points: 6877 More actions · Answer 13

This is a good example of why knowing the database matters!

I see too many work with data intensive systems where these kind of problems pop up that seem oblivious about what is involved to get performance and how the server ticks. Most as a result of simply not knowing enough about it and only seeing the SQL itself as a means of getting data out of some dumb storage.

Your case nicely illustrated what is involved and what understanding and reasoning is required to tackle these and similar, but smaller issues. I had to deal with such things in the past, and learned to try force order on cases where the optimizer isn't up to the task time wise. Query plans don’t always get full optimized and in complex cases there are simply too many options to evaluate and you can get stuck with an awful plan.

A complementary habit I have is to write queries in such an order that 'optimize (force order)' directly gets the best possible route to the result. Doing this means the query is easy to understand too as each join can be read as a separate step in a process and understood on its own (indention helps too here). This means all filters that can be applied to a join are found on that join and not hidden half a page away in the where clause. Just reading the join and knowing the consequences is so much more direct then having to decipher a whole query before you can begin to understand what the intention is :).

Good job and nice article!

quackhandle1975 SSChampion Points: 11055 More actions · Answer 14

Great article, enjoyed reading your predicament and how you solved it.

One important trait I feel of any DBA in a crisis is "keep an open mind", but you knew that already. 😎

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]