DBCC SHOWCONTIG (Transact-SQL)
Berlaku untuk:
SQL Server Azure SQL Managed Instance
Menampilkan informasi fragmentasi untuk data dan indeks tabel atau tampilan yang ditentukan.
Penting
Fitur ini akan dihapus dalam versi Microsoft 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 sys.dm_db_index_physical_stats sebagai gantinya.
Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru
Sintaks
DBCC SHOWCONTIG
[ (
{ table_name | table_id | view_name | view_id }
[ , index_name | index_id ]
) ]
[ WITH
{
[ , [ ALL_INDEXES ] ]
[ , [ TABLERESULTS ] ]
[ , [ FAST ] ]
[ , [ ALL_LEVELS ] ]
[ NO_INFOMSGS ]
}
]
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
| table_name | table_id | view_name view_id
Tabel atau tampilan untuk memeriksa informasi fragmentasi. Jika tidak ditentukan, semua tabel dan tampilan terindeks dalam database saat ini dicentang. Untuk mendapatkan ID tabel atau tampilan, gunakan fungsi OBJECT_ID .
| index_name index_id
Indeks untuk memeriksa informasi fragmentasi. Jika tidak ditentukan, pernyataan memproses indeks dasar untuk tabel atau tampilan yang ditentukan. Untuk mendapatkan ID indeks, gunakan tampilan katalog sys.indexes .
WITH
Menentukan opsi untuk jenis informasi yang dikembalikan oleh pernyataan DBCC.
CEPAT
Menentukan apakah akan melakukan pemindaian cepat indeks dan menghasilkan informasi minimal. Pemindaian cepat tidak membaca halaman tingkat daun atau data indeks.
ALL_INDEXES
Menampilkan hasil untuk semua indeks untuk tabel dan tampilan yang ditentukan, meskipun indeks tertentu ditentukan.
TABLERESULTS
Menampilkan hasil sebagai set baris, dengan informasi tambahan.
ALL_LEVELS
Dipertahankan hanya untuk kompatibilitas mundur. Bahkan jika ALL_LEVELS ditentukan, hanya tingkat daun indeks atau tingkat data tabel yang diproses.
NO_INFOMSGS
Menekan semua pesan informasi yang memiliki tingkat keparahan dari 0 hingga 10.
Tataan hasil
Tabel berikut ini menjelaskan informasi dalam tataan hasil.
| Statistik | Deskripsi |
|---|---|
| Halaman Yang Dipindai | Jumlah halaman dalam tabel atau indeks. |
| Jangkauan yang Dipindai | Jumlah jangkauan dalam tabel atau indeks. |
| Peralihan Jangkauan | Frekuensi pernyataan DBCC berpindah dari satu tingkat ke tingkat lain saat pernyataan melintasi halaman tabel atau indeks. |
| Rata-rata Halaman per Jangkauan | Jumlah halaman per jangkauan dalam rantai halaman. |
| Kepadatan Pemindaian [Hitungan Terbaik: Jumlah Aktual] | Persentase. Ini adalah rasio Jumlah Terbaik untuk Jumlah Aktual. Nilai ini adalah 100 jika semuanya bersebelah; jika nilai ini kurang dari 100, beberapa fragmentasi ada. Jumlah Terbaik adalah jumlah perubahan jangkauan yang ideal jika semuanya ditautkan secara berdebat. Jumlah Aktual adalah jumlah perubahan jangkauan aktual. |
| Fragmentasi Pemindaian Logis | Persentase halaman yang tidak berurutan dikembalikan dari pemindaian halaman daun indeks. Jumlah ini tidak relevan dengan timbunan. Halaman yang tidak berurutan adalah halaman di mana halaman fisik berikutnya yang dialokasikan ke indeks bukan halaman yang ditujukan oleh pointere pag berikutnya di halaman daun saat ini. |
| Fragmentasi Pemindaian Jangkauan | Persentase tingkat di luar urutan dalam memindai halaman daun indeks. Jumlah ini tidak relevan dengan timbunan. Tingkat di luar urutan adalah salah satu di mana sejauh yang berisi halaman saat ini untuk indeks tidak secara fisik sejauh berikutnya setelah jangkauan yang berisi halaman sebelumnya untuk indeks. Catatan: Angka ini tidak berarti ketika indeks mencakup beberapa file. |
| Rata-rata Byte Gratis per Halaman | Jumlah rata-rata byte gratis pada halaman yang dipindai. Semakin besar jumlahnya, semakin sedikit halaman penuh. Angka yang lebih rendah lebih baik jika indeks tidak akan memiliki banyak sisipan acak. Jumlah ini juga dipengaruhi oleh ukuran baris; ukuran baris yang besar dapat menyebabkan angka yang lebih besar. |
| Rata-rata kepadatan halaman (penuh) | Kepadatan halaman rata-rata, sebagai persentase. Nilai ini memperhitungkan ukuran baris. Oleh karena itu, nilainya adalah indikasi yang lebih akurat tentang seberapa lengkap halaman Anda. Semakin besar persentasenya, semakin baik. |
Saat table_id dan FAST ditentukan, DBCC SHOWCONTIG mengembalikan tataan hasil hanya dengan kolom berikut:
- Halaman Yang Dipindai
- Peralihan Jangkauan
- Kepadatan Pemindaian [Jumlah Terbaik:Jumlah Aktual]
- Fragmentasi Pemindaian Jangkauan
- Fragmentasi Pemindaian Logis
Ketika TABLERESULTS ditentukan, DBCC SHOWCONTIG mengembalikan kolom berikut dan juga sembilan kolom yang dijelaskan dalam tabel sebelumnya.
| Statistik | Deskripsi |
|---|---|
| Nama Objek | Nama tabel atau tampilan yang diproses. |
| ObjectId | ID nama objek. |
| IndexName | Nama indeks yang diproses. NULL untuk timbunan. |
| IndexId | ID indeks. 0 untuk timbunan. |
| Level | Tingkat indeks. Tingkat 0 adalah tingkat daun, atau data, indeks. Levelnya 0 untuk tumpuk. |
| Halaman | Jumlah halaman yang membentuk tingkat indeks atau seluruh tumpukan. |
| Baris | Jumlah data atau rekaman indeks pada tingkat indeks tersebut. Untuk timbunan, nilai ini adalah jumlah rekaman data di seluruh tumpuk. Untuk timbunan, jumlah rekaman yang dikembalikan dari fungsi ini mungkin tidak cocok dengan jumlah baris yang dikembalikan dengan menjalankan SELECT COUNT(*) terhadap heap. Ini karena baris mungkin berisi beberapa rekaman. Misalnya, dalam beberapa situasi pembaruan, satu baris timbunan mungkin memiliki catatan penerusan dan rekaman yang diteruskan sebagai hasil dari operasi pembaruan. Selain itu, sebagian besar baris LOB besar dibagi menjadi beberapa rekaman dalam penyimpanan LOB_DATA. |
| MinimumRecordSize | Ukuran rekaman minimum dalam tingkat indeks atau seluruh tumpukan. |
| MaximumRecordSize | Ukuran rekaman maksimum dalam tingkat indeks atau seluruh tumpukan. |
| AverageRecordSize | Ukuran rekaman rata-rata dalam tingkat indeks atau seluruh tumpukan. |
| ForwardedRecords | Jumlah rekaman yang diteruskan dalam tingkat indeks atau seluruh timbunan. |
| Jangkauan | Jumlah jangkauan dalam tingkat indeks atau seluruh timbunan tersebut. |
| ExtentSwitches | Frekuensi pernyataan DBCC berpindah dari satu tingkat ke tingkat lain saat pernyataan melintasi halaman tabel atau indeks. |
| AverageFreeBytes | Jumlah rata-rata byte gratis pada halaman yang dipindai. Semakin besar jumlahnya, semakin sedikit halaman penuh. Angka yang lebih rendah lebih baik jika indeks tidak akan memiliki banyak sisipan acak. Jumlah ini juga dipengaruhi oleh ukuran baris; ukuran baris yang besar dapat menyebabkan angka yang lebih besar. |
| AveragePageDensity | Kepadatan halaman rata-rata, sebagai persentase. Nilai ini memperhitungkan ukuran baris. Oleh karena itu, nilainya adalah indikasi yang lebih akurat tentang seberapa lengkap halaman Anda. Semakin besar persentasenya, semakin baik. |
| ScanDensity | Persentase. Ini adalah rasio BestCount dengan ActualCount. Nilai ini adalah 100 jika semuanya bersebelah; jika nilai ini kurang dari 100, beberapa fragmentasi ada. |
| BestCount | Jumlah perubahan jangkauan yang ideal jika semuanya ditautkan secara bersebelahan. |
| ActualCount | Jumlah perubahan jangkauan aktual. |
| LogicalFragmentation | Persentase halaman yang tidak berurutan dikembalikan dari pemindaian halaman daun indeks. Jumlah ini tidak relevan dengan timbunan. Halaman yang tidak berurutan adalah halaman di mana halaman fisik berikutnya yang dialokasikan ke indeks bukan halaman yang ditujukan oleh penunjuk halaman berikutnya di halaman daun saat ini. |
| ExtentFragmentation | Persentase tingkat di luar urutan dalam memindai halaman daun indeks. Jumlah ini tidak relevan dengan timbunan. Tingkat di luar urutan adalah salah satu di mana sejauh yang berisi halaman saat ini untuk indeks tidak secara fisik sejauh berikutnya setelah jangkauan yang berisi halaman sebelumnya untuk indeks. Catatan: Angka ini tidak berarti ketika indeks mencakup beberapa file. |
Ketika WITH TABLERESULTS dan FAST ditentukan, tataan hasilnya sama seperti saat WITH TABLERESULTS ditentukan, kecuali kolom berikut akan memiliki nilai null:
| Baris | Jangkauan |
|---|---|
| MinimumRecordSize | AverageFreeBytes |
| MaximumRecordSize | AveragePageDensity |
| AverageRecordSize | ExtentFragmentation |
| ForwardedRecords |
Keterangan
Pernyataan melintasi DBCC SHOWCONTIG rantai halaman pada tingkat daun indeks yang ditentukan saat index_id ditentukan. Jika hanya table_id yang ditentukan atau jika index_id adalah 0, halaman data tabel yang ditentukan akan dipindai. Operasi hanya memerlukan kunci tabel yang dibagikan niat (IS). Dengan cara ini semua pembaruan dan sisipan dapat dilakukan, kecuali yang memerlukan kunci tabel eksklusif (X). Ini memungkinkan tradeoff antara kecepatan eksekusi dan tidak ada pengurangan konkurensi terhadap jumlah statistik yang dikembalikan. Namun, jika perintah hanya digunakan untuk mengukur fragmentasi, kami sarankan Anda menggunakan WITH FAST opsi untuk performa optimal. Pemindaian cepat tidak membaca halaman tingkat daun atau data indeks. Opsi WITH FAST ini tidak berlaku untuk tumpukan.
Batasan
DBCC SHOWCONTIG tidak menampilkan data dengan jenis data ntext, teks, dan gambar . Ini karena indeks teks yang menyimpan data teks dan gambar tidak ada lagi.
Selain itu, DBCC SHOWCONTIG tidak mendukung beberapa fitur baru. Contohnya:
- Jika tabel atau indeks yang ditentukan dipartisi,
DBCC SHOWCONTIGhanya menampilkan partisi pertama dari tabel atau indeks yang ditentukan. DBCC SHOWCONTIGtidak menampilkan informasi penyimpanan luapan baris dan jenis data off-row baru lainnya, seperti nvarchar(max), varchar(max), varbinary(max), dan xml.- Indeks spasial tidak didukung oleh
DBCC SHOWCONTIG.
Semua fitur baru didukung penuh oleh tampilan manajemen dinamis sys.dm_db_index_physical_stats (Transact-SQL ).
Fragmentasi tabel
DBCC SHOWCONTIG menentukan apakah tabel sangat terfragmentasi. Fragmentasi tabel terjadi melalui proses modifikasi data (pernyataan INSERT, UPDATE, dan DELETE) yang dibuat terhadap tabel. Karena modifikasi ini biasanya tidak didistribusikan secara merata di antara baris tabel, kepenuhan setiap halaman dapat bervariasi dari waktu ke waktu. Untuk kueri yang memindai bagian atau semua tabel, fragmentasi tabel tersebut dapat menyebabkan pembacaan halaman tambahan. Ini menghambat pemindaian data paralel.
Saat indeks sangat terfragmentasi, pilihan berikut tersedia untuk mengurangi fragmentasi:
Hilangkan dan buat ulang indeks berkluster.
Membuat ulang indeks berkluster mengatur ulang data, dan menyebabkan halaman data lengkap. Tingkat kepenuhan dapat dikonfigurasi dengan menggunakan
FILLFACTORopsi diCREATE INDEX. Kelemahan dari metode ini adalah bahwa indeks offline selama siklus drop atau re-create, dan bahwa operasi adalah atomik. Jika pembuatan indeks terganggu, indeks tidak dibuat ulang.Urutkan ulang halaman tingkat daun indeks dalam urutan logis.
Gunakan
ALTER INDEX...REORGANIZEuntuk menyusun ulang halaman tingkat daun indeks dalam urutan logis. Karena operasi ini adalah operasi online, indeks tersedia saat pernyataan sedang berjalan. Operasi ini juga dapat diinterupsi tanpa kehilangan pekerjaan yang selesai. Kelemahan dari metode ini adalah bahwa metode tidak melakukan pekerjaan yang baik untuk mengatur ulang data sebagai penghilangan indeks berkluster atau membuat ulang operasi.Membangun ulang indeks.
Gunakan
ALTER INDEXdenganREBUILDuntuk membangun kembali indeks. Untuk informasi selengkapnya, lihat ALTER INDEX (T-SQL).
Rata-rata Byte gratis per halaman dan rata-rata statistik kepadatan halaman (penuh) dalam tataan hasil menunjukkan kepenuhan halaman indeks. Angka Rata-rata Byte gratis per nomor halaman harus rendah dan angka Kepadatan halaman (penuh) harus tinggi untuk indeks yang tidak akan memiliki banyak sisipan acak. Menghilangkan dan membuat ulang indeks dengan FILLFACTOR opsi yang ditentukan dapat meningkatkan statistik. Selain itu, ALTER INDEX dengan REORGANIZE akan memampatkan indeks, dengan mempertimbangkan FILLFACTOR, dan akan meningkatkan statistik.
Catatan
Indeks yang memiliki banyak sisipan acak dan halaman yang sangat lengkap akan memiliki peningkatan jumlah pemisahan halaman. Ini menyebabkan lebih banyak fragmentasi.
Tingkat fragmentasi indeks dapat ditentukan dengan cara berikut:
Dengan membandingkan nilai Extent Switches dan Extents Scanned.
Nilai Sakelar Jangkauan harus sedekat mungkin dengan Jangkauan yang Dipindai. Rasio ini dihitung sebagai nilai Kepadatan Pemindaian . Nilai ini harus setinggi mungkin, dan dapat ditingkatkan dengan mengurangi fragmentasi indeks.
Catatan
Metode ini tidak berfungsi jika indeks mencakup beberapa file.
Dengan memahami nilai Fragmentasi Pemindaian Logis dan Fragmentasi Pemindaian Jangkauan .
Fragmentasi Pemindaian Logis dan, hingga tingkat yang lebih rendah, nilai Fragmentasi Pemindaian Jangkauan adalah indikator terbaik dari tingkat fragmentasi tabel. Kedua nilai ini harus sedekat mungkin dengan nol, meskipun nilai dari 0 hingga 10 persen mungkin dapat diterima.
Catatan
Nilai Fragmentasi Pemindaian Jangkauan akan tinggi jika indeks mencakup beberapa file. Untuk mengurangi nilai-nilai ini, Anda harus mengurangi fragmentasi indeks.
Izin
Pengguna harus memiliki tabel, atau menjadi anggota peran server tetap sysadmin , peran database tetap db_owner , atau peran database tetap db_ddladmin .
Contoh
A. Menampilkan informasi fragmentasi untuk tabel
Contoh berikut menampilkan informasi fragmentasi untuk Employee tabel.
USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO
B. Gunakan OBJECT_ID untuk mendapatkan ID tabel dan sys.indexes untuk mendapatkan ID indeks
Contoh berikut menggunakan OBJECT_ID dan sys.indexes tampilan katalog untuk mendapatkan ID tabel dan ID indeks untuk AK_Product_Name indeks Production.Product tabel dalam AdventureWorks2022 database.
USE AdventureWorks2022;
GO
DECLARE @id INT, @indid INT
SET @id = OBJECT_ID('Production.Product');
SELECT @indid = index_id
FROM sys.indexes
WHERE object_id = @id
AND name = 'AK_Product_Name';
DBCC SHOWCONTIG (@id, @indid);
GO
C. Menampilkan kumpulan hasil singkatan untuk tabel
Contoh berikut mengembalikan kumpulan hasil singkatan Product untuk tabel dalam AdventureWorks2022 database.
USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO
D. Menampilkan tataan hasil lengkap untuk setiap indeks pada setiap tabel dalam database
Contoh berikut mengembalikan hasil tabel lengkap yang ditetapkan untuk setiap indeks pada setiap tabel dalam AdventureWorks2022 database.
USE AdventureWorks2022;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO
E. Menggunakan 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 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
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk