SQL Server Performance

DTA creates statistics

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by vitaly, Dec 6, 2006.

  1. vitaly New Member

    I tried to feed Database Tuning Advisor with Profiler trace. It started to run and then failed. But, as a result, the tables involved in the trace got TONS of strange-looking statistics with names like


    When I tried to use DROP STATISTICS on them, I got the message

    Msg 3739, Level 11, State 1, Line 1
    Cannot DROP the index 'TableName._dta_index_TableName_c_7_944774473__K13_K2_K6_5201' because it is not a statistics collection.

    I noticed that some of these statistics got updated when I run sp_updatestats in the database. I think that these unwanted statistics may create additional overhead, so I would rather like to get rid of them.
    Does anybody know what are they and how to remove them?
    Any help/hint would be greatly appreciated.
    Thank you.

  2. MohammedU New Member

    Did you try using drop index command?

    Mohammed U.
  3. Luis Martin Moderator

    Try with Drop Index.

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  4. vitaly New Member

    Thanks a lot!!! It works!
    So, I am just speculating, these are probably the "preparatory" structures DTA creates for the indexes it plans to suggest. If DTA would not fail, and I agree to implement these indexes, it would convert them to real ones, otherwise it should delete them. Am I right?
    sp_helpindex Tablename shows these indexes with "nonclustered, hypothetical located on PRIMARY" in index_description.
    Did you ever experience DTA failing to process Profiler trace? What could be the reason?

    Anyway - Thanks a lot!!!


Share This Page