SQL Server Performance Forum – Threads Archive

DTA creates statistics

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

Did you try using drop index command?
Mohammed U.

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.

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

]]>