sys.dm_db_index_physical_stats (T-SQL)

Artikel
06/19/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Mengembalikan informasi ukuran dan fragmentasi untuk data dan indeks tabel atau tampilan yang ditentukan di SQL Server. Untuk indeks, satu baris dikembalikan untuk setiap tingkat pohon B di setiap partisi. Untuk timbunan, satu baris dikembalikan untuk IN_ROW_DATA unit alokasi setiap partisi. Untuk data objek besar (LOB), satu baris dikembalikan untuk LOB_DATA unit alokasi setiap partisi. Jika data luapan baris ada dalam tabel, satu baris dikembalikan untuk ROW_OVERFLOW_DATA unit alokasi di setiap partisi.

Catatan

Dokumentasi SQL Server menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, SQL Server mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau penyimpanan data dalam memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

sys.dm_db_index_physical_stats tidak mengembalikan informasi tentang indeks penyimpan kolom yang dioptimalkan memori. Untuk informasi tentang penggunaan indeks yang dioptimalkan memori, lihat sys.dm_db_xtp_index_stats (Transact-SQL).

Jika Anda mengkueri sys.dm_db_index_physical_stats instans server yang menghosting replika sekunder yang dapat dibaca grup ketersediaan, Anda mungkin mengalami REDO masalah pemblokiran. Ini karena tampilan manajemen dinamis ini memperoleh IS kunci pada tabel pengguna atau tampilan yang ditentukan yang dapat memblokir permintaan berdasarkan REDO utas untuk X kunci pada tabel atau tampilan pengguna tersebut.

Konvensi sintaks transact-SQL

Sintaks

sys.dm_db_index_physical_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

Argumen

database_id | NULL | 0 | DEFAULT

ID database. database_id kecil. Input yang valid adalah ID database, NULL, 0, atau DEFAULT. Defaultnya adalah 0. NULL, 0, dan DEFAULT, adalah nilai yang setara dalam konteks ini.

Tentukan NULL untuk mengembalikan informasi untuk semua database dalam instans SQL Server. Jika Anda menentukan NULL untuk database_id, Anda juga harus menentukan NULL untuk object_id, index_id, dan partition_number.

Fungsi bawaan DB_ID dapat ditentukan. Saat menggunakan DB_ID tanpa menentukan nama database, tingkat kompatibilitas database saat ini harus 90 atau lebih besar.

object_id | NULL | 0 | DEFAULT

ID objek tabel atau tampilan indeks aktif. object_id adalah int.

Input yang valid adalah ID tabel dan tampilan, NULL, 0, atau DEFAULT. Defaultnya adalah 0. NULL, 0, dan DEFAULT, adalah nilai yang setara dalam konteks ini. Pada SQL Server 2016 (13.x), input yang valid juga menyertakan nama antrean broker layanan atau nama tabel internal antrean. Ketika parameter default diterapkan (yaitu, semua objek, semua indeks, dll.), informasi fragmentasi untuk semua antrean disertakan dalam tataan hasil.

Tentukan NULL untuk mengembalikan informasi untuk semua tabel dan tampilan dalam database yang ditentukan. Jika Anda menentukan NULL untuk object_id, Anda juga harus menentukan NULL untuk index_id dan partition_number.

index_id | 0 | NULL | -1 | DEFAULT

ID indeks. index_id int. Input yang valid adalah ID indeks, 0 jika object_id adalah heap, NULL, -1, atau DEFAULT. Defaultnya adalah -1. NULL, -1, dan DEFAULT, adalah nilai yang setara dalam konteks ini.

Tentukan NULL untuk mengembalikan informasi untuk semua indeks untuk tabel atau tampilan dasar. Jika Anda menentukan NULL untuk index_id, Anda juga harus menentukan NULL untuk partition_number.

partition_number | NULL | 0 | DEFAULT

Nomor partisi dalam objek. partition_number int. Input yang valid adalah partion_number indeks atau timbunan, NULL, 0, atau DEFAULT. Defaultnya adalah 0. NULL, 0, dan DEFAULT, adalah nilai yang setara dalam konteks ini.

Tentukan NULL untuk mengembalikan informasi untuk semua partisi objek pemilik.

partition_number berbasis 1. Indeks atau tumpukan yang tidak dipartisi telah partition_number diatur ke 1.

mode | NULL | DEFAULT

Nama mode. mode menentukan tingkat pemindaian yang digunakan untuk mendapatkan statistik. mode adalah sysname. Input yang valid adalah DEFAULT, NULL, LIMITED, SAMPLED, atau DETAIL. Default (NULL) adalah LIMITED.

