Table with no index has too many indexes

Question

Table with no index has too many indexes

magarity

Ten Centuries

Points: 1037
More actions
November 22, 2005 at 12:08 pm

#111520

A rather odd problem cropped up this morning. We thought to add an index to a table that has none and SQL Server complains it is unable to add more than 249 indexes or statistics on any one table. Since there aren't any indexes, where are these stats being collected and how the heck do I turn them off? Why would only one table have this problem when other tables are able to add and remove indexes without complaint?
Collect stats automatically is turned off as a globlal option and this table does not have that overridden.
Any ideas greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

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

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

One thing you may want to look at is if there are any hypothetical indexes. These are created when the database tuning wizard runs and can sometimes end up being left behind. These are named hind_% and can be found by executing the following:

SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%'

magarity Ten Centuries Points: 1037 More actions · Answer 2

I ran that and it didn't return any results.

Thanks for the suggestion. Any other ideas?

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 3

Adam Wilbur

SSCertifiable

Points: 7001

November 22, 2005 at 2:10 pm

#605325

Could you post the DDL?

A.J.
DBA with an attitude

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

I have not heard of hyperthetical indexes, however you may have system stats - search sysindexes

select o.name,i.name from sysindexes i join sysobjects o on o.id=i.id where i.name like '_WA%'

and o.name= 'mytablename'

I figure you have 248 stats - you may delete these stats and add your index.

Before I get shouted at for suggesting deleting system stats , these are created if you have auto create stats set to on. Any required stats will be recreated - which may incur an overhead.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

magarity Ten Centuries Points: 1037 More actions · Answer 5

It's too large a table to post here; it's a stage to hold a source system dump that's over 350 columns. But I did have the thing script the create with any indexes and constraints and the resulting script didn't show anything but a bunch of column names and types. No constraints or indexes.

steve block SSCertifiable Points: 7698 More actions · Answer 6

Enterprise Manager has a nice way of seeing all the indexes on a table. Choose the database that the table is in and change the View to Taskpad. Then click on the tab for Table Info. Scroll down to the table desired (clicking Next if needed). All the indexes for a table are displayed including the system stat ones (start with _WA_Sys).

Steve

noeld SSC Guru Points: 96590 More actions · Answer 7

to get rid of the statistics have a look at DROP STATISTICS on BOL

Cheers,

* Noel

magarity Ten Centuries Points: 1037 More actions · Answer 8

Yes, it was these system statistics that were causing the problem. I looked at the table with 2005's management studio and they are all listed neatly in their own category of a table's information; very handy. So thanks for pointing me in that direction.