Indexes beginning with _WA_Sys_

Indexes beginning with _WA_Sys_

Post by Colin Charnle » Wed, 03 Apr 2002 22:41:59



Hi,

I have a few tables, on which I create and drop indexes quite regularly.
I've noticed that every so often indexes appear which I have not created.
They are named like :-

    _WA_Sys_FileID_55FFB06A
    _WA_Sys_PhoneID_590D414E

I can't drop them, so I'm wondering what they are? and how I can, if
possible, avoid them?

Thanks for any help!
Colin

 
 
 

Indexes beginning with _WA_Sys_

Post by Tibor Karasz » Wed, 03 Apr 2002 22:54:36


Colin

This is statistics that the optimizer collects as it feels necessary. Don't worry about it, as
it is good for you (and they don't consume storage). If you really want to drop them, check out
DROP STATISTICS.

--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.


> Hi,

> I have a few tables, on which I create and drop indexes quite regularly.
> I've noticed that every so often indexes appear which I have not created.
> They are named like :-

>     _WA_Sys_FileID_55FFB06A
>     _WA_Sys_PhoneID_590D414E

> I can't drop them, so I'm wondering what they are? and how I can, if
> possible, avoid them?

> Thanks for any help!
> Colin


 
 
 

Indexes beginning with _WA_Sys_

Post by ch » Thu, 04 Apr 2002 02:28:33



> Colin

> This is statistics that the optimizer collects as it feels necessary. Don't worry about it, as
> it is good for you (and they don't consume storage).

each stats collection takes up at least a row sysindexes so they do consume storage, although it is
more than likely minimal.

Quote:> > I have a few tables, on which I create and drop indexes quite regularly.
> > I've noticed that every so often indexes appear which I have not created.
> > They are named like :-

> >     _WA_Sys_FileID_55FFB06A
> >     _WA_Sys_PhoneID_590D414E

> > I can't drop them, so I'm wondering what they are? and how I can, if
> > possible, avoid them?

to avoid them, turn off  the auto create stats option.  to get rid of them use drop statistics like
tibor said.  the ss2k query analyzer has a manage statistics function where you can easily delete
them.
to put them to good use, see which autostats you have and determine whether or not the columns
referenced in the stats collection would be better with an index on them instead of simply a stats
collection.

since you apparently have auto create stats on for this db, you probably want auto update stats on
as well.

charlie

 
 
 

1. Wa_sys indexes still there after adding real indexes

I use SQL server 7. I've created some new indexes and see that the
Wa_sys indexes still exist even though I've created 'real' indexes on
the columns.  From what I've read,these Wa_sys indexes are really
statistics created by sql server to help with queries when a real
index isn't available. I know I can get rid of them by dropping
statistics, but have read to be wary of dropping them.  Is it ok (or
rather should I) drop the Wa_sys index if I've now got a real index on
the column?

2. Automating SQL 7 backup

3. '_WA_Sys_' indexes in SQL 7.0

4. datagrid columsn

5. _WA_sys_ indexes?

6. UNICODE support?

7. system generated indexes (_WA_Sys_.....)

8. PB to Ingres thru Openlink

9. BEGIN TRANSACTION vs. BEGIN

10. begin/end begin tran/commit in while

11. VFP 6.0 Index open problem with Begin Transaction

12. _WA_sys_.. in sysindexes table

13. _WA_Sys_