System _WA_Sys Indexes

System _WA_Sys Indexes

Post by Michael Gardne » Fri, 21 Feb 2003 22:28:25

How do you drop the _WA_Sys indexes sql creates?  I ran
some ad-hoc queries that resulted in a ton of these being
created that i don't need.


System _WA_Sys Indexes

Post by Dean Thompso » Fri, 21 Feb 2003 22:35:49

These are statistics placeholders.  If you absolutely want to get rid of them,
use DROP STATISTICS and turn Auto Create Statistics off.  Otherwise, I would
just leave them there.


On Thu, 20 Feb 2003 12:28:25 -0800, "Michael Gardner"

>How do you drop the _WA_Sys indexes sql creates?  I ran
>some ad-hoc queries that resulted in a ton of these being
>created that i don't need.

Texas SQL Users <>
Please reply only to the newsgroups.
Add your SQL Server or Developer website to our Web Resources directory at
Sign up for our free monthly newsletter at


1. system generated indexes (_WA_Sys_.....)


I have recently noticed a large number of  indices on a client's
tables which were not purposely placed there by the client.
This is a SQL Server 7.0 database, with SP1 not yet applied.
This is a fairly new database. Very few useful indices had been created
by the client before he started performance testing queries against it.
The client reported that the completion times of some test queries
seemed to improve
noticeably after the first attempt.

The index  names are all of the format
_WA_Sys_ followed by a column name and an 8 digit
hex number. These are all single column indices.They definitely
seem to correspond to columns which commonly appear in
where clauses and join on clauses in some of the SQL which I have
seen at this client's site.

I have scanned the books online, MSDN, and Technet
CD's, but can not seem to find any explicit
mention of SQL server establishing indices on its
own, but nonetheless it seems to happen.

Is this related to statistics gathering? The
database in question has the autogenerate and
autoupdate statistics options set to true.

If the generation of these indices is not due to some bizarre
anomaly, can someone tell me...

1) What sql server component is creating these indices?
2) Under what circumstances are they created?
3) Does one have any control over whether they are created?
     (Although many look useful, some, such as an index on a gender code

      column in a million row table, do not seem useful).
4) Can you point me toward any official documentation of this feature?

Any enlightenment on these indices woul be appreciated.

Thanks in advance.

Paul Sliwinski

2. Delta Tool

3. Wa_sys indexes still there after adding real indexes

4. Found in the Error Log - What does it mean?

5. _wa_sys...indexes

6. DTS in VB

7. Indexes '_Wa_Sys'

8. Call System

9. Source of Phantom Indexes with names like _WA_SYS% ?

10. a lot of _WA_SYS indexes and 0 rows in mmc console

11. Indexes beginning with _WA_Sys_

12. '_WA_Sys_' indexes in SQL 7.0

13. _WA_sys_ indexes?