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 Microsoft SQL Server mendatang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini. Gunakan ALTER INDEX sebagai gantinya.
Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru
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 ]
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.
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.
| table_name | table_id | view_name view_id
Tabel atau tampilan yang berisi indeks yang akan didefragmentasi. Nama tabel dan tampilan harus mematuhi aturan untuk pengidentifikasi.
| index_name index_id
Nama atau ID indeks yang akan didefragmentasi. Jika tidak ditentukan, pernyataan mendefinisikan semua indeks tabel atau tampilan yang ditentukan. Nama indeks harus mematuhi aturan untuk pengidentifikasi.
partition_number | 0
Nomor partisi indeks yang akan didefragmentasi. Jika tidak ditentukan atau jika 0 ditentukan, pernyataan mendefinisikan 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, oleh karena itu 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. Halaman kosong apa pun 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 didefragmentasi 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 pencadangan 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 mendefinisikan 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
A. Menggunakan DBCC INDEXDEFRAG untuk mendefragmentasi indeks
Contoh berikut mendefinisikan semua partisi PK_Product_ProductID indeks dalam Production.Product tabel dalam AdventureWorks2022 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 batas yang dinyatakan.
/*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