Mengatur Ulang dan Membangun Ulang Indeks
Topik ini menjelaskan cara mengatur ulang atau membangun kembali indeks terfragmentasi di SQL Server 2014 dengan menggunakan SQL Server Management Studio atau Transact-SQL. Mesin SQL Server Database secara otomatis mempertahankan indeks setiap kali operasi sisipkan, perbarui, atau hapus dilakukan pada data yang mendasar. Seiring waktu, modifikasi ini dapat menyebabkan informasi dalam indeks tersebar dalam database (terfragmentasi). Fragmentasi ada ketika indeks memiliki halaman di mana pengurutan logis, berdasarkan nilai kunci, tidak cocok dengan urutan fisik di dalam file data. Indeks yang sangat terfragmentasi dapat menurunkan performa kueri dan menyebabkan aplikasi Anda merespons dengan lambat.
Anda dapat memperbaiki fragmentasi indeks dengan mengatur ulang atau membangun ulang indeks. Untuk indeks yang dipartisi yang dibangun pada skema partisi, Anda dapat menggunakan salah satu metode ini pada indeks lengkap atau partisi tunggal indeks. Membangun ulang indeks turun dan membuat ulang indeks. Ini menghapus fragmentasi, merebut kembali ruang disk dengan memampatkan halaman berdasarkan pengaturan faktor pengisian yang ditentukan atau yang ada, dan menyusun ulang baris indeks di halaman yang berdekatan. Ketika ALL ditentukan, semua indeks pada tabel dihilangkan dan dibangun kembali dalam satu transaksi. Mengatur ulang indeks menggunakan sumber daya sistem minimal. Ini mendefrori tingkat daun indeks berkluster dan non-kluster pada tabel dan tampilan dengan menyusun ulang halaman tingkat daun secara fisik agar sesuai dengan urutan node daun yang logis, kiri ke kanan. Mengatur ulang juga memampatkan halaman indeks. Pemadatan didasarkan pada nilai faktor pengisian yang ada.
Dalam Topik Ini
Sebelum Anda memulai:
Untuk memeriksa fragmentasi indeks, gunakan:
Untuk mengatur ulang atau membangun ulang indeks, menggunakan:
Sebelum Anda mulai
Mendeteksi Fragmentasi
Langkah pertama dalam memutuskan metode defragmentasi mana yang akan digunakan adalah menganalisis indeks untuk menentukan tingkat fragmentasi. Dengan menggunakan fungsi sistem sys.dm_db_index_physical_stats, Anda dapat mendeteksi fragmentasi 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 juga menyediakan informasi fragmentasi untuk setiap partisi.
Tataan hasil yang dikembalikan oleh fungsi sys.dm_db_index_physical_stats menyertakan kolom berikut.
| Kolom | Deskripsi |
|---|---|
| avg_fragmentation_in_percent | Persentase fragmentasi logis (halaman di luar urutan dalam indeks). |
| fragment_count | Jumlah fragmen (halaman daun berturut-turut secara fisik) dalam indeks. |
| avg_fragment_size_in_pages | Jumlah rata-rata halaman dalam satu fragmen dalam indeks. |
Setelah tingkat fragmentasi diketahui, gunakan tabel berikut untuk menentukan metode terbaik untuk memperbaiki fragmentasi.
| nilai avg_fragmentation_in_percent | Pernyataan korektif |
|---|---|
| > 5% dan < = 30% | UBAH REORGANISASI INDEKS |
| > 30% | UBAH PEMBANGUNAN ULANG INDEKS DENGAN (ONLINE = AKTIF) 1 |
1 Membangun ulang indeks dapat dijalankan secara online atau offline. Mengatur ulang indeks selalu dijalankan secara online. Untuk mencapai ketersediaan yang mirip dengan opsi reorganisasi, Anda harus membangun kembali indeks secara online.
Tip
Nilai-nilai ini memberikan pedoman kasar untuk menentukan titik di mana Anda harus beralih antara ALTER INDEX REORGANIZE dan ALTER INDEX REBUILD. Namun, nilai aktual dapat bervariasi dari kasus ke kasus. Penting bagi Anda untuk bereksperimen untuk menentukan ambang batas terbaik untuk lingkungan Anda. Misalnya, jika indeks tertentu digunakan terutama untuk operasi pemindaian, menghapus fragmentasi dapat meningkatkan performa operasi ini. Manfaat performa kurang terlihat untuk indeks yang digunakan terutama untuk operasi pencarian. Demikian pula, menghapus fragmentasi dalam tumpukan (tabel tanpa indeks berkluster) sangat berguna untuk operasi pemindaian indeks non-kluster, tetapi memiliki sedikit efek dalam operasi pencarian.
Tingkat fragmentasi yang sangat rendah (kurang dari 5 persen) biasanya tidak boleh ditangani oleh salah satu perintah ini, karena manfaat dari menghapus sejumlah kecil fragmentasi hampir selalu jauh lebih besar daripada biaya mengatur ulang atau membangun kembali indeks.
Catatan
Membangun kembali atau mengatur ulang indeks kecil sering kali tidak mengurangi fragmentasi. Halaman indeks kecil terkadang disimpan pada tingkat campuran. Tingkat campuran dibagikan hingga delapan objek, sehingga fragmentasi dalam indeks kecil mungkin tidak berkurang setelah mengatur ulang atau membangunnya kembali.
Pertimbangan defragmentasi indeks
Dalam kondisi tertentu, membangun kembali indeks berkluster akan secara otomatis membangun kembali indeks non-kluster yang mereferensikan kunci pengklusteran, jika pengidentifikasi fisik atau logis yang terkandung dalam rekaman indeks non-kluster perlu berubah.
Skenario yang memaksa semua indeks nonkluster untuk dibangun kembali secara otomatis pada tabel:
- Membuat indeks berkluster pada tabel
- Menghapus indeks berkluster, menyebabkan tabel disimpan sebagai tumpukan
- Mengubah kunci pengklusteran untuk menyertakan atau mengecualikan kolom
Skenario yang tidak mengharuskan semua indeks non-kluster dibuat ulang secara otomatis pada tabel:
- Membangun kembali indeks berkluster yang unik
- Membangun kembali indeks berkluster yang tidak unik
- Mengubah skema indeks, seperti menerapkan skema partisi ke indeks berkluster atau memindahkan indeks berkluster ke grup file yang berbeda
Batasan dan Pembatasan
Indeks 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 dihilangkan dalam transaksi singkat yang terjadi di latar belakang, dan tidak memerlukan banyak kunci. Untuk informasi selengkapnya tentang jangkauan, lihat Panduan Arsitektur Halaman dan Jangkauan.
Pernyataan ALTER INDEX REORGANIZE ini mengharuskan file data yang berisi indeks memiliki ruang yang tersedia, karena operasi hanya dapat mengalokasikan halaman kerja sementara pada file yang sama, bukan file lain dalam grup file. Jadi meskipun grup file mungkin memiliki halaman gratis 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.
Membuat dan membangun kembali indeks yang tidak selaras pada tabel dengan lebih dari 1.000 partisi dimungkinkan, tetapi tidak disarankan. Melakukannya dapat menyebabkan penurunan performa atau konsumsi memori yang berlebihan selama operasi ini.
Indeks tidak dapat diatur ulang atau dibangun ulang jika grup file tempat indeks berada offline atau diatur ke baca-saja. Ketika kata kunci ALL ditentukan dan satu atau beberapa indeks berada dalam grup file offline atau baca-saja, pernyataan gagal.
Keamanan
Izin
ALTER Memerlukan izin pada tabel atau tampilan. Pengguna harus menjadi anggota peran server tetap sysadmin atau peran database tetap db_ddladmin dan db_owner .
Menggunakan SQL Server Management Studio
Untuk memeriksa fragmentasi indeks
Di Object Explorer, Perluas database yang berisi tabel tempat Anda ingin memeriksa fragmentasi indeks.
Luaskan folder Tabel.
Perluas tabel tempat Anda ingin memeriksa fragmentasi indeks.
Perluas folder Indeks .
Klik kanan indeks yang ingin Anda periksa fragmentasinya dan pilih Properti.
Di bawah Pilih halaman, pilih Fragmentasi.
Informasi berikut ini tersedia di halaman Fragmentasi :
Kepenuhan halaman
Menunjukkan kepenuhan rata-rata halaman indeks, sebagai persentase. 100% berarti halaman indeks benar-benar penuh. 50% berarti bahwa, rata-rata, setiap halaman indeks setengah penuh.Total fragmentasi
Persentase fragmentasi logis. Ini menunjukkan jumlah halaman dalam indeks yang tidak disimpan secara berurutan.Ukuran baris rata-rata
Ukuran rata-rata baris tingkat daun.Kedalaman
Jumlah tingkat dalam indeks, termasuk tingkat daun.Rekaman yang diteruskan
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.)Baris hantu
Jumlah baris yang ditandai sebagai dihapus tetapi belum dihapus. Baris-baris ini akan dihapus oleh utas pembersihan, ketika server tidak sibuk. Nilai ini tidak termasuk baris yang dipertahankan karena transaksi isolasi rekam jepret yang luar biasa.Jenis indeks
Jenis indeks. Nilai yang mungkin adalah indeks Berkluster, Indeks non-kluster, dan XML Utama. Tabel juga dapat disimpan sebagai timbunan (tanpa indeks), tetapi kemudian halaman Properti Indeks ini tidak dapat dibuka.Baris tingkat daun
Jumlah baris tingkat daun.Ukuran baris maksimum
Ukuran baris tingkat daun maksimum.Ukuran baris minimum
Ukuran baris tingkat daun minimum.Halaman
Jumlah total halaman data.ID partisi
ID partisi pohon b yang berisi indeks.Baris hantu versi
Jumlah catatan hantu yang dipertahankan karena transaksi isolasi rekam jepret yang luar biasa.
Menggunakan T-SQL
Untuk memeriksa fragmentasi indeks
Di Object Explorer, sambungkan ke instans Mesin Database.
Pada bilah Standar, klik Kueri Baru.
Salin dan tempel contoh berikut ke dalam jendela kueri dan klik Jalankan.
USE AdventureWorks2012; GO -- Find the average fragmentation percentage of all indexes -- in the HumanResources.Employee table. SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GOPernyataan di atas mungkin mengembalikan tataan hasil yang mirip dengan yang berikut ini.
index_id name avg_fragmentation_in_percent ----------- ----------------------------------------------------- ---------------------------- 1 PK_Employee_BusinessEntityID 0 2 IX_Employee_OrganizationalNode 0 3 IX_Employee_OrganizationalLevel_OrganizationalNode 0 5 AK_Employee_LoginID 66.6666666666667 6 AK_Employee_NationalIDNumber 50 7 AK_Employee_rowguid 0 (6 row(s) affected)
Untuk informasi selengkapnya, lihat sys.dm_db_index_physical_stats (Transact-SQL).
Menggunakan SQL Server Management Studio
Untuk mengatur ulang atau membangun ulang indeks
Di Object Explorer, Perluas database yang berisi tabel tempat Anda ingin mengatur ulang indeks.
Luaskan folder Tabel.
Perluas tabel tempat Anda ingin mengatur ulang indeks.
Perluas folder Indeks .
Klik kanan indeks yang ingin Anda reorganisasi dan pilih Reorganisasi.
Dalam kotak dialog Reorganisasi Indeks , verifikasi bahwa indeks yang benar ada di kisi Indeks yang akan diatur ulang dan klik OK.
Pilih kotak centang Padatkan data kolom objek besar untuk menentukan bahwa semua halaman yang berisi data objek besar (LOB) juga dipadatkan.
Klik OK.
Untuk mengatur ulang semua indeks dalam tabel
Di Object Explorer, Perluas database yang berisi tabel tempat Anda ingin mengatur ulang indeks.
Luaskan folder Tabel.
Perluas tabel tempat Anda ingin mengatur ulang indeks.
Klik kanan folder Indeks dan pilih Reorganisasi Semua.
Dalam kotak dialog Reorganisasi Indeks , verifikasi bahwa indeks yang benar berada dalam Indeks yang akan diatur ulang. Untuk menghapus indeks dari kisi yang akan diatur ulang , pilih indeks lalu tekan tombol Hapus.
Pilih kotak centang Padatkan data kolom objek besar untuk menentukan bahwa semua halaman yang berisi data objek besar (LOB) juga dipadatkan.
Klik OK.
Untuk membangun kembali indeks
Di Object Explorer, Perluas database yang berisi tabel tempat Anda ingin mengatur ulang indeks.
Luaskan folder Tabel.
Perluas tabel tempat Anda ingin mengatur ulang indeks.
Perluas folder Indeks .
Klik kanan indeks yang ingin Anda reorganisasi dan pilih Reorganisasi.
Dalam kotak dialog Bangun Ulang Indeks , verifikasi bahwa indeks yang benar berada dalam Indeks untuk dibangun kembali kisi dan klik OK.
Pilih kotak centang Padatkan data kolom objek besar untuk menentukan bahwa semua halaman yang berisi data objek besar (LOB) juga dipadatkan.
Klik OK.
Menggunakan T-SQL
Untuk mengatur ulang indeks yang didefragmentasi
Di Object Explorer, sambungkan ke instans Mesin Database.
Pada bilah Standar, klik Kueri Baru.
Salin dan tempel contoh berikut ke dalam jendela kueri dan klik Jalankan.
USE AdventureWorks2012; GO -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE ; GO
Untuk mengatur ulang semua indeks dalam tabel
Di Object Explorer, sambungkan ke instans Mesin Database.
Pada bilah Standar, klik Kueri Baru.
Salin dan tempel contoh berikut ke dalam jendela kueri dan klik Jalankan.
USE AdventureWorks2012; GO -- Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; GO
Untuk membangun kembali indeks yang didefragmentasi
Di Object Explorer, sambungkan ke instans Mesin Database.
Pada bilah Standar, klik Kueri Baru.
Salin dan tempel contoh berikut ke dalam jendela kueri dan klik Jalankan. Contoh ini membangun kembali satu indeks pada
Employeetabel.USE AdventureWorks2012; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO
Untuk membangun kembali semua indeks dalam tabel
Di Object Explorer, sambungkan ke instans Mesin Database.
Pada bilah Standar, klik Kueri Baru.
Salin dan tempel contoh berikut ke dalam kueri Contoh menentukan kata kunci
ALL. Ini membangun kembali semua indeks yang terkait dengan tabel. Tiga opsi ditentukan.USE AdventureWorks2012; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO
Untuk informasi selengkapnya, lihat ALTER INDEX (T-SQL).
Lihat juga
Praktik Terbaik Defragmentasi Indeks Microsoft SQL Server 2000