Filter
Top Products
Microsoft SQL Server Index, Table Statistics, and Index Rebuilds
Your maintenance plan should include updating of statistics and rebuilding or
reorganizing 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 recommended 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:
v Clustered indexes if the underlying table contains LOB data types
v 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.
Windows Defragmentation
You must run Windows defragmentation on disks with SQL Server data files and
transaction logs once a month.
60 Sterling B2B Integrator: Performance Management