Optimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

Artikel ini membantu Anda memutuskan kapan dan bagaimana melakukan pemeliharaan indeks. Ini mencakup konsep seperti fragmentasi indeks dan kepadatan halaman, dan dampaknya pada performa kueri dan konsumsi sumber daya. Ini menjelaskan metode pemeliharaan indeks, mengatur ulang indeks dan membangun kembali indeks, dan menyarankan strategi pemeliharaan indeks yang menyeimbangkan potensi peningkatan performa terhadap konsumsi sumber daya yang diperlukan untuk pemeliharaan.

Catatan

Artikel ini tidak berlaku untuk kumpulan SQL khusus di Azure Synapse Analytics. Untuk informasi tentang pemeliharaan indeks untuk kumpulan SQL khusus di Azure Synapse Analytics, lihat Mengindeks tabel kumpulan SQL khusus di Azure Synapse Analytics.

Konsep: fragmentasi indeks dan kepadatan halaman

Apa itu fragmentasi indeks dan bagaimana dampaknya terhadap performa:

  • Dalam indeks B-tree (rowstore), fragmentasi ada ketika indeks memiliki halaman di mana urutan logis dalam indeks, berdasarkan nilai kunci indeks, tidak cocok dengan pengurutan fisik halaman indeks.

    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.

  • Mesin Database secara otomatis memodifikasi indeks setiap kali operasi sisipkan, perbarui, atau hapus dilakukan pada data yang mendasar. Misalnya, penambahan baris dalam tabel dapat menyebabkan halaman yang ada di indeks rowstore terpisah, sehingga memberi ruang untuk penyisipan baris baru. Seiring waktu modifikasi ini dapat menyebabkan data dalam indeks tersebar dalam database (terfragmentasi).

  • Untuk kueri yang membaca banyak halaman menggunakan pemindaian indeks penuh atau rentang, indeks yang sangat terfragmentasi dapat menurunkan performa kueri karena I/O tambahan mungkin diperlukan untuk membaca data yang diperlukan oleh kueri. Alih-alih sejumlah kecil permintaan I/O besar, kueri akan memerlukan sejumlah besar permintaan I/O kecil untuk membaca jumlah data yang sama.

  • Ketika subsistem penyimpanan memberikan performa I/O berurutan yang lebih baik daripada performa I/O acak, fragmentasi indeks dapat menurunkan performa karena I/O yang lebih acak diperlukan untuk membaca indeks terfragmentasi.

Apa itu kepadatan halaman (juga dikenal sebagai kepenuhan halaman) dan bagaimana dampaknya terhadap performa:

  • Setiap halaman dalam database dapat berisi jumlah variabel baris. Jika baris mengambil semua ruang pada halaman, kepadatan halaman adalah 100%. Jika halaman kosong, kepadatan halaman adalah 0%. Jika halaman dengan kepadatan 100% dibagi dalam dua halaman untuk mengakomodasi baris baru, kepadatan dua halaman baru sekitar 50%.
  • Ketika kepadatan halaman rendah, lebih banyak halaman diperlukan untuk menyimpan jumlah data yang sama. Ini berarti bahwa lebih banyak I/O diperlukan untuk membaca dan menulis data ini, dan lebih banyak memori diperlukan untuk menyimpan data ini. Ketika memori terbatas, lebih sedikit halaman yang diperlukan oleh kueri yang di-cache, menyebabkan lebih banyak I/O disk. Akibatnya, kepadatan halaman rendah berdampak negatif pada performa.
  • Ketika Mesin Database menambahkan baris ke halaman, itu tidak akan mengisi halaman sepenuhnya jika faktor pengisian untuk indeks diatur ke nilai selain 100 (atau 0, yang setara dalam konteks ini). Ini menyebabkan kepadatan halaman yang lebih rendah, dan juga menambahkan overhead I/O dan berdampak negatif pada performa.
  • Kepadatan halaman rendah dapat meningkatkan jumlah tingkat pohon B menengah. Ini secara moderat meningkatkan biaya CPU dan I/O untuk menemukan halaman tingkat daun dalam pemindaian dan pencarian indeks.
  • Saat Pengoptimal Kueri mengkompilasi rencana kueri, pengoptimal kueri mempertimbangkan biaya I/O yang diperlukan untuk membaca data yang diperlukan oleh kueri. Dengan kepadatan halaman rendah, ada lebih banyak halaman untuk dibaca, oleh karena itu biaya I/O lebih tinggi. Ini dapat memengaruhi pilihan rencana kueri. Misalnya, karena kepadatan halaman menurun dari waktu ke waktu karena pemisahan halaman, pengoptimal dapat mengkompilasi rencana yang berbeda untuk kueri yang sama, dengan performa dan profil konsumsi sumber daya yang berbeda.

Tip

Dalam banyak beban kerja, meningkatkan kepadatan halaman menghasilkan dampak performa positif yang lebih besar daripada mengurangi fragmentasi.

Untuk menghindari menurunkan kepadatan halaman yang tidak perlu, Microsoft tidak merekomendasikan pengaturan faktor pengisian ke nilai selain 100 atau 0, kecuali dalam kasus tertentu untuk indeks yang mengalami jumlah pemisahan halaman yang tinggi, misalnya indeks yang sering dimodifikasi dengan kolom terkemuka yang berisi nilai GUID non-berurutan.

Mengukur fragmentasi indeks dan kepadatan halaman

