Tentukan Faktor Isian untuk Indeks

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Topik ini menjelaskan apa itu faktor pengisian dan cara menentukan nilai faktor pengisian pada indeks di SQL Server dengan menggunakan SQL Server Management Studio atau Transact-SQL.

Opsi faktor pengisian disediakan untuk menyempurnakan penyimpanan dan performa data indeks. Ketika indeks dibuat atau dibangun kembali, nilai faktor pengisian menentukan persentase ruang pada setiap halaman tingkat daun untuk diisi dengan data, mempertahankan sisanya di setiap halaman sebagai ruang kosong untuk pertumbuhan di masa mendatang. Misalnya, menentukan nilai faktor pengisian 80 berarti bahwa 20 persen dari setiap halaman tingkat daun akan dibiarkan kosong, menyediakan ruang untuk ekspansi indeks saat data ditambahkan ke tabel yang mendasar. Ruang kosong dicadangkan di antara baris indeks daripada di akhir indeks.

Nilai faktor pengisian adalah persentase dari 1 hingga 100, dan default di seluruh server adalah 0 yang berarti bahwa halaman tingkat daun diisi ke kapasitas.

Catatan

Nilai faktor pengisian 0 dan 100 sama dalam segala hal.

Dalam Topik Ini

• Sebelum Anda mulai:

  Pertimbangan Performa

  Keamanan

• Untuk menentukan faktor pengisian dalam indeks, menggunakan:

  SQL Server Management Studio

  T-SQL

Sebelum Anda mulai

Pertimbangan Performa

Pemisahan Halaman

Nilai faktor pengisian yang dipilih dengan benar dapat mengurangi pemisahan halaman potensial dengan menyediakan ruang yang cukup untuk ekspansi indeks saat data ditambahkan ke tabel yang mendasar. Saat baris baru ditambahkan ke halaman indeks lengkap, Mesin Database memindahkan sekitar setengah baris ke halaman baru untuk memberi ruang bagi baris baru. Reorganisasi ini dikenal sebagai pemisahan halaman. Pemisahan halaman memberi ruang bagi rekaman baru, tetapi dapat memakan waktu untuk melakukan dan merupakan operasi intensif sumber daya. Selain itu, ini dapat menyebabkan fragmentasi yang menyebabkan peningkatan operasi I/O. Ketika pemisahan halaman yang sering terjadi, indeks dapat dibangun kembali dengan menggunakan nilai faktor pengisian baru atau yang sudah ada untuk mendistribusikan ulang data. Untuk informasi selengkapnya, lihat Mengatur ulang dan Membangun Ulang Indeks.

Meskipun nilai faktor pengisian nonzero rendah dapat mengurangi persyaratan untuk membagi halaman saat indeks tumbuh, indeks akan membutuhkan lebih banyak ruang penyimpanan dan dapat mengurangi performa baca. Bahkan untuk aplikasi yang berorientasi untuk banyak operasi sisipan dan pembaruan, jumlah pembacaan database biasanya lebih banyak daripada penulisan database dengan faktor 5 hingga 10. Oleh karena itu, menentukan faktor pengisian selain default dapat mengurangi performa baca database dengan jumlah yang berbanding terbalik dengan pengaturan faktor pengisian. Misalnya, nilai faktor pengisian 50 dapat menyebabkan performa baca database menurun dua kali lipat. Performa baca menurun karena indeks berisi lebih banyak halaman, oleh karena itu meningkatkan operasi IO disk yang diperlukan untuk mengambil data.

Menambahkan Data ke Akhir Tabel

Faktor pengisian bukan nol selain 0 atau 100 bisa baik untuk performa jika data baru didistribusikan secara merata di seluruh tabel. Namun, jika semua data ditambahkan ke akhir tabel, ruang kosong di halaman indeks tidak akan diisi. Misalnya, jika kolom kunci indeks adalah kolom IDENTITY, kunci untuk baris baru selalu meningkat dan baris indeks ditambahkan secara logis ke akhir indeks. Jika baris yang ada akan diperbarui dengan data yang memperpanjang ukuran baris, gunakan faktor pengisian kurang dari 100. Byte tambahan pada setiap halaman akan membantu meminimalkan pemisahan halaman yang disebabkan oleh panjang ekstra dalam baris.

Keamanan

Izin

Memerlukan izin ALTER 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 menentukan faktor pengisian dengan menggunakan Table Designer

1. Di Object Explorer, klik tanda plus untuk memperluas database yang berisi tabel tempat Anda ingin menentukan faktor pengisian indeks.

2. Klik tanda plus untuk memperluas folder Tabel .

3. Klik kanan tabel tempat Anda ingin menentukan faktor pengisian indeks dan pilih Desain.

4. Pada menu Desainer Tabel, klik Indeks/Kunci.

5. Pilih indeks dengan faktor isian yang ingin Anda tentukan.

6. Perluas Spesifikasi Isian, pilih baris Faktor Isian dan masukkan faktor isian yang Anda inginkan di baris.

7. Klik Tutup.

8. Pada menu File , pilih Simpantable_name.

Untuk menentukan faktor pengisian dalam indeks dengan menggunakan Object Explorer

1. Di Object Explorer, klik tanda plus untuk memperluas database yang berisi tabel tempat Anda ingin menentukan faktor pengisian indeks.

2. Klik tanda plus untuk memperluas folder Tabel .

3. Klik tanda plus untuk memperluas tabel tempat Anda ingin menentukan faktor pengisian indeks.

4. Klik tanda plus untuk memperluas folder Indeks .

5. Klik kanan indeks dengan faktor isian yang ingin Anda tentukan dan pilih Properti.

6. Di bawah Pilih halaman, pilih Opsi.

7. Di baris Faktor isian, masukkan faktor isian yang Anda inginkan.

8. Klik OK.

Menggunakan T-SQL

Untuk menentukan faktor pengisian dalam indeks yang ada

1. Di Object Explorer, sambungkan ke instans Mesin Database.

2. Pada bilah Standar, klik Kueri Baru.

3. Salin dan tempel contoh berikut ke dalam jendela kueri dan klik Jalankan. Contoh membangun kembali indeks yang ada dan menerapkan faktor pengisian yang ditentukan selama operasi pembangunan ulang.

   USE AdventureWorks2022;  
   GO  
   -- Rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index   
   -- with a fill factor of 80 on the HumanResources.Employee table.  
   
   ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
   REBUILD WITH (FILLFACTOR = 80);   
   GO  
   

Cara lain untuk menentukan faktor pengisian dalam indeks

1. Di Object Explorer, sambungkan ke instans Mesin Database.

2. Pada bilah Standar, klik Kueri Baru.

3. Salin dan tempel contoh berikut ke dalam jendela kueri dan klik Jalankan.

   USE AdventureWorks2022;  
   GO  
   -- Drops and re-creates the IX_Employee_OrganizationLevel_OrganizationNode index
   -- on the HumanResources.Employee table with a fill factor of 80.   
   
   CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
      (OrganizationLevel, OrganizationNode)   
   WITH (DROP_EXISTING = ON, FILLFACTOR = 80);   
   GO  
   

Untuk informasi selengkapnya, lihat ALTER INDEX (T-SQL).