Разбиение страниц SQL Server

Разбиение страниц возникает при вставке, а в некоторых случаях при обновлении данных в базе данных. Если недостаточно места для вставки нового значения или приходится иметь дело с последствиями обновления ключа, то к конечному уровню индекса может быть добавлена дополнительная страница (в зависимости от состояния промежуточных конечных уровней в индексе, страницы могут быть добавлены и в них). Затем строки в целях компенсации перемещаются влево или вправо. Итог — страница разбита.

Всем нам знакомо это крайне неприятное явление, разбиение страниц. Но возможны ли ситуации, когда оно не приносит вреда? Внешне события по-прежнему выглядят как разбиение страниц, но в действительности они происходят постоянно и не обязательно опасны. Разбиение страниц случается, если ключ индекса всегда увеличивается в одном направлении (примеры — метка времени, столбец целых чисел, ограниченных идентификатором, или столбец больших целых чисел). Страница индексов заполняется в зависимости от типа ключа, пока не будет достигнут предел заполнения (желательно, чтобы в данном случае он имел значение 0 или 100), а затем происходит разбиение страницы: новая страница создается в конце конечного уровня индекса и записи добавляются на новую страницу, пока она не заполнится. Затем процесс повторяется.

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

Начнем с динамических управляющих объектов (DMO) индексации в Microsoft SQL Server. Когда вместе со своим давним другом и обладателем сертификата MVP по SQL Server Луисом Дэвидсоном я работал над книгой об оптимизации производительности с использованием объектов DMO под названием Performance Tuning with SQL Server Dynamic Management Views (www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476/ref=tmm_pap_title_0), то сразу же выразил желание отвечать за главу о DMO индексации. Это одна из функций, весьма полезных при идентификации разбиений страниц в индексах и кучах SQL Server.

Далее я покажу, как можно использовать функцию динамического управления (DMF) sys.dm_db_index_operational_stats для передачи параметров для database_id, object_id, index_id и partition_id, чтобы собрать ценные метаданные об индексах в вашей среде. В этом случае, передавая db_id(), NULL, NULL, NULL соответственно, я указал обработчику запросов, что необходима информация обо всех секциях для всех индексов и объектов в текущей базе данных (см. листинг 1 и экран 1).

Экран 1. Подробные метаданные об индексах

Время существования метаданных разбиения страниц

Важно понимать, что значения leaf_allocation_count и nonleaf_allocation_count сбрасываются при перестроении индекса, как показано в следующем тесте. Моментальный снимок записи в sys.dm_db_index_operational_stats для индекса до и после перестроения дан в листинге 2.

В обоих случаях первые 7 столбцов до и после перестроения одинаковы (они просто описывают свойства индекса). Видно, что до перестроения общее число накопившихся с течением времени разбиений страниц на конечном уровне составляет 407 521 (см. экран 2).

Экран 2. Общее число накопившихся с течением времени разбиений страниц на конечном уровне

После перестроения индекса эти значения обнуляются. Значение non_leaf_allocation_count корректируется, чтобы учесть все разбиения страниц, произошедшие в процессе перестроения индекса на неконечных уровнях (см. экран 3).

Экран 3. Разбиения страниц, произошедшие в процессе перестроения индекса на неконечных уровнях

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

Физическая структура идентифицированных индексов

Важно учитывать не только разбиение страниц, но и процент фрагментации и число страниц, чтобы выяснить, приносит ли хоть какой-нибудь ущерб большое число разделений страниц. Почему разбиение страниц не обязательно приносит вред? Чтобы ответить на этот вопрос, рассмотрим первые три индекса, для которых произошло больше всего разбиений страниц на конечном уровне в нашем первом запросе. Передадим значения table id и index id в другую функцию динамического управления, sys.dm_db_index_physical_stats (см. листинг 3 и экран 4).

Экран 4. Результаты sys.dm_db_index_physical_stats

На первый взгляд результат удивляет, но присмотримся внимательнее. Число разбиений страниц значительно, но они не принесут никакого вреда. В каждом случае коэффициенту заполнения было присвоено значение 0, то есть страницы заполняются полностью. При этом процент фрагментации очень низок, менее 5% во всех случаях, несмотря на умеренные размеры индексов. Таким образом, можно сделать вывод, что в этих случаях (по крайней мере, в первых двух) индексы построены на основе ключей, значения которых постоянно автоматически увеличиваются. Это могут быть даты или целые числа, которые редко переплетаются с существующими значениями, хотя порой это случается (из-за некоторой фрагментации).

Однако для проверки правильности моих предположений следует рассмотреть еще одну деталь: время последнего перестроения индексов. Возможно ли большое число разбиений страниц с малой фрагментацией в течение короткого промежутка времени? Несомненно. Большой процесс мог загрузить большой объем данных почти последовательно. Но готов держать пари, что мы не обнаружим недавних перестроений индексов, а разбиения страниц происходят в течение длительного промежутка времени. Существует способ выяснить это, но он зависит от метода обслуживания индексов.

Определение даты последнего обслуживания индексов

Как и многие другие специалисты, я использую Maintenance Solution Ола Халленгрена. Если вы не знакомы с этим пакетом средств обслуживания, то обязательно уделите ему время. Один из компонентов этого продукта — таблица CommandLog, в которую можно вносить все команды обслуживания, применяемые к вашему экземпляру базы данных. С помощью этой таблицы мне удалось выяснить, когда в последний раз были перестроены интересующие меня индексы, и оценить длительность периода нарастания разбиений страниц (см. листинг 4).

После выполнения этих инструкций не возвращается никаких результатов. Конечно, рассматриваемое значение зависит от количества данных, сохраненных в таблице перед ее очисткой (для удаления записей старше определенной даты применяется средство SQL Agent Job). Я обычно храню данные примерно 60 дней. Если в этот период произошло перестроение, то дата последнего возвращается этими сценариями. При этом мне приходилось жертвовать производительностью запросов и скоростью поиска (sargability). Если не включить последний предикат

AND Command LIKE '%REBUILD%'

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

AND Command LIKE '%REBUILD%'

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

Экран 5. Реорганизация индексов в течение последних 60 ?дней

Главное достоинство запросов к таблице CommandLog состоит в том, что можно обойтись без запроса к sys.dm_db_index_physical_stats, чтобы получить сведения о фрагментации и числе страниц. Мне приходится сокращать столбцы, возвращаемые для ExtendedInfo и Command, но если развернуть их, то вы увидите следующую информацию:

ExtendedInfo возвращает XML для фрагментации и числа страниц нужного индекса:

2904845
0.478235

А столбец Command возвращает собственно команду, которая была выполнена во время перестроения/реорганизации:

ALTER INDEX [pkProcessed] ON [IFW].[dbo].[Processed] REORGANIZE
WITH (LOB_COMPACTION = ON)

Безвредные разбиения страниц

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

SELECT TOP 10 SO.[object_id]
, SO.[name] AS TABLE_NAME
, SI.index_id
, SI.[name] AS index_name
, SI.fill_factor
, SI.type_desc AS index_type
, ixO.partition_number
, ixO.leaf_allocation_count
, ixO.nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) AS ixO
INNER JOIN sys.indexes SI
ON ixO.[object_id] = SI.[object_id]
AND ixO.[index_id] = SI.[index_id]
INNER JOIN sys.objects SO ON SI.[object_id] = SO.[object_id]
ORDER BY ixO.leaf_allocation_count DESC;

