More about database maintenance

Optimizations

Note: please refer to SQL Server Books Online for more information regarding this topic.

Performance in a Relational Database Management System, like Microsoft SQL Server, is a large topic. It involves issues such as:

There exist a number of papers, books and courses regarding performance. Db Maint has a section in the job dialog that performs one part of what can be done to achieve good performance.

Re-building indexes

An index is sorted in a semi-physical sequence. This applies to the actual data pages for clustered indexes.

When the table (or index) is created, SQL Server allocates an extent (8 pages) for the table (as of 7.0, the first 8 pages come from mixed extents, which can hold pages from several indexes). This extent is contiguous space in the database file. As the table grows, new extents are allocated (not necessarily next to the old one, see SQL Server Books Online for more details).

If an insert has to be made in sequence, the page chain still has to be in order. This applies at the data level if a clustered index exists, and for all non-clustered indexes. A new page has to be allocated, but the new page might be on some other extent. So when scanning the table, a lot of extent switches have to be made. This increases the disk head movement and is also makes the Read-ahead process less efficient.

When rows are deleted (and updated), gaps are left on the pages, and eventually pages are de-allocated. These pages will be reused, but still not necessarily in sequence.

Whether this is a major issue or not depends on the indexing scheme and the read and update pattern against the table.

By rebuilding the index, the page chain is re-sorted to make it physically continuous again.

By specifying that some free spaces are to be left on each page, future inserts can still be made in physical continuous order (no page-split). SQL Server will not maintain this free space, so rebuilding indexes with a fill factor regularly is generally a good idea.

See SQL Server Books Online, DBCC SHOWCONTIG for information regarding how to check "fragmentation" level for a table/index. The Fragmentation Report window will show the output from the DBCC SHOWCONTIG statement for the selected index, or use the View fragmentation information dialog to display fragmentation information for several indexes...

Update index statistics

SQL Servers optimizer depends on statistics when it tries to calculate efficient access paths for your queries. This statistics is stored in each index' Distribution Page.

The distribution page is not continuously updated in version 6.5. As of version 7, statistics will be regularly, but SQL Server will not scan the entire table (unless it is a small table) to build the statistics.

There is no need to update statistics if you re-built indexes using DBCC DBREINDEX.