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

Область применения:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL Azure

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

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

В этом разделе описываются причины распространённых проблем с производительностью при использовании полнотекстовых индексов.

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

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

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

• CPU. Если загрузка процессора процессом узла управляющей программы фильтра (fdhost.exe) или процессом SQL Server (sqlservr.exe) приближается к 100 %, процессор является узким местом.

• Memory. Нехватка физической памяти может стать узким местом.

• Диск. Если средняя длина очереди ожидания диска более чем в два раза превышает количество головок диска, значит, узкое место находится в дисковой подсистеме. Основное решение заключается в создании полнотекстовых каталогов, которые отделены от файлов и журналов базы данных SQL Server. Разместите журналы, файлы баз данных и полнотекстовые каталоги на разных дисках. Кроме того, для повышения производительности индексирования можно установить более быстрый жесткий диск или диск с поддержкой RAID.

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

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

• Сколько времени требуется ядро СУБД для создания полнотекстовых пакетов.

• Как быстро фильтрующий демон поглощает эти партии.

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

• Тип населения. В отличие от полной популяции, постепенные, ручные и автоматические изменения не предназначены для максимального использования аппаратных ресурсов для повышения скорости. Поэтому рекомендации по настройке, приведённые в этой статье, могут не повысить производительность полнотекстового индексирования при использовании инкрементального, ручного или автоматического заполнения на основе отслеживания изменений.

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

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

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

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

• Чтобы максимально использовать все ядра процессора, измените max full-text crawl range количество ядер в системе. Для получения дополнительной информации см . Конфигурация сервера: максимальный полнотекстовый диапазон сканирования.

• Убедитесь, что для базовой таблицы установлен кластеризованный индекс. Первый столбец кластеризованного индекса должен иметь целочисленный тип данных. Старайтесь не использовать идентификатор GUID в качестве первого столбца кластеризованного индекса. Многодиапазонное заполнение кластеризованного индекса может обеспечить наивысшую скорость заполнения. Используйте целочисленный тип данных для столбца, который служит полнотекстовым ключом.

• Обновите статистику базовой таблицы с помощью инструкции UPDATE STATISTICS . Еще важнее обновить статистику кластеризованного индекса или ключа полнотекстового поиска для осуществления полного обновления. Это действие помогает многодиапазонной популяции создавать качественные разбиения в таблице.

• Перед тем как выполнять полное заполнение на большом многоядерном компьютере, временно ограничьте размер буферного пула, задав значение max server memory, чтобы оставить достаточно памяти для процесса fdhost.exe и операционной системы. Для получения дополнительной информации см. Оценка требований к памяти процесса фильтрующего демон-хоста (fdhost.exe), позже в этой статье.

• Если вы используете инкрементальное заполнение на основе столбца метки времени, создайте вторичный индекс для столбца timestamp, чтобы улучшить производительность инкрементального заполнения.

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

Обратитесь к следующему разделу для решения проблем с производительностью при полном составе населения.

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

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

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

Файлы журналов обхода следуют следующей схеме наименования.

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

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

• <DatabaseID>: ID базы данных в виде пятизначного числа с ведущими нулями.

• <FullTextCatalogID>: Полнотекстовый идентификатор каталога, в виде пятизначного числа с ведущими нулями.

• <n>: Целое число, указывающее на наличие одного или нескольких журналов обхода одного и того же полнотекстового каталога.

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

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

Во время полного заполнения полнотекстового индекса процесс fdhost.exe или sqlservr.exe может испытывать нехватку памяти или даже полностью исчерпать её.

• Если журнал полнотекстового обхода показывает, что fdhost.exe часто перезапускается или возвращает код ошибки 8007008, это означает, что одному из этих процессов не хватает памяти.

• Если fdhost.exe создаёт дампы, особенно на больших многоядерных системах, возможно, ему не хватает памяти.

• Для информации о буферах памяти, используемых полнотекстовым сканированием, см. sys.dm_fts_memory_buffers.

Возможные причины проблемы с нехваткой памяти или её отсутствием включают следующие пункты:

• Недостаток памяти. Если объём физической памяти, доступной при полной популяции, равен нулю, буферный пул ядро СУБД может потреблять большую часть физической памяти системы.

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

  Правильно задайте значение max server memory для буферного пула ядро СУБД, чтобы решить эту проблему. Для получения дополнительной информации см. Оценка требований к памяти процесса фильтрующего демон-хоста (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

Значения по умолчанию для переменных в предыдущей формуле следующие:

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

• F, что является оценкой необходимой памяти по fdhost.exe (в МБ).

• T: общий объем физической памяти, доступной в системе (в МБ).

• M, что является оптимальной max server memory настройкой.

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

1. Если одновременно выполняется несколько полных популяций, вычислите fdhost.exe требуемый объём памяти для каждой из них по отдельности, обозначив их как F1, F2 и так далее. Затем вычислите M как T - Σ(Fi).

2. 500 МБ — это ориентировочный объем памяти, необходимый другим процессам в системе. Если система выполняет дополнительную работу, то это значение следует соответствующим образом увеличить.

3. ism_size предполагается, что для платформ x64 это составляет 8 МБ.

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

Этот пример — 64-битный компьютер с 8 ГБ оперативной памяти и 4 двухъядерными процессорами. Первый расчёт оценивает объём памяти, необходимый для fdhost.exeF. Количество диапазонов обхода равно 8.

F = 8 * 10 * 8 = 640

Следующее вычисление даёт оптимальное значение для max server memory (M). Общая физическая память, доступная на этой системе в MB (T), равна 8192.

M = 8192 - 640 - 500 = 7052

Пример: Установите max server memory

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

USE master;
GO

EXECUTE sp_configure 'max server memory', 7052;
GO

RECONFIGURE;
GO

Для получения дополнительной информации о настройках памяти сервера см. Параметры конфигурации памяти сервера.

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

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

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

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

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

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

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

  Дополнительные сведения см. в sys.dm_os_wait_stats.

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

  Для создания пакетов производится полный просмотр основной таблицы. Такое сканирование таблиц может быть неэффективным в следующих ситуациях:

  • Если базовая таблица содержит высокий процент столбцов, значения которых хранятся вне строк и подвергаются полнотекстовому индексированию, узким местом может стать сканирование базовой таблицы для создания пакетов. В этом случае может помочь хранение меньших объёмов данных в строке с использованием varchar(max) или nvarchar(max).

  • Сканирование может выполняться неэффективно в том случае, если базовая таблица имеет высокую степень фрагментации. Для получения информации о вычислении данных вне строки и фрагментации индексов см. sys.dm_db_partition_stats и sys.dm_db_index_physical_stats.

    Чтобы снизить уровень фрагментации, можно выполнить реорганизацию или перестроение кластеризованного индекса. Дополнительные сведения см. в статье "Оптимизация обслуживания индекса", чтобы повысить производительность запросов и сократить потребление ресурсов.

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

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

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

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

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

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

Связанные материалы

• Параметры конфигурации памяти сервера
• Конфигурация сервера: максимальный диапазон полнотекстового сканирования
• Заполнение полнотекстовых индексов
• Создание полнотекстовых индексов и управление ими
• sys.dm_fts_memory_buffers (Transact-SQL)
• sys.dm_fts_memory_pools (Transact-SQL)
• Устранение неполадок с полнотекстовой индексацией
• Архитектура полнотекстового поиска