August 23, 2004 at 9:38 am
When I run sp_helpindex I get 7 indexes on a particular table. When I select from sysindexes it shows 43 indexes for the same table. All except the 7 user indexes, have names that start with "_WA_SYS...".
Where did these indexes come from?
If the system determined that it needs these indexes, then can I delete them and re-add them with a name that follows our naming convention?
August 24, 2004 at 6:46 am
_WA_Sys_ are for statistics which apparently need to be stored in sysindexes, but aren't actually indexes. The Internet claims there are books online to explain it although I haven't looked. Also found the following and thought you may find it interesting:
SELECT name AS IndexName
FROM sysindexes
WHERE
indid BETWEEN 1 AND 254 AND
INDEXPROPERTY(id, name, 'IsStatistics') = 1
August 24, 2004 at 6:49 am
Those are system generated indexes (or statistics.. maybe someone else could clarify this). As far as I understand they are generated when a where condition or order by is stated in a query and that the optimizer doesn't find any usefull index to satisfy the where condition. Sql server then creates that temporary index for the query and uses it.
As for renaming/recreating all those indexes I wouldn't advise it unless you have a slow running query that could benefit from a new index. I'm sure you can find plenty of article about best indexes pratices on this site if you need more help in choosing your indexes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply