Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 362

You can see that these statistics were updated July 31 2007 and there are 2000 rows of which 2000 were
sampled, so you know that these statistics were taken will a FULL/100% sample size. The statistics
consist of a histogram of 192 values (I've only printed the top 7) with a density of 1, which means that
each value is unique. The names column in the lastnames table was populated with random characters,
so these results are consistent with the table usage.
Drop the index now so it doesn't interfere with the tuning later on:
DROP INDEX lastnames.idx_name
CreatingStatistics
Statistics can be created in a number of different ways. You can create them automatically, manually, or
by creating an index.
CREATE INDEX
As you've already seen, when you create an index, statistics of the same name are automatically created
for the columns within the index to help the optimizer choose whether or not that index is useful. Thus,
the idx_name index has a set of statistics called idx_name.
AUTO_CREATE_STATISTICS
By default, SQL Server 2005 will automatically create statistics on columns where they can be of use to
the optimizer. This occurs when a column without an index is used in a WHERE clause and statistics
created as a result of this are prefixed by _WA_Sys_ . For example, if you look at the people table:
sp_helpstats 'people', 'ALL'
go
This object does not have any statistics or indexes.
You can see that there are no statistics currently. If you then run a SELECT statement with the lastname
column in the where clause:
SELECT firstname,lastname FROM people WHERE lastname = 'Bolton'
And then look for statistics again:
sp_helpstats 'people', 'ALL'
go
statistics_name statistics_keys
--------------------------- ---------------
_WA_Sys_00000003_0C85DE4D lastname
You can see that statistics have been automatically generated. Looking at the automatically generated
statistics in a database can give you an idea of where you might have missing indexes, because it indicates
where an index could have been used by the optimizer. You can use this code to check for all statistics in
your database that are not associated with an index:
sp_msforeachtable "print 'Table Name:?' EXEC sp_helpstats '?', STATS"
Automatic statistics creation is controlled by the AUTO_CREATE_STATISTICS database option and can
be disabled if necessary.