Microsoft SQL Server Index, Table Statistics, and Index Rebuilds

Edit online

Your Microsoft SQL Server maintenance plan for Sterling B2B Integrator requires updating of statistics and rebuilding indexes. Reorganizing the indexes is not recommended because it defragments only the leaf level of the index and not at all levels of the index (leaf and intermediate pages) that an index rebuild would do. If index rebuilds are not conducted then the Sterling B2B Integrator application performance will degrade due to large fragmentation in the intermediate level of the indexes.

If you have a maintenance plan for updating statistics, set IsAutoCreateStatistics and IsAutoUpdateStatistics, at the database level, to False. This helps control when the maintenance plan runs, which should be at low load periods. If you have not included this in your maintenance plan, then set IsAutoCreateStatistics and IsAutoUpdateStatistics, at the database level to True.

Following is an example of a T-SQL statement for updating the statistics on a table, including the statistics pertaining to all the indexes on the table:

UPDATE STATISTICS <TABLE NAME>

When the data in the tables have changed by approximately 20 per cent, index rebuilds are required for the Sterling B2B Integrator database. This must be validated by looking at system performance as a whole. Online index rebuilds, which cause minimal impact to the system, are possible. To find out more about document life-spans, and when the data in the tables change by approximately 20 per cent, speak to the Sterling B2B Integrator system administrator.

You can rebuild indexes either online or offline. Online indexes can be rebuilt, with the following exceptions:
  • Clustered indexes if the underlying table contains LOB data types
  • Nonclustered indexes that are defined with LOB data type columns. Nonclustered indexes can be rebuilt online if the table contains LOB data types, but none of these columns are used in the index definition as either key or nonkey columns.

For ease of maintenance, it is easier to either build all the indexes offline because offline rebuilding does not have the restrictions listed previously, or reorganize the index.

Following is an example of a T-SQL statement for rebuilding indexes offline:

ALTER INDEX ALL ON <TABLE NAME> REBUILD

Following is an example of a T-SQL statement for reorganizing indexes offline:

ALTER INDEX ALL ON <TABLE NAME> REORGANIZE

For more information about Reorganizing and Rebuilding Indexes, refer to the Microsoft Developer Network Web site, which can be accessed from: http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx.

For more information about Alter index (Transact-SQL), refer to the Microsoft Developer Network Web site, which can be accessed from: http://msdn.microsoft.com/en-us/library/ms188388(SQL.90).aspx.