Fragmentasi dan kepadatan halaman adalah salah satu faktor yang perlu dipertimbangkan saat memutuskan apakah akan melakukan pemeliharaan indeks, dan metode pemeliharaan mana yang akan digunakan.

Fragmentasi didefinisikan secara berbeda untuk indeks rowstore dan columnstore . Untuk indeks rowstore, sys.dm_db_index_physical_stats() memungkinkan Anda menentukan fragmentasi dan kepadatan halaman dalam indeks tertentu, semua indeks pada tabel atau tampilan terindeks, semua indeks dalam database, atau semua indeks di semua database. Untuk indeks yang dipartisi, sys.dm_db_index_physical_stats() berikan informasi ini untuk setiap partisi.

Kumpulan hasil yang dikembalikan dengan sys.dm_db_index_physical_stats menyertakan kolom berikut:

Kolom Deskripsi
avg_fragmentation_in_percent Fragmentasi logis (halaman di luar urutan dalam indeks).
avg_page_space_used_in_percent Kepadatan halaman rata-rata.

Untuk grup baris terkompresi dalam indeks penyimpan kolom, fragmentasi didefinisikan sebagai rasio baris yang dihapus terhadap baris total, yang telah dinyatakan sebagai persentase. sys.dm_db_column_store_row_group_physical_stats memungkinkan Anda untuk menentukan jumlah total dan baris yang dihapus per grup baris dalam indeks tertentu, semua indeks pada tabel, atau semua indeks di dalam database.

Kumpulan hasil yang dikembalikan dengan sys.dm_db_column_store_row_group_physical_stats menyertakan kolom berikut:

Kolom Deskripsi
total_rows Jumlah baris yang disimpan secara fisik dalam grup baris. Untuk grup baris terkompresi, ini mencakup baris yang ditandai sebagai dihapus.
deleted_rows Jumlah baris yang disimpan secara fisik dalam grup baris terkompresi yang ditandai untuk dihapus. 0 untuk grup baris yang berada di penyimpanan delta.

Fragmentasi grup baris yang terkompresi dalam indeks penyimpan kolom dapat dihitung menggunakan rumus ini:

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Tip

Untuk indeks rowstore dan penyimpan kolom, sangat penting untuk meninjau indeks atau fragmentasi timbunan dan kepadatan halaman setelah sejumlah besar baris dihapus atau diperbarui. Untuk timbunan, jika ada pembaruan yang sering, mungkin juga diperlukan untuk meninjau fragmentasi secara berkala untuk menghindari proliferasi rekaman penerusan. Untuk informasi selengkapnya tentang timbunan, lihat Timbunan (Tabel tanpa Indeks Berkluster).

Lihat Contoh kueri sampel untuk menentukan fragmentasi dan kepadatan halaman.

Metode pemeliharaan indeks: mengatur ulang dan membangun kembali

Anda dapat mengurangi fragmentasi indeks dan meningkatkan kepadatan halaman dengan menggunakan salah satu metode berikut:

  • Mengatur ulang indeks
  • Menyusun ulang indeks

Catatan

Untuk indeks yang dipartisi , Anda dapat menggunakan salah satu metode berikut pada semua partisi atau satu partisi indeks.

Mengatur ulang indeks

Mengatur ulang indeks lebih sedikit sumber daya intensif daripada membangun ulang indeks. Untuk alasan itu harus menjadi metode pemeliharaan indeks pilihan Anda, kecuali ada alasan khusus untuk menggunakan pembangunan kembali indeks. Mengatur ulang selalu merupakan operasi online. Ini berarti kunci tingkat objek jangka panjang tidak ditahan dan kueri atau pembaruan pada tabel yang mendasarinya dapat dilanjutkan ALTER INDEX ... REORGANIZE selama operasi.

  • Untuk indeks rowstore, Mesin Database hanya mendefinisikan tingkat daun indeks berkluster dan non-kluster pada tabel dan tampilan dengan menyusun ulang halaman tingkat daun secara fisik agar sesuai dengan urutan logis simpul daun (kiri ke kanan). Mengatur ulang juga memampatkan halaman indeks untuk membuat kepadatan halaman sama dengan faktor pengisian indeks. Untuk melihat pengaturan faktor pengisian, gunakan sys.indexes. Untuk contoh sintaks, lihat Contoh - Rowstore mengatur ulang.
  • Saat menggunakan indeks penyimpan kolom, penyimpanan delta mungkin berakhir dengan beberapa grup baris kecil setelah menyisipkan, memperbarui, dan menghapus data dari waktu ke waktu. Mengatur ulang indeks penyimpan kolom memaksa grup baris penyimpanan delta ke dalam grup baris yang terkompresi di penyimpan kolom, dan menggabungkan grup baris terkompresi yang lebih kecil ke dalam grup baris yang lebih besar. Operasi pengaturan ulang juga secara fisik menghapus baris yang telah ditandai sebagai dihapus di dalam penyimpan kolom. Mengatur ulang indeks penyimpan kolom mungkin memerlukan sumber daya CPU tambahan untuk mengompresi data, yang dapat memperlambat performa sistem secara keseluruhan saat operasi berjalan. Namun, setelah data dikompresi, performa kueri meningkat. Untuk contoh sintaks, lihat Contoh - Penyimpan kolom mengatur ulang.

Catatan

