В этой статье попытаемся понять, как изменились процедуры обслуживания индексов для таблиц Microsoft SQL Server в современных условиях: при размещении файлов данных и журнала транзакций на SSD-дисках, многократном увеличении числа процессорных ядер и в условиях, когда оперативная память сервера стала измеряться Терабайтами.Действительно, мир стал другим. С тех пор как появились первые версии SQL Server, многое изменилось и многие методики, основанные на старых компьютерных ресурсах, работают уже не так эффективно, как прежде, когда без них невозможно было обойтись. Одной из таких методик, которая с давних пор воспринимается чуть ли не «серебряной пулей», а на деле превратилась в миф, является обязательная дефрагментация индексов, если в данные индекса достаточно часто вносятся изменения. Цель статьи развеять этот миф.
В своей документации, Майкрософт обращает наше внимание на то, что не стоит выполнять обслуживание индексов, не убедившись, что это принесёт пользу. Вот цитата:
«Чтобы избежать ненужного использования ресурсов, что может негативно влиять на рабочие нагрузки запросов, корпорация Майкрософт рекомендует не применять обслуживание индекса без предварительной оценки. Следует опытным путем оценить повышение производительности от обслуживания индексов для каждой рабочей нагрузки, используя рекомендуемую стратегию, и сопоставить его с затратами ресурсов и влиянием на рабочую нагрузку, которые потребуются для достижения этих преимуществ.»
В том же документе рассматриваются причины повышения производительности запросов после обслуживания индексов и делается замечание, что не всегда это связано с дефрагментацией, бывают и иные причины. Вот ещё одна цитата:
«Перестроение индекса дает еще одно важное преимущество: позволяет обновить статистику по ключевым столбцам индекса, сканируя все строки в индексе. Это эквивалентно операции UPDATE STATISTICS ... WITH FULLSCAN, которая позволяет актуализировать статистику и иногда дает более точные данные, чем обычное обновление статистики по ограниченной выборке. При обновлении статистики заново компилируются все планы запросов, которые ее используют. Если прежний план запроса не был оптимальным из-за устаревшей статистики, недостаточного объема выборки для статистики или по любой другой причине, то после повторной компиляции многие планы дают лучшие результаты.»
Давайте попробуем определить, при каких условиях обслуживание индексов может принести больше вреда, чем пользы. Для начала вспомним, что такое дефрагментация и для чего она была нужна.
В документации дано следующее определение фрагментации индекса:
«В индексах сбалансированного дерева (rowstore) фрагментацией называют такое состояние, когда для некоторых страниц индекса логический порядок, основанный на значении ключа, не совпадает с физическим порядком страниц индексов». Отмечается также, что: «Если подсистема хранения имеет более высокую производительность последовательных операций ввода-вывода по сравнению с произвольными операциями ввода-вывода, то фрагментация индекса может привести к снижению производительности, ведь для чтения фрагментированных индексов требуется больше случайных операций ввода-вывода».
Это замечание важно, поскольку фактически является отсылкой к СХД на основе SSD, у которых все операции ввода вывода, как мы покажем дальше, являются случайными.
Есть ещё одно определение другого типа фрагментации, который в документации переведён на русский язык как «плотность страниц». С этим типом фрагментации связан важный факт, который влияет на актуальность статистик таблицы, не связанных с индексами. Вот, что написано в документации:
«Когда оптимизатор запросов компилирует план запроса, он учитывает стоимость операций ввода-вывода для чтения необходимых этому запросу данных. При низкой плотности страниц потребуется считывать больше страниц, а значит, и стоимость ввода-вывода будет выше. Это может повлиять на выбор плана запроса. Например, с течением времени плотность страниц уменьшается из-за разбиений, и оптимизатор может скомпилировать для того же запроса другой план с другой профилем потребления ресурсов и другой производительностью».
Пока что запомним это, поскольку подробнее коснёмся этого факта при анализе вызываемых дефрагментацией проблем с производительностью запросов.
В качестве резюме по типам фрагментации приведём тут выдержку из статьи: «Индексы. Теоретические основы»
Когда запись удаляется, в файле БД высвобождается место. Когда вставляется новая запись, это может привести к расщеплению страниц, что приводит к появлению пустого пространства на страницах данных. Когда данные обновляются, это может привести к изменению размера записи и к возникновению двух ранее упоминавшихся случаев. Все это приводит к фрагментации. В SQL Server рассматриваются два типа фрагментации: внутренняя и внешняя.1.Внутренняя подразумевает пустоты внутри страницы.2. Внешняя – непоследовательность связей страниц.Если страницы не полностью заполнены данными, это приводит к дополнительным операциям I/O и переиспользованию оперативной памяти. Помните, что страницы в оперативной памяти есть зеркальное отражение страниц на диске.В идеале страницы должны быть подлинкованы слева направо в порядке хранения данных. Вследствие расщепления страниц этот порядок может быть нарушен. Это приводит как к неполному заполнению страниц, так и к увеличению операций I/O вследствие непоследовательного положения цепочек страниц на диске – это вызывает дополнительные перемещения головок с цилиндра на цилиндр диска. А это одна из наиболее медленных дисковых операций.
В упомянутой только что статье были представлены методы обслуживания индексов, применяемые для SQL Server 2000. Обслуживание подразумевает две возможные операции: реорганизация индексов на уровне страниц для устранения внутренней фрагментации, или пересоздание индекса для устранения и внутренней и внешней фрагментации.
Давайте посмотрим, как с этим обстоит дело в современных версиях. Ещё в электронной документации к SQL Server 2005 было описано динамическое административное представление sys.dm_db_index_physical_stats. Тогда описание сопровождалось примерами использования, один из которых предлагал метод и правила автоматизации операций дефрагментации индексов в базе данных. Суть метода в том, что если значение avg_fragmentation_in_percent находится в диапазоне от 10 до 30, то в инструкции ALTER INDEX используется ключевое слово REORGANIZE, а если значение больше 30, то используется ключевое слово REBUILD.
В документе Разрешение фрагментации индекса путем реорганизации или перестроения индекса, предлагается использовать подобранные эмпирическим путём значения, взяв за основу данные из следующей таблицы:
avg_fragmentation_in_percent | Корректирующая_инструкция |
> 5 % и < = 30 % 1 | ALTER INDEX REORGANIZE |
> 30 % 1 | ALTER INDEX REBUILD WITH (ONLINE = ON) |
1 Эти значения дают примерное представление об определении точки, в которой необходимо переключаться между ALTER INDEX REORGANIZE и ALTER INDEX REBUILD. Однако фактические значения могут различаться в каждом конкретном случае. Важно определить наилучшее пороговое значение для используемой среды экспериментальным путем.
Эти рекомендации часто подвергались критике, поскольку пороги устанавливались исходя из очень пессимистичного сценария влияния фрагментации на производительность приложений, и большинство критиков считали их сильно заниженными. Вот одна из недавних статей на эту тему: Ваш скрипт, обслуживающий индексы, измеряет не то, что надо.
Существуют и другие предложения процентных порогов, один из которых был подробно изложен в книге: “Microsoft SQL Server 2005. Реализация и обслуживание. Учебный курс Microsoft“. Вот выдержка из этой книги, со страницы 368:
«Исполняйте инструкцию ALTER INDEX … REORGANIZE, чтобы выполнить дефрагментацию индекса, при соблюдении следующих пороговых значений: значение avg_page_space_used_in_percent в диапазоне от 60 до 75 или значение avg_fragmentation_in_percent в диапазоне от 10 до 15. Исполняйте инструкцию ALTER INDEX … REBUILD, чтобы выполнить дефрагментацию индекса, при соблюдении следующих пороговых значений: значение avg_page_space_used_in_percent меньше 60 или значение avg_fragmentation_in_percent больше 15».
В документации Майкрософт подчёркивается, что наибольший выигрыш дефрагментация даёт при операциях просмотра всего индекса или диапазона строк индекса или кучи. На операции поиска уровень фрагментации большого влияния не имеет. Это происходит потому, что просмотр выбирает данные не по дереву сбалансированного индекса, а по ссылкам на последовательность страниц, которые расположены непосредственно на самих страницах. На жёстких дисках время доступа к фрагментированным данным сильно зависит от времени позиционирования головок между цилиндрами. Операции с данными, расположенными на одном цилиндре, происходят быстро. Т.е. на время операций влияет Геометрия магнитного диска, которая схематично показана на рисунке из Википедии:
Всего этого механического «безобразия» больше нет на SSD дисках. Дефрагментация была полезна только для HDD c механическим перемещением головок над поверхностью диска. Расположение данных так, что для последовательного чтения не нужны перемещения готовки между «цилиндрами» позволяло повысить производительность последовательных операций чтения и записи. Надёжность и долговечность жёстких дисков тоже определяется возможностью перемещения головок. Пока это происходит штатно, диск будет работать, и возможности записи или чтения будут ограничены только старением или неисправностью привода.
В SSD дисках доступ к любым данным осуществляется за почти одинаковое время. Но в отличие от жёстких дисков, твердотельные диски изнашиваются, Для увеличения времени службы накопителя, ограниченного числом циклов перезаписи ячеек памяти, в SSD дисках используются алгоритмы рассеивания записи (для выравнивания износа), что превращает все последовательные операция чтения/записи в псевдослучайные. Схематично, это показано на рисунке ниже, взятого из стати Надежность SSD-накопителей: развенчиваем мифы и страхи пользователей.
С учётом этой особенности размещения данных на SSD можно отказаться от частого перестроения индексов, ограничившись реорганизацией или делать перестроение/реорганизацию вручную, при необходимости (например, для ускорения очистки фантомных строк или для сокращения времени пересчёта статистики). Прична банальна, SSD диск последовательную нагрузку всегда превращает в случайную. На логическом уровне индекс не будет фрагментирован, а на физическом фрагментация может быть абсолютной.
Для настольных компьютеров выполняемая не часто дефрагментация диска не приводит к заметному сокращению срока службы. Пользовательские файлы офисных приложений тоже фрагментируются. Те файлы, которые обычно пользователи размещают на дисках, не подвержены частой и интенсивной фрагментации. Если пользователь дефрагментирует файлы на диске раз в месяц – это не будет проблемой, хотя и не приведёт к ожидаемому повышению производительности, например, при создании моментального системного снимка. В отличие от этого, данные внутри файлов SQL Server могут подвергаться интенсивной фрагментации, и эта фрагментация может очень быстро достигать тех порогов, которые были обозначены выше, чтобы определить необходимость дефрагментации.
Для обслуживания индексов и статистики часто используют процедуру, автором которой является Ola Hallengren. В соответствии с выбранным набором параметров запуска, процедура устраняет внешнюю и внутреннею фрагментацию страниц. Следует отметить, что Майкрософт рекомендует для этих целей использовать процедуру AdaptiveIndexDefrag. Однако ни одна из этих процедур не умеет определять, располагаются ли файлы, в которых размещены индексы, на дисках SSD.
Те индексы, для которых был выбран REBUILD, обновляют статистику только для этих индексов. Цитата из документации Майкрософт, посвящённой статистике, глава Условия обновления статистики:
«Такие операции, как перестроение, дефрагментация и реорганизация индекса, не изменяют распределение данных, и поэтому после выполнения операций ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG и ALTER INDEX REORGANIZE не нужно обновлять статистику. Но оптимизатор запросов обновляет статистику, когда выполняется перестройку индекса для таблицы или представления с помощью инструкции ALTER INDEX REBUILD или DBCC DBREINDEX. Такое обновление статистики является побочным эффектом повторного создания индекса. Оптимизатор запросов не обновляет статистику после операций DBCC INDEXDEFRAG и ALTER INDEX REORGANIZE.»
Однако, для оптимизатора запросов важным является ещё и стоимость операций ввода-вывода, которая зависит от плотности страниц. Если плотность страниц (внутренняя фрагментация) значительно изменится, а статистика для колонок останется неизменной, это может потенциально ввести оптимизатор в заблуждение и стать причиной выбора оптимизатором неоптимального плана запроса. Следует учитывать эти риски планируя обслуживание индексов.
Для размещённых на SSD дисках файлов данных, в подавляющем большинстве случаев дефрагментация не приведёт к ожидаемому повышению производительности дисковых операций, а только сократит срок службы SSD дисков и может привести к появлению неоптимальных планов запросов. Для SSD лучше полностью отказаться от перестроений и реорганизаций индексов на регулярной основе (это перестанет «убивать» диски). Такое решение кажется на первый взгляд весьма рискованным, влияние мифа о необходимости дефрагментации на нас очень велико. Если шок от этого предложения преодолеть трудно, попробуйте хотя бы не делать дефрагментацию каждый день и понаблюдайте за временем исполнения запросов. Когда поймёте, что время не меняется – продолжайте снижать частоту такого обслуживания. Отказавшись от ежедневной дефрагментации совсем, не забывайте обслуживать статистики индексов. Чем актуальнее статистики, тем проще оптимизатору выбирать хорошие планы для запросов.
Также увеличение числа страниц после изменений можно попробовать снизить за счёт сжатия данных индекса на уровне строк или страниц, как это описано тут: SQL SERVER – Rebuilding Index with Compression
Отказ от частой дефрагментации приведёт к следующим улучшениям:
Сокращение потребления ресурсов сервера на дефрагментацию
Дефрагментация портит статистику по колонкам (стоимость операций ввода-вывода), не входящим в ключ индекса. Без неё станет лучше статистика, а значит повысится качество планов запросов и сократиться время затронутых запросов.
Изменения распределения данных при дефрагментации передаются на реплики, поэтому отказ от дефрагментации сократит очереди на передачу и применение таких изменений.
Увеличится время жизни дисков SSD из-за сокращения числа циклов перезаписи ячеек хранения.
Теперь давайте перечислим те минусы, которые наиболее очевидны:
Время исполнения коротких транзакций не должно заметно измениться, но большие просмотры могут заметно удлиниться.
Увеличится время пересчёта статистик (стоит подобрать минимальный SAMPLE).
Размер файлов данных может заметно прибавить в весе.
До сих пор мы говорили об обслуживании индексов только в целях дефрагментации внешней и внутренней. Но это не единственное её предназначение. Пересоздание индексов можно использоваться для переноса данных в другую файловую группу, но эта операция обычно выполняется редко, необходимость её может ограничиваться единичными случаями. Более интересно перестроение индекса для удаления фантомных строк, как это указано в статье документации: Руководство по процессу очистки фантомных записей. В тех случаях, когда фоновый процесс очистки фантомных строк не успевает их чистить, и не удаётся эффективно использовать системную процедуру sp_clean_db_free_space, перестроение индекса оказывается наиболее быстрым и эффективным решением проблем с производительностью.
Резонный вопрос, почему в документации Майкрософт нет рекомендаций отказаться от дефрагментации индексов на SSD? Похоже, мы являемся свидетелями консервативного подхода, ориентированного прежде всего на небольшие базы данных, где пересоздание индексов не принесёт большого и даже заметного вреда. Однако, представленные выше в цитатах из документации оговорки должны обратить наше внимание на то, что к этому вопросу стоит подойти с разумением.
Комментариев нет:
Отправить комментарий