Tam metin indekslerin performansını artırmak

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen Örneği

Bu makale, tam metin indeksler ve sorgular için düşük performansın yaygın nedenlerini ve bunların nasıl azaltılacağını ele almaktadır.

Performans sorunlarının yaygın nedenleri

Bu bölüm, tam metin indeksleri kullandığınızda yaygın performans sorunlarının nedenlerini açıklar.

Donanım kaynağı sorunları

Bellek, disk hızı, CPU hızı ve makine mimarisi gibi donanım kaynakları, tam metin indeksleme ve tam metin sorgularının performansını etkiler.

Donanım kaynak sınırları tam metin indeksleme performansının azalmasına neden olur.

• CPU. Eğer filter daemon host işleminin (fdhost.exe) veya SQL Server işleminin (sqlservr.exe) CPU kullanımı yüzde 100'e yakınsa, darboğaz CPU'dur.

• Bellek . Fiziksel hafıza eksikliği bir darboğaza yol açabilir.

• disk. Ortalama disk bekleme kuyruğu süresi disk başlığı sayısının iki katından fazlaysa, diskte bir darboğaz oluşur. Birincil geçici çözüm, SQL Server veritabanı dosyalarından ve günlüklerinden ayrı tam metin katalogları oluşturmaktır. Günlükleri, veritabanı dosyalarını ve tam metin kataloglarını ayrı disklere yerleştirin. Daha hızlı diskler yüklemek ve RAID kullanmak da dizin oluşturma performansını geliştirmeye yardımcı olabilir.

Tam metin toplu işlem sorunları

Sistemde donanım darboğazları yoksa, tam metin aramasının indeksleme performansı çoğunlukla aşağıdaki faktörlere bağlıdır:

• Database Engine'in tam metin toplu oluşturma süresi ne kadar sürer.

• Filtre daemonunun bu partileri ne kadar hızlı tükettiği.

Tam metin dizini popülasyon sorunları

• Popülasyon türü. Tam nüfusun aksine, kademeli, manuel ve otomatik değişim takip popülasyonu, donanım kaynaklarını maksimize ederek daha yüksek hıza ulaşmak için tasarlanmamıştır. Bu nedenle, bu makaledeki akort önerileri, tam metin indeksleme için artımlı, manuel veya otomatik değişim takip popülasyonu kullanıldığında performansı artırmayabilir.

• Ana birleştirme. Bir popülasyon tamamlandığında, nihai birleştirme süreci indeks parçalarını tek bir ana tam metin indeksinde birleştirir. Bu süreç, çok sayıda indeks parçası yerine yalnızca master indeksinin sorgulanması gerektiğinden sorgu performansını artırır. Daha iyi puanlama istatistikleri alaka sıralaması için kullanılabilir. Ancak, ana birleştirme I/O yoğun olabilir çünkü indeks parçaları birleştirildiğinde büyük miktarda veri yazılıp okunmalıdır. Yine de, gelen sorguları engellemez.

  Büyük miktarda verinin ana birleştirme işleminde birleştirilmesi, uzun süren bir işlem oluşturabilir ve denetim noktası sırasında işlem günlüğünün kısaltılmasını geciktirebilir. Bu durumda, tam kurtarma modeli altında işlem günlüğü önemli ölçüde büyüyebilir. En iyi yöntem olarak, tam kurtarma modelini kullanan bir veritabanındaki büyük bir tam metin dizinini yeniden düzenlemeden önce işlem günlüğünüzün uzun süre çalışan bir işlem için yeterli alan içerdiğinden emin olun. Daha fazla bilgi için bkz . İşlem günlüğü dosyasının boyutunu yönetme.

Tam metin dizinlerinin performansını ayarlama

Tam metin dizinlerinizin performansını en üst düzeye çıkarmak için aşağıdaki en iyi yöntemleri uygulayın:

• Tüm CPU çekirdeklerini maksimum kullanmak için sistemdeki çekirdek sayısını değiştirin max full-text crawl range . Daha fazla bilgi için Sunucu yapılandırması: en büyük tam metin tarama aralığı konusuna bakın.

• Temel tablonun kümelenmiş bir dizine sahip olduğundan emin olun. Kümelenmiş dizinin ilk sütunu için bir tamsayı veri türü kullanın. Kümelenmiş dizinin ilk sütununda GUID kullanmaktan kaçının. Kümelenmiş bir dizindeki çok aralıklı bir popülasyon en yüksek popülasyon hızını üretebilir. Tam metin anahtarı olarak hizmet veren sütun için tamsayı veri tipi kullanın.