SELECT TOP 1 SO.[object_id]
, SO.[name] AS TABLE_NAME
, SI.index_id
, SI.[name] AS index_name
, SI.fill_factor
, SI.type_desc AS index_type
, ixO.partition_number
, ixO.leaf_allocation_count
, ixO.nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats (db_id(), 545241543, 1, 1) AS ixO
INNER JOIN sys.indexes SI
ON ixO.[object_id] = SI.[object_id]
AND ixO.[index_id] = SI.[index_id]
INNER JOIN sys.objects SO ON SI.[object_id] = SO.[object_id]
ALTER INDEX dbo_AVAILABILITY_CT_clustered_idx ON cdc.dbo_AVAILABILITY_CT REBUILD;
SELECT TOP 1 SO.[object_id]
, SO.[name] AS TABLE_NAME
, SI.index_id
, SI.[name] AS index_name
, SI.fill_factor
, SI.type_desc AS index_type
, ixO.partition_number
, ixO.leaf_allocation_count
, ixO.nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats (db_id(), 545241543, 1, 1) AS ixO
INNER JOIN sys.indexes SI
ON ixO.[object_id] = SI.[object_id]
AND ixO.[index_id] = SI.[index_id]
INNER JOIN sys.objects SO ON SI.[object_id] = SO.[object_id]

SELECT
SO.[name] AS TABLE_NAME
, SI.index_id
, SI.[name] AS index_name
, SI.fill_factor
, SI.type_desc AS index_type
, ixP.avg_fragmentation_in_percent AS frag_pct
, ixP.page_count
FROM sys.dm_db_index_physical_stats (db_id(), 123147484, 1, 1, 'limited') AS ixP
INNER JOIN sys.indexes SI
ON ixP.[object_id] = SI.[object_id]
AND ixP.[index_id] = SI.[index_id]
INNER JOIN sys.objects SO ON SI.[object_id] = SO.[object_id];
SELECT
SO.[name] AS TABLE_NAME
, SI.index_id
, SI.[name] AS index_name
, SI.fill_factor
, SI.type_desc AS index_type
, ixP.avg_fragmentation_in_percent AS frag_pct
, ixP.page_count
FROM sys.dm_db_index_physical_stats (db_id(), 402816497, 18, 1, 'limited') AS ixP
INNER JOIN sys.indexes SI
ON ixP.[object_id] = SI.[object_id]
AND ixP.[index_id] = SI.[index_id]
INNER JOIN sys.objects SO ON SI.[object_id] = SO.[object_id]
SELECT
SO.[name] AS TABLE_NAME
, SI.index_id
, SI.[name] AS index_name
, SI.fill_factor
, SI.type_desc AS index_type
, ixP.avg_fragmentation_in_percent AS frag_pct
, ixP.page_count
FROM sys.dm_db_index_physical_stats (db_id(), 123147484, 77, 1, 'limited') AS ixP
INNER JOIN sys.indexes SI
ON ixP.[object_id] = SI.[object_id]
AND ixP.[index_id] = SI.[index_id]
INNER JOIN sys.objects SO ON SI.[object_id] = SO.[object_id]

SELECT TOP 1 ObjectName
, IndexName
, ExtendedInfo
, Command
, StartTime
, EndTime
FROM iDBA.dbo.CommandLog
WHERE [ObjectName] = 'Processed'
AND IndexName = 'pkProcessed'
AND CommandType = 'ALTER_INDEX'
AND Command LIKE '%REBUILD%'
ORDER BY EndTime DESC;
SELECT TOP 1 ObjectName
, IndexName
, ExtendedInfo
, Command
, StartTime
, EndTime
FROM iDBA.dbo.CommandLog
WHERE [ObjectName] = 'TrendSumOrg'
AND IndexName = 'ndxOwnerDate'
AND CommandType = 'ALTER_INDEX'
AND Command LIKE '%REBUILD%'
ORDER BY EndTime DESC;
SELECT TOP 1 ObjectName
, IndexName
, ExtendedInfo
, Command
, StartTime
, EndTime
FROM iDBA.dbo.CommandLog
WHERE [ObjectName] = 'Processed'
AND IndexName = 'ndxProcessedCallHandlingFields'
AND CommandType = 'ALTER_INDEX'
AND Command LIKE '%REBUILD%'
ORDER BY EndTime DESC;