sys.dm_db_index_physical_stats (T-SQL)

Artikel
10/04/2024

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 Mesin Database 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 menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, Mesin Database mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau indeks pada tabel yang dioptimalkan 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 yang dioptimalkan memori. Untuk informasi tentang penggunaan indeks yang dioptimalkan memori, lihat sys.dm_db_xtp_index_stats.

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 kunci Intent-Shared (IS) pada tabel pengguna atau tampilan yang ditentukan yang dapat memblokir permintaan berdasarkan REDO utas untuk kunci Eksklusif (X) 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, 0atau DEFAULT. Default adalah 0. NULL, 0, dan DEFAULT merupakan 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 Anda 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, , 0atau DEFAULT. Default adalah 0. NULL, 0, dan DEFAULT merupakan nilai yang setara dalam konteks ini.

Di SQL Server 2016 (13.x) dan versi yang lebih baru, 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 tumpukan, NULL, -1, atau DEFAULT. Default adalah -1. NULL, -1, dan DEFAULT merupakan 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 tumpukan, NULL, , 0atau DEFAULT. Default adalah 0. NULL, 0, dan DEFAULT merupakan 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, , LIMITEDNULL, SAMPLED, atau DETAILED. Defaultnya (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 tumpukan yang tidak dipartisi.
`index_type_desc`	nvarchar(60)	Deskripsi jenis indeks: - `HEAP` - `CLUSTERED INDEX` - `NONCLUSTERED INDEX` - `PRIMARY XML INDEX` - `EXTENDED INDEX` - `XML INDEX` - `COLUMNSTORE MAPPING INDEX` (internal) - `COLUMNSTORE DELETEBUFFER INDEX` (internal) - `COLUMNSTORE DELETEBITMAP INDEX` (internal)
`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. Unit `ROW_OVERFLOW_DATA` alokasi berisi data yang disimpan dalam kolom jenis varchar(n), nvarchar(n), varbinary(n), dan sql_variant yang didorong dari baris.
`index_depth`	kecil	Jumlah tingkat indeks. `1` = Timbunan, atau `LOB_DATA` unit `ROW_OVERFLOW_DATA` 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 ketika mode adalah `DETAILED`.
`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` unit alokasi dan `ROW_OVERFLOW_DATA` . `NULL` untuk timbunan ketika mode adalah `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 ketika mode adalah `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 ketika mode adalah `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` ketika mode adalah `LIMITED`.
`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 dapat 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 `LOB_DATA` penyimpanan. Untuk `LOB_DATA` unit alokasi atau `ROW_OVERFLOW_DATA` , jumlah total rekaman di unit alokasi lengkap. `NULL` ketika mode* adalah `LIMITED`.
`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` ketika mode adalah `LIMITED`.
`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` ketika mode adalah `LIMITED`.
`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` ketika mode adalah `LIMITED`.
`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` ketika mode adalah `LIMITED`.
`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` ketika mode adalah `LIMITED`.
`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 ketika mode adalah `LIMITED`.
`compressed_page_count`	bigint	Jumlah halaman terkompresi. Untuk timbunan, halaman yang baru dialokasikan tidak `PAGE` dikompresi. Timbunan dikompresi `PAGE` dalam dua kondisi khusus: ketika data diimpor secara massal atau ketika timbunan dibangun kembali. Operasi DML umum yang menyebabkan alokasi halaman tidak `PAGE` dikompresi. 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 `PAGE` .
`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.
`columnstore_delete_buffer_state`	kecil	`0` = `NOT_APPLICABLE` `1` = `OPEN` `2` = `DRAINING` `3` = `FLUSHING` `4` = `RETIRING` `5` = `READY` 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)	`NOT VALID` - indeks induk bukan indeks penyimpan kolom. `OPEN` - deleter dan pemindai menggunakan ini. `DRAINING` - deleter menguras keluar tetapi pemindai masih menggunakannya. `FLUSHING` - buffer ditutup dan baris dalam buffer sedang ditulis ke bitmap penghapusan. `RETIRING` - baris dalam buffer penghapusan tertutup 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. `READY` - 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, , SAMPLEDatau DETAILED. 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.

LIMITED Mode ini 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 LIMITED mode.

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

Mode DETAILED 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 LIMITED mode . 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 menafsirkan 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 NULL nilai; atau, jika nama objek ada dalam konteks database saat ini dan database yang ditentukan, pesan kesalahan 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 (INSERT, UPDATE, dan DELETE pernyataan) 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 lebih banyak pembacaan halaman, yang 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 penunjuk halaman berikutnya 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 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 Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

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 FILLFACTOR opsi di CREATE INDEX. Kelemahan dalam metode ini adalah bahwa indeks offline selama siklus drop dan pembuatan ulang, dan bahwa operasinya adalah atomik. Jika pembuatan indeks terganggu, indeks tidak dibuat ulang. Untuk informasi selengkapnya, lihat MEMBUAT INDEKS.
Gunakan ALTER INDEX REORGANIZE, penggantian untuk , 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 , untuk DBCC DBREINDEXmembangun kembali indeks 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 tidak akan 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 memampatkan ALTER INDEX REORGANIZE halaman yang berisi data objek besar (LOB). Karena halaman LOB tidak dibatalkan alokasinya saat kosong, memadamkan data ini dapat meningkatkan penggunaan ruang disk jika banyak data LOB 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 yang ditentukan memungkinkan kepenuhan halaman diubah agar sesuai dengan FILLFACTOR 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.

Batasan

Tidak mengembalikan data untuk indeks penyimpan kolom berkluster.

Izin

Memerlukan izin berikut:

CONTROL izin pada objek yang ditentukan dalam database.
VIEW DATABASE STATEatau VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) izin untuk mengembalikan informasi tentang semua objek dalam database yang ditentukan, dengan menggunakan wildcard objek @object_id = NULL.
VIEW SERVER STATEatau VIEW SERVER PERFORMANCE STATE (SQL Server 2022) izin untuk mengembalikan informasi tentang semua database, dengan menggunakan kartubebas database @database_id = NULL.

Pemberian VIEW DATABASE STATE izin memungkinkan semua objek dalam database dikembalikan, terlepas dari izin apa pun CONTROL yang ditolak pada objek tertentu.

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

Untuk informasi selengkapnya, lihat Tampilan manajemen dinamis sistem.

Contoh

Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

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, CONTROL izin 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 timbunan dbo.DatabaseLog AdventureWorks2022 dalam database. 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, CONTROL izin 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 VIEW SERVER STATE izin.

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

D. Menggunakan 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 VIEW DATABASE STATE izin. Contoh ini menentukan DB_ID sebagai parameter pertama tanpa menentukan nama database.

-- 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. Menggunakan sys.dm_db_index_physical_stats dalam mode SAMPLED

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

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);

Bagikan melalui

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

Batasan

Izin

Contoh

J. Mengembalikan informasi tentang tabel tertentu

B. Mengembalikan informasi tentang timbunan

C. Mengembalikan informasi untuk semua database

D. Menggunakan 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. Menggunakan sys.dm_db_index_physical_stats dalam mode SAMPLED

G. Antrean broker layanan kueri untuk fragmentasi indeks

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

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

Batasan

Izin

Contoh

J. Mengembalikan informasi tentang tabel tertentu

B. Mengembalikan informasi tentang timbunan

C. Mengembalikan informasi untuk semua database

D. Menggunakan 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. Menggunakan sys.dm_db_index_physical_stats dalam mode SAMPLED

G. Antrean broker layanan kueri untuk fragmentasi indeks

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: