Real vs. Auto-Created Indexes

I've noticed a lot of entries in the sysindexes table for indexes that I didn't create. I'm told they're not real indexes but statistics that SQL Server's optimizer has automatically created. How can I tell if an index is "real" or one automatically created by SQL Server?

Novice users often think these statistics are indexes because they have entries in the sysindexes table, and they wonder how the statistics got there. By default, SQL Server creates statistics on columns in a table if an index doesn't already exist on the column and the optimizer judges that statistical information about the distributions of data ranges within the column will let the optimizer choose a more efficient query-processing plan. Telling auto-created statistics from real indexes is easy. An auto-created statistic starts with the prefix _WA_Sys (in SQL Server 2000 and 7.0).

You can also determine whether an index is real or an auto-created statistic by using the IsAutoStatistics property of the INDEXPROPERTY() function. Letting the SQL Server optimizer pick which statistics to create is the right choice. I always keep the auto_create_statistics option enabled for databases that I'm responsible for.

Many people miss the next obvious conclusion. The presence of an auto-created statistic suggests that a real index might be beneficial. Consider the output from the code that Listing 1 shows. The batch makes a copy of the Northwind Orders table in tempdb, selects a row, then checks to see whether SQL Server added a statistic. Sure enough, this table didn't have an index on the OrderId column, so SQL Server automatically created a statistic named _WA_Sys_OrderID_58D1301D. The presence of a statistic on the OrderId column is a clue that the Orders table would benefit from additional indexes.

The query in Listing 2 shows the count of auto-created statistics for every user table in a database that has at least one auto-created statistic. Don't get carried away! Not all statistics should be replaced with a real index. I've seen cases where SQL Server has automatically created more than 50 statistics for a single table. Needless to say, these tables had poor indexing strategies. A quick count of tables and the number of auto-created statistics associated with them can give you a map of tables needing index work.