Однажды несколько месяцев назад я был разбужен настойчивым гудением своего BlackBerry: клиенты, использовавшие одну из моих баз данных, жаловались, что обращение к созданному Web-приложению при загрузке часто используемой страницы занимает от 20 до 30 секунд. В течение предыдущих недель производительность постепенно ухудшалась и находилась теперь на таком уровне, что небольшая дополнительная нагрузка могла остановить систему. Я должен был найти причину проблемы, причем сделать это следовало безотлагательно. Как я выяснил, имела место неисправность, которая возникла из-за сочетания фрагментации в таблицах и файлах базы данных и неэффективного наполнения страницы. И, разобрав задачу последовательно, я решил ее.

Обозначим проблему

Первым делом я открыл монитор производительности, чтобы увидеть, состоянием какого из четырех аппаратных компонентов — процессора, памяти, диска или сети — обусловлено замедление. Счетчик Processor:% Processor Time был в области нормы, SQL Server:Buffer Manager:Free Pages показывал более 2000 открытых страниц, а Network Interface:Bytes Total/sec был только на 1/20 от возможностей сети гигабитного Ethernet. Physical Disk:Disk Bytes/sec были, соответственно, от 100 до 200% выше нормы для нашего сервера, так что источником проблемы, казалось, было использование диска.

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

Жесткий диск как сдерживающий фактор

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

Итак, ограниченность вращения диска определяет порядок и длину значительного числа запросов по данным. Компонент ввода/вывода SQL Server читает и записывает данные с максимально возможной скоростью и выстраивает страницы в той последовательности, в которой они расположены на диске. Цель состоит в том, чтобы читать данные со страниц в порядке, близком к их нумерации, насколько это возможно, потому что в результате мы получим наименьшее время ожидания того момента, когда нужная секция окажется под головкой. Счетчики монитора производительности Avg Disk Bytes/Transfer, Avg Disk Bytes/Read и Avg Disk Bytes/Write сообщают, сколько байтов задействуется при каждой операции ввода/вывода. Буфера диска гарантируют, что база данных SQL Server никогда не будет иметь менее чем 8196 байт за оборот диска, но что нам нужно, так это последовательные 65,536 (или более) байт за оборот (65,536 байт, или 64 Кбайт). Если вы видите, что данная величина меньше, чем 65,536, значит, возникли проблемы с фрагментацией данных.

