How to Defragment Indexes ?
|
This is indeed an interesting topic. But in this
script we would bee how we can use the Defragmenting command in SQL Server
2000. Run the below script and copy paste the statements generated.
SELECT
'DBCC INDEXDEFRAG (' + DB_NAME() + ', ' + OBJECT_NAME(id) + ', ' + name + ')'
FROM sysindexes
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND indid NOT IN (0, 255)
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
AND INDEXPROPERTY(id, name, 'IsHypothetical') = 0
AND INDEXPROPERTY(id, name, 'IsAutoStatistics') = 0
AND INDEXPROPERTY(id, name, 'IsFulltextKey') = 0
Just to walk you through the various clauses:
OBJECTPROPERTY(id, 'IsMSShipped') = 0: Leaves
out indexes on system tables (DBCC
INDEXDEFRAG is not supported on system tables)
indid NOT IN (0, 255): Leaves out tables without an
index (indid = 0) and indexes for text,
ntext and image columns (indid = 255)
INDEXPROPERTY(id, name, 'IsStatistics') = 0: leaves
out entries in sysindexes that are
actually statistics and not indexes. Statistics can be
updated with UPDATE STATISTICS.
INDEXPROPERTY(id, name, 'IsHypothetical') = 0:
leaves out (temporary) indexes created
by the Index Tuning Wizard to aid in performance tuning
INDEXPROPERTY(id, name, 'IsAutoStatistics') = 0: not
necessary in this query. IsStatistics
covers these as well.
INDEXPROPERTY(id, name, 'IsFulltextKey') : leaves
out full text indexes, they are handled
indepently of normal SQL Server indexes
|