Улучшение производительности полнотекстовых индексов

Статья
05/23/2023

Область применения:SQL Server Azure SQL Database

В этой статье описываются некоторые из основных причин снижения производительности полнотекстовых индексов и запросов. Кроме того, тут представлены несколько предложений по устранению этих проблем и повышению производительности.

Распространенные причины проблем с производительностью

Проблемы с ресурсами оборудования

Производительность полнотекстового индекса и полнотекстовых запросов зависит от архитектуры компьютера, а также от объема памяти, скорости работы ЦП, жесткого диска и других ресурсов оборудования.

Основной причиной снижения производительности полнотекстового индексирования являются ограничения ресурсов оборудования.

ЦП. Если использование ЦП процессом узла управляющей программы фильтра (fdhost.exe) или процесс SQL Server (sqlservr.exe) близок к 100 процентам, ЦП является узким местом.
Память. Если не хватает физической памяти, то узким местом системы может оказаться память.
Диск. Если средняя длина очереди ожидания обращения к жесткому диску в два или больше раз превышает количество головок диска, то узким местом является жесткий диск. Основное решение заключается в создании полнотекстовых каталогов, которые отделены от файлов и журналов базы данных SQL Server. Разместите журналы, файлы баз данных и полнотекстовые каталоги на разных дисках. Кроме того, для повышения производительности индексирования можно установить более быстрый жесткий диск или диск с поддержкой RAID.

Заметка

Начиная с SQL Server 2008 (10.0.x), полнотекстовый модуль может использовать память AWE, так как полнотекстовый модуль является частью процесса sqlservr.exe. Дополнительные сведения см. в разделе Архитектура полнотекстового поиска.

Проблемы полнотекстовой пакетной обработки

Если в системе нет узких мест, связанных с оборудованием, то производительность индексирования полнотекстового поиска в большей степени будет зависеть от следующих аспектов.

Сколько времени занимает SQL Server для создания полнотекстовых пакетов.
Скорость, с которой управляющая программа фильтрации может обрабатывать эти пакеты.

Проблемы заполнения полнотекстового индекса

Тип заполнения. Добавочное, ручное и автоматическое заполнение отслеживания изменений (в отличие от полного заполнения) не рассчитаны на максимальную загрузку ресурсов оборудования ради повышения скорости работы. Таким образом, предложения по настройке из этой статьи могут не дать результата в виде улучшения производительности полнотекстового индексирования, если оно использует добавочное, ручное или автоматическое заполнения отслеженных изменений.
Слияние в единый файл. После завершения операции заполнения инициируется заключительный процесс слияния фрагментов индекса в один главный полнотекстовый индекс. Это повышает производительность запросов за счет использования одного главного индекса вместо нескольких фрагментов индексов и позволяет использовать более точные количественные оценки для ранжирования данных по релевантности. Однако слияние в единый файл может сильно нагружать подсистему ввода/вывода вследствие записи и чтения больших объемов данных, но не приводит к блокировке входящих запросов.

При слиянии больших объемов данных в единый файл может создаваться транзакция с большим временем выполнения, в результате чего усечение журнала транзакций по достижении контрольной точки будет отложено. В этом случае размер журнала транзакций в модели полного восстановления может значительно увеличиться. Перед реорганизацией большого полнотекстового индекса в базе данных, использующей модель полного восстановления, убедитесь, что в журнале транзакций достаточно свободного места для транзакций с большим временем выполнения. Дополнительные сведения см. в разделе Управление размером файла журнала транзакций.

Настройка производительности полнотекстовых индексов

Чтобы получить максимальную производительность полнотекстовых индексов, воспользуйтесь следующими рекомендациями.