Tabel dikembalikan

Nama kolom	Jenis data	Deskripsi
database_id	smallint	ID database tabel atau tampilan. Di Azure SQL Database, nilainya unik dalam satu database atau kumpulan elastis, tetapi tidak dalam server logis.
object_id	int	ID objek tabel atau tampilan tempat indeks berada.
index_id	int	ID indeks indeks. 0 = Timbunan.
partition_number	int	Nomor partisi berbasis 1 dalam objek pemilik; tabel, tampilan, atau indeks. 1 = Indeks atau timbunan yang tidak dipartisi.
index_type_desc	nvarchar(60)	Deskripsi jenis indeks: - HEAP - INDEKS BERKLUSTER - INDEKS NONCLUSTERED - INDEKS XML UTAMA - INDEKS DIPERPANJANG - INDEKS XML - INDEKS PEMETAAN PENYIMPAN KOLOM (internal) - COLUMNSTORE DELETEBUFFER INDEX (internal) - COLUMNSTORE DELETEBITMAP INDEX (internal)
hobt_id	bigint	ID tumpukan atau pohon B dari indeks atau partisi. Untuk indeks penyimpan kolom, ini adalah ID untuk set baris yang melacak data penyimpan kolom internal untuk partisi. Kumpulan baris disimpan sebagai tumpukan data atau pohon B. Mereka memiliki ID indeks yang sama dengan indeks penyimpan kolom induk. Untuk informasi selengkapnya, lihat sys.internal_partitions (Transact-SQL).
alloc_unit_type_desc	nvarchar(60)	Deskripsi jenis unit alokasi: - IN_ROW_DATA - LOB_DATA - ROW_OVERFLOW_DATA Unit `LOB_DATA` alokasi berisi data yang disimpan dalam kolom jenis teks, ntext, gambar, varchar(max), nvarchar(max), varbinary(max), dan xml. Untuk informasi selengkapnya, lihat Jenis Data (Transact-SQL). Unit `ROW_OVERFLOW_DATA` alokasi berisi data yang disimpan dalam kolom jenis varchar(n), nvarchar(n), varbinary(n), dan sql_variant yang telah didorong dari baris.
index_depth	kecil	Jumlah tingkat indeks. 1 = Timbunan, atau `LOB_DATA` atau `ROW_OVERFLOW_DATA` unit alokasi.
index_level	kecil	Tingkat indeks saat ini. 0 untuk tingkat daun indeks, timbunan, dan `LOB_DATA` atau `ROW_OVERFLOW_DATA` unit alokasi. Lebih besar dari 0 untuk tingkat indeks nonleaf. index_level adalah yang tertinggi pada tingkat akar indeks. Tingkat indeks nonleaf hanya diproses saat mode = TERPERINCI.
avg_fragmentation_in_percent	float	Fragmentasi logis untuk indeks, atau fragmentasi tingkat untuk timbunan di `IN_ROW_DATA` unit alokasi. Nilai diukur sebagai persentase dan memperhitungkan beberapa file. Untuk definisi fragmentasi logis dan luas, lihat Komentar. 0 untuk `LOB_DATA` dan `ROW_OVERFLOW_DATA` unit alokasi. NULL untuk timbunan saat mode = SAMPLED.
fragment_count	bigint	Jumlah fragmen dalam tingkat daun unit alokasi `IN_ROW_DATA` . Untuk informasi selengkapnya tentang fragmen, lihat Komentar. NULL untuk tingkat nonleaf indeks, dan `LOB_DATA` atau `ROW_OVERFLOW_DATA` unit alokasi. NULL untuk timbunan saat mode = SAMPLED.
avg_fragment_size_in_pages	float	Jumlah rata-rata halaman dalam satu fragmen dalam tingkat daun unit alokasi `IN_ROW_DATA` . NULL untuk tingkat nonleaf indeks, dan `LOB_DATA` atau `ROW_OVERFLOW_DATA` unit alokasi. NULL untuk timbunan saat mode = SAMPLED.
page_count	bigint	Jumlah total halaman indeks atau data. Untuk indeks, jumlah total halaman indeks di tingkat pohon B saat ini di `IN_ROW_DATA` unit alokasi. Untuk timbunan, jumlah total halaman data di `IN_ROW_DATA` unit alokasi. Untuk `LOB_DATA` unit alokasi atau `ROW_OVERFLOW_DATA` , jumlah total halaman di unit alokasi.
avg_page_space_used_in_percent	float	Persentase rata-rata ruang penyimpanan data yang tersedia yang digunakan di semua halaman. Untuk indeks, rata-rata berlaku untuk tingkat pohon B saat ini di `IN_ROW_DATA` unit alokasi. Untuk timbunan, rata-rata semua halaman data di `IN_ROW_DATA` unit alokasi. Untuk `LOB_DATA` unit alokasi atau `ROW_OVERFLOW_DATA` , rata-rata semua halaman di unit alokasi. NULL saat mode = TERBATAS.
record_count	bigint	Jumlah total rekaman. Untuk indeks, jumlah total rekaman berlaku untuk tingkat pohon B saat ini di `IN_ROW_DATA` unit alokasi. Untuk timbunan, jumlah total rekaman di `IN_ROW_DATA` unit alokasi. Catatan: 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 `LOB_DATA` penyimpanan. Untuk `LOB_DATA` unit alokasi atau `ROW_OVERFLOW_DATA` , jumlah total rekaman di unit alokasi lengkap. NULL saat mode* = TERBATAS.
ghost_record_count	bigint	Jumlah catatan hantu yang siap dihapus oleh tugas pembersihan hantu di unit alokasi. 0 untuk tingkat nonleaf indeks di `IN_ROW_DATA` unit alokasi. NULL saat mode = TERBATAS.
version_ghost_record_count	bigint	Jumlah catatan hantu yang dipertahankan oleh transaksi isolasi rekam jepret yang luar biasa di unit alokasi. 0 untuk tingkat nonleaf indeks di `IN_ROW_DATA` unit alokasi. NULL saat mode = TERBATAS.
min_record_size_in_bytes	int	Ukuran rekaman minimum dalam byte. Untuk indeks, ukuran rekaman minimum berlaku untuk tingkat pohon B saat ini di `IN_ROW_DATA` unit alokasi. Untuk timbunan, ukuran rekaman minimum dalam `IN_ROW_DATA` unit alokasi. Untuk `LOB_DATA` unit alokasi atau `ROW_OVERFLOW_DATA` , ukuran rekaman minimum di unit alokasi lengkap. NULL saat mode = TERBATAS.
max_record_size_in_bytes	int	Ukuran rekaman maksimum dalam byte. Untuk indeks, ukuran rekaman maksimum berlaku untuk tingkat pohon B saat ini di `IN_ROW_DATA` unit alokasi. Untuk timbunan, ukuran rekaman maksimum dalam `IN_ROW_DATA` unit alokasi. Untuk `LOB_DATA` unit alokasi atau `ROW_OVERFLOW_DATA` , ukuran rekaman maksimum di unit alokasi lengkap. NULL saat mode = TERBATAS.
avg_record_size_in_bytes	float	Ukuran rekaman rata-rata dalam byte. Untuk indeks, ukuran rekaman rata-rata berlaku untuk tingkat pohon B saat ini di `IN_ROW_DATA` unit alokasi. Untuk timbunan, ukuran rekaman rata-rata dalam `IN_ROW_DATA` unit alokasi. Untuk `LOB_DATA` unit alokasi atau `ROW_OVERFLOW_DATA` , ukuran rekaman rata-rata dalam unit alokasi lengkap. NULL saat mode = TERBATAS.
forwarded_record_count	bigint	Jumlah rekaman dalam timbunan yang memiliki penunjuk maju ke lokasi data lain. (Status ini terjadi selama pembaruan, ketika tidak ada cukup ruang untuk menyimpan baris baru di lokasi asli.) NULL untuk unit alokasi apa pun selain `IN_ROW_DATA` unit alokasi untuk timbunan. NULL untuk timbunan saat mode = TERBATAS.
compressed_page_count	bigint	Jumlah halaman terkompresi. Untuk timbunan, halaman yang baru dialokasikan tidak dikompresi HALAMAN. Tumpukan dikompresi HALAMAN dalam dua kondisi khusus: ketika data diimpor secara massal atau ketika tumpukan dibangun kembali. Operasi DML umum yang menyebabkan alokasi halaman tidak dikompresi HALAMAN. Bangun kembali tumpukan ketika `compressed_page_count` nilai tumbuh lebih besar dari ambang yang Anda inginkan. Untuk tabel yang memiliki indeks berkluster, `compressed_page_count` nilai menunjukkan efektivitas pemadatan HALAMAN.
columnstore_delete_buffer_state	kecil	0 = NOT_APPLICABLE 1 = BUKA 2 = PENGURASAN 3 = PEMBILASAN 4 = PENSIUN 5 = SIAP Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance
columnstore_delete_buffer_state_desc	nvarchar(60)	TIDAK VALID - indeks induk bukan indeks penyimpan kolom. OPEN - deleter dan scanner menggunakan ini. PENGURASAN - penghapusan menguras keluar tetapi pemindai masih menggunakannya. FLUSHING - buffer ditutup dan baris dalam buffer sedang ditulis ke bitmap penghapusan. PENGHENTIAN - baris dalam buffer penghapusan tertutup telah ditulis ke bitmap penghapusan, tetapi buffer belum dipotong karena pemindai masih menggunakannya. Pemindai baru tidak perlu menggunakan buffer yang dihentikan karena buffer terbuka sudah cukup. SIAP - Buffer penghapusan ini siap digunakan. Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance
version_record_count	bigint	Ini adalah jumlah rekaman versi baris yang dipertahankan dalam indeks ini. Versi baris ini dikelola oleh fitur Pemulihan Database Yang Dipercepat. Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru, dan Azure SQL Database
inrow_version_record_count	bigint	Jumlah rekaman versi ADR yang disimpan di baris data untuk pengambilan cepat. Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru, dan Azure SQL Database
inrow_diff_version_record_count	bigint	Jumlah rekaman versi ADR yang disimpan dalam bentuk perbedaan dari versi dasar. Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru, dan Azure SQL Database
total_inrow_version_payload_size_in_bytes	bigint	Ukuran total dalam byte rekaman versi dalam baris untuk indeks ini. Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru, dan Azure SQL Database
offrow_regular_version_record_count	bigint	Jumlah rekaman versi yang disimpan di luar baris data asli. Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru, dan Azure SQL Database
offrow_long_term_version_record_count	bigint	Jumlah rekaman versi yang dianggap jangka panjang. Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru, dan Azure SQL Database