Монитор производительности показывал чрезмерное использование диска, но, чтобы выяснить причину, мне потребовался другой инструмент. FileMon — это свободно распространяемый инструмент от Sysinternals (http://www.sysinternals.com), который описывает все, что читает и пишет, и число байтов за операцию для каждого файла. Установив в FileMon фильтр для контроля только файлов базы данных, можно увидеть, сколько байтов SQL Server читает и пишет по каждому из группы файлов. Когда я заметил, что FileMon сообщает только о 8196 байт, прочитанных в файле, который имел таблицу в 2,5 и 1 Гбайт в индексах, я подумал, что, вероятно, обнаружил виновника ухудшения производительности.

Важно помнить, что каждый раз процесс ввода/вывода включает задержку, во время которой контроллер ждет, когда диск повернется под читающую/пишущую головку. Поэтому всегда на прочтение восьми страниц требуется больше времени, чем на одну страницу такого же объема. Кроме того, компонент ввода/вывода для эффективного доступа не может упорядочивать чтение, потому что не в состоянии предсказать, какие запросы его ожидают в будущем. Оптимизация диска происходит, когда комбинируют хранение данных большими порциями с минимально возможным обращением к диску. Отчет FileMon сообщает, что компонент ввода/вывода прочитал табличную страницу в 2,5 Гбайт за один раз, значит, что-то мешало двигателю сгруппировать запросы в последовательность. Поэтому требуется взглянуть на некоторые таблицы и выяснить причину.

Обнаружение фрагментации

Полезный инструмент для идентификации степени фрагментации в таблице — команда SQL Server DBCC SHOWCONTIG. К сожалению, ее запуск ухудшает производительность, которую надо как раз улучшить, потому что команда создает большое количество операций ввода/вывода. C ключом FAST воздействие на производительность сервера меньше, чем если запускать команду без него. Но даже выполнение команды DBCC SHOWCONTIG FAST замедляет систему достаточно сильно, если она уже перегружена. Лучше всего при использовании Database Consistency Checker (DBCC) — если, конечно, есть время — выполнить полное сканирование и устанавливать TABLERESULTS, чтобы сканировать таблицу в выходной файл и затем сохранить результаты.

Я написал на языке T-SQL хранимую процедуру uspBuildFraglist, текст которой показан в листинге 1. Она должна автоматизировать работу команды DBCC SHOWCONTIG WITH TABLERESULTS. UspBuildFraglist в цикле проходит по списку таблиц в указанной базе данных и для каждой таблицы запускает DBCC SHOWCONTIG WITH TABLE RESULTS,ALL_INDEXES, который показывает информацию о фрагментации данных и индексов указанной таблицы. Результаты сначала сохраняются во временной таблице, затем перемещаются в постоянную таблицу в базе данных, которая была предназначена для удерживаемых данных, созданных административными процессами поддержки вроде этого.

Листинг 1 . Хранимая процедура uspBuildFraglist

Постоянная таблица имеет поле с именем LastScanTime, служащее для отслеживания просмотров таблицы. Условие IF в листинге 1 существует для проверки такого поля в каждой таблице и игнорирует выполнение оператора DBCC, если в этот день таблица была просканирована. Такая проверка позволяет процедуре uspBuildFraglist многократно запускаться без дублирования работы, проделанной предыдущими просмотрами.

Второе свойство процедуры uspBuildFraglist — это задержка после каждого сканирования. Из текста кода во фрагменте B видно, что проверяется таблица sysprocesses, чтобы определить, блокирует ли процесс просмотра какие-нибудь процессы. Если так, процедура ждет 30 секунд. Если нет, процедура ожидает 5 секунд, а затем запускает следующее сканирование. Возможность делать паузу между просмотрами — элементарное преимущество сканирования по каждой таблице отдельно вместо сканирования сразу всей базы данных. Это простой способ минимизировать возможные проблемы с потенциальными блокировками, которые могут быть вызваны DBCC.

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

Интерпретация результатов работы DBCC

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

Другой обычный метод фрагментирования как кластеризованных, так некластеризованных индексов состоит в распределении данных через какое-то время. В системах OLTP обычно новые строки добавляются почти одновременно и небольшими блоками. Поэтому во все таблицы в группе файлов будет размещаться по одному или два блока дополнительных данных одновременно. Это означает, что блоки дополнительных данных могут быть разбросаны по всему файлу. Если новые строки добавляются с одной скоростью по всем таблицам, распределение будет в достаточной степени ровным. Если в одну таблицу добавляется больше строк, чем в другие, тогда дополнительные строки таблицы могут группироваться, тогда они будут просто маленькими островами непрерывности в море разделенных строк. SQL Server никак не пытается размещать строки таблицы вместе. Поэтому по мере роста системы OLTP всегда фрагментируются.

Лучший индикатор фрагментирования индексов доступен при оценке результатов сканирования логической фрагментации с помощью команды DBCC SHOWCONTIG. Эти результаты показывают процент времени, когда страницы индекса не следуют друг за другом в порядке, заданном индексом. Так как большинство данных в индексе хранится на уровне листьев, любое значение больше 0% означает большой процент операций ввода/вывода на диске и меньше вероятность, что компонент ввода/вывода в SQL Server может читать отдельный блок дополнительных данных вместо восьми индивидуальных страниц.

Другая оценка с помощью DBCCSHOWCONTIG служит для проверки средней плотности наполнения страницы, которая показывает, насколько средняя страница наполнена данными. Хотя, возможно, в таблице нет никакой фрагментации страниц, потому что все страницы действительно непрерывны, а сама страница может иметь пустые места из-за стирания, модернизации и расщепления страниц. Поскольку SQL Server всегда читает минимум данных с полной страницы, сканирование таблицы или сканирование листьев индекса будет всегда требовать чтения большего числа страниц на количество используемых байтов, поделенных по 8192, если страница содержит пустые промежутки. Чтение этих дополнительных страниц приводит к высокой активности диска, и, поскольку таблица становится большой, имеет место растущее влияние на производительность чтения, особенно при сканировании таблицы.

Проблемы могут быть и с разбиением страниц. Простой совет: для того, чтобы уменьшить разбиение страницы, можно использовать фактор заполнения, который оставляет место для новых данных. Разбиение страниц приводит к существенному, хотя и кратковременному уменьшению производительности, по мере того как строки перемещаются на новую страницу; однако проблема в том, что SQL Server приходится читать большее количество страниц, поскольку страницы заполнены не до конца. Для таблицы с 1 млн. страниц данных и 20-процентным коэффициентом заполнения результатом будет 1,2 млн. страниц, в которцых будет дополнительно всего 16 Мбайт данных. Ключевой вопрос — будет ли краткосрочный эффект от разбиения страницы больше, чем долговременный эффект от чтения большего количества страниц? Если получается, что поддержание разрыва в страницах, дает эффект коэффициента заполнения в 50%, это говорит о том, что поддержание высокой средней плотности страницы так же важно, как наличие низкой фрагментации.

Фрагментация файлов базы данных

Если монитор производительности показывает чрезмерное количество операций ввода/вывода, о чем это говорит? И если FileMon показывает по крайней мере 65,536 байт при выполнении ввода/вывода? Это означает, что файл самой базы данных фрагментирован. Монитор производительности выводит в отчете физические метрики диска, несмотря на то что FileMon описывает ввод/вывод на уровне файловой системы. Чтобы загрузить страницу с данными, операционная система Windows должна найти смещение в файле, чтобы установить физическое расположение байтов на диске. Система NTFS пытается уменьшить фрагментацию диска, определяя место для файла в самом большом куске свободного пространства на диске, без попытки оптимизировать такое размещение. Если база данных расширялась и сжималась регулярно (т.е. использовалась команда DBCC SHRINKDB), тогда возможно, что части файлов базы данных распределены по диску, и маловероятно, что эти части упорядочены, а менеджер хранения упорядочит страницы по номеру.

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

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

Более эффективное решение для уже существующих баз данных следующее: выполнить полное резервирование базы данных, удалить базу данных (с удалением файлов), дефрагментировать диск, затем восстановить базу данных. Процесс восстановления заставит Windows выделять дисковое пространство из самых больших доступных порций свободной дисковой памяти, поэтому страницы будут непосредственно на диске, скорее всего физически непрерывны. В итоге компонент ввода/вывода должен упорядочивать запросы на страницы более эффективно, потому что диспетчер может получить доступ к большим фрагментам данных при единственной операции ввода/вывода.

Решение задачи

Ключевые проблемы, которые я обнаружил: большая таблица и фрагментированный файл базы данных, а также неэффективная плотность заполнения страниц. Я рассматривал все три проблемы, которые мне необходимо устранить, вместе, чтобы решить проблему производительности базы данных. Первая: самые большие, наиболее наполненные, используемые для решения таблицы нуждаются в индексе с внешним ключом. Эта конкретная проблема действительно не была вызвана фрагментацией, а была связана со сканированием таблицы размером в 2,5 Гбайт с внешним ключом во время обновления таблицы по первичному ключевому полю. Такое сканирование было причиной большого количества операций дискового ввода/вывода, так что нельзя было установить, существовали ли другие проблемы с диском. Добавление индекса радикально уменьшило время модернизации и время ввода/вывода операций с диском примерно до 80% при операции модернизации. Чтобы решить оставшиеся две проблемы, я написал хранимую процедуру uspDefragTables, которую ниже мы рассмотрим более подробно.

Вторая проблема, которую я обнаружил, заключалась в том, что таблица была неупорядочена, то есть ее данные были «сложены» в том порядке, в котором они были вставлены. Это само по себе не страшно; страницы таблицы были лишь немного фрагментированы. С того момента как данные были получены, приблизительно от 75 до 80% данных общего количества вставленных во все таблицы базы данных хранились близко друг к другу. Помните тем не менее, что логическая дефрагментация данных в DBCC SHOWCONTIG неприменима к неупорядоченным данным; поэтому такие данные с 0% фрагментации все еще могут быть отрицательным фактором при формировании запросов к данным приложения.

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

Третья проблема, которая делает организацию данных менее эффективной, — это когда команда DBCC SHOWCONTIG показывает, что плотность заполнения страницы менее 50%. Подразумевается, что для хранения данных таблица использовала страницы более двух раз. Слишком большое количество полупустых страниц, разбросанных по файлу базы данных, означает в реальности плохую производительность.

Хранимая процедура uspDefragTables из листинга 2 решает проблемы с плотностью заполнения страницы и организацией данных. Процедура выполняет итерацию по всем строкам таблицы, которая является результатом вывода команды DBCC SHOWCONTIG из хранимой процедуры uspBuild-Fraglist, и она ищет таблицы, которые превышают уровень фрагментации, указанный в параметре @MaxFrag, как показано во фрагменте кода А листинга 2.

Листинг 2 . Хранимая процедура uspDefragTables

Процедура UspDefragTables выполняет команду DBCC DBREINDEX для любого индекса, который имеет логическую фрагментацию или фрагментацию в данных, либо если средняя плотность заполнения страницы составляет меньше чем 100-@MaxFrag. Для кластеризованных индексов эта команда реорганизует всю таблицу. Для некластеризованных индексов она реорганизует индекс на уровне листьев.

Заметим, что процедура uspDefragTables определенно игнорирует неупорядоченные данные (indexID = 0), потому что команда DBCC DBREINDEX с такими данными не работает. Вместо DBCC DBREINDEX можно создать дефрагментированные некластеризованные индексы. Для улучшения организации потребуются другие методы, которые часто приходится применять вручную.

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

Поддержка дефрагментации

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

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

Морис Льюис (morris@holistech.com) — администратор баз данных в компании Digichart, провайдере услуг приложений для здравоохранения