Meningkatkan performa indeks teks lengkap

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Artikel ini membahas penyebab umum performa buruk untuk indeks dan kueri teks lengkap, dan cara menguranginya.

Penyebab umum masalah performa

Bagian ini menjelaskan penyebab masalah performa umum saat Anda menggunakan indeks teks lengkap.

Masalah sumber daya perangkat keras

Sumber daya perangkat keras seperti memori, kecepatan disk, kecepatan CPU, dan arsitektur mesin memengaruhi performa pengindeksan teks lengkap dan kueri teks lengkap.

Batas sumber daya perangkat keras menyebabkan berkurangnya performa pengindeksan teks lengkap.

• CPU. Jika penggunaan CPU oleh proses host daemon filter (fdhost.exe) atau proses SQL Server (sqlservr.exe) mendekati 100 persen, CPU adalah hambatan.

• Memori. Kekurangan memori fisik dapat menyebabkan kemacetan.

• Disk. Jika panjang antrean tunggu disk rata-rata lebih dari dua kali jumlah kepala disk, ada kemacetan pada disk. Solusi utamanya adalah membuat katalog teks lengkap yang terpisah dari file dan log database SQL Server. Letakkan log, file database, dan katalog teks lengkap pada disk terpisah. Menginstal disk yang lebih cepat dan menggunakan RAID juga dapat membantu meningkatkan performa pengindeksan.

Masalah pengelompokan teks lengkap

Jika sistem tidak memiliki kemacetan perangkat keras, performa pengindeksan pencarian teks lengkap sebagian besar bergantung pada faktor-faktor berikut:

• Berapa lama waktu yang dibutuhkan Database Engine untuk membuat batch teks lengkap.

• Seberapa cepat daemon filter mengkonsumsi batch tersebut.

Masalah populasi indeks teks lengkap

• Jenis populasi. Tidak seperti populasi penuh, populasi pelacakan perubahan inkremental, manual, dan otomatis tidak dirancang untuk memaksimalkan sumber daya perangkat keras untuk mencapai kecepatan yang lebih cepat. Oleh karena itu, saran penyetelan dalam artikel ini mungkin tidak meningkatkan performa untuk pengindeksan teks lengkap saat menggunakan populasi pelacakan perubahan inkremental, manual, atau otomatis.

• Penggabungan master. Ketika populasi selesai, proses penggabungan akhir menggabungkan fragmen indeks menjadi satu indeks teks lengkap master . Proses ini menghasilkan peningkatan performa kueri karena hanya indeks master yang perlu dikueri daripada sejumlah fragmen indeks. Statistik penilaian yang lebih baik dapat digunakan untuk peringkat relevansi. Namun, penggabungan utama dapat memerlukan I/O yang intensif karena sejumlah besar data harus ditulis dan dibaca saat fragmen indeks digabungkan. Namun, tidak memblokir kueri yang masuk.

  Penggabungan master data dalam jumlah besar dapat menyebabkan transaksi berjalan lama, sehingga menunda pemotongan log transaksi selama checkpoint. Dalam hal ini, di bawah model pemulihan penuh, log transaksi mungkin tumbuh secara signifikan. Sebagai praktik terbaik, sebelum mengatur ulang indeks teks lengkap besar dalam database yang menggunakan model pemulihan penuh, pastikan bahwa log transaksi Anda berisi ruang yang cukup untuk transaksi yang berjalan lama. Untuk informasi selengkapnya, lihat Mengelola ukuran file log transaksi.

Menyetel performa indeks teks lengkap

Untuk memaksimalkan performa indeks teks lengkap Anda, terapkan praktik terbaik berikut:

• Untuk menggunakan semua inti CPU secara maksimal, ubah max full-text crawl range ke jumlah inti pada sistem. Untuk informasi selengkapnya, lihat Konfigurasi server: rentang perayapan teks lengkap maks.

• Pastikan bahwa tabel dasar memiliki indeks berkluster. Gunakan jenis data bilangan bulat untuk kolom pertama indeks berkluster. Hindari menggunakan GUID di kolom pertama indeks berkluster. Populasi multi-rentang pada indeks berkluster dapat menghasilkan kecepatan populasi tertinggi. Gunakan tipe data bilangan bulat untuk kolom yang berfungsi sebagai kunci teks lengkap.

