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 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 ]
         }
    ]

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_id index_name

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 kumpulan hasil.

Statistik	Deskripsi
Halaman Yang Dipindai	Jumlah halaman dalam tabel atau indeks.
Jangkauan yang Dipindai	Jumlah tingkat dalam tabel atau indeks.
Peralihan Tingkat	Berapa kali pernyataan DBCC berpindah dari satu tingkat ke tingkat lain sementara pernyataan melintasi halaman tabel atau indeks.
Rata-rata Halaman per Tingkat	Jumlah halaman per tingkat dalam rantai halaman.
Kepadatan Pemindaian [Jumlah 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 yang ideal jika semuanya ditautkan secara berdekatan. Jumlah Aktual adalah jumlah perubahan yang sebenarnya.
Fragmentasi Pemindaian Logis	Persentase halaman di luar urutan yang dikembalikan dari memindai halaman daun indeks. Jumlah ini tidak relevan dengan timbunan. Halaman di luar urutan adalah halaman di mana halaman fisik berikutnya yang dialokasikan ke indeks bukan halaman yang diarahkan oleh pointer e pagberikutnya di halaman daun saat ini.
Fragmentasi Pemindaian Tingkat Luas	Persentase tingkat di luar urutan dalam memindai halaman daun indeks. Jumlah ini tidak relevan dengan timbunan. Tingkat di luar urutan adalah tingkat yang berisi halaman saat ini untuk indeks tidak secara fisik tingkat berikutnya setelah sejauh 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. Angka ini juga dipengaruhi oleh ukuran baris; ukuran baris yang besar dapat menyebabkan jumlah 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 Tingkat
• Kepadatan Pemindaian [Jumlah Terbaik:Jumlah Aktual]
• Fragmentasi Pemindaian Tingkat Luas
• 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 tumpuk.
Tingkat	Tingkat indeks. Tingkat 0 adalah daun, atau data, tingkat 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 dalam seluruh timbunan. Untuk timbunan, jumlah rekaman yang dikembalikan dari fungsi ini mungkin tidak cocok dengan jumlah baris yang dikembalikan dengan menjalankan SELECT COUNT(*) terhadap timbunan. Ini karena baris mungkin berisi beberapa rekaman. Misalnya, di bawah 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 timbunan.
MaximumRecordSize	Ukuran rekaman maksimum dalam tingkat indeks atau seluruh timbunan.
AverageRecordSize	Ukuran rekaman rata-rata dalam tingkat indeks atau seluruh timbunan.
ForwardedRecords	Jumlah rekaman yang diteruskan dalam tingkat indeks atau seluruh timbunan.
Tingkat	Jumlah tingkat dalam tingkat indeks atau seluruh timbunan.
ExtentSwitches	Berapa kali pernyataan DBCC berpindah dari satu tingkat ke tingkat lain sementara 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. Angka ini juga dipengaruhi oleh ukuran baris; ukuran baris yang besar dapat menyebabkan jumlah 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 untuk ActualCount. Nilai ini adalah 100 jika semuanya bersebelah; jika nilai ini kurang dari 100, beberapa fragmentasi ada.
BestCount	Jumlah ideal perubahan tingkat jika semuanya ditautkan secara berdingin.
ActualCount	Jumlah aktual perubahan tingkat.
LogicalFragmentation	Persentase halaman di luar urutan yang dikembalikan dari memindai halaman daun indeks. Jumlah ini tidak relevan dengan timbunan. Halaman di luar urutan adalah halaman di mana halaman fisik berikutnya yang dialokasikan ke indeks bukan halaman yang diarahkan 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 tingkat yang berisi halaman saat ini untuk indeks tidak secara fisik tingkat berikutnya setelah sejauh 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 dengan kapan WITH TABLERESULTS ditentukan, kecuali kolom berikut akan memiliki nilai null:

Baris	Tingkat
MinimumRecordSize	AverageFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
ForwardedRecords

Keterangan

Pernyataan DBCC SHOWCONTIG melintasi rantai halaman pada tingkat daun indeks yang ditentukan ketika 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 penyisipan 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 digunakan hanya 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 tidak berlaku untuk tumpukan.

Batasan

DBCC SHOWCONTIG tidak menampilkan data dengan tipe data ntext, teks, dan gambar . Ini karena indeks teks yang menyimpan data teks dan gambar tidak lagi ada.

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.

Ketika 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.

• Susun 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 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).

Statistik rata-rata Byte gratis per halaman dan Rata-rata kepadatan halaman (penuh) dalam kumpulan hasil menunjukkan kepenuhan halaman indeks. Angka rata-rata Byte gratis per halaman harus rendah dan angka kepadatan halaman Rata-rata (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 Tingkat harus sedekat mungkin dengan Tingkat 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 fragmentasi Pemindaian Logis dan nilai Fragmentasi Pemindaian Tingkat Luas.

  Fragmentasi Pemindaian Logis dan, hingga tingkat yang lebih rendah, nilai Fragmentasi Pemindaian Tingkat 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 Tingkat Tinggi 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

J. 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 yang disingkat untuk Product tabel dalam AdventureWorks2022 database.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO

D. Menampilkan hasil lengkap yang ditetapkan 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. 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

• ALTER INDEX (Transact-SQL)
• BUAT INDEKS (Transact-SQL)
• DBCC (T-SQL)
• DROP INDEX (Transact-SQL)
• sys.dm_db_index_physical_stats (T-SQL)
• OBJECT_ID (T-SQL)
• sys.indexes (Transact-SQL)