Чтобы в максимальной степени задействовать все процессоры или ядра ЦП, присвойте параметру max full-text crawl range процедуры sp_configure значение, равное числу ЦП в системе. Сведения об этом параметре конфигурации см. в разделе Параметр конфигурации сервера max full-text crawl range.
Убедитесь, что для базовой таблицы установлен кластеризованный индекс. Первый столбец кластеризованного индекса должен иметь целочисленный тип данных. Старайтесь не использовать идентификатор GUID в качестве первого столбца кластеризованного индекса. Многодиапазонное заполнение кластеризованного индекса может обеспечить наивысшую скорость заполнения. Рекомендуется, чтобы столбец, служащий в качестве полнотекстового ключа, имел целочисленный тип данных.
Обновите статистику базовой таблицы с помощью инструкции UPDATE STATISTICS . Еще важнее обновить статистику кластеризованного индекса или полнотекстового ключа для полного заполнения. Это позволит при многодиапазонном заполнении сформировать для таблицы хорошие секции.
Перед выполнением полного заполнения на мощном многопроцессорном компьютере рекомендуется временно ограничить размер буферного пула, задав для параметра Макс. памяти сервера значение, оставляющее достаточно памяти для процесса fdhost.exe и для использования операционной системой. Дополнительные сведения см. в подразделе Оценка требований к памяти для хост-процесса управляющей программы полнотекстовой фильтрации (fdhost.exe) далее в этом разделе.
При использовании добавочного заполнения на основе значений столбца метки времени создайте вторичный индекс по столбцу timestamp, если нужно повысить производительность добавочного заполнения.

Устранение неполадок с производительностью полной совокупности

Просмотр журналов полнотекстового сканирования

Для диагностики проблем производительности следует изучить журналы полнотекстового сканирования.

При возникновении ошибки во время сканирования модуль протоколирования сканирования, входящий в механизм полнотекстового поиска, создает и обновляет журнал сканирования, хранящийся в текстовом файле. Каждый журнал сканирования соответствует конкретному полнотекстовому каталогу. По умолчанию журналы сканирования для конкретного экземпляра (в нашем случае — для экземпляра по умолчанию) хранятся в папке %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG.

Имена файлов журналов сканирования имеют следующий формат.

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

Ниже перечислены переменные части в именах файла журнала сканирования.

<DatabaseID> — идентификатор базы данных. <dbid> — это пять цифр с начальными нулями.
<FullTextCatalogID> — идентификатор полнотекстового каталога. <catid> — это пять цифр с начальными нулями.
<n> — целое число, указывающее, что существует один или несколько журналов обхода одного полнотекстового каталога.

Например, SQLFT0000500008.2 является файлом журнала сканирования для базы данных с идентификатором базы данных 5 и идентификатором полнотекстового каталога 8. Двойка в конце имени файла показывает, что этой паре базы данных и каталога соответствуют два файла журналов сканирования.

Проверка использования физической памяти

Во время полнотекстового заполнения существует вероятность того, что у процесса fdhost.exe или sqlservr.exe произойдет нехватка или переполнение памяти.

Если журнал полнотекстового сканирования показывает, что fdhost.exe часто перезапускается или возвращает код ошибки 8007008, то это значит, что одному из этих процессов не хватает памяти.
Если процесс fdhost.exe создает дампы (особенно на мощных, многопроцессорных компьютерах), то ему может не хватать памяти.
Сведения о буферах памяти, используемых полнотекстовой обходом, см. в разделе sys.dm_fts_memory_buffers (Transact-SQL).

Ниже перечислены возможные причины проблем нехватки памяти.

Недостаток памяти. Если объем физической памяти, доступной во время полного заполнения, равен нулю, пул буферов SQL Server может потреблять большую часть физической памяти в системе.

Процесс sqlservr.exe пытается захватить всю доступную память для буферного пула, ограничиваясь установленным максимумом памяти сервера. Если для параметра Макс. памяти сервера выделено слишком много памяти, в процессе fdhost.exe могут возникнуть проблемы с недостатком памяти и сбои при выделении общей памяти.

