INDEXING CONSIDERATIONS - A Developer's Guide to Data Modeling for SQL Server - page 235

Index Statistics
One often overlooked component of indexes is its statistics. Statistics are
the pieces of information stored by SQL Server that help it determine
whether a given index will be useful and what access method to use to get
to the data being requested by any given query. Specifically, SQL Server
needs to know the distribution of values in every column and the occur-
rences of each step in the distribution. In fact, you can keep statistics for
any column or group of columns on a table without even creating an index.
This capability is helpful when there is no natural index, such as in highly
denormalized tables.
Unfortunately, gathering exhaustive statistics on every value in every
column could be cumbersome on larger systems, so SQL Server can cal-
culate the statistics based on a sampling of the data. To make sure that the
query optimizer has as much information as possible, we can manually
force the creation of and updating of statistics on columns. When indexes
are created, statistics are automatically created to go along with that index.
SQL Server then tries to maintain these statistics (or optionally can be left
outdated, in rare circumstances). Most of the time, the statistics on a table
are adequately maintained if the indexes are adequately maintained.
Index Maintenance Considerations
We've mentioned repeatedly that indexes must be maintained. What does
this mean? Basically, indexes must be occasionally reordered, or defrag-
mented, because they've been updated or changed and SQL Server hasn't
been able to keep up with the changes. This includes statistical information
being updated. So DBAs must periodically execute some basic mainte-
nance on the indexes (we discuss the specifics later this chapter). What you
need to keep in mind when designing indexes is the frequency of changes
to the data, and therefore to the indexes. If you've created a clustered
index and several nonclustered indexes, you need to take another look at
your usage information to determine how often the indexes may need to
be maintained.
If you have an extremely volatile database, wherein hundreds of thou-
sands of rows are changing or being inserted daily, you need to work with
the DBA to make sure to maintain the indexes quite often. However, if
your traffic is primarily read-intensive or if the system just isn't taxed (think
fewer than 10,000 updates per day), you may need to have maintenance
run on the indexes only weekly. Again, your mileage will vary, but be sure