Dimulai dengan SQL Server 2019 (15.x), Azure SQL Database, dan Azure SQL Managed Instance, tuple-mover dibantu oleh tugas penggabungan latar belakang yang secara otomatis memadatkan grup baris delta terbuka yang lebih kecil yang telah ada selama beberapa waktu sebagaimana ditentukan oleh ambang batas internal, atau menggabungkan grup baris terkompresi dari tempat sejumlah besar baris telah dihapus. Ini meningkatkan kualitas indeks penyimpan kolom dari waktu ke waktu. Untuk sebagian besar kasus, ini menutup kebutuhan untuk mengeluarkan ALTER INDEX ... REORGANIZE perintah.

Tip

Jika Anda membatalkan operasi reorganisasi, atau jika terganggu, kemajuan yang dibuatnya ke titik tersebut akan disimpan dalam database. Untuk mengatur ulang indeks besar, operasi dapat dimulai dan dihentikan beberapa kali hingga selesai.

Menyusun ulang indeks

Membangun ulang indeks turun dan membuat ulang indeks. Tergantung pada jenis indeks dan versi Mesin Database, operasi pembangunan ulang dapat dilakukan secara offline atau online. Membangun ulang indeks secara offline biasanya akan lebih cepat daripada membangun ulang secara online, tetapi operasi ini memegang kunci tingkat objek selama operasi membangun ulang, sehingga memblokir kueri untuk mengakses tabel atau tampilan.

Membangun ulang indeks online tidak memerlukan kunci tingkat objek sampai akhir operasi, ketika kunci harus dipegang untuk durasi singkat untuk menyelesaikan pembangunan ulang. Tergantung pada versi Mesin Database, membangun ulang indeks online dapat dimulai sebagai operasi yang dapat dilanjutkan. Membangun ulang indeks yang dapat dilanjutkan dapat dijeda, menjaga kemajuan yang dibuat sampai saat itu. Operasi pembangunan ulang yang dapat dilanjutkan setelah dijeda atau diinterupsi, atau dibatalkan jika menyelesaikan pembangunan ulang menjadi tidak perlu.

Untuk sintaks T-SQL, lihat ALTER INDEX REBUILD. Untuk informasi selengkapnya tentang membangun ulang indeks online, lihat Melakukan Operasi Indeks Online.

Catatan

Sementara indeks sedang dibangun kembali secara online, setiap modifikasi data dalam kolom yang diindeks harus memperbarui salinan tambahan indeks. Hal ini dapat mengakibatkan penurunan performa kecil dari pernyataan modifikasi data selama pembangunan ulang online.

Jika operasi indeks resume online dijeda, dampak performa ini berlanjut sampai operasi yang dapat dilanjutkan selesai atau dibatalkan. Jika Anda tidak berniat untuk menyelesaikan operasi indeks yang dapat diulang, batalkan alih-alih menjedanya.

Tip

Bergantung pada sumber daya dan pola beban kerja yang tersedia, menentukan nilai yang lebih tinggi dari nilai default MAXDOP dalam pernyataan ALTER INDEX REBUILD dapat mempersingkat durasi pembangunan kembali dengan mengorbankan pemanfaatan CPU yang lebih tinggi.

  • Untuk indeks rowstore, pembangunan ulang menghapus fragmentasi di semua tingkat indeks, dan memadamkan halaman berdasarkan faktor pengisian yang ditentukan atau saat ini. Ketika ALL ditentukan, semua indeks pada tabel dihilangkan dan dibangun kembali dalam satu operasi. Ketika indeks dengan 128 atau lebih luas dibangun kembali, Mesin Database menunjuk dealokasi halaman dan memperoleh kunci terkait sampai setelah pembangunan kembali selesai. Untuk contoh sintaks, lihat Contoh - Pembangunan ulang rowstore.

  • Untuk indeks penyimpan kolom, pembangunan ulang menghapus fragmentasi, memindahkan baris penyimpanan delta apa pun ke penyimpanan kolom, dan secara fisik menghapus baris yang telah ditandai untuk dihapus. Untuk contoh sintaks, lihat Contoh - Pembangunan ulang columnstore.

    Tip

    Dimulai dengan SQL Server 2016 (13.x), membangun kembali indeks penyimpan kolom biasanya tidak diperlukan karena REORGANIZE melakukan hal-hal penting dari pembangunan kembali sebagai operasi online.

Menggunakan pembangunan ulang indeks untuk memulihkan dari kerusakan data

Dalam versi SQL Server sebelumnya, Anda kadang-kadang dapat membangun kembali indeks yang tidak terkluster rowstore untuk memperbaiki inkonsistensi karena kerusakan data dalam indeks.

Dimulai dengan SQL Server 2008 (10.0.x), Anda mungkin masih dapat memperbaiki inkonsistensi tersebut dalam indeks yang tidak terkluster dengan membangun kembali indeks yang tidak terkluster secara offline. Namun, Anda tidak dapat memperbaiki inkonsistensi indeks non-kluster dengan membangun kembali indeks secara online, karena mekanisme pembangunan ulang online menggunakan indeks non-kluster yang ada sebagai dasar untuk pembangunan kembali dan dengan demikian membawa inkonsistensi. Membangun kembali indeks offline terkadang dapat memaksa pemindaian indeks berkluster (atau tumpukan) sehingga mengganti data yang tidak konsisten dalam indeks non-kluster dengan data dari indeks atau timbunan berkluster.

Untuk memastikan bahwa indeks atau timbunan berkluster digunakan sebagai sumber data, hilangkan dan buat ulang indeks non-kluster alih-alih membangunnya kembali. Seperti versi sebelumnya, sebaiknya pulihkan dari inkonsistensi dengan memulihkan data yang terpengaruh dari cadangan; namun, Anda mungkin dapat memperbaiki inkonsistensi indeks yang tidak terkluster dengan membangunnya kembali secara offline atau membuatnya kembali. Untuk informasi selengkapnya, lihat DBCC CHECKDB (Transact-SQL).