Эту проблему можно решить, задав максимальное значение памяти сервера буферного пула SQL Server соответствующим образом. Дополнительные сведения см. в подразделе Оценка требований к памяти для хост-процесса управляющей программы полнотекстовой фильтрации (fdhost.exe) далее в этом разделе. Разрешить проблему можно, уменьшив размер пакета, используемого для полнотекстового индексирования.
Конфликты памяти. Во время проведения полнотекстового заполнения на многопроцессорном компьютере может возникнуть конфликт между процессами fdhost.exe и sqlservr.exe за память буферного пула. Возникающая в результате этого нехватка общей памяти вызывает повторы пакетов, пробуксовку памяти и создание дампов процессом fdhost.exe.
Проблемы с подкачкой. Недостаточный размер файла подкачки (например, если в системе задан небольшой файл подкачки с ограниченным ростом) также может вызвать переполнение памяти для процессов fdhost.exe и sqlservr.exe. Если в журналах сканирования не зарегистрированы сбои, связанные с памятью, то, скорее всего, проблемы с производительностью вызваны излишней подкачкой.

Оценка требований к памяти процесса узла управляющей программы фильтрации (fdhost.exe)

Объем памяти, необходимый процессу fdhost.exe для заполнения, зависит в основном от числа используемых диапазонов полнотекстового сканирования, размера входящей общей памяти (ISM) и максимального числа экземпляров ISM.

Объем памяти (в байтах), занимаемый узлом управляющей программы фильтрации, может быть примерно рассчитан с использованием следующей формулы:

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

Ниже приводятся значения по умолчанию для переменных в приведенной выше формуле.

Переменная	Значение по умолчанию
number_of_crawl_ranges	Число процессоров
ism_size	1 МБ для компьютеров x86 4, 8 и 16 МБ для компьютеров для компьютеров x64 (в зависимости от общего объема физической памяти)
max_outstanding_isms	25 МБ для компьютеров x86 5 для компьютеров x64

В следующей таблице представлены рекомендации по оценке требований к памяти fdhost.exe. В формулах данной таблицы используются следующие значения.

F: оценка объема памяти, необходимого для fdhost.exe (в МБ).
T: общий объем физической памяти, доступной в системе (в МБ).
M: оптимальный параметр Макс. памяти сервера .

Основные сведения о следующих формулах см. в примечаниях после таблицы.

Платформа	Оценка потребностей в памяти для fdhost.exe в МБ — F^1	Формула для вычисления значения параметра "Макс. памяти сервера" — M^2
x86	F = число диапазонов сканирования * 50	M = минимальное значение (T, 2000) - F - 500
x64	F = число диапазонов сканирования * 10 * 8	M = T - F - 500

Примечания о формулах

Если параллельно выполняется несколько полных заполнений, то требования к памяти fdhost.exe для каждого из них следует вычислять отдельно, как F1, F2и т. д. Затем вычислите M как T- sigma**(_F_i)**.
500 МБ — это ориентировочный объем памяти, необходимый другим процессам в системе. Если система выполняет дополнительную работу, то это значение следует соответствующим образом увеличить.
.ism_size равно 8 МБ для платформ x64.

Пример. Оценка требований к памяти fdhost.exe

В этом примере используется 64-разрядный компьютер с 8 ГБ ОЗУ и четырьмя двухъядерными процессорами. Первое вычисление оценивает объем памяти, необходимый для fdhost.exe, — F. Число диапазонов сканирования: 8.

F = 8*10*8 = 640

При следующем вычислении получается оптимальное значение Макс. памяти сервера -M. Общий объем физической памяти, доступной в системе (в МБ), — T — равен 8192.

M = 8192-640-500 = 7052

Пример. Задание значения параметра "Макс. памяти сервера"

В этом примере используются инструкции sp_configure и RECONFIGURE Transact-SQL, чтобы задать максимальное значение памяти сервера, вычисляемое для M в предыдущем примере: 7052

USE master;  
GO  
EXEC sp_configure 'max server memory', 7052;  
GO  
RECONFIGURE;  
GO

Сведения о настройке параметра max_server_memory см. в статье Server Memory Server Configuration Options (Параметры конфигурации сервера Server Memory).

Проверка загрузки ЦП

Производительность полного заполнения считается неоптимальной, если уровень загруженности ЦП ниже 30%. Вот некоторые факторы, которые влияют на уровень загруженности ЦП.

Высокое время ожидания страниц

Чтобы узнать, является ли время ожидания страницы высоким, выполните следующую инструкцию Transact-SQL:

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

В следующей таблице описаны типы значений ожидания, представляющие интерес.

