_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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

762 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