Catatan

Keterangan

Fungsi sys.dm_db_index_physical_stats manajemen dinamis menggantikan DBCC SHOWCONTIG pernyataan.

Mode pemindaian

Mode di mana fungsi dijalankan menentukan tingkat pemindaian yang dilakukan untuk mendapatkan data statistik yang digunakan oleh fungsi. mode ditentukan sebagai LIMITED, SAMPLED, atau DETAIL. Fungsi ini melintasi rantai halaman untuk unit alokasi yang membentuk partisi tabel atau indeks yang ditentukan. sys.dm_db_index_physical_stats hanya memerlukan kunci tabel Intent-Shared (IS), terlepas dari mode yang dijalankannya.

Mode TERBATAS adalah mode tercepat dan memindai jumlah halaman terkecil. Untuk indeks, hanya halaman tingkat induk pohon B (yaitu, halaman di atas tingkat daun) yang dipindai. Untuk tumpukan, halaman PFS dan IAM terkait diperiksa dan halaman data tumpukan dipindai dalam mode TERBATAS.

Dengan mode TERBATAS, compressed_page_count adalah NULL karena Mesin Database hanya memindai halaman nonleaf dari pohon B dan halaman IAM dan PFS dari tumpukan. Gunakan mode SAMPLED untuk mendapatkan perkiraan nilai untuk compressed_page_count, dan gunakan mode TERPERINCI untuk mendapatkan nilai aktual untuk compressed_page_count. Mode SAMPLED mengembalikan statistik berdasarkan sampel 1 persen dari semua halaman dalam indeks atau timbunan. Hasil dalam mode SAMPLED harus dianggap sebagai perkiraan. Jika indeks atau tumpukan memiliki kurang dari 10.000 halaman, mode TERPERINCI digunakan alih-alih SAMPLED.

