Post reply

Database Tuning Advisor

  • May 27, 2013 at 12:26 am

    #273123

    Hi all,

    Recently i have been assigned a task to optimize the performance of the database.

    I decide to run profiler and done the same and captured the SP's and give the trace as work load for DTA .

    DTA analysed the trace and come up with so many recommendations.

    So what we have to do now. Is there any option that automatically applies all the recommendations from DTA. Is it trustworthy to implement all the suggestions from DTA.

  • May 27, 2013 at 1:33 am

    #1618695

    No. You should not be applying all the recommendations given by the DTA without more analysis.

    The recommendations may be good but you should decide based on a lot of other factors if the index is really mandatory.

    I have given the links to articles by Gail Shaw which might help you

    https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • May 27, 2013 at 3:05 am

    #1618717

    m.rajesh.uk (5/27/2013)

    Is there any option that automatically applies all the recommendations from DTA.

    Yes.

    Is it trustworthy to implement all the suggestions from DTA.

    No. You need to see which of the suggestions help and which don't.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 27, 2013 at 5:14 am

    #1618753

    1. How to apply all these recommendations all at a time. How to apply them.

    2. How to verify which of them useful and which of them not useful. Can any one provide some material on this.

  • May 27, 2013 at 5:54 am

    #1618763

    m.rajesh.uk (5/27/2013)

    1. How to apply all these recommendations all at a time. How to apply them.

    There's an option in DTA, accept all recommendations

    2. How to verify which of them useful and which of them not useful. Can any one provide some material on this.

    Test queries, take a benchmark. Implement one of the recommendations. Test queries again, compare with earlier benchmark.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 27, 2013 at 5:56 am

    #1618764

    Thanks Gail

Viewing 6 posts - 1 through 5 (of 5 total)

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