• deyimini kullanarak temel tablonun istatistiklerini güncelleştirin UPDATE STATISTICS . Daha da önemlisi, kümelenmiş dizindeki istatistikleri veya tam popülasyon için tam metin anahtarını güncelleştirin. Bu işlem, çok aralıklı bir popülasyonun tabloda uygun bölümlendirmeler oluşturmasına yardımcı olur.

• Büyük çok çekirdekli bir bilgisayarda tam bir popülasyon gerçekleştirmeden önce, süreç ve işletim sistemi kullanımı için max server memory yeterli bellek bırakacak değeri ayarlayarak tampon havuzunun fdhost.exe boyutunu geçici olarak sınırlayın. Daha fazla bilgi için, bu makalenin ilerleyen bölümlerinde Filtre daemon konak sürecinin bellek gereksinimlerini tahmin et (fdhost.exe) bölümüne bakınız.

• Zaman damgası sütununu temel alan artımlı popülasyon kullanıyorsanız, artımlı popülasyonun performansını artırmak için zaman damgası sütununda ikincil bir dizin oluşturun.

Tam popülasyonların performansıyla ilgili sorunları giderme

Tam popülasyonlarla ilgili performans sorunlarını çözmek için aşağıdaki bölüme bakabilirsiniz.

Tam metin gezinme günlüklerini gözden geçirme

Performans sorunlarını teşhis etmek için tam metin tarama kayıtlarını inceleyin.

Gezinme sırasında bir hata oluştuğunda, Full-Text Arama gezinme günlüğü özelliği düz metin dosyası olan bir gezinme günlüğü oluşturur ve korur. Her gezinme günlüğü belirli bir tam metin kataloğuna karşılık gelir. Varsayılan olarak, belirli bir örneğin gezinme günlükleri (bu örnekte varsayılan örnek) %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG klasörde bulunur.

Gezinme günlüğü dosyası aşağıdaki adlandırma düzenini izler:

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

Gezinme günlüğü dosya adının değişken bölümleri şunlardır.

• <DatabaseID>: Bir veritabanının kimliği, başındaki sıfırlarla beş haneli bir sayı olarak.

• <FullTextCatalogID>: Tam metin katalog kimliği, başında sıfırlar bulunan beş basamaklı bir sayı olarak.

• <n>: Aynı tam metin kataloğun bir veya daha fazla tarama logunun var olduğunu gösteren bir tam sayı.

Örneğin, SQLFT0000500008.2 veritabanı kimliği = 5 ve tam metin katalog kimliği = 8 olan bir veritabanının gezinme günlüğü dosyasıdır. Dosya adının sonundaki 2, bu veritabanı/katalog çifti için iki gezinme günlüğü dosyası olduğunu gösterir.

Fiziksel bellek kullanımını denetleme

Tam metin doldurma işlemi sırasında, fdhost.exe veya sqlservr.exe işlemi yeterli belleğe sahip olmayabilir, hatta belleği tamamen tükenebilir.

• Tam metin tarama günlüğü, fdhost.exe öğesinin sık sık yeniden başlatıldığını veya 8007008 hata kodunu döndürdüğünü gösteriyorsa, bu işlemlerden birinin belleğinin tükendiği anlamına gelir.

• Eğer fdhost.exe özellikle büyük, çok çekirdekli sistemlerde dökümler üretirse, bellek tükeniyor olabilir.

• Tam metin taramasında kullanılan bellek tamponları hakkında bilgi için bkz. sys.dm_fts_memory_buffers.

Düşük bellek veya hafıza dışı sorunların olası nedenleri şunlardır:

• Yetersiz bellek. Tam bir nüfus boyunca mevcut olan fiziksel bellek miktarı sıfırsa, Database Engine tampon havuzu sistemdeki fiziksel belleğin çoğunu tüketiyor olabilir.

  sqlservr.exe işlemi, arabellek havuzu için kullanılabilir tüm belleği, yapılandırılan en yüksek sunucu belleğine kadar almaya çalışır. max server memory ayırma işlemi çok büyükse, fdhost.exe işlemi için bellek yetersizliği durumları ve paylaşılan belleğin ayrılamaması ortaya çıkabilir.

  Bu sorunu çözmek için Database Engine tampon havuzunun değerini uygun şekilde ayarlayınmax server memory. Daha fazla bilgi için, bu makalenin ilerleyen bölümlerinde Filtre daemon konak sürecinin bellek gereksinimlerini tahmin et (fdhost.exe) bölümüne bakınız. Tam metin indeksleme için kullanılan toplu boyutun küçültülmesi de yardımcı olabilir.

