_WA_Sys_. . . Indexes

[David Fine]

Questions:

We use the product DB Artisan for many of our dba tasks. When we use DB Artisan to migrate tables from unit test to system test, for example, the _WA_Sys... indexes are changed from indexes that do not appear on the DB Artisan list of indexes (in the source database) to ones that do appear in the DB Artisan list of indexes (in the target database).

1) I'm trying to find out if this subtle change in status for the statistics indexes harms their functioning for statistics use.

2) Also, does this subtle change in status for the indexes lead to performance problems such as increased response time.

3) Should we delete the _WA_Sys_ indexes in the target database and let the system create its own there?

Background:

With SQL-Server 7.0, certain indexes are automatically created. They have names that start with _WA_Sys_. They are used by SQL-Server 7.0 for statistics that SQL-Server uses for its decision making.

One place to find out more about these automatically created statistical 'indexes' is in SQL Server Books Online, part of the SQL-Server 7.0 package. Use the 'search' method and search for 'statistical information'. The first entry returned gives some information about the indexes that appear when the database option 'auto create statistics' is set to true.

Dave

Please read question before replying


------------
Ray Miao at 11/5/99 12:02:42 PM

First of all, they are not index even listed in sysindex table. Second, sql server creates and uses them to improve performance.


------------
David Fine at 11/5/99 10:50:22 AM

Questions:

We use the product DB Artisan for many of our dba tasks. When we use DB Artisan to migrate tables from unit test to system test, for example, the _WA_Sys... indexes are changed from indexes that do not appear on the DB Artisan list of indexes (in the source database) to ones that do appear in the DB Artisan list of indexes (in the target database).

1) I'm trying to find out if this subtle change in status for the statistics indexes harms their functioning for statistics use.

2) Also, does this subtle change in status for the indexes lead to performance problems such as increased response time.

3) Should we delete the _WA_Sys_ indexes in the target database and let the system create its own there?

Background:

With SQL-Server 7.0, certain indexes are automatically created. They have names that start with _WA_Sys_. They are used by SQL-Server 7.0 for statistics that SQL-Server uses for its decision making.

One place to find out more about these automatically created statistical 'indexes' is in SQL Server Books Online, part of the SQL-Server 7.0 package. Use the 'search' method and search for 'statistical information'. The first entry returned gives some information about the indexes that appear when the database option 'auto create statistics' is set to true.

Dave