• Perbarui statistik tabel dasar dengan menggunakan UPDATE STATISTICS pernyataan . Lebih penting lagi, perbarui statistik pada indeks berkerumun atau kunci teks lengkap untuk seluruh populasi. Langkah ini membantu populasi dengan beberapa rentang untuk menghasilkan partisi yang baik pada tabel.

• Sebelum Anda melakukan pengisian data penuh pada komputer multi-core berukuran besar, batasi sementara ukuran pool buffer dengan menetapkan nilai max server memory agar tersedia cukup memori untuk proses fdhost.exe dan penggunaan sistem operasi. Untuk informasi selengkapnya, lihat Memperkirakan persyaratan memori proses host daemon filter (fdhost.exe), nanti di artikel ini.

• Jika Anda menggunakan populasi inkremental berdasarkan kolom tanda waktu, buat indeks sekunder pada kolom tersebut untuk meningkatkan performa populasi inkremental.

Mengatasi masalah kinerja populasi penuh

Lihat bagian berikut untuk menyelesaikan masalah performa dengan populasi penuh.

Meninjau log perayapan teks lengkap

Untuk membantu mendiagnosis masalah performa, tinjau log crawling teks lengkap.

Ketika kesalahan terjadi selama perayapan, fasilitas pengelogan perayapan Pencarian Teks Lengkap membuat dan memelihara log perayapan, yang merupakan file teks biasa. Setiap log perayapan sesuai dengan katalog teks lengkap tertentu. Secara default, log perayapan untuk instans tertentu (dalam contoh ini, instans default) terletak di %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG folder.

File log perayapan mengikuti skema penamaan berikut:

SQLFT<DatabaseID><FullTextCatalogID>.[<n>]

Bagian variabel dari nama file log perayapan adalah sebagai berikut.

• <DatabaseID>: ID database, sebagai angka lima digit dengan nol di depan.

• <FullTextCatalogID>: ID katalog teks lengkap, sebagai angka lima digit dengan nol di depan.

• <n>: Bilangan bulat yang menunjukkan ada satu atau beberapa log crawling dari katalog teks lengkap yang sama.

Misalnya, SQLFT0000500008.2 adalah file log perayapan untuk database dengan ID database = 5, dan ID katalog teks lengkap = 8. 2 di akhir nama file menunjukkan bahwa ada dua file log perayapan untuk pasangan database/katalog ini.

Periksa penggunaan memori fisik

Selama pengisian teks lengkap, proses fdhost.exe atau sqlservr.exe dapat mengalami kekurangan memori, atau bahkan kehabisan memori.

• Jika log perayapan teks lengkap menunjukkan bahwa fdhost.exe sering dimulai ulang atau mengembalikan kode kesalahan 8007008, itu berarti salah satu proses ini kehabisan memori.

• Jika fdhost.exe menghasilkan dump, terutama pada sistem besar dengan banyak inti, program tersebut mungkin kehabisan memori.

• Untuk informasi tentang buffer memori yang digunakan oleh perayapan teks lengkap, lihat sys.dm_fts_memory_buffers.

Kemungkinan penyebab masalah memori rendah atau kehabisan memori mencakup item berikut:

• Memori tidak cukup. Jika jumlah memori fisik yang tersedia selama populasi penuh adalah nol, kumpulan buffer Database Engine mungkin menghabiskan sebagian besar memori fisik pada sistem.

  Proses ini sqlservr.exe mencoba mengambil semua memori yang tersedia untuk kumpulan buffer, hingga memori server maksimum yang dikonfigurasi. Jika alokasi max server memory terlalu besar, kondisi kehabisan memori dan kegagalan untuk mengalokasikan memori bersama dapat terjadi untuk proses tersebut fdhost.exe .

  Atur max server memory nilai kumpulan buffer Database Engine dengan tepat untuk mengatasi masalah ini. Untuk informasi selengkapnya, lihat Memperkirakan persyaratan memori proses host daemon filter (fdhost.exe), nanti di artikel ini. Mengurangi ukuran batch yang digunakan untuk pengindeksan teks lengkap juga dapat membantu.