• Bellek çekişmesi. Çok çekirdekli bir sistemde fdhost.exe tam metin popülasyonu sırasında ve sqlservr.exe tampon havuzu belleği için rekabet edebilir. Ortaya çıkan paylaşılan bellek yetersizliği, toplu iş yeniden denemelerine, bellek aşırı yüklenmesine ve fdhost.exe işlemi tarafından oluşturulan dökümlere neden olur.

• Sayfalama sorunları. Büyümesi kısıtlanmış küçük bir sayfa dosyasına sahip sistemlerde olduğu gibi, sayfa dosyası boyutunun yetersiz olması da fdhost.exe veya sqlservr.exe işleminin belleğinin tükenmesine neden olabilir. Tarama kayıtları bellekle ilgili herhangi bir arıza göstermiyorsa, aşırı sayfalama muhtemelen performansın yavaşlamasına neden oluyor.

Filtre daemon ana sürecinin bellek gereksinimlerini tahmin edin (fdhost.exe)

Sürecin fdhost.exe doldurulması için ihtiyaç duyduğu bellek miktarı, esas olarak kullandığı tam metin tarama aralıklarının sayısına, gelen paylaşılan bellek (ISM) büyüklüğüne ve maksimum ISM örneği sayısına bağlıdır.

Filtre daemon konakçısının bellek tüketimini kabaca tahmin ederek aşağıdaki formülü kullanarak yapabilirsiniz:

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

Önceki formüldeki değişkenler için varsayılan değerler şunlardır:

Aşağıdaki tablo, bellek gereksinimlerinin tahmini fdhost.exeiçin yönergeler sunmaktadır. Bu tablodaki formüller aşağıdaki değerleri kullanır:

• F, fdhost.exe için gereken bellek miktarının (MB cinsinden) tahminidir.

• T, sistemde kullanılabilen toplam fiziksel bellektir (MB cinsinden).

• M, bu da optimal max server memory ayardır.

Aşağıdaki formüller hakkında temel bilgiler için, tabloyu izleyen notlara bakın.

1. Birden fazla tam dolum işlemi sürüyorsa, her birinin fdhost.exe bellek gereksinimini ayrı ayrı F1, F2 vb. olarak hesaplayın. Sonra M'yiT - Σ(Fi) olarak hesaplayın.

2. 500 MB, sistemdeki diğer işlemler için gereken belleğin tahminidir. Sistem ek iş yapıyorsa, bu değeri buna göre artırın.

3. ism_size x64 platformları için 8 MB olduğu varsayılır.

Örnek: Bellek gereksinimlerini tahmin edin fdhost.exe

Bu örnek, 8 GB RAM ve 4 çift çekirdekli işlemciye sahip 64-bit bir bilgisayar içindir. İlk hesaplama, fdhost.exeF için gereken belleği tahmin eder. Tarama aralıklarının sayısı 8.

F = 8 * 10 * 8 = 640

Sonraki hesaplama, (max server memory) için optimal değeri elde eder. Bu sistemde mevcut olan toplam fiziksel bellek MB cinsinden, (T), 'dir.8192

M = 8192 - 640 - 500 = 7052

Örnek: Ayarla max server memory

Bu örnek, önceki örnekte M için hesaplanan değere ayarlanmak amacıyla sp_configure ve max server memory Transact-SQL ifadelerini kullanır: 7052

USE master;
GO

EXECUTE sp_configure 'max server memory', 7052;
GO

RECONFIGURE;
GO

Sunucu belleği seçenekleri hakkında daha fazla bilgi için Sunucu belleği yapılandırma seçeneklerine bakınız.

CPU kullanımını denetleme

Ortalama CPU tüketimi yaklaşık yüzde 30'un altında olduğunda tam popülasyonların performansı optimal değildir. CPU tüketimini etkileyen bazı faktörler aşağıdadır.

• Sayfalar için yüksek bekleme süresi

  Sayfa bekleme süresinin yüksek olup olmadığını öğrenmek için aşağıdaki Transact-SQL deyimini çalıştırın:

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

  Aşağıdaki tablo, ilgilenilen bekleme türlerini açıklar.

  Bekleme türü	Açıklama	Olası çözünürlük
  PAGEIO_LATCH_SH (_EX veya _UP)	Bu bekleme türü bir G/Ç darboğazını gösterebilir, bu durumda genellikle yüksek ortalama disk kuyruğu uzunluğu da görülür.	Tam metin indeksini farklı bir dosya grubuna ve farklı bir diske taşımak, I/O darboğazını azaltmaya yardımcı olabilir.
  PAGELATCH_EX (veya _UP)	Bu bekleme türü, aynı veritabanı dosyasına yazmaya çalışan iş parçacıkları arasında çok fazla çekişme olduğunu gösterebilir.	Tam metin indeksin bulunduğu dosya grubuna dosya eklemek, bu tür çekişmeleri hafifletebilir.

  Daha fazla bilgi için bkz. sys.dm_os_wait_stats.