Manajemen indeks dan statistik otomatis

Manfaatkan solusi seperti Defrag Indeks Adaptif untuk mengelola fragmentasi indeks dan pembaruan statistik secara otomatis untuk satu atau beberapa database. Prosedur ini secara otomatis memilih apakah akan membangun kembali atau mengatur ulang indeks sesuai dengan tingkat fragmentasinya, di antara parameter lainnya, dan memperbarui statistik dengan ambang batas linier.

Pertimbangan khusus untuk membangun kembali dan mengatur ulang indeks rowstore

Skenario berikut menyebabkan semua indeks nonclustered rowstore pada tabel dibuat ulang secara otomatis:

  • Membuat indeks berkluster pada tabel, termasuk membuat ulang indeks berkluster dengan kunci yang berbeda menggunakan CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • Menghilangkan indeks berkluster, yang menyebabkan tabel disimpan sebagai tumpukan

Skenario berikut tidak secara otomatis membangun ulang semua indeks nonclustered rowstore pada tabel yang sama:

  • Membangun kembali indeks berkluster
  • Mengubah penyimpanan indeks berkluster, seperti menerapkan skema partisi atau memindahkan indeks berkluster ke grup file yang berbeda

Penting

Indeks tidak dapat diatur ulang atau dibangun ulang jika grup file tempat indeks berada offline atau baca-saja. Ketika kata kunci ALL ditentukan dan satu atau beberapa indeks berada di grup file offline atau baca-saja, pernyataan gagal.

Saat pembangunan ulang indeks terjadi, media fisik harus memiliki ruang yang cukup untuk menyimpan dua salinan indeks. Setelah pembangunan ulang selesai, Mesin Database akan menghapus indeks asli.

Ketika ALL ditentukan dengan ALTER INDEX ... REORGANIZE pernyataan, indeks berkluster, non-kluster, dan XML pada tabel diatur ulang.

Membangun kembali atau mengatur ulang indeks rowstore kecil mungkin tidak mengurangi fragmentasi. Hingga, dan termasuk, SQL Server 2014 (12.x), Mesin Database SQL Server mengalokasikan ruang menggunakan tingkat campuran. Oleh karena itu, halaman indeks kecil terkadang disimpan pada tingkat campuran, yang secara implisit membuat indeks tersebut terfragmentasi. Tingkat campuran dibagikan hingga delapan objek, sehingga fragmentasi dalam indeks kecil mungkin tidak berkurang setelah mengatur ulang atau membangunnya kembali.

Pertimbangan khusus untuk membangun kembali indeks penyimpan kolom

Saat membangun kembali indeks penyimpan kolom, Mesin Database membaca semua data dari indeks penyimpan kolom asli, termasuk penyimpanan delta. Ini menggabungkan data ke dalam grup baris baru, dan memadatkan semua grup baris ke dalam penyimpan kolom. Mesin Database mendefinisikan penyimpan kolom dengan menghapus baris yang telah ditandai sebagai dihapus secara fisik.

Catatan

Dimulai dengan SQL Server 2019 (15.x), penggerak tuple dibantu oleh tugas penggabungan latar belakang yang secara otomatis memadatkan grup baris penyimpanan delta terbuka yang lebih kecil yang telah ada selama beberapa waktu seperti yang ditentukan oleh ambang internal, atau menggabungkan grup baris terkompresi di mana sejumlah besar baris telah dihapus. Ini meningkatkan kualitas indeks penyimpan kolom dari waktu ke waktu. Untuk informasi selengkapnya tentang istilah dan konsep penyimpan kolom, lihat Indeks penyimpan kolom: Gambaran Umum.

Membangun kembali partisi alih-alih seluruh tabel

Membangun ulang seluruh tabel membutuhkan waktu lama jika indeks besar, dan memerlukan ruang disk yang cukup untuk menyimpan salinan tambahan seluruh indeks selama pembangunan ulang.

Untuk tabel yang dipartisi, Anda tidak perlu membangun kembali seluruh indeks penyimpan kolom jika fragmentasi hanya ada di beberapa partisi, misalnya dalam partisi di mana UPDATEpernyataan , , DELETEatau MERGE telah memengaruhi sejumlah besar baris.

Membangun kembali partisi setelah memuat atau memodifikasi data memastikan semua data disimpan dalam grup baris terkompresi di penyimpan kolom. Saat proses pemuatan data menyisipkan data ke dalam partisi menggunakan batch yang lebih kecil dari 102.400 baris, partisi dapat berakhir dengan beberapa grup baris terbuka di penyimpanan delta. Membangun kembali memindahkan semua baris penyimpanan delta ke dalam grup baris terkompresi di penyimpan kolom.

Pertimbangan khusus untuk mengatur ulang indeks penyimpan kolom