• Persaingan memori. Selama populasi teks lengkap pada sistem multi-core, fdhost.exe dan sqlservr.exe dapat bersaing untuk memori kumpulan buffer. Kurangnya memori bersama yang dihasilkan menyebabkan percobaan ulang batch, pemukulan memori, dan pembuangan oleh fdhost.exe prosesnya.

• Masalah pemanggilan. Ukuran berkas halaman yang tidak mencukupi, seperti pada sistem yang memiliki berkas halaman kecil dengan pertumbuhan yang dibatasi, juga dapat menyebabkan proses fdhost.exe atau sqlservr.exe kehabisan memori. Jika log perayapan tidak menunjukkan adanya kegagalan terkait memori, paging yang berlebihan kemungkinan menjadi penyebab lambatnya kinerja.

Perkirakan kebutuhan memori dari proses host daemon filter (fdhost.exe)

Jumlah memori yang diperlukan oleh proses fdhost.exe untuk pengisian data terutama bergantung pada jumlah rentang perayapan teks lengkap yang digunakannya, ukuran memori bersama masuk (ISM), dan jumlah maksimum instans ISM.

Anda dapat memperkirakan konsumsi memori host daemon filter secara kasar dengan menggunakan rumus berikut:

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

Nilai default untuk variabel dalam rumus sebelumnya adalah sebagai berikut:

Tabel berikut menyajikan panduan untuk memperkirakan persyaratan fdhost.exememori . Rumus dalam tabel ini menggunakan nilai berikut:

• F, yang merupakan perkiraan memori yang dibutuhkan oleh fdhost.exe (dalam MB).

• T, yang merupakan total memori fisik yang tersedia pada sistem (dalam MB).

• M, yang merupakan pengaturan optimal max server memory .

Untuk informasi penting tentang rumus berikut ini, lihat catatan yang mengikuti tabel.

1. Jika beberapa populasi penuh sedang berlangsung, hitung fdhost.exe kebutuhan memori masing-masing secara terpisah, seperti F1, F2, dan seterusnya. Kemudian hitung M sebagai T - Σ(Fi).

2. 500 MB adalah perkiraan memori yang diperlukan oleh proses lain dalam sistem. Jika sistem melakukan pekerjaan tambahan, tingkatkan nilai ini sesuai kebutuhan.

3. ism_size diasumsikan menjadi 8 MB untuk platform x64.

Contoh: Memperkirakan kebutuhan memori fdhost.exe

Contoh ini untuk komputer 64-bit yang memiliki RAM 8 GB dan 4 prosesor dual-core. Perhitungan pertama memperkirakan memori yang dibutuhkan oleh fdhost.exeF. Jumlah rentang crawl adalah 8.

F = 8 * 10 * 8 = 640

Perhitungan berikutnya memperoleh nilai optimal untuk max server memory (M). Total memori fisik yang tersedia pada sistem ini dalam MB, (T), adalah 8192.

M = 8192 - 640 - 500 = 7052

Contoh: Set max server memory

Contoh ini menggunakan pernyataan sp_configure dan RECONFIGURE Transact-SQL untuk mengatur max server memory ke nilai yang dihitung untuk M dalam contoh sebelumnya, 7052:

USE master;
GO

EXECUTE sp_configure 'max server memory', 7052;
GO

RECONFIGURE;
GO

Untuk informasi selengkapnya tentang opsi memori server, lihat Opsi konfigurasi memori server.

Periksa penggunaan CPU

Kinerja populasi penuh tidak optimal ketika konsumsi CPU rata-rata lebih rendah dari sekitar 30 persen. Berikut adalah beberapa faktor yang memengaruhi konsumsi CPU.