• Temel tabloyu taramadaki verimsizlikler

  Tam popülasyon, toplu iş oluşturmak için temel tabloyu tarar. Bu tablo taraması aşağıdaki senaryolarda verimsiz olabilir:

  • Temel tablo, tam metin dizini oluşturulan satır dışı sütunların yüksek bir yüzdesine sahipse, toplu iş oluşturmak için temel tabloyu taramak darboğaz olabilir. Bu durumda, varchar(max) veya nvarchar(max) kullanarak daha küçük verileri sıraya taşımak yardımcı olabilir.

  • Temel tablo çok parçalanmışsa tarama verimsiz olabilir. Sıra dışı veri hesaplama ve indeks parçalanması hakkında bilgi için bkz. sys.dm_db_partition_stats ve sys.dm_db_index_physical_stats.

    Parçalanmayı azaltmak için kümelenmiş dizini yeniden düzenleyebilir veya yeniden oluşturabilirsiniz. Daha fazla bilgi için bkz. Sorgu performansını geliştirmek ve kaynak tüketimini azaltmak için dizin bakımını iyileştirme.

Belgelerin yavaş dizine alma sorunlarını giderme

Full-Text Motoru, tam metin indeksini doldururken iki tür filtre kullanır: çoklu iş parçacıklı filtreler ve tekli iş parçacıklı filtreler.

• Bazı belgeler, örneğin Word belgeleri, çok iş parçacıklı filtreler kullanır.
• Adobe Acrobat Portable Document Format (PDF) gibi diğer belgeler ise tek parçacıklı filtreler kullanır.

Güvenlik nedeniyle filtreler, filtre daemon ana bilgisayar işlemleri tarafından yüklenir. Sunucu örneği, tüm çok iş parçacıklı filtreler için çok iş parçacıklı bir işlem ve tüm tek iş parçacıklı filtreler için tek iş parçacıklı bir süreç kullanır. Çok iş parçacıklı filtre kullanan bir belge, tek iş parçacıklı filtre kullanan ekli bir belge içerdiğinde, Full-Text Altyapısı eklenmiş belge için tek iş parçacıklı bir işlem başlatır. Örneğin, PDF belgesi içeren bir Word belgesiyle karşılaşıldığında, Full-Text Altyapısı Word belgesi içeriği için çok iş parçacıklı işlemi kullanır ve PDF içeriği için tek iş parçacıklı bir işlem başlatır. Ancak, tek parçacıklı bir filtre bu ortamda iyi çalışmayabilir ve filtreleme sürecini istikrarsızlaştırabilir.

Bu tür eklemelerin yaygın olduğu bazı durumlarda, istikrarsızlaştırma sürecin çökmesine neden olabilir. Bu durum oluştuğunda, Full-Text Engine başarısız olan herhangi bir belgeyi (örneğin, gömülü PDF içeriği içeren bir Word belgesini) tek iş parçacıklı filtreleme işlemine yeniden yönlendirir. Yeniden yönlendirme sık sık gerçekleşirse, tam metin dizin oluşturma işleminin performans düşüşüyle sonuçlanırsa.

Bu sorunu aşmak için, kapsayıcı belgeye ait filtreyi (bu örnekte Word belgesini) tek iş parçacıklı bir filtre olarak işaretleyin. Bir filtreyi tek iş parçacıklı filtre olarak işaretlemek için, filtrenin ThreadingModel kayıt defteri değerini Apartment Threaded olarak ayarlayın. Tek iş parçacıklı apartment'lar hakkında bilgi için COM İş Parçacığı Modellerini Anlama ve Kullanma konusuna bakın.

İlgili içerik

• Sunucu belleği yapılandırma seçenekleri
• Sunucu yapılandırması: maksimum tam metin tarama aralığı
• Full-Text Dizinlerini Doldurma
• Tam metin dizinleri oluşturma ve yönetme
• sys.dm_fts_memory_buffers (Transact-SQL)
• sys.dm_fts_memory_pools (Transact-SQL)
• Tam metin indeksleme sorun giderme
• Full-Text Arama mimarisi