sys.dm_db_index_physical_stats со всеми "потрохами"

Автор: Paul Randal, Inside sys.dm_db_index_physical_stats

Давным‑давно, на излёте прошлого века, я написал для SQL Server 2000 команду DBCC SHOWCONTIG, в пару к своему новому изобретению — DBCC INDEXDEFRAG.

А ещё я постоянно носил шорты и люминесцентные оранжевые, жёлтые или зелёные носки.

Многое меняется — скажем, у меня теперь (иногда) есть чувство вкуса. Среди прочего, с появлением в SQL Server 2005 динамических представлений (DMV) DBCC SHOWCONTIG уступил место sys.dm_db_index_physical_stats. Однако под капотом у них один и тот же код — и характеристика ввода‑вывода (I/O) не изменилась.

Эту заметку я собирался написать уже давно, а окончательно подтолкнул меня T‑SQL Tuesday на тему I/O, который сегодня проводит Mike Walsh (blog). Идея отличная, решил присоединиться. Перечитывая перед публикацией, понимаю, что слегка увлёкся (угрохал пару часов) — но это один из моих «детей», так что я имею право! :-)

Это не текст о том, как пользоваться DMV вообще, как работать именно с этим DMV или про фрагментацию индексов. Эта заметка о том, как устроено это DMV внутри.

DMV — удобный собирательный термин (которым пользуются многие, включая меня) для обозначения различных служебных представлений в SQL Server. DMV = Dynamic Management View. Но у этого собирательного имени есть нюанс: часть «DMV» на самом деле функции (DMF). Настоящее DMV берёт информацию из памяти SQL Server (или системных таблиц) и выводит её в каком‑то виде. DMF, напротив, прежде чем вернуть результат, должно выполнить некоторую работу. Представление sys.dm_db_index_physical_stats (далее просто «DMV») — самое «дорогое» среди них, но именно по части I/O.

Идея DMV — показать физические характеристики индексов (и особый случай — кучи). Чтобы это сделать, оно должно просканировать страницы, из которых состоит индекс, попутно вычисляя статистику. Многие DMV поддерживают так называемый predicate pushdown, то есть если вы задаёте WHERE, DMV учитывает это уже при подготовке информации. Это DMV — нет. Если вы попросите выдать только индексы в базе с логической фрагментацией > 30%, оно просканирует все индексы, а затем просто сообщит о тех, что удовлетворяют критерию. Ему приходится так поступать, потому что заранее оно не знает, какие индексы пройдут фильтр, пока не проанализирует каждый — следовательно, predicate pushdown невозможен.

И вот тут важно понимать, что именно происходит внутри — это суть статьи.

LIMITED
Режим работы по умолчанию называется LIMITED. Кимберли до сих пор подтрунивает над эквивалентной опцией для DBCC SHOWCONTIG, которую я, юный и наивный, назвал WITH FAST. Что ж, зато по делу!

В LIMITED можно вернуть только логическую фрагментацию на листовом уровне и количество страниц. Сам листовой уровень при этом не читается. Используется тот факт, что на уровне выше листа хранится упорядоченный по ключу список идентификаторов страниц (page ID) листового уровня, так что достаточно проверить: совпадает ли порядок page ID с порядком распределения (allocation order) — отсюда и логическая фрагментация.

Задумка опции — находить фрагментацию индекса, читая минимально возможное число страниц, то есть за наименьшее время. Этот вариант может работать на порядки быстрее, чем DETAILED, и степень выигрыша зависит от ветвления (fanout) индекса. Не углубляясь, ветвление определяется размером ключа индекса и задаёт, сколько указателей на дочерние страницы может содержать страница индекса (например, сколько листовых страниц описывает страница на следующем уровне).

Рассмотрим индекс с ключом char(800). Каждая запись на уровне выше листа должна содержать ключ (минимальный ключ, который может встретиться на ссылочной странице), плюс page ID, плюс накладные расходы записи и место под массив слотов — итого 812 байт. Страница вместит лишь 8096/812 = 9 таких записей. Ветвление максимум 9.

Для индекса с ключом bigint каждая запись — 13 байт, и страница выдержит 8096/13 = 622 записи. Ветвление максимум 622, но на практике может быть меньше из‑за фрагментации на нелистовых уровнях.

Если в таблице 1 млн страниц на листе, у первого индекса будет как минимум 1 млн/9 = 111 112 страниц на уровне выше листа. У второго — минимум 1608. Выгода по I/O от LIMITED очевидно зависит от ветвления.

Я создал 100‑гигабайтный кластерный индекс (на том же железе, что и для серии бенчмарков) с 13421760 листовыми страницами и максимальным ветвлением 540. На деле я заполнял индекс 16 параллельными потоками, так что некоторая фрагментация есть. На уровне выше листа 63 012 страниц, эффективное ветвление 213. Даже так LIMITED прочитает в 213 раз меньше, чем DETAILED — но будет ли в 213 раз быстрее?