Mode TERPERINCI memindai semua halaman dan mengembalikan semua statistik.

Mode semakin lambat dari LIMITED ke DETAILED, karena lebih banyak pekerjaan dilakukan di setiap mode. Untuk mengukur ukuran atau tingkat fragmentasi tabel atau indeks dengan cepat, gunakan mode TERBATAS. Ini adalah yang tercepat dan tidak mengembalikan baris untuk setiap tingkat nonleaf di IN_ROW_DATA unit alokasi indeks.

Menggunakan fungsi sistem untuk menentukan nilai parameter

Anda dapat menggunakan fungsi Transact-SQL DB_ID dan OBJECT_ID untuk menentukan nilai untuk parameter database_id dan object_id . Namun, meneruskan nilai yang tidak valid untuk fungsi-fungsi ini dapat menyebabkan hasil yang tidak diinginkan. Misalnya, jika database atau nama objek tidak dapat ditemukan karena tidak ada atau salah dieja, kedua fungsi mengembalikan NULL. Fungsi ini sys.dm_db_index_physical_stats menginterpretasikan NULL sebagai nilai kartubebas yang menentukan semua database atau semua objek.

Selain itu, fungsi diproses OBJECT_ID sebelum sys.dm_db_index_physical_stats fungsi dipanggil dan oleh karena itu dievaluasi dalam konteks database saat ini, bukan database yang ditentukan dalam database_id. Perilaku ini dapat menyebabkan OBJECT_ID fungsi mengembalikan nilai NULL; atau, jika nama objek ada dalam konteks database saat ini dan database yang ditentukan, pesan kesalahan dapat dikembalikan. Contoh berikut menunjukkan hasil yang tidak diinginkan ini.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database.
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2022;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

