DTA Indexes and Statistics

Question

DTA Indexes and Statistics

burfos

Ten Centuries

Points: 1141
More actions
November 19, 2013 at 9:24 pm

#279261

I am reviewing a large database that used to be maintained by a previous DBA and I have run a check over the database that lists out any exact duplicate indexes. It appears that in the past, the DBA has run the Database Tuning Advisor over the database and created a raft of indexes which has made it very messy and made numerous duplicate indexes as a lot of them listed are named _dta_index%
what I find strange though is a lot of these duplicate indexes that appear in the results, when I go to look at them in object explorer within SSMS, the dat index appears to be listed under the Statistics folder. This confuses me. Is this an index or a statistic? If I right click on the index/statistic that is listed in the statistics folder, I notice script statistic as is greyed out as is the delete option. If I were to remove this object I have to run drop index rather than drop statistic. Everything points to this thing being an index but why is it listed under statistics in SSMS? This issue only occurs for the dta indexes however some of the dta indexes are listed in the indexes folder and some are listed in the statistics folder.
Also note that this database may have been brought over from SQL 2005 and is now on SQL 2008 R2 at running at compatibility level 100. I am guessing these dta indexes may have been created when the database was on sql 2005.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

SQL Show SSCertifiable Points: 6143 More actions · Answer 1

when index being accessed first time, their statsitics with same name wiill be created automatically and listed under "Statistics".

http://blog.idera.com/sql-server/understanding-sql-server-statistics/

burfos Ten Centuries Points: 1141 More actions · Answer 2

Ok so as I understand it, it is both an index and a statistic as they both exist in sys.indexes and sys.stats. My question now changes to, how come that particular index is not listed under the indexes folder in ssms?

burfos Ten Centuries Points: 1141 More actions · Answer 3

Ok it seems that these "hidden indexes" actually have is_hypothetical =1 from the dta run that was doen before my time. I need to look in to this more as they come up in my as duplicate index query.