Sysindexes
-
I have a row in sysindexes with indid=5 and id=258868039. I also have a row in sysobjects with id=258868039 which corresponds to a table. When I fetch the index name from sysindexes and the tablename from sysobjects and run
drop index tablename.indexname
I am informed that SQL Server
Cannot drop the index 'tablename.indexname', because it does not exist in the system catalog.
Why?
-
For some reason SQL Server is not able to identify the index.
Run sp_help <tablename> and see if it shows this index name or not.
if not, run DBCC CheckDB (<dbname>
and see if it reports any issues. -
Is it not an auto-created statistics then? Does the name start with '_WA_Sys_...'?
-
Yes, the name is _WA_Sys_PERSON_ID_1A69E950. Do you know what that is?
-
It's a statistic. When you do a select with a where condition with no index, sql server will sometimes (if now always), create or read statistics to speed up the query. The syntaxe you are looking for to drop 'em (which I wouldn't recommend) is
DROP Statistics TableName.statsName
note that views also have statistics which you could drop (but still not recommended).
-
OK, thanks a lot everyone...
I guess I will drop it and create the corresponding index on column PERSON_ID.
-
ids, foreign keys should ALWAYS be indexed.
You have a clustered index on every table, right???
-
Sure, a clustered index on every table, and other indexes as well...
(except that it appears I was missing one... ).What I am planning to do is to drop all indexes in the db and then recreate them, such that I am sure that I have the indexes I want (with the names that I want them to have). Don't worry about the performance of this operation, it is a small db
-
They always start small
. -
I think it is automagically dropped when you create a 'real' index that SQL Server can use instead of it.
-
Sounds very reasonable...
However, I have decided to drop all statistics along with the indexes and then recreate the indexes - and hopefully I won't see statistics any more... -
Don't do that... stats HELP the server, they are not a bad thing in any way. You might suffer a performance drop for a few minutes while they are recreated (and they will be).
-
Yep, what you want to do instead is look for autocreated stats and probably create an index for those columns, since SQL Server is basically saying to you that it needs it. This is not always right, but often. And like I said, when the index is created the stats are removed automatically. In fact I wouldn't be surprised if the new index 'takes' the indid from the autostats.
-
Well, my plan was to add a few indexes that should replace the statistics
Anyway, the table is very small (max 100 rows), so don't worry -
I thaught that you meant for the whole db/server
.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply