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

Konvensi sintaks Transact-SQL

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 SHOWCONTIG hanya menampilkan partisi pertama dari tabel atau indeks yang ditentukan.
• DBCC SHOWCONTIG tidak 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 FILLFACTOR opsi di CREATE 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...REORGANIZE untuk 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 INDEX dengan REBUILD untuk 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

• ALTER INDEX (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• DBCC (Transact-SQL)
• DROP INDEX (Transact-SQL)
• sys.dm_db_index_physical_stats (Transact-SQL)
• OBJECT_ID (Transact-SQL)
• sys.indexes (Transact-SQL)