Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Statistics on a table but no index to match Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 1:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 159, Visits: 638
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?
Post #1510401
Posted Thursday, October 31, 2013 2:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
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 2008, MVP
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

Post #1510424
Posted Thursday, October 31, 2013 2:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 159, Visits: 638
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
Post #1510428
Posted Thursday, October 31, 2013 3:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
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 2008, MVP
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

Post #1510431
Posted Thursday, October 31, 2013 3:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 159, Visits: 638
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.
Post #1510434
Posted Thursday, October 31, 2013 3:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
Run a CheckDB please?
DBCC CheckDB (<database name>) WITH NO_INFOMSGS, ALL_ERRORMSGS




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1510438
Posted Thursday, October 31, 2013 4:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 159, Visits: 638
It just came back with:
Command(s) completed successfully.

No error messages.
Post #1510447
Posted Thursday, October 31, 2013 9:23 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:37 PM
Points: 3,005, Visits: 2,606
What schema are the tables in ? Try including the schema name with the drop index/stats statements


Post #1510488
Posted Friday, November 1, 2013 1:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 916, Visits: 2,881
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
Post #1510524
Posted Friday, November 1, 2013 7:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 159, Visits: 638
Yes, all of the _dta_ listings are showing as hypothetical.
Post #1510573
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse