_WA indexes

Posted on 2003-03-11
Medium Priority
Last Modified: 2008-01-16
Hi experts,

My company sells software products.  One of the customers production DBAs has noticed that their prodn database has a lot of indexes in the sysindex table named '_WA%'
(select * from sysindexes where name like '_WA%')

I have been asked to investigate these indexes as the customer suggests that their presence indicates a lack of suitable 'real' indexes.

I have looked in BOL for details of _WA indexes and cant find anything.

Can any expert help me understand:-
1. What are these entries about?
2. How can I interpret the data?
3. Do they suggest that I am missing indexes or are they normal internal SQL server operations?
4. Any other useful info on these peculiar entries.

Thanks in advance
Question by:meowsh
LVL 43

Accepted Solution

TimCottee earned 600 total points
ID: 8110127
Hi meowsh,

I have to agree that there is very little information about these indexes. As it happens they are actually SQL server's own statistics data based on the table usage. This data is created automatically based on how much the data changes in the table(s). I think that answers 1, 3 and 4 of your questions, as for how to interpret; I am not sure that you can easily do so.

I would imagine that you have AUTO_CREATE_STATISTICS set on for this database so SQL server will automatically build these statistics indexes when columns are used as a predicate in a query.

If you have already indexed columns/tables as are appropriate for your database then there is no harm in keeping these statistics indexes as they will certainly assist in less common query processing. If you wish to remove them then turn of auto_create_statistics.


Brainbench MVP for Visual Basic
LVL 18

Expert Comment

ID: 8110284
These are statistics which give the distribution of values in columns.
They will indicate that queries have been performed without useful indexes but that doesn't mean that the indexes should be created.

Does the system perform adequately?
If so then there is no reason to be concerned.

A common mistake is to create indexes to support queries without looking at system as a whole and so cause the overall system performance to decline.

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question