Решение проблем фрагментации индекса SQL Server

Фрагментация индекса в SQL Server относится к фрагментации данных в базе данных. Данные, хранящиеся в различных индексах и таблицах в базе данных на языке структурированных запросов (SQL), могут стать фрагментированными, если они не хранятся последовательно на диске.

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

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

 

Устранение проблем с фрагментацией индекса SQL Server

В этом разделе описываются различные причины фрагментации индекса и стратегии их выявления. Затем рассматривается, как можно превентивно уменьшить фрагментацию индекса SQL Server.

 

Что вызывает фрагментацию индекса?

Фрагментация индекса в SQL Server возникает, когда логический порядок индекса не совпадает с его физическим порядком. Это может произойти, когда:

• Вставка или обновление строк: Когда вы добавляете новую или дополнительную строку в таблицу базы данных, она может отображаться не в том месте, где она соответствовала бы “логическому порядку” индекса SQL Server. Аналогично, обновление строки, которое приводит к ее перемещению в другое место в индексе, может вызвать фрагментацию.
• Удаление строк: Удаление строки из таблицы оставляет пробел в индексе SQL Server. Фрагментация может произойти, если вы не заполните этот пробел новыми вставками.
• Изменение размера страниц данных: Изменение размера страниц данных, составляющих изменения индекса SQL Server, может привести к фрагментации.
• Реорганизация или перестроение индекса: Реорганизация или перестроение индекса SQL Server может вызвать фрагментацию, если выполняется неправильно или нечасто.

 

Фрагментация индекса делится на две подкатегории: внутреннюю и внешнюю. Ниже подробно описаны обе.

 

Внутренняя фрагментация

Внутренняя фрагментация относится к неиспользуемому дисковому пространству в пределах одного выделенного блока памяти. Например, если вы выделяете блок памяти для хранения файла размером 500 байт, но сам блок составляет 1024 байта, то внутренняя фрагментация составляет 512 байт. Эта фрагментация возникает из-за того, что используется только 500 байт, а оставшиеся 512 байт тратятся впустую, поскольку их нельзя использовать ни для чего другого.

 

Внешняя фрагментация

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

 

Выявление и измерение фрагментации индекса SQL Server

В этом разделе рассматриваются два способа выявления и измерения случаев фрагментации индекса SQL Server.

 

Использование инструментов мониторинга базы данных

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

 

С помощью функции T-SQL sys.dm_db_index_physical_stats()

Вы можете использовать Transact-SQL, называемый T-SQL, для определения фрагментации индекса. T-SQL – это расширение SQL от Microsoft, специфичное для SQL Server, и оно содержит процедурное программирование и локальные переменные.

T-SQL использует функцию sys.dm_db_index_physical_stats() для определения фрагментации индекса. Эта функция позволяет просматривать размер фрагментации данных и индексы таблиц.

Приведенный ниже запрос, написанный в среде SQL Server Management Studio, может помочь вычислить фрагментацию и плотность страниц индексов rowstore. Каждое значение NULL соответствует идентификатору базы данных, идентификатору объекта, идентификатору индекса, номеру раздела и режиму. Запрос вычисляет логическую фрагментацию индексов для каждого объекта в базе данных, поскольку avg_fragmentation_in_percent. он также может вычислять другие показатели, такие как процент использования внутренне фрагментированных страниц. Эта информация выводится в столбце под названием avg_page_space-used_in_percent. В нем также можно найти общее количество страниц и описать тип единицы размещения:

SELECT * 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)  
WHERE avg_fragmentation_in_percent > 0 
ORDER BY avg_fragmentation_in_percent DESC;

 

Вы можете передавать режимы в качестве аргументов функциям sys.dm_db_index_physical_stats . Режимы определяют уровень детализации, который вы получаете после выполнения команды. Вы можете использовать режим SAMPLED для быстрого возврата результатов или режим DETAILED для получения более точных результатов, поскольку он сканирует все страницы индекса и может потребовать некоторого времени для возврата результатов.

Этот запрос также работает для данных rowstore, которые хранятся в памяти по строкам и состоят из таблицы, организованной по строкам и столбцам. Это традиционный способ хранения табличных данных.

Результатом будет:

 

 

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

DECLARE @DBID int; 
SET @DBID = DB_ID('SampleDatabase’) 

SELECT * 
FROM sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL)  
WHERE avg_fragmentation_in_percent > 0 
ORDER BY avg_fragmentation_in_percent DESC

 

Результатом будет:

 

Обратите внимание, что результат соответствует фрагментации только в одной базе данных.

 

Рекомендации по устранению фрагментации индекса

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

Ниже перечислены некоторые рекомендации по устранению фрагментации индекса.

 

Перестроить индекс

В зависимости от типа индексов и версии Database Engine вы можете выполнить перестройку как в автономном, так и в оперативном режиме с помощью команды ALTER INDEX NAME_OF_INDEX ON NAME_OF_TABLE REBUILD . Восстановление автономного индекса часто занимает меньше времени, чем сетевого, но оно блокирует объекты на уровне объекта, предотвращая доступ запросов к таблице или представлениям во время выполнения восстановления.

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

Используйте команду sys.dm_db_index_phyical_stats() для вычисления фрагментации вашего индекса. Если она превышает 40%, вам следует перестроить индекс.

Начинать перестроение следует только после того, как большая часть таблицы будет фрагментирована. Хотя этот метод ресурсоемкий, он устраняет все формы фрагментации и создает оптимизированное дерево. Операция выполняется путем создания новой копии индекса и удаления старой, удаляя фрагментацию.

 

Реорганизуйте индекс

Reorganize работает исключительно в режиме онлайн с помощью команды ALTER INDEX NAME_OF_INDEX ON NAME_OF_TABLE REORGANIZE . Вы можете начать эту операцию во время обработки запросов или изменений в базовой базе данных, поскольку вы не поддерживаете долгосрочную блокировку на уровне объекта.

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

Используйте команду sys.dm_db_index_physical_stats(), чтобы проверить фрагментацию вашего индекса. Если она превышает 10%, но меньше 40%, что влечет за собой умеренный уровень фрагментации, вам следует реорганизовать индекс.

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

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

 

Рекомендации по поддержанию вашего индекса SQL Server

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

 

Используйте план обслуживания

План обслуживания – это набор задач, которые вы можете запланировать для автоматического выполнения для обслуживания базы данных. Этот план может включать задачи по перестроению или реорганизации индексов, обновлению статистики и проверке целостности базы данных.

 

Используйте команду UPDATE STATISTICS

Команда UPDATE STATISTICS обновляет статистику распространения, которую Оптимизатор запросов SQL Server использует для создания планов выполнения. Обновление статистики может помочь оптимизатору запросов выбрать более эффективный план выполнения (EEP), повышая производительность запроса.

 

Выберите правильное значение коэффициента заполнения

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

 

Используйте опцию ONLINE

Если вам нужно перестроить индекс, используйте опцию ONLINE, чтобы база данных по-прежнему была доступна пользователям. Это сводит к минимуму время простоя и сбои в работе базы данных.

 

Регулярно отслеживайте фрагментацию индекса

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

 

Заключение

Базы данных SQL Server могут фрагментироваться из-за регулярного использования, что приводит к проблемам с производительностью и пустой трате ресурсов.

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