Pedoman untuk operasi indeks online

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Saat Anda melakukan operasi indeks online, panduan berikut berlaku:

  • Indeks berkluster harus dibuat, dibangun kembali, atau dihilangkan secara offline saat tabel yang mendasar berisi jenis data objek besar (LOB) berikut: gambar, ntext, dan teks.
  • Indeks nonunique nonclustered dapat dibuat secara online ketika tabel berisi jenis data LOB tetapi tidak ada kolom ini yang digunakan dalam definisi indeks sebagai kolom kunci atau non-kunci (disertakan).
  • Indeks pada tabel temp lokal tidak dapat dibuat, dibangun kembali, atau dihilangkan secara online. Pembatasan ini tidak berlaku bagi indeks pada tabel temp global.
  • Indeks dapat dilanjutkan dari tempatnya berhenti setelah kegagalan tak terduga, kegagalan database, atau PAUSE perintah. Lihat Membuat Indeks dan Mengubah Indeks.

Catatan

Operasi indeks online tidak tersedia di setiap edisi Microsoft SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022.

Tabel berikut menunjukkan operasi indeks yang dapat dilakukan secara online, indeks yang dikecualikan dari operasi online ini, dan pembatasan indeks yang dapat dilanjutkan. Pembatasan tambahan juga disertakan.

Operasi indeks online Indeks yang dikecualikan Pembatasan lain
ALTER INDEX REBUILD Indeks terkluster yang dinonaktifkan atau tampilan terindeks yang dinonaktifkan

Indeks XML

Indeks Penyimpan Kolom

Indeks pada tabel sementara lokal		 Menentukan kata kunci ALL dapat menyebabkan operasi gagal ketika tabel berisi indeks yang dikecualikan.

Pembatasan tambahan untuk membangun kembali indeks yang dinonaktifkan berlaku. Untuk informasi selengkapnya, lihat Menonaktifkan Indeks dan Batasan.
CREATE INDEX Indeks XML

Indeks kluster unik awal pada tampilan

Indeks pada tabel sementara lokal
CREATE INDEX WITH DROP_EXISTING Indeks terkluster yang dinonaktifkan atau tampilan terindeks yang dinonaktifkan

Indeks pada tabel sementara lokal

Indeks XML
DROP INDEX Indeks yang dinonaktifkan

Indeks XML

Indeks nonclustered

Indeks pada tabel sementara lokal		 Beberapa indeks tidak dapat ditentukan dalam satu pernyataan.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY atau UNIQUE) Indeks pada tabel sementara lokal

Indeks dalam kluster		 Hanya satu subklasul yang diizinkan pada satu waktu. Misalnya, Anda tidak dapat menambahkan dan menghilangkan PRIMARY KEY atau UNIQUE membatasi dalam pernyataan yang sama ALTER TABLE .
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY atau UNIQUE) Indeks dalam kluster

Tabel yang mendasar tidak dapat dimodifikasi, dipotong, atau dihilangkan saat operasi indeks online sedang dalam proses.

Pengaturan opsi online (ON atau OFF) yang ditentukan saat Anda membuat atau menghilangkan indeks berkluster diterapkan ke indeks non-kluster apa pun yang harus dibangun kembali. Misalnya, jika indeks berkluster dibangun secara online dengan menggunakan CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, semua indeks non-kluster terkait juga dibuat ulang secara online.

Saat Anda membuat atau membangun UNIQUE ulang indeks secara online, pembangun indeks dan transaksi pengguna bersamaan mungkin mencoba menyisipkan kunci yang sama, oleh karena itu melanggar keunikan. Jika baris yang dimasukkan oleh pengguna dimasukkan ke dalam indeks baru (target) sebelum baris asli dari tabel sumber dipindahkan ke indeks baru, operasi indeks online gagal.

Meskipun tidak umum, operasi indeks online dapat menyebabkan kebuntuan saat berinteraksi dengan pembaruan database karena aktivitas pengguna atau aplikasi. Dalam kasus yang jarang terjadi ini, Mesin Database SQL Server memilih aktivitas pengguna atau aplikasi sebagai korban kebuntuan.

Anda dapat melakukan operasi DDL indeks online bersamaan pada tabel atau tampilan yang sama hanya saat Anda membuat beberapa indeks nonclustered baru, atau mengatur ulang indeks nonclustered. Semua operasi indeks online lainnya yang dilakukan pada saat yang sama gagal. Misalnya, Anda tidak dapat membuat indeks baru secara online saat membangun kembali indeks yang ada secara online pada tabel yang sama.

Operasi online tidak dapat dilakukan ketika indeks berisi kolom jenis objek besar, dan dalam transaksi yang sama ada operasi pembaruan sebelum operasi online ini. Untuk mengatasi masalah ini, tempatkan operasi online di luar transaksi atau tempatkan sebelum pembaruan dalam transaksi.

