SQL 7. In the table sysindexes, under the 'name' column, we see many indexes that begin with '_WA_Sys'. What are these? Example: We have a table that has 2 indexes and there are 24 of these '_WA_Sys' indexes for every field in the table not used in our 2 indexes.
This is a manifestation of the new dynamic configuration that exists in SQL Server 7.0 or later. You create indexes on colums to improve the data access. Previously, you had to do a lot of monitoring as data and access patterns change over time. This meant you had to either create new indexes or realign old ones. SQL Server 7.0 tracks the access patterns that are being used. If an index doesn't exist on a column that would have benefitted a query, SQL Server will create its own on the fly. These are not true indexes, but instead are statistics that SQL Server created on the fly to satisfy a query just as an index would do.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL Server gurus are waiting to answer your toughest questions.
Dig Deeper on Microsoft SQL Server Installation
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.