Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Statistics on a table but no index to match


Statistics on a table but no index to match

Author
Message
GoldenDBA
GoldenDBA
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 810
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47255 Visits: 44387
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


GoldenDBA
GoldenDBA
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 810
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47255 Visits: 44387
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


GoldenDBA
GoldenDBA
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 810
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47255 Visits: 44387
Run a CheckDB please?

DBCC CheckDB (<database name>Wink 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


GoldenDBA
GoldenDBA
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 810
It just came back with:
Command(s) completed successfully.

No error messages.
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3565 Visits: 3107
What schema are the tables in ? Try including the schema name with the drop index/stats statements



Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
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

@SeanPearceSQL

About Me
GoldenDBA
GoldenDBA
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 810
Yes, all of the _dta_ listings are showing as hypothetical.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search