SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Orders of Magnitude Problem (T-SQL Tuning)


An Orders of Magnitude Problem (T-SQL Tuning)

Author
Message
Jerry_Manioci
Jerry_Manioci
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 169
Comments posted to this topic are about the item An Orders of Magnitude Problem (T-SQL Tuning)
okbangas
okbangas
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3445 Visits: 1387
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)SSC Guru (269K reputation)

Group: General Forum Members
Points: 269128 Visits: 42192
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4587 Visits: 2967
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
Jerry_Manioci
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 169
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 Hamilton
Tom Hamilton
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3560 Visits: 794
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
DavidL
SSC Eights!
SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)SSC Eights! (906 reputation)

Group: General Forum Members
Points: 906 Visits: 764
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
george sibbald
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31414 Visits: 13702
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
Chris Harshman
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13224 Visits: 4896
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
pdanes
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2234 Visits: 1354
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search