Pertimbangan ruang disk

Operasi indeks online membutuhkan lebih banyak persyaratan ruang disk daripada operasi indeks offline.

  • Selama pembuatan indeks dan operasi membangun ulang indeks, ruang tambahan diperlukan untuk indeks yang sedang dibangun (atau dibangun ulang).
  • Selain itu, ruang disk diperlukan untuk indeks pemetaan sementara. Indeks sementara ini digunakan dalam operasi indeks online yang membuat, membangun ulang, atau menjatuhkan indeks berkluster.
  • Menghilangkan indeks berkluster secara online membutuhkan ruang sebanyak membuat (atau membangun ulang) indeks berkluster secara online.

Untuk informasi selengkapnya, lihat Persyaratan Ruang Disk untuk Operasi DDL Indeks.

Pertimbangan performa

Meskipun operasi indeks online mengizinkan aktivitas pembaruan pengguna bersamaan, operasi indeks dapat memakan waktu lebih lama jika aktivitas pembaruan sangat berat. Biasanya, operasi indeks online lebih lambat dari operasi indeks offline yang setara, terlepas dari tingkat aktivitas pembaruan bersamaan.

Karena struktur sumber dan target dipertahankan selama operasi indeks online, penggunaan sumber daya untuk menyisipkan, memperbarui, dan menghapus transaksi ditingkatkan, berpotensi hingga dua kali lipat. Ini dapat menyebabkan penurunan performa dan penggunaan sumber daya yang lebih besar, terutama waktu CPU, selama operasi indeks. Operasi indeks online dicatat sepenuhnya.

Meskipun kami merekomendasikan operasi online, Anda harus mengevaluasi lingkungan dan persyaratan khusus Anda. Mungkin optimal untuk menjalankan operasi indeks secara offline. Dengan demikian, akses pengguna ke data dibatasi selama operasi, tetapi operasi selesai lebih cepat dan menggunakan lebih sedikit sumber daya.

Pada komputer multiprosesor yang menjalankan SQL Server 2016 (13.x), pernyataan indeks dapat menggunakan lebih banyak prosesor untuk melakukan operasi pemindaian dan pengurutan yang terkait dengan pernyataan indeks seperti yang dilakukan kueri lain. Anda dapat menggunakan MAXDOP opsi indeks untuk mengontrol jumlah prosesor yang didedikasikan untuk operasi indeks online. Dengan cara ini, Anda dapat menyeimbangkan sumber daya yang digunakan oleh operasi indeks dengan sumber daya pengguna bersamaan. Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel. Untuk informasi selengkapnya tentang edisi SQL Server yang mendukung operasi indeks paralel, lihat Edisi dan fitur yang didukung SQL Server 2022.

Karena kunci S atau kunci Sch-M ditahan pada fase akhir operasi indeks, berhati-hatilah saat Anda menjalankan operasi indeks online di dalam transaksi pengguna eksplisit, seperti BEGIN TRANSACTION ... COMMIT blokir. Tindakan ini bisa menyebabkan kunci ditahan hingga akhir transaksi, sehingga menghambat konkurensi pengguna.

Pembangunan ulang indeks online dapat meningkatkan fragmentasi ketika dijalankan dengan MAXDOP lebih besar dari 1, dan ALLOW_PAGE_LOCKS=OFF. Untuk informasi selengkapnya, lihat Cara Kerjanya: Pembangunan Ulang Indeks Online - Dapat Menyebabkan Peningkatan Fragmentasi.

Pertimbangan log transaksi

Operasi indeks skala besar yang dilakukan secara offline atau online dapat menghasilkan beban data besar, yang menyebabkan log transaksi terisi dengan cepat. Ini karena operasi pembangunan ulang indeks offline dan online sepenuhnya dicatat. Untuk memastikan bahwa operasi indeks dapat digulung balik, log transaksi tidak dapat dipotong sampai operasi indeks selesai; namun, log dapat dicadangkan selama operasi indeks.

Oleh karena itu, log transaksi harus memiliki ruang yang cukup untuk menyimpan transaksi operasi indeks dan transaksi pengguna bersamaan selama operasi indeks. Untuk informasi selengkapnya, lihat Ruang Disk Log Transaksi untuk Operasi Indeks.

Pertimbangan indeks yang dapat dilanjutkan

Opsi indeks yang dapat dilanjutkan untuk membuat indeks dan pembangunan ulang indeks berlaku untuk SQL Server (pembangunan ulang indeks dimulai dengan SQL Server 2017 (14.x), dan membuat indeks yang didukung di SQL Server 2019 (15.x)), dan Azure SQL Database. Untuk informasi selengkapnya, lihat Membuat Indeks dan Mengubah Indeks.