Тип ожидания	Description	Возможное решение
PAGEIO_LATCH_SH (_EX или _UP)	Это может свидетельствовать о наличии узкого места в подсистеме ввода-вывода. В этом случае средняя длина очереди диска обычно велика.	Перемещение полнотекстового индекса в другую файловую группу на другом диске может помочь снизить влияние узкого места в операциях ввода-вывода.
PAGELATCH_EX (или _UP)	Это может свидетельствовать о высокой конкуренции между потоками, которые пытаются выполнить запись в один и тот же файл базы данных.	Уровень конкуренции можно снизить, добавив файлы в файловую группу, в которой расположен полнотекстовый индекс.

Дополнительные сведения см. в разделе sys.dm_os_wait_stats (Transact-SQL).

Неэффективное сканирование базовой таблицы.

Во время создания пакетов при полном заполнении производится просмотр базовой таблицы. Ее сканирование может оказаться неэффективным в следующих случаях.
- Если в базовой таблице присутствует высокий процент столбцов, значения в которых хранятся вне строк, для которых выполняется полнотекстовое индексирование, то узким местом может оказаться сканирование базовой таблицы для создания пакетов. В этом случае можно попробовать решить проблему, переместив значения данных с небольшими длинами в строки, используя типы varchar(max) или nvarchar(max) .
- Сканирование может выполняться неэффективно в том случае, если базовая таблица имеет высокую степень фрагментации. Сведения о вычислении внестроковых данных и фрагментации индексов см. в sys.dm_db_partition_stats (Transact-SQL) и sys.dm_db_index_physical_stats (Transact-SQL).
  
  Чтобы снизить уровень фрагментации, можно выполнить реорганизацию или перестроение кластеризованного индекса. Дополнительные сведения см. в статье Реорганизация и перестроение индексов.

Устранение неполадок с медленным индексированием документов

Заметка

В этом разделе описывается проблема, затрагивающая только пользователей, которые индексируют документы (например, документы Microsoft Word), в которые внедрены другие типы документов.

Средство полнотекстового поиска использует два типа фильтров при заполнении полнотекстового индекса: многопоточные и однопоточные.

Некоторые документы, такие как документы Microsoft Word, фильтруются с помощью многопоточного фильтра.
Другие, например документы формата Adobe Acrobat Portable Document Format (PDF), фильтруются с помощью однопоточного фильтра.

Из соображений безопасности фильтры загружаются процессами узла управляющей программы фильтрации. Экземпляр сервера использует многопоточный процесс для всех многопоточных фильтров и однопоточный процесс для однопоточных фильтров. Если документ, использующий многопоточный фильтр, содержит внедренный документ, использующий однопоточный фильтр, средство полнотекстового поиска запускает однопоточный процесс для внедренного документа. Например, если документ Word содержит PDF-документ, средство полнотекстового поиска использует многопоточный процесс для содержимого Word и запускает однопоточный процесс для PDF-содержимого. Однако однопоточный фильтр может неправильно работать в такой среде, дестабилизируя процесс фильтрации. В некоторых случаях, когда часто встречаются такие внедрения, дестабилизация может привести к сбою процесса. В этом случае средство полнотекстового поиска перенаправляет сбойный документ (например, документ Word, содержащий внедренный PDF-документ) в однопоточный процесс фильтрации. Если перенаправление происходит достаточно часто, производительность полнотекстового индексирования снижается.

Для устранения этой проблемы необходимо пометить фильтр для документа-контейнера (например, документ Word) как однопоточный фильтр. Для этого необходимо присвоить ключу реестра ThreadingModel для этого фильтра значение Apartment Threaded. Сведения об однопоточных подразделениях см. в техническом документе Общие сведения о моделях потоков COM и их использовании.

См. также

Параметры конфигурации сервера «Server Memory»
Параметр конфигурации сервера max full-text crawl range
Заполнение полнотекстовых индексов
Создание и управление полнотекстовыми индексами
sys.dm_fts_memory_buffers (Transact-SQL)
sys.dm_fts_memory_pools (Transact-SQL)
Устранение неполадок полнотекстового индексирования
Архитектура полнотекстового поиска