Statistics on a table but no index to match

Question

Statistics on a table but no index to match

ImYourDBA

SSCertifiable

Points: 5654
More actions
October 31, 2013 at 1:38 pm

#300608

So, I inherited a server with 2012 on it that seems to have some tables with _dta_index_ stats (evidently someone used the Tuning Adviser) on it but the indexes themselves were deleted. Now when I try to go run a DROP STATISTICS command, it errors saying the index doesn't exist.
Anyone know if this is a bug or if I'm doing something wrong here?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are they stats (show up in sys.stats) or are they indexes (show up in sys.indexes)? What's the exact statement you're running and the exact error you're getting?

btw, if you drop an index, the stats associated with that index are dropped along with it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 2

Yes, they are actual stats...they show up as named in sys.stats. I thought it was weird for them to be there without a corresponding index too.

Table:

ABCTable

Indexes:

IX_ABCTable

IX_ABCTable_1

Statistics:

_dta_index_ABCTable_7_745806881__K2

_dta_index_ABCTable_7_745806881__K2_5

_dta_index_ABCTable_7_745806881__K2_K1

_dta_index_ABCTable_7_745806881__K5

_dta_index_ABCTable_7_745806881__K5_K2

_dta_index_ABCTable_c_7_745806881__K1_K2

_dta_index_ABCTable_c_7_745806881__K10

_dta_index_ABCTable_c_7_745806881__K2

_dta_stat_745806881_2_1

_dta_stat_745806881_5_2

_WA_Sys_00000003_2C741C21

_WA_Sys_EmailID_2C741C21

_WA_Sys_PK_2C741C21

_WA_Sys_TransactionID_2C741C21

IX_ABCTable

IX_ABCTable_1

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Nothing at all unusual about column stats (stats that aren't associated with indexes). The naming's a little odd though.

What do each of these return?

DROP STATISTICS ABCTable._dta_index_ABCTable_c_7_745806881__K2

DROP STATISTICS ABCTable._dta_stat_745806881_2_1

DROP INDEX _dta_index_ABCTable_c_7_745806881__K2 ON ABCTable

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 4

I get the following errors:

Msg 3701, Level 11, State 7, Line 1

Cannot drop the statistics 'ABCTable._dta_index_ABCTable_c_7_745806881__K2', because it does not exist or you do not have permission.

Msg 3739, Level 11, State 1, Line 2

Cannot DROP the index 'ABCTable._dta_stat_745806881_2_1' because it is not a statistics collection.

Msg 3701, Level 11, State 7, Line 3

Cannot drop the index 'ABCTable._dta_index_ABCTable_c_7_745806881__K2', because it does not exist or you do not have permission.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Run a CheckDB please?

DBCC CheckDB (<database name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 6

It just came back with:

Command(s) completed successfully.

No error messages.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

happycat59 One Orange Chip Points: 29386 More actions · Answer 7

What schema are the tables in ? Try including the schema name with the drop index/stats statements

Sean Pearce SSCoach Points: 15999 More actions · Answer 8

It's possible they are hypothetical indexes, a remnant of database tuning advisor.

What does the following return?

SELECT

name,

is_hypothetical

FROM

sys.indexes

WHERE

OBJECT_NAME(object_id) = 'ABCTable'

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 9

Yes, all of the _dta_ listings are showing as hypothetical.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Sean Pearce SSCoach Points: 15999 More actions · Answer 10

SQLJocky (11/1/2013)
Yes, all of the _dta_ listings are showing as hypothetical.

You can safely remove those.

SELECT

'DROP INDEX ' + QUOTENAME(name) + ' ON ' + QUOTENAME(OBJECT_NAME(object_id))

FROM

sys.indexes

WHERE

is_hypothetical = 1;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 11

Ahhh, okay I see. Thank you all for your help!

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/