Saat mengatur ulang indeks penyimpan kolom, Mesin Database memadatkan setiap grup baris tertutup di penyimpanan delta ke penyimpanan kolom sebagai grup baris terkompresi. Dimulai dengan SQL Server 2016 (13.x) dan di Azure SQL Database, REORGANIZE perintah melakukan pengoptimalan defragmentasi tambahan berikut secara online:

  • Secara fisik menghapus baris dari grup baris ketika 10% atau lebih baris telah dihapus secara logis. Misalnya, jika grup baris terkompresi 1 juta baris memiliki 100.000 baris yang dihapus, Mesin Database menghapus baris yang dihapus dan mengkompresi ulang grup baris dengan 900.000 baris, mengurangi jejak penyimpanan.
  • Menggabungkan satu atau beberapa grup baris terkompresi untuk menambah baris per grup baris, hingga maksimum 1.048.576 baris. Misalnya, jika Anda menyisipkan lima batch massal masing-masing 102.400 baris, Anda akan mendapatkan lima grup baris terkompresi. Jika Anda menjalankan REORGANIZE, grup baris ini akan digabungkan menjadi satu grup baris terkompresi dengan 512.000 baris. Ini mengasumsikan tidak ada ukuran kamus atau batasan memori.
  • Mesin Database mencoba menggabungkan grup baris di mana 10% atau lebih baris telah ditandai sebagai dihapus dengan grup baris lain. Misalnya, grup baris 1 dikompresi dan memiliki 500.000 baris, sementara grup baris 21 dikompresi dan memiliki 1.048.576 baris. Grup Baris 21 memiliki 60% baris yang ditandai sebagai dihapus, yang meninggalkan 409.830 baris. Mesin Database lebih memilih menggabungkan kedua grup baris ini untuk memadatkan grup baris baru yang memiliki 909.830 baris.

Setelah melakukan pemuatan data, Anda dapat memiliki beberapa grup baris kecil di penyimpanan delta. Anda dapat menggunakan ALTER INDEX REORGANIZE untuk memaksa grup baris ini ke penyimpanan kolom, lalu menggabungkan grup baris terkompresi yang lebih kecil ke dalam grup baris terkompresi yang lebih besar. Operasi reorganisasi juga akan menghapus baris yang telah ditandai sebagai dihapus dari penyimpan kolom.

Catatan

Mengatur ulang indeks penyimpan kolom menggunakan Management Studio menggabungkan grup baris terkompresi bersama-sama, tetapi tidak memaksa semua grup baris untuk dikompresi ke dalam penyimpan kolom. Grup baris tertutup akan dikompresi, tetapi grup baris terbuka tidak akan dikompresi ke dalam penyimpan kolom. Untuk memadatkan semua grup baris secara paksa, gunakan contoh Transact-SQL yang menyertakan COMPRESS_ALL_ROW_GROUPS = ON.

Apa yang harus dipertimbangkan sebelum melakukan pemeliharaan indeks

Pemeliharaan indeks, yang dilakukan dengan mengatur ulang atau membangun ulang indeks, bersifat intensif sumber daya. Ini menyebabkan peningkatan signifikan penggunaan CPU, memori yang digunakan, dan I/O penyimpanan. Namun, tergantung pada beban kerja database dan faktor-faktor lain, manfaat yang diberikannya berkisar dari yang sangat penting hingga minuskule.

Untuk menghindari pemanfaatan sumber daya yang tidak perlu yang mungkin merugikan beban kerja kueri, Microsoft tidak menyarankan untuk melakukan pemeliharaan indeks tanpa sembarangan. Sebaliknya, manfaat performa dari pemeliharaan indeks harus ditentukan secara emmpirik untuk setiap beban kerja menggunakan strategi yang direkomendasikan, dan ditimbang terhadap biaya sumber daya dan dampak beban kerja yang diperlukan untuk mencapai manfaat ini.

Kemungkinan melihat manfaat performa dari mengatur ulang atau membangun kembali indeks lebih tinggi ketika indeks sangat terfragmentasi, atau ketika kepadatan halamannya rendah. Namun, ini bukan satu-satunya hal yang perlu dipertimbangkan. Faktor-faktor seperti pola kueri (pemrosesan transaksi vs. analitik dan pelaporan), perilaku subsistem penyimpanan, memori yang tersedia, dan peningkatan mesin database dari waktu ke waktu memainkan peran.

Penting

Keputusan pemeliharaan indeks harus dibuat setelah mempertimbangkan beberapa faktor dalam konteks spesifik setiap beban kerja, termasuk biaya pemeliharaan sumber daya. Mereka tidak boleh didasarkan pada fragmentasi tetap atau ambang kepadatan halaman saja.

Efek samping positif dari pembangunan ulang indeks

Pelanggan sering mengamati peningkatan performa setelah membangun kembali indeks. Namun, dalam banyak kasus, peningkatan ini tidak terkait dengan mengurangi fragmentasi atau meningkatkan kepadatan halaman.

Pembangunan ulang indeks memiliki manfaat penting: memperbarui statistik pada kolom kunci indeks dengan memindai semua baris dalam indeks. Ini setara dengan menjalankan UPDATE STATISTICS ... WITH FULLSCAN, yang membuat statistik saat ini dan kadang-kadang meningkatkan kualitasnya dibandingkan dengan pembaruan statistik sampel default. Saat statistik diperbarui, rencana kueri yang mereferensikannya dikompresi ulang. Jika rencana sebelumnya untuk kueri tidak optimal karena statistik kedaluarsa, rasio pengambilan sampel statistik yang tidak mencukup, atau karena alasan lain, rencana kompilasi ulang akan sering berkinerja lebih baik.

Pelanggan sering salah mengaitkan peningkatan ini dengan pembangunan kembali indeks itu sendiri, mengambilnya sebagai akibat dari pengurangan fragmentasi dan peningkatan kepadatan halaman. Pada kenyataannya, manfaat yang sama sering dapat dicapai dengan biaya sumber daya yang jauh lebih murah dengan memperbarui statistik alih-alih membangun kembali indeks.

