DBCC INDEXDEFRAG (Transact-SQL)

Berlaku untuk: SQL Server Azure SQL Managed Instance

Defragmentasi indeks tabel atau tampilan yang ditentukan.

Penting

Fitur ini akan dihapus dalam versi SQL Server yang akan datang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini. Gunakan UBAH INDEKS sebagai gantinya.

Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru

Konvensi sintaks transact-SQL

Sintaks

DBCC INDEXDEFRAG
(
    { database_name | database_id | 0 }
    , { table_name | table_id | view_name | view_id }
    [ , { index_name | index_id } [ , { partition_number | 0 } ] ]
)
    [ WITH NO_INFOMSGS ]

Argumen

| database_name database_id | 0

Database yang berisi indeks untuk defragmentasi. Jika 0 ditentukan, database saat ini digunakan. Nama database harus mematuhi aturan untuk pengidentifikasi.

| | |

Tabel atau tampilan yang berisi indeks untuk defragmentasi. Nama tabel dan tampilan harus mematuhi aturan untuk pengidentifikasi.

| index_id index_name

Nama atau ID indeks untuk defragmentasi. Jika tidak ditentukan, pernyataan mendefrimasi semua indeks tabel atau tampilan yang ditentukan. Nama indeks harus mematuhi aturan untuk pengidentifikasi.

partition_number | 0

Nomor partisi indeks untuk defragmen. Jika tidak ditentukan atau jika 0 ditentukan, pernyataan defragmentasi semua partisi dalam indeks yang ditentukan.

DENGAN NO_INFOMSGS

Menekan semua pesan informasi yang memiliki tingkat keparahan dari 0 hingga 10.

Keterangan

DBCC INDEXDEFRAG defragmentasi tingkat daun indeks sehingga urutan fisik halaman cocok dengan urutan logis kiri-ke-kanan dari node daun, sehingga meningkatkan performa pemindaian indeks.

Catatan

Ketika DBCC INDEXDEFRAG dijalankan, defragmentasi indeks terjadi secara serial. Ini berarti bahwa operasi pada satu indeks dilakukan menggunakan satu utas. Tidak ada paralelisme yang terjadi. Selain itu, operasi pada beberapa indeks dari pernyataan yang sama DBCC INDEXDEFRAG dilakukan pada satu indeks pada satu waktu.

DBCC INDEXDEFRAG juga memampatkan halaman indeks, dengan mempertimbangkan faktor pengisian yang ditentukan ketika indeks dibuat. Setiap halaman kosong yang dibuat karena pemadatan ini dihapus. Untuk informasi selengkapnya, lihat Menentukan Faktor Pengisian untuk Indeks.

Jika indeks mencakup lebih dari satu file, DBCC INDEXDEFRAG defragmentasi satu file pada satu waktu. Halaman tidak bermigrasi antar file.

DBCC INDEXDEFRAG melaporkan perkiraan persentase yang diselesaikan setiap lima menit. DBCC INDEXDEFRAG dapat dihentikan kapan saja dalam proses, dan pekerjaan yang telah selesai dipertahankan.

Tidak seperti DBCC DBREINDEX, atau operasi pembangunan indeks umumnya, DBCC INDEXDEFRAG adalah operasi online. Ini tidak menahan kunci jangka panjang. Oleh karena itu, DBCC INDEXDEFRAG tidak memblokir kueri atau pembaruan yang sedang berjalan. Karena waktu untuk defragmentasi terkait dengan tingkat fragmentasi, indeks yang relatif tidak difragmentasi dapat didfragmentasi lebih cepat daripada indeks baru dapat dibangun. Indeks yang sangat terfragmentasi mungkin membutuhkan waktu jauh lebih lama untuk didefragmentasi daripada membangun kembali.

Defragmentasi selalu dicatat sepenuhnya, terlepas dari pengaturan model pemulihan database. Untuk informasi selengkapnya, lihat MENGUBAH DATABASE (Transact-SQL). Defragmentasi indeks yang sangat terfragmentasi dapat menghasilkan lebih banyak log daripada pembuatan indeks yang dicatat sepenuhnya. Namun, defragmentasi dilakukan sebagai serangkaian transaksi pendek, sehingga log besar tidak perlu jika cadangan log sering diambil atau jika pengaturan model pemulihan SEDERHANA.

Batasan

DBCC INDEXDEFRAG mengacak halaman daun indeks di tempat. Oleh karena itu, jika indeks diselingi dengan indeks lain pada disk, berjalan DBCC INDEXDEFRAG terhadap indeks tersebut tidak membuat semua halaman daun dalam indeks berdekatan. Untuk meningkatkan pengklusteran halaman, bangun ulang indeks.

DBCC INDEXDEFRAG tidak dapat digunakan untuk defragmentasi indeks berikut:

Indeks yang dinonaktifkan.
Indeks dengan penguncian halaman diatur ke NONAKTIF.
Indeks spasial.

DBCC INDEXDEFRAG tidak didukung untuk digunakan pada tabel sistem.

Tataan hasil

DBCC INDEXDEFRAG mengembalikan tataan hasil berikut (nilai dapat bervariasi) jika indeks ditentukan dalam pernyataan (kecuali WITH NO_INFOMSGS ditentukan):

Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
359           346         8
  
(1 row(s) affected)
  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Izin

Pemanggil harus memiliki tabel, atau menjadi anggota peran server tetap sysadmin , peran database tetap db_owner , atau peran database tetap db_ddladmin .

Contoh

J. Menggunakan DBCC INDEXDEFRAG untuk mendefragmentasi indeks

Contoh berikut mendefrori semua partisi PK_Product_ProductID indeks dalam Production.Product tabel dalam AdventureWorks2025 database.

DBCC INDEXDEFRAG (AdventureWorks2022, 'Production.Product', PK_Product_ProductID);
GO

B. Gunakan DBCC SHOWCONTIG dan DBCC INDEXDEFRAG untuk mendefragmentasi indeks dalam database

Contoh berikut menunjukkan cara sederhana untuk mendefragmentasi semua indeks dalam database yang terfragmentasi di atas ambang yang dideklarasikan.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(255);
DECLARE @execstr   VARCHAR(400);
DECLARE @objectid  INT;
DECLARE @indexid   INT;
DECLARE @frag      DECIMAL;
DECLARE @maxfrag   DECIMAL;
  
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
  
-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';
  
-- Create the table.
CREATE TABLE #fraglist (
   ObjectName CHAR(255),
   ObjectId INT,
   IndexName CHAR(255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL);
  
-- Open the cursor.
OPEN tables;
  
-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;
  
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;
  
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
  
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
  
-- Open the cursor.
OPEN indexes;
  
-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;
  
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);
  
   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;
  
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
  
-- Delete the temporary table.
DROP TABLE #fraglist;
GO

Lihat juga

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-01-02