Database Tuning Advisor not giving recommendations for large query

  • Elizabeth.Block

    Hall of Fame

    Points: 3516

    I have a major production server whose performance has suddenly tanked. There's one very large query that runs very frequently that has changed from running in 5 seconds to running in 30 seconds. I tried to analyze it in DTA but it gives me no recommendations. Does anyone have any suggestions? I'm pressed for time and I'm stumped on why DTA didn't find anything.

    Thanks!

  • Lynn Pettis

    SSC Guru

    Points: 442332

    Maybe nothing to find. The problem could be stale statistics on one or more of the tables used by the query.

  • Elizabeth.Block

    Hall of Fame

    Points: 3516

    Thanks, that sounds possible. I have added some new indexes so maybe there's nothing to find. I just found it disconcerting to see a monster query that the DTA finds nothing to change.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Elizabeth.Block (5/15/2012)


    Thanks, that sounds possible. I have added some new indexes so maybe there's nothing to find. I just found it disconcerting to see a monster query that the DTA finds nothing to change.

    DTA is, at best, a wonderful moron.

    I agree with Lynn, if you're suddenly getting different performance there's 3 usual internal causes to a database, and then you need to explore external bottlenecks, like Disk I/O.

    1) Statistics, as mentioned.

    2) Fragmentation

    3) Parameter Sniffing

    For completeness

    4)You've passed a 'tipping point' in the data. Unlikely as you didn't describe a large data change.

    If you'd like, you can always post the execution plan and schema/query def and we'll take a gander at it.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Elizabeth.Block

    Hall of Fame

    Points: 3516

    All good ideas. I'll have to look into those. It may be a combination of a tipping point in the data (the largest table has 81 million rows), a tempdb configuration problem, and possibly bad statistics. The statistics should be fine because we run update statistics on all the tables every night, but I really don't know. We've been meaning to redo the tempdb configuration because it was set up poorly. Unfortunately, tempdb has one data file and 8 log files, which is totally backwards. I may be reconfiguring that tonight, because there have been a lot of large disk queue lengths on that drive. It's a puzzler.

  • Jeff Moden

    SSC Guru

    Points: 996622

    Elizabeth.Block (5/15/2012)


    Thanks, that sounds possible. I have added some new indexes so maybe there's nothing to find. I just found it disconcerting to see a monster query that the DTA finds nothing to change.

    Along with all the other things folks have suggested, it may be that the query simply isn't written well enough for DTA to figure out anything to do with it. For example, if the WHERE clauses aren't SARGable to begin with, there's no way that DTA is going to be able to recommend an index that could be built to speed things up.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Grant Fritchey

    SSC Guru

    Points: 396551

    The DTA works best, when it works at all, on entire loads. It's not terribly good at tuning individual queries. But if you have six hours worth of capture metrics that it can play back, it may come up with useful suggestions, including your problematic query. But the most likely outcome is that it just can't help for the reasons that others have listed. For good old fashioned tuning, check out my book.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Elizabeth.Block

    Hall of Fame

    Points: 3516

    Thanks, Grant, for your info. I didn't know that DTA wasn't good at a single query. I'll have to check out your books. I don't really do much query tuning because I'm mostly a production DBA, so this was a stumper. When the dust settles I'll have to do more research so I'm ready for the next time.

    So the good news - we fixed the performance problem! I rebuilt 3 of the largest tables and now life is good again. The weird thing is that we have a smart reindexing job that rebuilds if the indexes need it and reorgs if they're not too bad. So I thought the indexes were fine. The indexes weren't fragmented but they weren't good either. This is one I'll never forget.

    Thanks all for your help!

  • curious_sqldba

    SSC-Dedicated

    Points: 36303

    Elizabeth.Block (5/16/2012)


    Thanks, Grant, for your info. I didn't know that DTA wasn't good at a single query. I'll have to check out your books. I don't really do much query tuning because I'm mostly a production DBA, so this was a stumper. When the dust settles I'll have to do more research so I'm ready for the next time.

    So the good news - we fixed the performance problem! I rebuilt 3 of the largest tables and now life is good again. The weird thing is that we have a smart reindexing job that rebuilds if the indexes need it and reorgs if they're not too bad. So I thought the indexes were fine. The indexes weren't fragmented but they weren't good either. This is one I'll never forget.

    Thanks all for your help!

    My two cents, i have seen many times when DTA doesn't detect a missing index. Try to look at the execution plan and see if you can come up with your own index for the piece which is taking most cost. If DTA doesn't come up with anything doesn't mean that there is a missing index. If you can post the actual execution plan, someone here can come up with some more recommendations.

  • maroon-78

    Ten Centuries

    Points: 1014

    check writes to tempdb the tipping point might be a working table set that has to be flushed to tempdb because of memory constraints. This is a real performance killer and a great example of a tipping point with a large drop off in performance.

  • Elizabeth.Block

    Hall of Fame

    Points: 3516

    I think the performance issue was a combination of things. I think somehow one of the large indexes became corrupt, maybe there was new growth on one of the big tables, and tempdb started having performance issues. After the dust settles I have to reconfigure tempdb because it was set up incorrectly. Right now tempdb has 1 data file and 8 log files, which is crazy. I'm planning on creating a job that rebuilds all the indexes once a week to mitigate the original performance problem. Then I'll reconfigure tempdb.

    I think the biggest lesson I learned from all this is that indexes can become corrupt without being fragmented and this will cause huge performance problems.

    Thank you all for your great ideas! I have much research to do now on performance tuning. Very cool stuff!

  • Scott D. Jacobson

    SSCertifiable

    Points: 6039

    sqldba_newbie (5/16/2012)

    My two cents, i have seen many times when DTA doesn't detect a missing index. Try to look at the execution plan and see if you can come up with your own index for the piece which is taking most cost. If DTA doesn't come up with anything doesn't mean that there is a missing index. If you can post the actual execution plan, someone here can come up with some more recommendations.

    This. I was going to say the same thing but I made sure to check the whole thread first. If you've already narrowed it down to that specific query and you're sure it's that, I would start here before going to DTA. Run the query with the Actual Execution Plan, then check the plan when the result set is returned. It's very good at suggesting indexes.

    That being said, you should also take these suggestions cautiously. Sure adding the suggested index(es) will improve performance in this query. It's possible though the suggestion could be detrimental to something else. You should exercise caution, test before applying to production, etc. etc.

    Also, all the other suggestions regarding Disk I/O, fragmentation, file structure were good ones. This was a fun thread to read through.

  • Elizabeth.Block

    Hall of Fame

    Points: 3516

    Scott, I agree that you have to proceed cautiously. I started a simple spreadsheet noting what things I tried, indexes, tools, etc. so I wouldn't forget what I had done and I could back out any changes if performance got worse. I have the interesting problem of having too many tools - SQL Diagnostic Manager, SQL Doctor, and all the SQL Server tools. So I got a lot of ideas for indexes but didn't put many in because I wanted to make sure I didn't make performance worse. I definitely learned some good things about performance tuning but I also learned how much I don't know...

  • Jeff Moden

    SSC Guru

    Points: 996622

    Elizabeth.Block (5/16/2012)


    The indexes weren't fragmented but they weren't good either.

    Can you be more specific about that please? For example, how do you know they weren't good if they weren't fragmented?

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Elizabeth.Block

    Hall of Fame

    Points: 3516

    Jeff Moden (5/17/2012)


    Elizabeth.Block (5/16/2012)


    The indexes weren't fragmented but they weren't good either.

    Can you be more specific about that please? For example, how do you know they weren't good if they weren't fragmented?

    I know they weren't fragmented because I checked the tools say they're not fragmented. I found the slowest query that they used very frequently. We timed the query on test and it ran in 2 seconds; in production it ran in 24 seconds. We restored production to test and then the query on test ran in 24 seconds. After rebuilding the indexes for the 3 largest tables for that query, voila, the query ran in 2 seconds again. I had never seen this before so it was a new one to me.

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

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