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 Ist Eine Kostenlose & Open-Source-Datenbank-GUI
Das beste SQL-Abfrage- und Editor-Tool, das ich je benutzt habe. Es bietet alles, was ich zur Verwaltung meiner Datenbank brauche. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio ist schnell, intuitiv und einfach zu bedienen. Beekeeper unterstützt viele Datenbanken und funktioniert hervorragend unter Windows, Mac und 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.