Praktik terbaik

Selalu pastikan bahwa ID yang valid dikembalikan saat Anda menggunakan DB_ID atau OBJECT_ID. Misalnya, saat Anda menggunakan OBJECT_ID, tentukan nama tiga bagian seperti OBJECT_ID(N'AdventureWorks2022.Person.Address'), atau uji nilai yang dikembalikan oleh fungsi sebelum Anda menggunakannya dalam sys.dm_db_index_physical_stats fungsi . Contoh A dan B yang mengikuti menunjukkan cara yang aman untuk menentukan ID database dan objek.

Mendeteksi fragmentasi

Fragmentasi terjadi melalui proses modifikasi data (pernyataan INSERT, UPDATE, dan DELETE) yang dibuat terhadap tabel dan, oleh karena itu, ke indeks yang ditentukan pada tabel. Karena modifikasi ini biasanya tidak didistribusikan secara merata di antara baris tabel dan indeks, kepenuhan setiap halaman dapat bervariasi dari waktu ke waktu. Untuk kueri yang memindai bagian atau semua indeks tabel, fragmentasi semacam ini dapat menyebabkan pembacaan halaman tambahan. Ini menghambat pemindaian data paralel.

Tingkat fragmentasi indeks atau tumpukan ditampilkan di avg_fragmentation_in_percent kolom. Untuk tumpukan, nilai mewakili fragmentasi luas dari tumpukan. Untuk indeks, nilai mewakili fragmentasi logis indeks. Tidak seperti DBCC SHOWCONTIG, algoritma perhitungan fragmentasi dalam kedua kasus mempertimbangkan penyimpanan yang mencakup beberapa file dan, oleh karena itu, akurat.

Fragmentasi logis

Ini adalah persentase halaman di luar urutan di halaman daun indeks. 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 tingkat

Ini adalah persentase tingkat di luar urutan di halaman daun tumpuk. Tingkat di luar urutan adalah tingkat yang berisi halaman saat ini untuk timbunan tidak secara fisik tingkat berikutnya setelah sejauh yang berisi halaman sebelumnya.

Nilai untuk avg_fragmentation_in_percent harus sedekat mungkin dengan nol untuk performa maksimum. Namun, nilai dari 0 persen hingga 10 persen mungkin dapat diterima. Semua metode pengurangan fragmentasi, seperti membangun kembali, mengatur ulang, atau membuat ulang, dapat digunakan untuk mengurangi nilai-nilai ini. Untuk informasi selengkapnya tentang cara menganalisis tingkat fragmentasi dalam indeks, lihat Mengatur ulang dan Membangun Ulang Indeks.

Mengurangi fragmentasi dalam indeks

Saat indeks terfragmentasi dengan cara fragmentasi memengaruhi performa kueri, ada tiga pilihan untuk mengurangi fragmentasi:

Hilangkan dan buat ulang indeks berkluster.

Membuat ulang indeks berkluster mendistribusikan ulang data dan menghasilkan halaman data lengkap. Tingkat kepenuhan dapat dikonfigurasi dengan menggunakan opsi FILLFACTOR di CREATE INDEX. Kelemahan dalam metode ini adalah bahwa indeks offline selama siklus penurunan dan pembuatan ulang, dan bahwa operasinya adalah atomik. Jika pembuatan indeks terganggu, indeks tidak dibuat ulang. Untuk informasi selengkapnya, lihat MEMBUAT INDEKS (Transact-SQL).
Gunakan ALTER INDEX REORGANIZE, pengganti , untuk DBCC INDEXDEFRAGmenyusun ulang halaman tingkat daun indeks dalam urutan logis. Karena ini adalah operasi online, indeks tersedia saat pernyataan sedang berjalan. Operasi juga dapat terganggu tanpa kehilangan pekerjaan yang sudah selesai. Kelemahan dalam metode ini adalah tidak melakukan pekerjaan yang baik untuk mengatur ulang data sebagai operasi pembangunan ulang indeks, dan tidak memperbarui statistik.
Gunakan ALTER INDEX REBUILD, pengganti , untuk DBCC DBREINDEXmembangun kembali indeks secara online atau offline. Untuk informasi selengkapnya, lihat ALTER INDEX (T-SQL).

