Opsi SORT_IN_TEMPDB Untuk Indeks
Berlaku untuk:
SQL ServerAzure SQL Database Azure SQL Managed Instance
Saat Anda membuat atau membangun ulang indeks, dengan mengatur opsi SORT_IN_TEMPDB ke AKTIF, Anda dapat mengarahkan Mesin Database SQL Server untuk menggunakan tempdb untuk menyimpan hasil pengurutan menengah yang digunakan untuk membangun indeks. Meskipun opsi ini meningkatkan jumlah ruang disk sementara yang digunakan untuk membuat indeks, opsi ini dapat mengurangi waktu yang diperlukan untuk membuat atau membangun kembali indeks ketika tempdb berada pada sekumpulan disk yang berbeda dari database pengguna. Untuk informasi selengkapnya tentang tempdb, lihat Mengonfigurasi indeks buat opsi Konfigurasi Server memori.
Fase Pembangun Indeks
Saat Mesin Database membangun indeks, mesin tersebut melewati fase berikut:
Mesin Database terlebih dahulu memindai halaman data tabel dasar untuk mengambil nilai kunci dan membangun baris daun indeks untuk setiap baris data. Ketika buffer pengurutan internal telah diisi dengan entri indeks daun, entri diurutkan dan ditulis ke disk sebagai eksekusi pengurutan menengah. Mesin Database kemudian melanjutkan pemindaian halaman data hingga buffer pengurutan kembali terisi. Pola pemindaian beberapa halaman data ini diikuti dengan mengurutkan dan menulis eksekusi pengurutan berlanjut hingga semua baris tabel dasar telah diproses.
Dalam indeks berkluster, baris daun indeks adalah baris data tabel; oleh karena itu, eksekusi pengurutan menengah berisi semua baris data. Dalam indeks non-kluster, baris daun mungkin berisi kolom non-kunci, tetapi umumnya lebih kecil dari indeks berkluster. Jika kunci indeks besar, atau ada beberapa kolom non-kunci yang disertakan dalam indeks, eksekusi pengurutan yang tidak terdaftar bisa besar. Untuk informasi selengkapnya tentang menyertakan kolom non-kunci, lihat Membuat Indeks dengan Kolom yang Disertakan.
Mesin Database menggabungkan eksekusi baris daun indeks yang diurutkan ke dalam satu aliran yang diurutkan. Komponen penggabungan sortir dari Mesin Database dimulai dengan halaman pertama dari setiap eksekusi pengurutan, menemukan kunci terendah di semua halaman, dan meneruskan baris daun tersebut ke komponen pembuatan indeks. Kunci terendah berikutnya diproses, lalu yang berikutnya, dan seterusnya. Saat baris indeks daun terakhir diekstrak dari halaman eksekusi pengurutan, proses bergeser ke halaman berikutnya dari eksekusi pengurutan tersebut. Ketika semua halaman dalam jangkauan eksekusi pengurutan telah diproses, sejauh ini dibebesarkan. Karena setiap baris indeks daun diteruskan ke komponen pembuatan indeks, baris ini disertakan dalam halaman indeks daun di buffer. Setiap halaman daun ditulis saat diisi. Saat halaman daun ditulis, Mesin Database juga membangun tingkat atas indeks. Setiap halaman indeks tingkat atas ditulis saat diisi.
Opsi SORT_IN_TEMPDB
Saat SORT_IN_TEMPDB diatur ke NONAKTIF, defaultnya, eksekusi pengurutan disimpan dalam grup file tujuan. Selama fase pertama pembuatan indeks, bacaan bergantian dari halaman tabel dasar dan penulisan eksekusi pengurutan memindahkan kepala baca/tulis disk dari satu area disk ke area lain. Kepala berada di area halaman data saat halaman data dipindai. Mereka pindah ke area ruang kosong ketika buffer pengurutan terisi dan eksekusi pengurutan saat ini harus ditulis ke disk, lalu berpindah kembali ke area halaman data saat pemindaian halaman tabel dilanjutkan. Gerakan kepala baca/tulis lebih besar pada fase kedua. Pada saat itu proses pengurutan biasanya bergantian membaca dari setiap area eksekusi pengurutan. Eksekusi pengurutan dan halaman indeks baru dibangun dalam grup file tujuan. Ini berarti bahwa pada saat yang sama Mesin Database menyebarkan bacaan di seluruh eksekusi pengurutan, mesin database harus secara berkala melompat ke tingkat indeks untuk menulis halaman indeks baru saat diisi.
Jika opsi SORT_IN_TEMPDB diatur ke AKTIF dan tempdb berada pada sekumpulan disk terpisah dari grup file tujuan, selama fase pertama, pembacaan halaman data terjadi pada disk yang berbeda dari penulisan ke area kerja pengurutan di tempdb. Ini berarti pembacaan disk kunci data umumnya berlanjut lebih serial di seluruh disk, dan tulisan ke disk tempdb juga umumnya serial, seperti halnya penulisan untuk membangun indeks akhir. Bahkan jika pengguna lain menggunakan database dan mengakses alamat disk terpisah, pola keseluruhan baca dan tulis lebih efisien ketika SORT_IN_TEMPDB ditentukan daripada ketika tidak.
Opsi SORT_IN_TEMPDB dapat meningkatkan kesesuaian tingkat indeks, terutama jika operasi CREATE INDEX tidak diproses secara paralel. Batas area kerja sortir dibebaskan secara agak acak sehubungan dengan lokasinya dalam database. Jika area kerja pengurutan terkandung dalam grup file tujuan, karena tingkat kerja pengurutan dibebaskan, area kerja tersebut dapat diperoleh oleh permintaan sejauh mana untuk menyimpan struktur indeks saat dibangun. Ini dapat mengacak lokasi indeks sejauh derajat. Jika tingkat pengurutan disimpan secara terpisah dalam tempdb, urutan di mana mereka dibebaskan tidak berpengaruh pada lokasi tingkat indeks. Selain itu, ketika eksekusi pengurutan perantara disimpan dalam tempdb alih-alih grup file tujuan, ada lebih banyak ruang yang tersedia di grup file tujuan. Ini meningkatkan kemungkinan tingkat indeks akan berdekatan.
Opsi SORT_IN_TEMPDB hanya memengaruhi pernyataan saat ini. Tidak ada rekaman metadata yang indeksnya atau tidak diurutkan dalam tempdb. Misalnya, jika Anda membuat indeks non-kluster menggunakan opsi SORT_IN_TEMPDB, dan di lain waktu membuat indeks berkluster tanpa menentukan opsi, Mesin Database tidak menggunakan opsi saat membuat ulang indeks non-kluster.
Catatan
Jika operasi pengurutan tidak diperlukan atau jika pengurutan dapat dilakukan dalam memori, opsi SORT_IN_TEMPDB diabaikan.
Persyaratan Ruang Disk
Saat Anda mengatur opsi SORT_IN_TEMPDB ke AKTIF, Anda harus memiliki ruang disk kosong yang cukup yang tersedia di tempdb untuk menahan eksekusi pengurutan menengah, dan ruang disk kosong yang cukup di grup file tujuan untuk menahan indeks baru. Pernyataan CREATE INDEX gagal jika ruang kosong tidak mencukup dan ada beberapa alasan database tidak dapat secara otomatis untuk memperoleh lebih banyak ruang, seperti tidak ada ruang pada disk atau autogrow yang diatur ke nonaktif.
Jika SORT_IN_TEMPDB diatur ke NONAKTIF, ruang disk kosong yang tersedia di grup file tujuan harus kira-kira ukuran indeks akhir. Selama fase pertama, eksekusi pengurutan dibangun dan memerlukan jumlah ruang yang sama dengan indeks akhir. Selama fase kedua, setiap tingkat eksekusi pengurutan dibesarkan setelah diproses. Ini berarti bahwa batas eksekusi pengurutan dibesarkan pada tingkat yang sama di mana sejauh mana diperoleh untuk menyimpan halaman indeks akhir; oleh karena itu, persyaratan ruang keseluruhan tidak sangat melebihi ukuran indeks akhir. Salah satu efek samping dari ini adalah bahwa jika jumlah ruang kosong sangat dekat dengan ukuran indeks akhir, Mesin Database umumnya akan menggunakan kembali jangkauan eksekusi sortir dengan sangat cepat setelah dibebesarkan. Karena batas eksekusi sortir dibebaskan dengan cara yang agak acak, ini mengurangi kelangsungan tingkat indeks dalam skenario ini. Jika SORT_IN_TEMPDB diatur ke NONAKTIF, kelangsungan tingkat indeks ditingkatkan jika ada cukup ruang kosong yang tersedia di grup file tujuan bahwa tingkat indeks dapat dialokasikan dari kumpulan yang berdekatan alih-alih dari tingkat eksekusi sortir yang baru ditangani.
Saat Anda membuat indeks non-klusster, Anda harus memiliki ruang kosong:
Jika SORT_IN_TEMPDB diatur ke AKTIF, harus ada ruang kosong yang cukup dalam tempdb untuk menyimpan eksekusi pengurutan, dan ruang kosong yang memadai di grup file tujuan untuk menyimpan struktur indeks akhir. Eksekusi pengurutan berisi baris daun indeks.
Jika SORT_IN_TEMPDB diatur ke NONAKTIF, ruang kosong dalam grup file tujuan harus cukup besar untuk menyimpan struktur indeks akhir. Kelangsungan perluasan indeks dapat ditingkatkan jika lebih banyak ruang kosong tersedia.
Saat Anda membuat indeks berkluster pada tabel yang tidak memiliki indeks non-kluster, Anda harus memiliki ruang kosong:
Jika SORT_IN_TEMPDB diatur ke AKTIF, harus ada cukup ruang kosong dalam tempdb untuk menyimpan eksekusi pengurutan. Ini termasuk baris data tabel. Harus ada ruang kosong yang cukup dalam grup file tujuan untuk menyimpan struktur indeks akhir. Ini termasuk baris data tabel dan indeks B-tree. Anda mungkin harus menyesuaikan perkiraan untuk faktor-faktor seperti memiliki ukuran kunci yang besar atau faktor pengisian dengan nilai rendah.
Jika SORT_IN_TEMPDB diatur ke NONAKTIF, ruang kosong dalam grup file tujuan harus cukup besar untuk menyimpan tabel akhir. Ini termasuk struktur indeks. Kelangsungan tingkat tabel dan indeks dapat ditingkatkan jika lebih banyak ruang kosong tersedia.
Saat Anda membuat indeks berkluster pada tabel yang memiliki indeks non-kluster, Anda harus memiliki ruang kosong:
Jika SORT_IN_TEMPDB diatur ke AKTIF, harus ada ruang kosong yang cukup dalam tempdb untuk menyimpan kumpulan eksekusi sortir untuk indeks terbesar, biasanya indeks berkluster, dan ruang kosong yang cukup dalam grup file tujuan untuk menyimpan struktur akhir dari semua indeks. Ini termasuk indeks berkluster yang berisi baris data tabel.
Jika SORT_IN_TEMPDB diatur ke NONAKTIF, ruang kosong dalam grup file tujuan harus cukup besar untuk menyimpan tabel akhir. Ini termasuk struktur semua indeks. Kelangsungan tingkat tabel dan indeks dapat ditingkatkan jika lebih banyak ruang kosong tersedia.
Tugas Terkait
Mengatur ulang dan Membangun Ulang Indeks