Opsi SORT_IN_TEMPDB Untuk Indeks

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Managed Instance Database Azure SQL

Saat Anda membuat atau membangun kembali 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 di sekumpulan disk yang berbeda dari database pengguna. Untuk informasi selengkapnya tentang tempdb, lihat Mengonfigurasi indeks membuat opsi konfigurasi server memori.

Fase Bangunan Indeks

Saat Mesin Database membangun indeks, mesin tersebut melewati fase berikut:

  • Mesin Database pertama-tama 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 nonkluster 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 pengurutan 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 kunci 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, jangkauannya dikosongkan. Karena setiap baris indeks daun diteruskan ke komponen pembuatan indeks, baris tersebut 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 berpindah 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 baca/tulis kepala 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 baca dan tulis secara keseluruhan lebih efisien ketika SORT_IN_TEMPDB ditentukan daripada ketika tidak.

Opsi SORT_IN_TEMPDB dapat meningkatkan kontiguitas 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 dikosongkan, area kerja tersebut dapat diperoleh berdasarkan permintaan sejauh mana untuk menahan struktur indeks saat dibangun. Ini dapat mengacak lokasi indeks hingga satu derajat. Jika jangkauan pengurutan ditahan 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 catatan metadata bahwa indeks tersebut 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 cukup dan ada beberapa alasan database tidak dapat masuk 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 jangkauan eksekusi sortir dibebaskan setelah diproses. Ini berarti bahwa jangkauan eksekusi sortir dikosongkan pada tingkat yang sama di mana tingkat diperoleh untuk menahan 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 dikosongkan. Karena jangkauan 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 jangkauan eksekusi sortir yang baru dialokasikan.

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 cukup dalam 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 indeks diperpanjang 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 ruang kosong yang cukup dalam tempdb untuk menyimpan eksekusi sortir. 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 tabel dan tingkat 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 tabel dan tingkat indeks dapat ditingkatkan jika lebih banyak ruang kosong tersedia.

CREATE INDEX (Transact-SQL)

Mengatur ulang dan Membangun Ulang Indeks

ALTER INDEX (Transact-SQL)

Mengonfigurasi indeks membuat opsi konfigurasi server memori

Persyaratan Ruang Disk untuk Operasi DDL Indeks