sys.dm_db_index_physical_stats (T-SQL)
Berlaku untuk: SQL ServerAzure 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 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.
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
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.
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 INDEXDEFRAG
menyusun 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 DBREINDEX
membangun 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);
Lihat juga
Saran dan Komentar
https://aka.ms/ContentUserFeedback.
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat:Kirim dan lihat umpan balik untuk