Tip

Biaya sumber daya untuk memperbarui statistik kecil dibandingkan dengan pembangunan ulang indeks, dan operasi sering selesai dalam hitungan menit alih-alih jam yang mungkin diperlukan untuk pembangunan ulang indeks.

Strategi pemeliharaan indeks

Microsoft menyarankan agar pelanggan mempertimbangkan dan mengadopsi strategi pemeliharaan indeks berikut:

  • Jangan berasumsi bahwa pemeliharaan indeks akan selalu terlihat meningkatkan beban kerja Anda.
  • Ukur dampak spesifik dari mengatur ulang atau membangun ulang indeks pada performa kueri dalam beban kerja Anda. Penyimpanan Kueri adalah cara yang baik untuk mengukur performa "sebelum pemeliharaan" dan "setelah pemeliharaan" dengan menggunakan teknik pengujian A/B.
  • Jika Anda mengamati bahwa membangun kembali indeks meningkatkan performa, coba ganti dengan memperbarui statistik. Ini dapat mengakibatkan peningkatan yang sama. Dalam hal ini, Anda mungkin tidak perlu membangun ulang indeks sesering mungkin, atau sama sekali, dan sebaliknya dapat melakukan pembaruan statistik berkala. Untuk beberapa statistik, Anda mungkin perlu meningkatkan rasio pengambilan sampel menggunakan WITH SAMPLE ... PERCENT klausa atau WITH FULLSCAN (ini tidak umum).
  • Pantau fragmentasi indeks dan kepadatan halaman dari waktu ke waktu untuk melihat apakah ada korelasi antara nilai-nilai ini yang sedang tren naik atau turun, dan performa kueri. Jika fragmentasi yang lebih tinggi atau performa penurunan kepadatan halaman yang lebih rendah tidak dapat diterima, susun ulang atau bangun ulang indeks. Seringkali cukup untuk hanya mengatur ulang atau membangun kembali indeks tertentu yang digunakan oleh kueri dengan performa yang terdegradasi. Ini menghindari biaya sumber daya yang lebih tinggi untuk mempertahankan setiap indeks dalam database.
  • Membangun korelasi antara fragmentasi/kepadatan halaman dan performa juga memungkinkan Anda menentukan frekuensi pemeliharaan indeks. Jangan berasumsi bahwa pemeliharaan harus dilakukan pada jadwal tetap. Strategi yang lebih baik adalah memantau fragmentasi dan kepadatan halaman, dan menjalankan pemeliharaan indeks sesuai kebutuhan sebelum performa menurun secara tidak dapat diterima.
  • Jika Anda telah menentukan bahwa pemeliharaan indeks diperlukan dan biaya sumber dayanya dapat diterima, lakukan pemeliharaan selama waktu penggunaan sumber daya yang rendah, jika ada, perlu diingat bahwa pola penggunaan sumber daya dapat berubah dari waktu ke waktu.

Pemeliharaan indeks di Azure SQL Database dan Azure SQL Managed Instance

Selain pertimbangan dan strategi di atas, di Azure SQL Database dan Azure SQL Managed Instance, sangat penting untuk mempertimbangkan biaya dan manfaat pemeliharaan indeks. Pelanggan harus melakukannya hanya ketika ada kebutuhan yang ditunjukkan, dan mempertimbangkan poin-poin berikut.

  • Azure SQL Database dan Azure SQL Managed Instance menerapkan tata kelola sumber daya untuk mengatur batas pada konsumsi CPU, memori, dan I/O sesuai dengan tingkat harga yang disediakan. Batas ini berlaku untuk semua beban kerja pengguna, termasuk pemeliharaan indeks. Jika konsumsi sumber daya kumulatif oleh semua beban kerja mendekati batas sumber daya, operasi pembangunan ulang atau reorganisasi dapat menurunkan performa beban kerja lain karena ketidakcocokan sumber daya. Misalnya, beban data massal mungkin menjadi lebih lambat karena I/O log transaksi berada di 100% karena pembangunan ulang indeks bersamaan. Di Azure SQL Managed Instance, dampak ini dapat dikurangi dengan menjalankan pemeliharaan indeks dalam grup beban kerja Resource Governor terpisah dengan alokasi sumber daya terbatas, dengan mengorbankan durasi pemeliharaan indeks yang diperpanjang.
  • Untuk penghematan biaya, pelanggan sering menyediakan database, kumpulan elastis, dan instans terkelola dengan headroom sumber daya minimal. Tingkat harga dipilih agar cukup untuk beban kerja aplikasi. Untuk mengakomodasi peningkatan signifikan penggunaan sumber daya karena pemeliharaan indeks tanpa menurunkan performa aplikasi, pelanggan mungkin harus menyediakan lebih banyak sumber daya dan meningkatkan biaya, tanpa harus meningkatkan performa aplikasi.
  • Di kumpulan elastis, sumber daya dibagikan di semua database dalam kumpulan. Bahkan jika database tertentu menganggur, melakukan pemeliharaan indeks pada database tersebut dapat memengaruhi beban kerja aplikasi yang berjalan bersamaan di database lain di kumpulan yang sama. Untuk informasi selengkapnya, lihat Manajemen sumber daya di kumpulan elastis yang padat.
  • Untuk sebagian besar jenis penyimpanan yang digunakan dalam Azure SQL Database dan Azure SQL Managed Instance, tidak ada perbedaan performa antara I/O berurutan dan I/O acak. Ini mengurangi dampak fragmentasi indeks pada performa kueri.
  • Saat menggunakan replika Read Scale-out atau Geo-replication , latensi data pada replika sering meningkat saat pemeliharaan indeks sedang dilakukan pada replika utama. Jika geo-replika disediakan dengan sumber daya yang tidak mencukupi untuk mempertahankan peningkatan pembuatan log transaksi yang disebabkan oleh pemeliharaan indeks, itu mungkin tertinggal jauh di belakang primer, menyebabkan sistem mengubahnya. Itu membuat replika tidak tersedia sampai reseeding selesai. Selain itu, di tingkat layanan Premium dan Bisnis Kritis, replika yang digunakan untuk ketersediaan tinggi juga mungkin jauh di belakang primer selama pemeliharaan indeks. Jika failover diperlukan selama atau segera setelah pemeliharaan indeks, itu bisa memakan waktu lebih lama dari yang diharapkan.
  • Jika pembangunan ulang indeks berjalan pada replika utama, dan kueri yang berjalan lama dijalankan pada replika yang dapat dibaca pada saat yang sama, kueri mungkin dihentikan secara otomatis untuk mencegah pemblokiran utas pengulangan pada replika.

