Fragmented SQL Server indexes can result in performance degradation and longer index scanning time. DBCC INDEXDEFRAG helps improve index scanning performance by defragmenting clustered and nonclustered indexes.
Beekeeper Studio Est Une Interface de Base de Données Gratuite et Open Source
Le meilleur outil de requêtes SQL et éditeur que j'ai jamais utilisé. Il fournit tout ce dont j'ai besoin pour gérer ma base de données. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio est rapide, intuitif et facile à utiliser. Beekeeper prend en charge de nombreuses bases de données et fonctionne très bien sur Windows, Mac et Linux.
Syntax
DBCC INDEXDEFRAG (database1, table1, index1)
Results
DBCC INDEXDEFRAG returns a result set similar to this:
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
359 346 8
Details
DBCC INDEXDEFRAG also compacts the pages of an index, taking into account the FILLFACTOR specified when the index was created. Any empty pages created as a result of this compaction will be removed.
A relatively unfragmented index can be defragmented faster than a new index can be built because the time to defragment is related to the amount of fragmentation. A very fragmented index might take considerably longer to defragment than to rebuild. In addition, the defragmentation is always fully logged, regardless of the database recovery model setting. The defragmentation of a very fragmented index can generate more log than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE.