Saat Anda melakukan pembuatan atau pembangunan ulang indeks online yang dapat dilanjutkan, panduan berikut berlaku:

  • Mengelola, merencanakan, dan memperluas jendela pemeliharaan indeks. Anda dapat menjeda dan memulai ulang operasi pembuatan atau pembangunan ulang indeks beberapa kali agar sesuai dengan jendela pemeliharaan Anda.
  • Memulihkan dari pembuatan indeks atau membangun kembali kegagalan (seperti failover database atau kehabisan ruang disk).
  • Ketika operasi indeks dijeda, indeks asli dan yang baru dibuat memerlukan ruang disk dan perlu diperbarui selama operasi DML.
  • Mengaktifkan pemotongan log transaksi selama operasi pembuatan atau pembangunan ulang indeks.
  • SORT_IN_TEMPDB=ON opsi tidak didukung.
  • Indeks yang dinonaktifkan tidak didukung.

Penting

Membuat atau membangun kembali indeks yang dapat dilanjutkan tidak mengharuskan Anda untuk terus membuka transaksi yang berjalan lama, memungkinkan pemotongan log selama operasi ini dan manajemen ruang log yang lebih baik. Dengan desain baru, kami berhasil menyimpan data yang diperlukan dalam database bersama dengan semua referensi yang diperlukan untuk memulai ulang operasi yang dapat dilanjutkan.

Umumnya, tidak ada perbedaan performa antara pembangunan kembali indeks online yang dapat diulang dan tidak dapat ditahan. Untuk membuat indeks yang dapat dilanjutkan, ada overhead konstan yang menyebabkan perbedaan performa kecil antara pembuatan indeks yang dapat dilanjutkan dan tidak dapat dilanjutkan. Perbedaan ini sebagian besar hanya terlihat untuk tabel yang lebih kecil.

Saat Anda memperbarui indeks yang dapat dilanjutkan saat operasi indeks dijeda:

  • Untuk beban kerja baca sebagian besar, efek performa tidak signifikan.
  • Untuk beban kerja yang berat, Anda dapat mengalami beberapa degradasi throughput (pengujian kami menunjukkan kurang dari 10% degradasi).

Umumnya, tidak ada perbedaan dalam kualitas defragmentasi antara pembuatan atau pembangunan kembali indeks online yang dapat dilanjutkan dan tidak dapat dilanjutkan.

Catatan

Saat operasi indeks online dijeda, operasi apa pun yang memerlukan kunci eksklusif tingkat tabel pada tabel yang berisi indeks yang dijeda akan gagal. Ini paling sering ditemui dengan INSERT ... WITH (TABLOCK) operasi. Anda mungkin melihat kesalahan berikut:

Msg 10637, Level 16, State 1, Line 32: Cannot perform this operation on 'object' with ID (objectid) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

Untuk mengatasi kesalahan 10637, hapus TABLOCK petunjuk dari transaksi Anda, atau batalkan jeda operasi indeks dan tunggu hingga selesai sebelum mencoba transaksi Anda lagi.

Opsi default online

Anda dapat mengatur opsi default untuk online atau dapat dilanjutkan pada tingkat database dengan mengatur ELEVATE_ONLINEELEVATE_RESUMABLE atau opsi konfigurasi tercakup database. Dengan opsi default ini, Anda dapat menghindari melakukan operasi yang secara tidak sengaja membuat tabel database Anda offline. Kedua opsi menyebabkan mesin secara otomatis meningkatkan operasi tertentu ke eksekusi online atau yang dapat dilanjutkan.
Anda dapat mengatur salah satu opsi sebagai FAIL_UNSUPPORTED, WHEN_SUPPORTED, atau OFF menggunakan perintah UBAH KONFIGURASI CAKUPAN DATABASE. Anda dapat mengatur nilai yang berbeda untuk online dan dapat diulang.

Baik ELEVATE_ONLINE dan ELEVATE_RESUMABLE hanya berlaku untuk pernyataan DDL yang masing-masing mendukung sintaks online dan dapat diulang. Misalnya, jika Anda mencoba membuat indeks XML dengan ELEVATE_ONLINE=FAIL_UNSUPORTED, operasi akan berjalan offline karena indeks XML tidak mendukung ONLINE= sintaks. Opsi hanya memengaruhi pernyataan DDL yang dikirimkan tanpa menentukan opsi ONLINE atau RESUMABLE. Misalnya, dengan mengirimkan pernyataan dengan ONLINE=OFF atau RESUMABLE=OFF, pengguna dapat mengambil FAIL_UNSUPPORTED alih pengaturan dan menjalankan pernyataan secara offline dan/atau nonresumably.

Catatan

ELEVATE_ONLINE dan ELEVATE_RESUMABLE tidak berlaku untuk operasi indeks XML.

Konten terkait