Вот что показывает perfmon для LIMITED‑сканирования индекса:

Внутри LIMITED ничего особенного не происходит — цепочка страниц уровня выше листа читается по связям между страницами (page linkage), без упреждающего чтения. На графике:

• Avg. Disk Read Queue Length (голубая) стабильно равна 1;
• Avg. disk sec/Read (розовая) стабильно около 4 мс;
• Disk Read Bytes/sec (зелёная) примерно 14,5 МБ/с;
• Page reads/sec (тёмно‑синяя) примерно 1800.

DETAILED
Режим DETAILED делает две вещи:

• Считает фрагментацию, выполняя LIMITED‑скан;
• Считает всю остальную статистику, читая все страницы на каждом уровне индекса.

Очевидно, он самый медленный. Ему нужно сначала выполнить LIMITED, чтобы вычислить логическую фрагментацию, поскольку листовые страницы он читает максимально быстрым способом — в порядке распределения (allocation order). Для этого DBCC использует свой, особо агрессивный механизм упреждающего чтения в allocation order, который задействуется и в этом DMV, и в DBCC CHECK*‑командах. Он очень агрессивен и нагружает диски по максимуму, особенно когда DBCC CHECK* работает в параллель.

Perfmon для DETAILED‑сканирования индекса:

Не такой красивый, как LIMITED, но мне нравится :-) Что видно:

• Avg. Disk Read Queue Length (чёрная) — сотни. Аппетит к данным явно превосходит возможности моего RAID. По сути, механизм пытается насытить подсистему I/O, чтобы максимально быстро «гнать» данные в SQL Server.
• Avg. disk sec/Read (розовая внизу) измеряется целыми секундами, а не миллисекундами — ожидаемо при такой очереди.
• DBCC Logical Scan Bytes/sec  (красный цвет) существенно меняется в зависимости от того, как работает механизм упреждающего чтения, но в среднем составляет до 80 МБ/с. Примерно в 9:49:20 видно просадку до нуля на пару секунд.
• Readahead pages/sec (зелёная) следует за DBCC‑сканом. Это счётчик буферного пула; счётчик DBCC относится к Access Methods (команда, которой я руководил во время разработки 2005). Если бы я показал Disk Read Bytes/sec и Page reads/sec, они бы идеально совпадали с двумя предыдущими — я их скрывал ради наглядности.

Итак, DETAILED читает не только гораздо больше данных, но и делает это куда более агрессивно, сильнее влияя на общую способность системы к I/O во время работы.

SAMPLED
Есть и третий режим, добавленный специально для этого DMV. Идея: если у вас очень большая таблица, и вам нужна прикидочная оценка некоторой листовой статистики, но вы не хотите платить по производительности за DETAILED, можно использовать его. Он выполняет:

• LIMITED‑скан;
• Если листовых страниц < 10 000 — читает все страницы, иначе — каждую сотую (то есть выборка 1%).

Итого
У DMV (и DBCC SHOWCONTIG) нет индикации прогресса, но если посмотреть на столбец reads в sys.dm_exec_sessions, можно оценить, как далеко продвинулась операция. Этот способ лучше всего работает для DETAILED‑сканов: число можно сравнить с in_row_data_page_count для индекса в sys.dm_db_partition_stats (да, с секционированными индексами придётся повозиться).

По времени я прогнал все три режима до конца. Результаты:

• LIMITED: 282 секунды;
• SAMPLED: 414 секунд;
• DETAILED: 3700 секунд.

Хотя LIMITED прочитал примерно в 200 раз меньше, чем DETAILED, он оказался только в 13 раз быстрее — потому что механизм упреждающего чтения в DETAILED намного эффективнее, чем (необходимое) «следование по связям страниц» в LIMITED.

Ради интереса я запустил SELECT COUNT(*) по индексу, чтобы посмотреть, как справится обычный Access Methods readahead. Он завершился за 3870 секунд — на 5% медленнее, причём работы у него было меньше, чем у DMV. Однозначно, DBCC рулит! :-)

Хотя DETAILED даёт самый полный вывод, ему приходится делать больше всех. Для очень больших индексов это может означать, что буферный пул будет вынужден интенсивно «перелопачиваться» lazywriter’ом, освобождая место для чтения и обработки страниц DMV (он, впрочем, не «вымоет» весь пул: страницы, прочитанные DMV, будут первыми кандидатами на выталкивание). Это одна из причин, по которой я советую запускать DMV только для тех индексов, которые вам действительно интересны — а ещё лучше делать это на восстановленной копии базы.

Надеюсь, было полезно!

PS И ещё: осторожнее с мастером фрагментации в SSMS. Он использует SAMPLED‑скан, но я не нашёл способа его отменить!