Fragmentasi saja bukan alasan yang memadai untuk mengatur ulang atau membangun ulang indeks. Efek utama fragmentasi adalah memperlambat throughput read-ahead halaman selama pemindaian indeks. Ini menyebabkan waktu respons yang lebih lambat. Jika beban kerja kueri pada tabel atau indeks terfragmentasi tidak melibatkan pemindaian, karena beban kerja terutama pencarian singleton, menghapus fragmentasi mungkin tidak berpengaruh.

Catatan

Menjalankan DBCC SHRINKFILE atau DBCC SHRINKDATABASE dapat memperkenalkan fragmentasi jika indeks sebagian atau sepenuhnya dipindahkan selama operasi penyusutan. Oleh karena itu, jika operasi penyusutan harus dilakukan, Anda harus melakukannya sebelum fragmentasi dihapus.

Mengurangi fragmentasi dalam tumpukan

Untuk mengurangi fragmentasi tingkat timbunan, buat indeks berkluster pada tabel lalu jatuhkan indeks. Ini mendistribusikan ulang data saat indeks berkluster dibuat. Ini juga membuatnya optimal mungkin, mengingat distribusi ruang kosong yang tersedia dalam database. Ketika indeks berkluster kemudian dihilangkan untuk membuat ulang tumpukan, data tidak dipindahkan dan tetap optimal dalam posisi. Untuk informasi tentang cara melakukan operasi ini, lihat MEMBUAT INDEKS dan MENGHILANGKAN INDEKS.

Perhatian

Membuat dan menghilangkan indeks berkluster pada tabel, membangun kembali semua indeks non-kluster pada tabel tersebut dua kali.

Padatkan data objek besar

Secara default, pernyataan ALTER INDEX REORGANIZE memampatkan halaman yang berisi data objek besar (LOB). Karena halaman LOB tidak dialokasikan saat kosong, memampatkan data ini dapat meningkatkan penggunaan ruang disk jika banyak data LOB telah dihapus, atau kolom LOB dihilangkan.

Mengatur ulang indeks berkluster tertentu memampatkan semua kolom LOB yang terkandung dalam indeks berkluster. Mengatur ulang indeks nonclustered memadatkan semua kolom LOB yang merupakan kolom non-kunci (disertakan) dalam indeks. Ketika ALL ditentukan dalam pernyataan, semua indeks yang terkait dengan tabel atau tampilan yang ditentukan diatur ulang. Selain itu, semua kolom LOB yang terkait dengan indeks terkluster, tabel yang mendasar, atau indeks non-kluster dengan kolom yang disertakan, dipadatkan.

Mengevaluasi penggunaan ruang disk

Kolom avg_page_space_used_in_percent menunjukkan kepenuhan halaman. Untuk mencapai penggunaan ruang disk yang optimal, nilai ini harus mendekati 100 persen untuk indeks yang tidak memiliki banyak sisipan acak. Namun, indeks yang memiliki banyak sisipan acak dan memiliki halaman yang sangat penuh memiliki peningkatan jumlah pemisahan halaman. Ini menyebabkan lebih banyak fragmentasi. Oleh karena itu, untuk mengurangi pemisahan halaman, nilainya harus kurang dari 100 persen. Membangun ulang indeks dengan opsi FILLFACTOR yang ditentukan memungkinkan kepenuhan halaman diubah agar sesuai dengan pola kueri pada indeks. Untuk informasi selengkapnya tentang faktor pengisian, lihat Menentukan Faktor Pengisian untuk Indeks. Selain itu, ALTER INDEX REORGANIZE akan mempadatkan indeks dengan mencoba mengisi halaman ke FILLFACTOR yang terakhir ditentukan. Ini meningkatkan nilai dalam avg_space_used_in_percent. ALTER INDEX REORGANIZE tidak dapat mengurangi kepenuhan halaman. Sebagai gantinya, pembangunan ulang indeks harus dilakukan.

Mengevaluasi fragmen indeks