• Waktu tunggu tinggi untuk halaman

  Untuk mengetahui apakah waktu tunggu halaman tinggi, jalankan pernyataan Transact-SQL berikut:

  SELECT TOP 10 *
  FROM sys.dm_os_wait_stats
  ORDER BY wait_time_ms DESC;
  

  Tabel berikut menjelaskan jenis tunggu yang diminati.

  Jenis tunggu	Deskripsi	Kemungkinan resolusi
  PAGEIO_LATCH_SH (_EX atau _UP)	Jenis tunggu ini dapat menunjukkan kemacetan I/O, dalam hal ini Anda biasanya juga melihat panjang antrean disk rata-rata yang tinggi.	Memindahkan indeks teks lengkap ke grup file yang berbeda pada disk yang berbeda dapat membantu mengurangi kemacetan I/O.
  PAGELATCH_EX (atau _UP)	Jenis tunggu ini dapat menunjukkan adanya banyak perebutan akses di antara utas yang mencoba menulis ke berkas database yang sama.	Menambahkan file ke grup file tempat indeks teks lengkap berada dapat membantu mengurangi perselisihan tersebut.

  Untuk informasi selengkapnya, lihat sys.dm_os_wait_stats.

• Inefisiensi dalam memindai tabel dasar

  Populasi penuh memindai tabel dasar untuk menghasilkan batch. Pemindaian tabel ini mungkin tidak efisien dalam skenario berikut:

  • Jika tabel dasar memiliki persentase tinggi kolom di luar baris yang sedang diindeks teks lengkap, memindai tabel dasar untuk menghasilkan batch mungkin menjadi hambatan. Dalam hal ini, memindahkan data yang lebih kecil dalam baris dengan menggunakan varchar(max) atau nvarchar(max) mungkin membantu.

  • Jika tabel dasar sangat terfragmentasi, pemindaian mungkin tidak efisien. Untuk informasi tentang menghitung data di luar baris dan fragmentasi indeks, lihat sys.dm_db_partition_stats dan sys.dm_db_index_physical_stats.

    Untuk mengurangi fragmentasi, Anda dapat mengatur ulang atau membangun kembali indeks berkerumun. Untuk informasi selengkapnya, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

Memecahkan masalah pengindeksan dokumen yang lambat

Mesin Teks Lengkap menggunakan dua jenis filter saat mengisi indeks teks lengkap: filter multi-utas dan filter utas tunggal.

• Beberapa dokumen, seperti dokumen Word, menggunakan filter multithread.
• Dokumen lain, seperti dokumen Adobe Acrobat Portable Document Format (PDF), menggunakan filter berutas tunggal.

Untuk alasan keamanan, filter dimuat oleh proses host daemon filter. Instans server menggunakan proses multithreaded untuk semua filter berutas ganda dan proses berutas tunggal untuk semua filter berutas tunggal. Saat dokumen yang menggunakan filter multithreaded berisi dokumen tertanam yang menggunakan filter utas tunggal, Mesin Pencarian Teks Penuh mengaktifkan proses utas tunggal untuk dokumen tertanam. Misalnya, ketika menemukan dokumen Word yang mengandung dokumen PDF, Mesin Teks Penuh menggunakan proses multibenang untuk konten Word dan meluncurkan proses satu benang untuk konten PDF. Namun, filter berulir tunggal mungkin tidak berfungsi dengan baik di lingkungan ini dan dapat mengacaukan proses pemfilteran.

Dalam keadaan tertentu di mana penyematan seperti itu umum, destabilisasi dapat menyebabkan kerusakan pada proses. Ketika kondisi ini terjadi, Full-Text Engine merutekan ulang dokumen yang gagal (misalnya, dokumen Word yang berisi konten PDF yang disematkan) ke proses pemfilteran utas tunggal. Jika perutean ulang sering terjadi, hal ini menghasilkan penurunan performa proses pengindeksan teks lengkap.

Untuk mengatasi masalah ini, tandai filter untuk dokumen kontainer (dokumen Word, dalam contoh ini) sebagai filter utas tunggal. Untuk menandai filter sebagai filter utas tunggal, atur ThreadingModel nilai registri untuk filter ke Apartment Threaded. Untuk informasi tentang apartemen utas tunggal, lihat Memahami dan Menggunakan Model Threading COM.

Konten terkait

• Opsi konfigurasi memori server
• Konfigurasi server: rentang crawl teks penuh maksimal
• Mengisi Indeks Teks Lengkap
• Membuat dan mengelola indeks teks lengkap
• sys.dm_fts_memory_buffers (T-SQL)
• sys.dm_fts_memory_pools (T-SQL)
• Memecahkan masalah pengindeksan teks lengkap
• Arsitektur Pencarian Teks Lengkap