Post reply

Database Tuning Advisor not giving recommendations for large query

  • May 15, 2012 at 1:18 pm

    #256516

    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!

  • May 15, 2012 at 2:02 pm

    #1488002

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

  • May 15, 2012 at 2:32 pm

    #1488024

    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.

  • May 15, 2012 at 2:36 pm

    #1488029

    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

  • May 15, 2012 at 2:53 pm

    #1488043

    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.

  • May 15, 2012 at 5:36 pm

    #1488095

    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.

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

  • May 16, 2012 at 6:01 am

    #1488317

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 16, 2012 at 9:49 am

    #1488508

    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!

  • May 16, 2012 at 12:37 pm

    #1488619

    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.

  • May 17, 2012 at 9:19 am

    #1489104

    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.

  • May 17, 2012 at 9:58 am

    #1489133

    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!

  • May 17, 2012 at 10:05 am

    #1489142

    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.

  • May 17, 2012 at 10:19 am

    #1489155

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

  • May 17, 2012 at 11:42 am

    #1489261

    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.

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

  • May 17, 2012 at 12:13 pm

    #1489289

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

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