How SQL Server recognizes real and automatically created indexes

Q: I recently found that many of the entries in the sysindexes Index table were not created by myself. Listening to colleagues say that they are not real indexes, but rather statistics created automatically by the SQL Server query optimizer. How do you identify which are true indexes and which statistics are created automatically by SQL Server?

A: By default, if a column in a table has no indexes, SQL Server automatically creates statistics for that column. The query optimizer then evaluates the statistics for the range of data in the column to select a more efficient query processing scenario. Resolving automatically created statistics is simple, in SQL Server 7.0 and SQL Server 2000, the automatically created statistics are prefixed by _wa_sys.

You can also use the Isautostatistics property of the Indexproperty () function to differentiate between whether an index is true or automatically created, and let the SQL Server optimizer select the statistics that need to be created. You can also enable the Automatically create Statistics tab option for the database that you manage.

Many people overlook the obvious conclusion below. The automatic creation of statistics means that a real index might benefit from it. Consider the output of the following code:

USE tempdb
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.orders'), 'IsUserTable')=1
DROP TABLE dbo.orders
GO
SELECT * INTO tempdb..orders FROM northwind..orders
GO
SELECT * FROM tempdb..orders WHERE orderid = 10248
GO
SELECT * FROM tempdb..sysindexes WHERE id = object_id('orders')
AND name LIKE
'_wa_sys%'
GO

The code copies the Northwind Orders table in tempdb, selects a row, and then checks to see if SQL Server added a statistic. Obviously, the table does not have an index of the OrderID column, so SQL Server automatically creates a statistic named _wa_sys_orderid_58d1301d. The presence of the OrderID column tables indicates that the Northwind Orders table will benefit from additional indexes.

The following query shows the number of statistics that are automatically created for each user table in the database, which has at least one automatically created statistic.

SELECT
object_name(id) TableName
,count(*) NumberOfAutoStats
FROM
sysindexes
WHERE
OBJECTPROPERTY(id, N'IsUserTable') = 1
AND INDEXPROPERTY ( id , name , 'IsAutoStatistics' ) = 1
GROUP BY
object_name(id)
ORDER BY
count(*) DESC

Not all statistics can be replaced by real indexes. In some cases, SQL Server automatically creates more than 50 statistics for a table. Obviously, these tables have very poor indexing strategies. A quick count of the tables and the associated statistics that you automatically create can help you determine which tables need to be indexed.