SQL в вопросах и ответах : Тонкая настройка для обеспечения оптимальной производительности

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

Пол С. Рэндал

Вопрос

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

Ответ Это очень нехорошо, что SQL Server позволяет дублировать индексы, потому что дубликаты не дают никаких преимуществ. На самом деле, индексы-дубликаты во многих отношениях вредны.

Дубликаты возникают, когда создаются индексы с точно таким порядком и спецификацией ASC или DESC, как уже существующий индекс. Входящие в индекс столбцы те же самые (хотя их порядок может и меняться).

Для выполнения запросов SQL Server будет использовать только один из дублирующихся индексов, но при вставке, обновлении и удалении будут обновляться все индексы в таблице. Это означает, что при каждой операции вставки или удаления из таблицы это должно быть учтено во всех индексах. То же самое применимо к обновляемым столбцам, которые входят в индекс.

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

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

SQL Server уведомляет о создании дубликатов индексов, поэтому вина за их появление лежит целиком на вас. Проверка наличия дубликатов является непростой задачей. Она предусматривает получение средствами сценария всех определений индексов и ручного их сравнения или объемного программного анализа системных каталогов. В прошлом году Кимберли Трипп (Kimberly Tripp) опубликовала полное решение этой задачи:

Берегись отката!

Вопрос Недавно мне пришлось отменить «долгоиграющее» обновление. На следующий день после отката операции журнал транзакций вырос до невиданных размеров. Он должен быть очень маленьким, потому что в базе данных ничего не изменилось. Можете ли вы объяснить такую аномалию?

Ответ Это довольно популярное заблуждение. При откате объемной операции следующий разнозностный архив должен быть маленьким, правда? Нет, неправда.

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

При откате операции SQL Server приходится откатить все вызванные ею измененения. Это означает, что SQL Server анализирует все записи журнала транзакций, созданные в процессе выполнения операции. Ему нужно выполнить все обратные изменения в обратном порядке. Каждая запись в журнале транзакций описывает одно изменение базы данных, выполненное в рамках операции. Чтобы откатить изменение, нужно внести другое изменение, которое отменить исходное. Например, откат вставки записи выполняется путем ее удаления. В результате все выглядит так, как будто записи вообще не было.

А сейчас самая интересная часть: каждое выполненное в процессе отката изменение представляет собой еще одно (хотя и специальное) изменение. Каждому изменению в базе данных должна отвечать запись в журнале транзакций. Поэтому изменения тоже должны записываться в журнал. Это означает, что при откате крупной операции в журнале транзакций записываются действия не только «прямой» части операции, но и ее отката. В архивах журнала транзакций будут сохранены все эти записи — это объясняет большой размер архива журнала транзакций.

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

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

В поисках выбросов

Вопрос Я пытаюсь устранить неполадку, которая состоит в том, что периодически наблюдаются периоды повышенной нагрузки на подсистему ввода/вывода одного из наших серверов SQL Server. Средствами PerfMon я локализовал неполадку до конкретных контрольных точек, но не могу определить проблемную базу данных. Как мне выявить причину?

Ответ Контрольные точки создаются по двум причина. Во-первых, они обновляют страницы файлов данных информацией, ранее записанной в журнал транзакций. В SQL Server используется механизм, который называется упреждающее протоколирование и в котором изменения базы данных перед записью в файлы данных регистрируются в журнале транзакций. Это гарантирует возможность продолжения работы в случае сбоя. Во-вторых, они снижают уровень постоянной нагрузки на подсистему ввода/вывода за счет периодической (а не постоянной) записи накопленных изменений в файлы с данными.

Контрольные точки в базах данных создаются независимо. Они инициируются определенными событиями, в числе которых интервал восстановления. Он представляет собой оценку сервером SQL Server того, достаточно ли создано в журнале транзакций записей, чтобы восстановление в случае аварии заняло примерно минуту (по умолчанию).

Обычно это число равно многим десяткам тысяч записей. Чем больше страниц файлов данных меняется вследствие этих записей в журнале, тем больший объем операций ввода/вывода должно выполняться контрольными точками базы данных.

Операции ввода/вывода, связанные с контрольными точками, можно отслеживать в Мониторе производительности с помощью счетчика «Checkpoint pages/sec» объекта SQL Server:Buffer Manager. Но это дает общее число для всех баз данных определенного экземпляра SQL Server. Чтобы определить базу данных, в которой в тот или иной момент создается контрольная точка, нужно использовать флаги трассировки.

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

Включить эти флаги трассировки можно следующей командой:

DBCC TRACEON (3502, 3504, 3605, -1)

А отключить — такой командой:

DBCC TRACEOFF (3502, 3504, 3605, -1)

При создании следующих контрольных точек в журнале ошибок появятся примерно такие записи:

2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 started (8) 2011-12-30 05:07:14.390 spid17s About to log Checkpoint begin. 2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 phase 1 ended (8) 2011-12-30 05:07:14.830 spid17s FlushCache: cleaned up 4307 bufs with 201 writes in 441 ms (avoided 23 new dirty bufs) 2011-12-30 05:07:14.830 spid17s average throughput: 76.30 MB/sec, I/O saturation: 198, context switches 392 2011-12-30 05:07:14.830 spid17s last target outstanding: 15, avgWriteLatency 2 2011-12-30 05:07:14.830 spid17s About to log Checkpoint end. 2011-12-30 05:07:14.830 spid17s Ckpt dbid 21 complete

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

Проблемы консолидации

Вопрос У нас в компании введена новая политика, предусматривающая максимальную консолидацию машин для снижения затрат на оборудование. Меня также заставляют сократить число экземпляров SQL Server для снижения затрат на лицензирование. Есть ли какие-то рекомендации по тому, сколько баз данных должны размещаться на экземпляре SQL Server?

Ответ Ответ на этот вопрос: «Все очень сильно зависит от обстоятельств». Среди этих обстоятельств размер баз данных, тип нагрузки, которую они обслуживают, уровень изменчивости данных, вид необходимого регулярного обслуживания, а также требования по восстановлению в случае аварии и по высокой доступности.

У каждого экземпляра SQL Server ограниченный объем памяти, где могут размещаться обрабатываемые страницы данных, (он еще называется буферным пулом). Чем больше в экземпляре баз данных с разными типами нагрузки, тем больше конкуренция этих нагрузок за пространство буферного пула.

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

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

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

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

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

Пол С. Рэндал носит звание SQL Server MVP и занимает посты исполнительного директора SQLskills.com и регионального директора Microsoft. Пол работал в команде ядра хранения SQL Server в Microsoft с 1999 до 2007 года. Рэндал написал DBCC-инструкцию CHECKDB /repair для SQL Server 2005 и отвечал за ядро хранения при разработке SQL Server 2008. Он является экспертом по восстановлению после аварий, высокой доступности и обслуживанию баз данных и регулярно выступает с докладами на конференциях в разных странах. Адрес его блога — SQLskills.com/blogs/paul, а также его можно найти на Twitter по адресу Twitter.com/@PaulRandal.