В этой статье попытаемся понять, как изменились процедуры обслуживания индексов для таблиц 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? Похоже, мы являемся свидетелями консервативного подхода, ориентированного прежде всего на небольшие базы данных, где пересоздание индексов не принесёт большого и даже заметного вреда. Однако, представленные выше в цитатах из документации оговорки должны обратить наше внимание на то, что к этому вопросу стоит подойти с разумением.
Комментарии (28)
osipov_dv
08.09.2021 19:01какая-то у вас сплошная теория... где с измерениями? Логическая дефрагментация никак не связана с физической, более того - это разные явления. Да и пассаж про SSD и размещение данных, по сравнению с жеским диском, выглядит странно - диски тоже все используют в RAID и контроллер массива сам решает что и как ему делать.
mssqlhelp Автор
08.09.2021 20:29Вам какие измерения нужно? Насколько продлится срок службы RAID массива SSD после отказа от бездумной дефрагментации?
Статья и так длинная получилась, вот Вы её даже до конца не прочитали, а что будет, если я ещё и графики сюда выложу со всеми возможными типами нагрузки и конфигурациями дисковой подсистемы. Поверье на слово, измерял и видел то, что написано выше.
Дефрагментации страниц и внутри страниц действительно ничего не знает о том, во что эти "стройные ряды" превращаются на массиве SSD. Я вроде против этого не возражал, но всё равно спасибо, что ещё раз обратили внимание на эту несправедливость :)
RAID массив решает, а вот максимум числа циклов перезаписи ячеек располагает. Массив "подохнет" весь и сразу, и никакие подменные диски его не спасут. Только постоянная слежка за прогнозом даты "смерти" может уберечь от конфуза.
edo1h
08.09.2021 20:51Статья и так длинная получилась,
я бы не сказал
а что будет, если я ещё и графики сюда выложу со всеми возможными типами нагрузки и конфигурациями дисковой подсистемы.
ну так выложите, будет гораздо интереснее
mssqlhelp Автор
08.09.2021 20:58-1Интереснее не будет, а вот вопросов возникнет только больше.
Я призываю вас самих выбрать критичные для ваших нагрузок метрики и ориентироваться только на них. Например, можете выбрать наиболее чувствительные для приложения или пользователей запросы и фиксировать их время исполнения до и после дефрагментации.
osipov_dv
08.09.2021 21:23+2Лично мне, статью с цифрами тестов, но без лишней воды (которая есть сейчас), было бы интереснее прочитать. А так, зашел на интересный заголовок, а ничего в замен не получил.
Эту же информацию я получил несколько лет назад, из статьи Брента Озара. Он рекомендовал поменять трешхолды в дефолтных значениях скрипта Ola.
https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/
mssqlhelp Автор
09.09.2021 10:54Статья не является откровением, и ничего нового и неизвестного не превносит. Одни ссылки и цитаты. Так и задумано было, поскольку снова и снова нахожу планы обслуживания, которые подчинены этому мифу. Если позволите - это крик души.
Цифры тоже с умыслом не привожу - они очень сильно зависят от внешних условий тестирования и графики получаются не показательные - ничего не меняется на них :) Тем более, что каждый может легко у себя найти фрагментированныю таблицу и погаонять в студии запросы с хронометражом до и после дефрагментации...
Я бы рекомендовал не использовать скрипт Ола одновременно для дефрагментации и обновления статистики. Посмотрите код процедуры и поймёте, что такой подход не всегда корректен. Сделайте лучше два шага в задании, первый на дефрагментацию, а второй на обновление статистики. Заодно посомтрите, не даст ли вам выигрыш более частое обновление прокисшей статистики ;)
edo1h
08.09.2021 19:37Сокращение потребления ресурсов сервера на дефрагментацию
у меня как-то ms sql ассоциируется с 1с )
в случае с 1с обычно вполне есть технологическое окно для перестройки индексовУвеличится время жизни дисков SSD из-за сокращения числа циклов перезаписи ячеек хранения.
ну… сейчас посмотрел: p4510, аптайм 2.5 года, еженощные перестроения индексов в терабайтной базе.
по мнению накопителя израсходованный ресурс 7%. при этом накопитель совсем не рекордсмен по ресурсу, ЕМНИП 1 DWPD.mssqlhelp Автор
08.09.2021 20:36Да, ваша нагрузка не стоит того, чтобы "ломать копья", по крайней мере, пока скорость перезаписи ячеек такая, как Вы написали.
Для 1С стоит разделить дефрагментацию и обновление статистики. Второе делать отдельно после первого.
У меня сейчас на базах 1С дефрагментация раз в месяц - хотя большие проценты каждый день фрагментируются. Запросы после отказа от ежедневной экзекуции не пострадали ;)
Зато от обновления прокисшей статистики раз в пол часа они стали лучше порою на два порядка (хотя у нас не типовая ИС).
edo1h
08.09.2021 21:17+1У меня сейчас на базах 1С дефрагментация раз в месяц — хотя большие проценты каждый день фрагментируются.
у меня работает известный скрипт от ola, каждую ночь переиндексация таблиц с более, чем 5% изменений, раз в неделю — всех. ну и обновление статистики, разумеется.
одной из мотиваций сделать так было то, что полная переиндексация при высоком maxdop идёт очень быстро.именно дефрагментации (INDEX_REORGANIZE) нет, согласен, что на ssd она не особо полезна.
Размер файлов данных может заметно прибавить в весе.
это же не только размер файлов сам по себе, это более «рыхлые» индексы, соответственно больше дискового io, меньше полезных данных помещается в кэше и т.п.
Дефрагментация портит статистику по колонкам (стоимость операций ввода-вывода), не входящим в ключ индекса. Без неё станет лучше статистика, а значит повысится качество планов запросов и сократиться время затронутых запросов.
про это можно подробнее, где почитать?
mssqlhelp Автор
09.09.2021 10:45Когда данных и индексов не много, дефрагментация безвредна. Сложности начинаются на больших объёмах и нагрузках.
Почитать можно цельные цитаты документации выше в статье и ссылки там же. Пройдите по ссылке и найдите место по цитате.
edo1h
08.09.2021 20:03Это замечание важно, поскольку фактически является отсылкой к СХД на основе SSD, у которых все операции ввода вывода, как мы покажем дальше, являются случайными.
а вы точно это показали? )
разница между случайным и последовательным доступом на ssd, конечно, не такая фатальная, как на hdd, но всё равно…параметры fio для случайного/последовательного чтения блоками 8кб с очередью 1fio --name=randread --rw=randread --bs=8k --runtime=10 --time_based=1 --filename=/dev/nvme0n1p1 --iodepth=1 --randrepeat=0 --direct=1 fio --name=read --rw=read --bs=8k --runtime=10 --time_based=1 --filename=/dev/nvme0n1p1 --iodepth=1 --randrepeat=0 --direct=1READ: bw=114MiB/s (120MB/s), 114MiB/s-114MiB/s (120MB/s-120MB/s), io=1140MiB (1196MB), run=10001-10001msecREAD: bw=643MiB/s (674MB/s), 643MiB/s-643MiB/s (674MB/s-674MB/s), io=6430MiB (6743MB), run=10001-10001msecразница в 5-6 раз
mssqlhelp Автор
08.09.2021 20:49Мне казалось, что показал, даже с иллюстрациями... может слово не удачно выбрал, вместо "покажем", можно было написать "увидим".
Или стоило уточнить, что речь идёт не о типе нагрузки, а о запросе ввода-вывода.
Дествительно, есть много факторов, которые определяют, какую поизводительность показывает СХД для операций поседовательного и случайного чтения. Это и политики кеша, и выбор типа RAID, страйпа, количества шпинделей и многого другого. А ещё бывает коктель из разнотипных нагрузок и ещё с разной многопоточностью и путями доступа... и ещё много чего...
Но я имел ввиду банальную вещ, что последовательное чтение с SSD диска будет собирать случайно разбросанные по ячейчас блоки (которые размером могут и не совпадать с размером страницы MSSQL). Критику принимаю, попробую перефразировать текст в том месте. Может подскажете, как Вам видится эта фраза корректней?
edo1h
08.09.2021 20:56Но я имел ввиду банальную вещ, что последовательное чтение с SSD диска будет собирать случайно разбросанные по ячейчас блоки (которые размером могут и не совпадать с размером страницы MSSQL)
да.
но практика показывает, что внутри ssd есть оптимизации, направленные на ускорение последовательного чтения, и случайное чтение если и может приблизиться по скорости к последовательному, то только при увеличении глубины очереди.
Stillgray
09.09.2021 13:17Также Вы упустили из вида упреждающее чтение (readahead), за счёт которого можно получить значительный прирост скорости на дефрагментированных индексах как на локальных ссд, так и на сетевых дисковых массивах особенно.
Рекомендую посмотреть Березнёва Александра. Там наглядно приводятся цифры и факты.
mssqlhelp Автор
09.09.2021 14:36Увы, упреждающее чтение одинаково влияет на фрагментированные и не фрагментированные индексы, уточните, что именно было упущено?
Видео Александра досмотрел до 14-й минуты, где у него получилось нечто обратное тому, что Вы утверждаете :) плохой пример.
Но хочу уточнить, что я не оспариваю, что запросы к дефрагментированным индексам с большей вероятностью будут заканчиваться быстрее. В теории это так, и, если поставить эксперимент, подобный тому, что у Александра на видео, можно это даже продемонстрировать.
В реальной жизни всё получается иначе, время исполнения зачастую слабо зависит от увеличения числа страниц на 30%. Если оперативки достаточно, эффекта не будет почти никакого. А ситуация, когда фрагментированные индексы показывают себя даже лучше, мне в моих тестах тоже попадалась, и чаще, чем это могло быть обяснено случайностью.
Я не делал какой то серъёзной аналитики сравнению времён запросов в зависимости от уровня фрагментации, но "на глазок", когда фрагментация не выше 30% запросы не дольше на 3%.
Если мне в руки попадуться необходимые ресурсы для "правильных" тестов, я обязательно попробую нарисовать вам цифры и графики :)
Stillgray
10.09.2021 03:53Увы, упреждающее чтение одинаково влияет на фрагментированные и не фрагментированные индексы, уточните, что именно было упущено?
Упреждающее чтение позволяет считывать последовательные экстенты индексов с диска одним блоком до 512 Кб. Как об этом написано в документации. По факту, видимо, более длинными. Но это работает только в том случае, если индекс дефрагментирован.
Если оперативки достаточно, эффекта не будет почти никакого.
Вот, кстати, зря, что не досмотрели, за14 минутой начинается самая мякотка - влияние скорости выполнения запросов по фрагментированым и дефрагментированным индексам, которые расположены в буферном кеше. В том числе и с уменьшением fillfactor дефрагментированного индекса, для получения сравнимых объёмов.
mssqlhelp Автор
10.09.2021 10:24Упреждающее чтение при выборке одного дефрагментированного экстента или двух фрагментированных заберёт с диска 512Кб. Тут выигрыша не получить. Кроме того, контроллеры SSD оперируют обычно блоками занчительно превышающими 512Кб. Вы меня не убедили с упреждающим чтением :)
Если дефрагментация ежедневно, например с помощью процедуры Ола, значит за сутки индекс и так фрагментируется сильно и влияние фрагментации будет присутствовать в той или иной мере. Но влиять это будет только для просмотров или просмотров достаточно больших диапазанов. Процент таких операций не у всех значителен, чтобы выигрыш от дефрагментации сказался на метриках. Если скан по кластерному индексу, наверняка ожидания заблокированных другими ключей окажет большее влияние, чем фрагментация. С покрывающими для запросов индексами влияние дефрагментации будет больше, его как раз стоит оценить, принимая решение, а надо ли дефрагментировать ежедневно.
Видео так и быть досмотрю, и , если потребуется что-то добавить, напишу...
Существуют несколько обстоятельств, которые плохо совместимы с дефрагментацией (если она потребляет существенные ресурсы). Не всегда есть технологическое окно, когда можно так нагрузить сервер. Не всегда дефрагментациа по силам процессу REDO в AlwaysON, особенно если реплику используют для чтения или нужно обеспечить высокую доступность с автоматическим переключением с приемлемым числом девяток после запятой. Ну и не все таблицы стоит дефрагментировать, если, например, данные в них за сутки могут многократно перезаписываться все, а то и несколько раз удаляться (и есть противопоказания для использования таблиц в памяти). Часто бывает так, что индексы в таблице приходят к некоторому проценту фрагментации и остаются потом с этим процентов почти без изменений - дефрагментировать такие может статься "сизифов труд". Посмотрите внимательно именно на свои индексы, понаблюдайте за их фрагментацией во времени, вполне может оказаться что огульная дефрагментация не нужна.
Пока что я продолжаю настаивать, что необходимость частой дефрагментации - МИФ :)
mssqlhelp Автор
13.09.2021 10:07Досмотрел видео, оставил там комментарий такого содержания:
Простите, что поднимаю тему через три года с публикации:) Бесспорно, фрагментация влияла, влияет и будет влиять на производительность. Мой комментарий не в этой плоскости. Натан так подобрал условия теста, чтобы утрировать проблематику. В реальности цифры и поведение движка могут существенно отличаться. Если у вас будет другая средняя длина строки, другой процент фрагментации (дефрагментированный индекс не будет таким вечно), СХД будет активнее кешировать, буферный пул не будет только для теста использоваться, блокировки будут, процессоры тоже будут не только дефрагментированные индексы на конвейерах обслуживать, MAXDOP будет иной, и ещё много чего можно придумать... Тогда результаты теста будут как первый запуск на 14-й минуте :) Не ведитесь на сенсации с сайта SQLSkils, они умеют красиво, даже броско подать материал. Из всей этой статьи блога Натана надо запомнить одно, что дефрагментация полезна. Но с появлением SSD мир стал другим. Скорость доступа к физическому носителю и обслуживания запросов ввода-вывода выросли многократно. Зачастую разницей от дефрагментации можно пренебречь. Отказ от дефрагментации не станет катастрофой. Его можно и не заметить. А иногда дефрагментация становиться явным злом, например, когда у вас с ней не справляется REDO, или когда она тупо убивает диски. Нельзя бездумно от неё отказываться, но и дефрагментировать бездумно тоже не стоит. К видео же особых претензий нет, кроме того, что как-то незаметно сравнивать стали фрагментированную таблицу и таблицу с филфактором не по умолчанию. Также стоило бы указать источники, которые призывают совсем отказаться от дефрагментации - я бы тоже "кинул в них камень". Спасибо автору видео.
edo1h
13.09.2021 14:07К видео же особых претензий нет, кроме того, что как-то незаметно сравнивать стали фрагментированную таблицу и таблицу с филфактором не по умолчанию
так это логично как раз же, нет?
я до просмотра этого видео как раз считал, что на ssd основным фактором снижения производительности в случае фрагментации будет «распухание» индексов (фактически мы увеличиваем весь дисковый io, плюс уменьшаем буферный пул, что по сути тоже ведёт к увеличению дискового io, плюс несколько увеличиваем нагрузку на процессор — все эти неиспользуемые области в страницах индекса будут попадать в процессорный кэш, вымывая оттуда полезные данные).
честно говоря, мне не очень понятно, откуда взялась эта разница в видео. ИМХО тема требует дальнейшего изучения.mssqlhelp Автор
13.09.2021 15:47Я тоже заметил нестыковку в том, что упреждающее чтение работало только для дефрагментированных индексов, а для сильнофрагментированных оно даже не пыталось использовать упреждение в 512Кб. Тема эта мало освещена в доках, по идее, эти маленькие запросы фрагментов должно были быть объеденины. По крайне мере, запросы из разных сессий движок давно умеет объединять... чтототутнетакнахимичили :)
edo1h
09.09.2021 13:19увидел ваш комментарий на sql.ru:
И что делать, если изменний и дефрагментаций получается так много, что SSD диски энтерпрайз-класса заканчиваются за год-два?
если я правильно понимаю доку, то переиндексация порождает запись в размере двухкратного размера индекса (причём половина записи может быть перенаправлена в tempdb).
с учётом того, что, всё-таки, перестраивать приходится далеко не все индексы, не очень понятно, как у вас получается «стачивать» ssd за год. берёте диски с dwpd=0.3?mssqlhelp Автор
09.09.2021 14:13Уменя так закончились первые интеловские NVME, не помню, сколько у них было dwpd.
Диски мрут не только от дефрагментации. Тогда приложение так их колбасило, а дефрагментацию там вообще не делали.
Но вот в другом месте объёмы индексов были такие, что мама не горюй, вполне бы одной дефрагментации хватило.
Кстати, dwpd около единицы не такая уж редкость для олфлешей...
Serg02041989
07.10.2021 12:14Обслуживание подразумевает две возможные операции: реорганизация индексов на уровне страниц для устранения внутренней фрагментации, или пересоздание индекса для устранения и внутренней и внешней фрагментации.
Почему только внутреннюю? Ведь реорганизация устраняет и внешнюю фрагментацию.
P.S. есть ли где-то описание имплементации алгоритма реорганизации?
mssqlhelp Автор
07.10.2021 12:17Внимательно перечитал документацию: https://docs.microsoft.com/ru-ru/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017#reorganize-an-index
Действительно, страницы упорядочиваются на листовом уровне, т.е. частично (как я понимаю, если это возможно/есть место) дефрагментация получается и внешней. Однако, подробного описания я тоже не видел и буду весьма признателен, если кто подскажет, где его найти или процитирует?
Kneqj
Хм, а вот немцы из oo-software считают иначе.
Более того, если обратите внимание, сама Microsoft является золотым партнером этой компании и её продукта(ов).
Скажите пожалуйста, что думаете по этому поводу?
mssqlhelp Автор
Не слыхал про эту компанию и не видел опубликованного ими мнения на этот счёт.
Простите, но ваш коммент больше похож на рекламу... Немцы всё считают иначе, язык у них немецкий.
edo1h
вы бы расшифровали что значит «считают иначе»