SQL Server Performance

Trace for Database Tuning Advisor

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Anoop, Jul 15, 2008.

  1. Anoop New Member

    Hi All,
    I am using TUNING Template to generate a trace file for DTA and planning to add a filter to remove rows with Duration = 0 or (<1). I have lots of rows with 0 duration. Will it have any effect on DTA recommendations?
    Thanks in advance.
  2. Luis Martin Moderator

    I don't recommend to use DTA with trace file.
    Is better to open trace file, find long queries, copy and paste into Management Studio and call DTA from there.
  3. Anoop New Member

    Thanks Luis, My trace file includes INSERT, UPDATE and DELETE queries. If I run DTA against long queries only, the recommendations may not be accurate.
    Please correct me if I am wrong.
  4. Luis Martin Moderator

    Insert, Update and delete and select is what I call long queries.
    The idea is: DTA with a trace give you a big picture about indexes. May be you will find recommendations for some big select and, if you apply those recommendations, will impact in insert and so on.
    That is why I prefer to work with single queries and think if any index suggestion will impact in insert, etc.
  5. Anoop New Member

    Thank Luis.
    I will try that.
    BTW, i ran DTA last night against the trace file and it only recommends drop indexes. Is this normal? Maybe my tables are over-indexed.
  6. Luis Martin Moderator

    No, I don't think so.
    You have to set "keep indexes" in DTA options.

Share This Page