Maintaining index and column statistics

The histogram and density values for an index are not maintained as data rows are added and deleted. The database owner must issue an update statistics command to ensure that statistics are current. Run update statistics:

Run update statistics after inserts to any index that includes an IDENTITY column or any increasing key value. Date columns often have regularly increasing keys.

Running update statistics on these types of indexes is especially important if the IDENTITY column or other increasing key is the leading column in the index. After a number of rows have been inserted past the last key in the table when the index was created, all that the optimizer can tell is that the search value lies beyond the last row in the distribution page.

It cannot accurately determine how many rows match a given value.

NoteFailure to update statistics can severely hurt performance.

See Chapter 34, “Using Statistics to Improve Performance,” for more information.