An Orders of Magnitude Problem (T-SQL Tuning)

  • Jerry_Manioci

    SSC Journeyman

    Points: 78

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

  • okbangas

    SSChampion

    Points: 11773

    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: 995641

    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
    "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: 12284

    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

    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: 5244

    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: 3463

    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: 104200

    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: 41969

    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: 8346

    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: 5388

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

  • Jeff Moden

    SSC Guru

    Points: 995641

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 995641

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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

    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: 10963

    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]

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

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