Fragmen terdiri dari halaman daun berturut-turut secara fisik dalam file yang sama untuk unit alokasi. Indeks memiliki setidaknya satu fragmen. Fragmen maksimum yang dapat dimiliki indeks sama dengan jumlah halaman dalam tingkat daun indeks. Fragmen yang lebih besar berarti bahwa I/O disk yang lebih sedikit diperlukan untuk membaca jumlah halaman yang sama. Oleh karena itu, semakin besar nilainya avg_fragment_size_in_pages , semakin baik performa pemindaian rentang. Nilai avg_fragment_size_in_pages dan avg_fragmentation_in_percent berbanding terbalik satu sama lain. Oleh karena itu, membangun kembali atau mengatur ulang indeks harus mengurangi jumlah fragmentasi dan meningkatkan ukuran fragmen.

Pembatasan dan batasan

Tidak mengembalikan data untuk indeks penyimpan kolom berkluster.

Izin

Memerlukan izin berikut:

Izin CONTROL pada objek yang ditentukan dalam database.
LIHAT IZIN STATUS DATABASE atau TAMPILKAN STATUS PERFORMA DATABASE (SQL Server 2022) untuk mengembalikan informasi tentang semua objek dalam database yang ditentukan, dengan menggunakan wildcard objek @object_id=NULL.
LIHAT IZIN STATUS SERVER atau TAMPILKAN STATUS PERFORMA SERVER (SQL Server 2022) untuk mengembalikan informasi tentang semua database, dengan menggunakan kartubebas database @database_id = NULL.

Memberikan STATUS LIHAT DATABASE memungkinkan semua objek dalam database dikembalikan, terlepas dari izin CONTROL apa pun yang ditolak pada objek tertentu.

Menolak TAMPILKAN STATUS DATABASE melarang semua objek dalam database untuk dikembalikan, terlepas dari izin CONTROL apa pun yang diberikan pada objek tertentu. Selain itu, ketika kartubebas database @database_id=NULL ditentukan, database dihilangkan.

Untuk informasi selengkapnya, lihat Tampilan dan Fungsi Manajemen Dinamis (Transact-SQL).

Contoh

J. Mengembalikan informasi tentang tabel tertentu

Contoh berikut mengembalikan statistik ukuran dan fragmentasi untuk semua indeks dan partisi Person.Address tabel. Mode pemindaian diatur ke 'LIMITED' untuk performa terbaik dan untuk membatasi statistik yang dikembalikan. Menjalankan kueri ini memerlukan, minimal, izin CONTROL pada Person.Address tabel.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
  
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address');
  
IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. Mengembalikan informasi tentang timbunan

Contoh berikut mengembalikan semua statistik untuk heap dbo.DatabaseLog dalam database AdventureWorks2022. Karena tabel berisi data LOB, baris dikembalikan untuk LOB_DATA unit alokasi selain baris yang dikembalikan untuk IN_ROW_ALLOCATION_UNIT yang menyimpan halaman data timbunan. Menjalankan kueri ini memerlukan, minimal, izin CONTROL pada dbo.DatabaseLog tabel.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.DatabaseLog');
IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

C. Mengembalikan informasi untuk semua database

Contoh berikut mengembalikan semua statistik untuk semua tabel dan indeks dalam instans SQL Server dengan menentukan wildcard NULL untuk semua parameter. Menjalankan kueri ini memerlukan izin TAMPILKAN STATUS SERVER.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

D. Gunakan `sys.dm_db_index_physical_stats` dalam skrip untuk membangun kembali atau mengatur ulang indeks

Contoh berikut secara otomatis mengatur ulang atau membangun ulang semua partisi dalam database yang memiliki fragmentasi rata-rata lebih dari 10 persen. Menjalankan kueri ini memerlukan izin TAMPILKAN STATUS DATABASE. Contoh ini menentukan DB_ID sebagai parameter pertama tanpa menentukan nama database. Kesalahan dihasilkan jika database saat ini memiliki tingkat kompatibilitas 80 atau lebih rendah. Untuk mengatasi kesalahan, ganti DB_ID() dengan nama database yang valid. Untuk informasi selengkapnya tentang tingkat kompatibilitas database, lihat MENGUBAH Tingkat Kompatibilitas DATABASE (Transact-SQL).

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
    AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;

    FETCH NEXT
    FROM partitions
    INTO @objectid,
        @indexid,
        @partitionnum,
        @frag;

    IF @@FETCH_STATUS < 0
        BREAK;

    SELECT @objectname = QUOTENAME(o.name),
        @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid
        AND index_id = @indexid;

    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid
        AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

