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は無料でオープンソースのデータベースGUIです
今まで使った中で最高のSQLクエリ＆エディタツールです。データベース管理に必要なすべてが揃っています。 - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studioは高速で直感的、使いやすいです。Beekeeperは多くのデータベースをサポートし、Windows、Mac、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.
