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

    [_dta_index_TableName_c_7_944774473__K13_K2_K6_5201]

    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.


    -vitaly
  2. MohammedU New Member

    Did you try using drop index command?


    Mohammed U.
  3. Luis Martin Moderator

    Try with Drop Index.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

    -vitaly

Share This Page