E. Gunakan `sys.dm_db_index_physical_stats` untuk memperlihatkan jumlah halaman yang dikompresi halaman

Contoh berikut menunjukkan cara menampilkan dan membandingkan jumlah total halaman dengan halaman yang dikompresi baris dan halaman. Informasi ini dapat digunakan untuk menentukan manfaat yang disediakan kompresi untuk indeks atau tabel.

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count,
    ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count,
    ips.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

F. Gunakan `sys.dm_db_index_physical_stats` dalam mode SAMPLED

Contoh berikut menunjukkan bagaimana mode SAMPLED mengembalikan perkiraan yang berbeda dari hasil mode TERPERINCI.

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

DECLARE @idx INT = 0;

WHILE @idx < 1000000
BEGIN
    INSERT INTO t3 (col1, col2)
    VALUES (
        @idx,
        REPLICATE('a', 100) + CAST(@idx AS VARCHAR(10)) + REPLICATE('a', 380)
        )

    SET @idx = @idx + 1
END

COMMIT;
GO

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'SAMPLED');

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'DETAILED');

G. Antrean broker layanan kueri untuk fragmentasi indeks

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru.

Contoh berikut menunjukkan cara mengkueri antrean broker server untuk fragmentasi.

--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);

--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

sys.dm_db_index_physical_stats (T-SQL)

Sintaks

Argumen

database_id | NULL | 0 | DEFAULT

object_id | NULL | 0 | DEFAULT

index_id | 0 | NULL | -1 | DEFAULT

partition_number | NULL | 0 | DEFAULT

mode | NULL | DEFAULT

Tabel dikembalikan

Keterangan

Mode pemindaian

Menggunakan fungsi sistem untuk menentukan nilai parameter

Praktik terbaik

Mendeteksi fragmentasi

Fragmentasi logis

Fragmentasi tingkat

Mengurangi fragmentasi dalam indeks

Mengurangi fragmentasi dalam tumpukan

Padatkan data objek besar

Mengevaluasi penggunaan ruang disk

Mengevaluasi fragmen indeks

Pembatasan dan batasan

Izin

Contoh

J. Mengembalikan informasi tentang tabel tertentu

B. Mengembalikan informasi tentang timbunan

C. Mengembalikan informasi untuk semua database

D. Gunakan `sys.dm_db_index_physical_stats` dalam skrip untuk membangun kembali atau mengatur ulang indeks

E. Gunakan `sys.dm_db_index_physical_stats` untuk memperlihatkan jumlah halaman yang dikompresi halaman

F. Gunakan `sys.dm_db_index_physical_stats` dalam mode SAMPLED

G. Antrean broker layanan kueri untuk fragmentasi indeks

Baca juga

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

sys.dm_db_index_physical_stats (T-SQL)

Sintaks

Argumen

database_id | NULL | 0 | DEFAULT

object_id | NULL | 0 | DEFAULT

index_id | 0 | NULL | -1 | DEFAULT

partition_number | NULL | 0 | DEFAULT

mode | NULL | DEFAULT

Tabel dikembalikan

Keterangan

Mode pemindaian

Menggunakan fungsi sistem untuk menentukan nilai parameter

Praktik terbaik

Mendeteksi fragmentasi

Fragmentasi logis

Fragmentasi tingkat

Mengurangi fragmentasi dalam indeks

Mengurangi fragmentasi dalam tumpukan

Padatkan data objek besar

Mengevaluasi penggunaan ruang disk

Mengevaluasi fragmen indeks

Pembatasan dan batasan

Izin

Contoh

J. Mengembalikan informasi tentang tabel tertentu

B. Mengembalikan informasi tentang timbunan

C. Mengembalikan informasi untuk semua database

D. Gunakan sys.dm_db_index_physical_stats dalam skrip untuk membangun kembali atau mengatur ulang indeks

E. Gunakan sys.dm_db_index_physical_stats untuk memperlihatkan jumlah halaman yang dikompresi halaman

F. Gunakan sys.dm_db_index_physical_stats dalam mode SAMPLED

G. Antrean broker layanan kueri untuk fragmentasi indeks

Baca juga

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

D. Gunakan `sys.dm_db_index_physical_stats` dalam skrip untuk membangun kembali atau mengatur ulang indeks

E. Gunakan `sys.dm_db_index_physical_stats` untuk memperlihatkan jumlah halaman yang dikompresi halaman

F. Gunakan `sys.dm_db_index_physical_stats` dalam mode SAMPLED