Ada skenario khusus tetapi tidak jarang ketika pemeliharaan indeks satu kali atau berkala mungkin diperlukan di Azure SQL Database dan Azure SQL Managed Instance:

  • Pemeliharaan indeks mungkin diperlukan untuk meningkatkan kepadatan halaman dan mengurangi ruang yang digunakan dalam database, dan dengan demikian tetap berada dalam batas ukuran tingkat harga. Ini menghindari harus meningkatkan skala ke tingkat harga yang lebih tinggi dengan batas ukuran yang lebih tinggi.
  • Jika perlu untuk menyusutkan file, membangun kembali atau mengatur ulang indeks sebelum menyusutkan file akan meningkatkan kepadatan halaman. Ini membuat operasi penyusutan lebih cepat, karena perlu memindahkan lebih sedikit halaman. Untuk informasi selengkapnya, kunjungi:

Tip

Jika Anda telah menentukan bahwa pemeliharaan indeks diperlukan untuk beban kerja Azure SQL Database dan Azure SQL Managed Instance, Anda harus mengatur ulang indeks, atau menggunakan pembangunan ulang indeks online. Ini memungkinkan beban kerja kueri mengakses tabel saat indeks sedang dibangun kembali.

Selain itu, membuat operasi dapat dilanjutkan memungkinkan Anda menghindari memulai ulang dari awal jika terganggu oleh failover database yang direncanakan atau tidak direncanakan. Menggunakan operasi indeks yang dapat diulang sangat penting ketika indeks besar.

Tip

Operasi indeks offline biasanya selesai lebih cepat daripada operasi online. Mereka harus digunakan ketika tabel tidak akan diakses oleh kueri selama operasi, misalnya setelah memuat data ke dalam tabel penahapan sebagai bagian dari proses ETL berurutan.

Pembatasan dan batasan

Indeks rowstore dengan lebih dari 128 tingkat dibangun kembali dalam dua fase terpisah: logis dan fisik. Dalam fase logis, unit alokasi yang ada yang digunakan oleh indeks ditandai untuk pembatalan alokasi, baris data disalin dan diurutkan, lalu dipindahkan ke unit alokasi baru yang dibuat untuk menyimpan indeks pembangunan ulang. Dalam fase fisik, unit alokasi yang sebelumnya ditandai untuk dealokasi secara fisik dijatuhkan dalam transaksi pendek yang terjadi di latar belakang, dan tidak memerlukan banyak kunci. Untuk informasi selengkapnya tentang unit alokasi, lihat Panduan Arsitektur Halaman dan Tingkat.

Pernyataan ini ALTER INDEX REORGANIZE mengharuskan file data yang berisi indeks memiliki ruang yang tersedia, karena operasi hanya dapat mengalokasikan halaman kerja sementara dalam file yang sama, bukan dalam file lain dalam grup file yang sama. Meskipun grup file mungkin memiliki ruang kosong yang tersedia, pengguna masih dapat mengalami kesalahan 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup selama operasi reorganisasi jika file data kehabisan ruang.

Indeks tidak dapat diatur ulang ketika ALLOW_PAGE_LOCKS diatur ke NONAKTIF.

Hingga SQL Server 2017 (14.x), membangun kembali indeks penyimpan kolom berkluster adalah operasi offline. Mesin Database harus memperoleh kunci eksklusif pada tabel atau partisi saat pembangunan kembali terjadi. Data offline dan tidak tersedia selama pembangunan kembali bahkan saat menggunakan NOLOCK, isolasi rekam jepret yang diterapkan baca (RCSI), atau isolasi rekam jepret. Dimulai dengan SQL Server 2019 (15.x), indeks penyimpan kolom berkluster dapat dibangun kembali menggunakan ONLINE = ON opsi .

Peringatan

Membuat dan membangun kembali indeks yang tidak ditandatangani pada tabel dengan lebih dari 1.000 partisi dimungkinkan, tetapi tidak didukung. Melakukannya dapat menyebabkan penurunan performa atau konsumsi memori yang berlebihan selama operasi ini. Microsoft merekomendasikan untuk hanya menggunakan indeks yang selaras saat jumlah partisi melebihi 1.000.

Batasan statistik

  • Saat indeks dibuat atau dibangun kembali, statistik dibuat atau diperbarui dengan memindai semua baris dalam tabel, yang setara dengan menggunakan FULLSCAN klausa di CREATE STATISTICS atau UPDATE STATISTICS. Namun, dimulai dengan SQL Server 2012 (11.x), ketika indeks yang dipartisi dibuat atau dibangun kembali, statistik tidak dibuat atau diperbarui dengan memindai semua baris dalam tabel. Sebaliknya, rasio pengambilan sampel default digunakan. Untuk membuat atau memperbarui statistik pada indeks yang dipartisi dengan memindai semua baris dalam tabel, gunakan CREATE STATISTICS atau UPDATE STATISTICS dengan FULLSCAN klausul .
  • Demikian pula, ketika pembuatan indeks atau operasi pembangunan ulang dapat dilanjutkan, statistik dibuat atau diperbarui dengan rasio pengambilan sampel default. Jika statistik dibuat atau terakhir diperbarui dengan PERSIST_SAMPLE_PERCENT klausul diatur ke ON, operasi indeks yang dapat dilanjutkan menggunakan rasio pengambilan sampel yang bertahan untuk membuat atau memperbarui statistik.
  • Ketika indeks diatur ulang, statistik tidak diperbarui.

Contoh

Periksa fragmentasi dan kepadatan halaman indeks rowstore menggunakan Transact-SQL

Contoh berikut menentukan fragmentasi rata-rata dan kepadatan halaman untuk semua indeks rowstore dalam database saat ini. Ini menggunakan SAMPLED mode untuk mengembalikan hasil yang dapat ditindakkan dengan cepat. Untuk hasil yang DETAILED lebih akurat, gunakan mode . Ini memerlukan pemindaian semua halaman indeks, dan mungkin memakan waktu lama.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Pernyataan sebelumnya mengembalikan tataan hasil yang mirip dengan yang berikut ini:

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

Untuk informasi selengkapnya, lihat sys.dm_db_index_physical_stats.

Periksa fragmentasi indeks penyimpan kolom menggunakan Transact-SQL

Contoh berikut menentukan fragmentasi rata-rata untuk semua indeks penyimpan kolom dengan grup baris terkompresi dalam database saat ini.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

Pernyataan sebelumnya mengembalikan tataan hasil yang mirip dengan yang berikut ini:

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Mempertahankan indeks menggunakan SQL Server Management Studio

Mengatur ulang atau membangun ulang indeks

  1. Di Object Explorer, perluas database yang berisi tabel tempat Anda ingin mengatur ulang indeks.
  2. Luaskan folder Tabel.
  3. Perluas tabel tempat Anda ingin mengatur ulang indeks.
  4. Perluas folder Indeks.
  5. Klik kanan indeks yang ingin Anda reorganisasi dan pilih Reorganisasi.
  6. Dalam kotak dialog Reorganisasi Indeks , verifikasi bahwa indeks yang benar ada di kisi Indeks yang akan diatur ulang dan pilih OK.
  7. Pilih kotak centang Padatkan data kolom objek besar untuk menentukan bahwa semua halaman yang berisi data objek besar (LOB) juga dipadatkan.
  8. Pilih OK.

Mengatur ulang semua indeks dalam tabel

  1. Di Object Explorer, perluas database yang berisi tabel tempat Anda ingin mengatur ulang indeks.
  2. Luaskan folder Tabel.
  3. Perluas tabel tempat Anda ingin mengatur ulang indeks.
  4. Klik kanan folder Indeks dan pilih Reorganisasi Semua.
  5. Dalam kotak dialog Reorganisasi Indeks , verifikasi bahwa indeks yang benar ada di Indeks yang akan diatur ulang. Untuk menghapus indeks dari indeks yang akan direorganisasi kisi, pilih indeks lalu tekan tombol Hapus.
  6. Pilih kotak centang Padatkan data kolom objek besar untuk menentukan bahwa semua halaman yang berisi data objek besar (LOB) juga dipadatkan.
  7. Pilih OK.

Mempertahankan indeks menggunakan Transact-SQL

Catatan

Untuk contoh selengkapnya tentang menggunakan Transact-SQL untuk membangun kembali atau mengatur ulang indeks, lihat MENGUBAH Contoh INDEKS - Indeks Rowstore dan Mengubah Contoh INDEKS - Indeks Penyimpan Kolom.

Mengatur ulang indeks

Contoh berikut mengatur IX_Employee_OrganizationalLevel_OrganizationalNode ulang indeks pada HumanResources.Employee tabel dalam AdventureWorks2022 database.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

Contoh berikut mengatur ulang indeks penyimpan IndFactResellerSalesXL_CCI kolom pada dbo.FactResellerSalesXL_CCI tabel dalam AdventureWorksDW2022 database. Perintah ini memaksa semua grup baris tertutup dan terbuka ke dalam penyimpan kolom.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Mengatur ulang semua indeks dalam tabel

Contoh berikut mengatur ulang semua indeks pada HumanResources.Employee tabel dalam AdventureWorks2022 database.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Menyusun ulang indeks

Contoh berikut membangun kembali satu indeks pada Employee tabel dalam AdventureWorks2022 database.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Membangun ulang semua indeks dalam tabel

Contoh berikut membangun kembali semua indeks yang terkait dengan tabel dalam AdventureWorks2022 database menggunakan ALL kata kunci. Tiga opsi ditentukan.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

Untuk informasi selengkapnya, lihat MENGUBAH INDEKS.

Langkah berikutnya