Лучший способ решения проблем с производительностью — просто не допустить их

Оптимизация производительности базы данных SQL Server начинается с выбора корректной конфигурации базы данных и модели данных. Можно повысить быстродействие, дополнив базу данных индексами различных типов и более мощными аппаратными средствами, но полностью ликвидировать недостатки модели данных все равно не удастся. Следствием неудачной конфигурации базы данных или модели данных может стать слишком большое время отклика системы, блокированные или зависшие транзакции, неверные или неточные результаты при подготовке бизнес-отчетов, рассинхронизация данных, несогласованность данных и невозможность составить запрос для извлечения нужных данных. Но неудачная модель данных — не единственная причина таких проблем. Например, медленный отклик системы может быть результатом перегруженности сервера. Неудачное сочетание обновлений транзакции от конфликтующих приложений может привести к зависанию или блокировке. Следует всегда тщательно исследовать причины неполадок. Если не удается обнаружить перегруженный процессор или конфликт между двумя транзакциями, которые пытаются монопольно завладеть одним информационным ресурсом, необходимо внимательно рассмотреть конфигурацию базы данных и модели данных; именно они могут быть причиной неприятностей.

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

Подготовка среды

Оптимизация производительности SQL Server начинается с операционной системы Windows. SQL Server может работать только в среде Microsoft, поэтому исключительно важное условие успеха — взаимопонимание с системным администратором Windows. Два важнейших параметра сервера базы данных — файловая система и файл подкачки. Для SQL Server следует использовать файловую систему NTFS — она более стабильна и лучше защищена, нежели FAT, хотя считается, что операции записи чуть быстрее выполняются в FAT. При настройке файла подкачки практическое правило для виртуальной памяти — установить статический размер в 1,5 раза больше размера физической памяти. Кроме того, если какой-нибудь компонент сервера, такой как сетевая плата или жесткий диск, переходит в режим ожидания после периода неактивности, то следует запретить переход в режим ожидания (или поручить сделать это системному администратору). Лучше не рисковать необходимостью «холодной» загрузки для активизации элемента компьютера. В многопротокольной среде следует убедиться, что TCP/IP — первый в наборе протоколов. Если сетевые соединения имеют малую пропускную способность, то нужно, чтобы стандартная величина тайм-аута при регистрации превышала время регистрации приложений, использующих базу данных.

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

Оптимизация производительности SQL Server невозможна без корректной конфигурации базы данных. Для повышения быстродействия базы данных следует отнести основные типы данных, которые предстоит хранить в базе, к отдельным группам файлов. Нужно отделить системные таблицы от пользовательских таблиц, данные от индексов, табличные данные от изображений, текста и n-текста (ntext используется для хранения строк символов Unicode). Применяя эту схему для разделения данных на несколько групп файлов, можно построить чрезвычайно масштабируемую базу данных. Напомню, что масштабируемость — это возможность увеличить число обрабатываемых транзакций без снижения производительности. В малых системах можно собрать все файловые группы на одном диске (за исключением журнала транзакций, который следует всегда хранить на другом диске, отдельно от прочих данных). По мере расширения системы, увеличения числа пользователей и объема данных, можно перемещать различные группы файлов на отдельные диски, тем самым распределяя рабочую нагрузку между несколькими дисками. Благодаря разделению базы данных на несколько групп файлов управление резервным копированием упрощается. Можно использовать группы файлов для резервного копирования очень больших баз данных (very large database, VLDB) во временном окне, отведенном для копирования базы данных. Более подробная информация об использовании групп файлов для резервного копирования приведена в статье Кимберли Трипп «Пока не грянул гром» на сайте www.windowsitpro.ru по адресу http://www.osp.ru/win2000/sql/200309sq477.htm. Группы файлов можно использовать и для горизонтального разделения, которое описано в статье «Возвращение к жизни» по адресу http://www.osp.ru/win2000/sql/admsecrets/401_1.htm (www.windowsitpro.ru). При проектировании высокопроизводительной базы данных группы файлов — полезный инструмент, который поможет избежать возникновения проблем еще до начала работы.

Исходный текст в листинге 1 иллюстрирует мой обычный способ построения базы данных. Каждая группа файлов имеет три имени: имя группы файлов, логическое имя файла и физическое имя файла. Эти имена можно увидеть, открыв любое окно свойств базы данных, а затем выбрав вкладку Data Files. На этой вкладке элементы столбца Filegroup соответствуют PRIMARY и именам групп файлов, которые приведены в первой части листинга 1. Элементы в столбце Location — это имена физических файлов, в которые входит полный путь к месту хранения физического файла на жестком диске. Системные таблицы SQL Server следует поместить в группу PRIMARY, а пользовательские таблицы и индексы — в соответствующие группы файлов, отдельно от системных таблиц SQL Server. Изображения и текстовые данные размещаются в собственной группе файлов, как показано во фрагменте исходного текста с меткой A (листинг 2).

Параметры конфигурирования, следующие за командой CREATE DATABASE, устанавливаются в соответствии со стандартом ANSI SQL-92. Возможно, один или несколько параметров придется изменить в соответствии с требованиями конкретного предприятия. Следует убедиться, что заданные параметры конфигурирования совместимы с конкретной средой.

Целостность — обязательное условие

Следующий шаг в оптимизации производительности базы данных — настроить SQL Server на принудительную целостность ссылок. Например, между таблицами Store и Sale в листинге 2, адаптированном из базы данных pubs, существует отношение зависимости. Продажа (Sale) не может осуществляться без связи со складом (Store). Целостность ссылок означает, что данная бизнес-связь реализуется одним из двух способов. Можно назначить эту задачу приложению вне SQL Server или предоставить SQL Server возможность установить данное правило. На мой взгляд, ведение статических правил, таких как целостность ссылок между Store и Sale, лучше предоставить базе данных. Исходный текст для правила составляется один раз (фрагмент с меткой B в листинге 2). Затем SQL Server применяет правило для всех пользователей базы данных. Если ввести правило с помощью приложения, то оно может исчезнуть из будущих версий этой программы или будет не распознано другими приложениями, которые обращаются к тем же данным. Это может привести к нарушениям целостности ссылок и, возможно, искажению данных.

В дополнение к обеспечению целостности ссылок внутри SQL Server я рекомендую использовать DRI (declarative referential integrity — декларативная целостность ссылок) вместо триггеров или хранимых процедур. DRI — ограничение, а ограничения исполняются более эффективно, чем триггеры или хранимые процедуры, особенно если для передачи данных в базу используются массивные операции загрузки информации.

В листинге 2 показано, как организовать DRI между таблицами Store и Sale. Как отмечалось выше, эти две таблицы адаптированы из базы данных pubs. Я изменила столбцы и вставила параметры, необходимые в хорошо организованной производственной среде, а также добавила несколько столбцов, чтобы проиллюстрировать влияние модели данных на производительность. Оператор CREATE TABLE для таблицы dbo.Store добавляет новый столбец, StorePhoto, в котором содержатся изображения. Следует отметить, что в последней строке оператора CREATE TABLE данные направляются на хранение в группу файлов MyDatabase_data, но связанные с ними изображения будут сохраняться в группе файлов MyDatabase_image.

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

Очевидно, что стандартные имена ограничений, назначаемых SQL Server, являются значимыми. Например, FK__sales__stor_id0AD2A005, имя оригинального ограничения внешнего ключа между Stores и Sales в моем экземпляре базы данных pubs, явно относится к внешнему ключу в таблице Sales, который использует столбец stor_id. Однако из имени ограничения нельзя понять, на какую таблицу производится ссылка. Мое соглашение об именовании, FK_Sale2Store, более краткое, и, хотя в моем имени не содержится информации о столбце, оно говорит о том, что Sale зависит от Store. Кроме того, мне не нужно выполнять запрос, чтобы получить полное имя ограничения. Достаточно следовать стандарту, установленному для имен ограничений внешнего ключа. Имя ограничения любого внешнего ключа будет начинаться с FK_, за которым следует имя зависимой таблицы, номер 2 и, наконец, имя независимой таблицы.

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

Иллюстрация данного подхода к кластеризации — кластеризованный индекс во фрагменте C листинга 2, который был построен для таблицы Sale с использованием StoreCode и SaleID. Отчеты о продажах будут выпускаться ежедневно или ежечасно и сортироваться сначала по магазинам (представленным StoreCode), затем по SaleID. Значения SaleID возрастают в течение дня (SaleID — идентификатор), поэтому они фактически представляют собой последовательные номера записей; как они, так и значения SaleID увеличиваются с каждой новой продажей. Большинство отчетов представляют собой итоговые отчеты с итоговыми данными по отдельным магазинам. StoreCode вместе с SaleID — отличный кандидат на кластеризацию. Благодаря кластерному индексу ускоряется извлечение данных, так как данные уже упорядочены — в данном случае сначала по Store Code, а затем, внутри каждого значения Store Code, по SaleID. Собственно, StoreCode связывает таблицу Sale с таблицей Store. Наличие двух индексов — одного кластерного, одного некластерного, каждый из которых начинается со StoreCode, возможно, избыточно, но в ходе эксплуатации базы данных можно будет увидеть, как SQL Server использует (или не использует) эти индексы. Если SQL Server не задействует некластеризованный индекс, то от него можно смело отказаться. Однако на стадии проектирования рекомендуется построить отдельный индекс для каждого столбца внешнего ключа, даже если поначалу это приведет к избыточности индексов.

Если возможно, следует определить стандартные значения и контрольные ограничения (check constraint задает диапазон значений элементов столбцов) в базе данных, а не на прикладном уровне по той же причине, по которой предпочтительно возложить на базу данных управление целостностью ссылок. Ограничения — объекты базы данных, поэтому они выполняются быстрее и эффективнее, чем программный код во внешнем приложении. Если правила, которые могут быть выражены по умолчанию, и контрольные ограничения статичны — не меняются каждую неделю или каждый месяц, то эти правила можно определить как ограничения столбцов или таблиц. В операторе CREATE TABLE dbo.Sale листинга 2 столбец SaleDate имеет стандартное значение CURRENT_TIMESTAMP. Это правило вряд ли изменится за все время эксплуатации базы данных, поэтому его удобно определить как ограничение столбца. Следующее правило, SalePayTerms, выражено как контрольное ограничение. SalePayTerms не может иметь значение NULL, и первоначально для SalePayTerms определены значения Net 30, Net 60 и On Invoice. Приложение не может изменить этот набор значений. Если нужно добавить значение в набор, то следует изменить данное ограничение столбца. Если предполагается, что набор значений будет часто изменяться, то более удобный способ ввести ограничение — составить просмотровую таблицу условий оплаты покупок, присвоить каждой строке таблицы уникальное значение в качестве идентификатора и сопоставить просмотровую таблицу с dbo.Sale в соотношении «один ко многим» (1:M). Отношение между просмотровой таблицей и dbo.Sale будет зависимым отношением, осуществляемым таким же образом, как отношение между dbo.Store и dbo.Sale.

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

Мишель Пуле - Имеет сертификаты MCIS и MCP. Входит в число основателей консалтинговой компании Mount Vernon Data Systems, шт. Колорадо. В университете Дэнвера в качестве адъюнкта преподает программирование и проектирование баз данных. mapoolet@sqlmag.com

SQL Server на массиве RAID

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

В RAID уровня 0 задействован метод расщепления данных, который формирует дисковый раздел, охватывающий несколько жестких дисков; при этом используются преимущества наличия нескольких рабочих головок чтения/записи на многих шпинделях (аналогично методу чередования в Windows NT). RAID уровня 0 — самый быстрый тип RAID, но в отличие от большинства реализаций RAID он не обеспечивает отказоустойчивости. Если один из дисков в массиве RAID 0 отказывает, то все данные теряются. Не следует использовать RAID 0 для хранения важных данных.

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

В массиве RAID 1 используется зеркалирование (mirroring) — метод, при котором зеркальная копия каждого диска сохраняется на другом диске. Зеркалирование — самая отказоустойчивая конфигурация RAID, но одновременно и самая дорогостоящая из-за необходимости применения дополнительных аппаратных средств и программного обеспечения. SQL Server сохраняет данные последовательно в журналах транзакций и в TempDB, так что эти важные части базы данных хорошо приспособлены для защиты по RAID с уровнем 1. Журнал транзакций и TempDB следует разместить по крайней мере на устройстве RAID 1, даже если финансовые возможности компании не позволяют применить RAID для других частей базы данных.

RAID 10 — комбинация RAID 1 и RAID 0, в которой используются методы зеркалирования и расщепления. Это дорогостоящий, но самый быстрый вариант, который обеспечивает лучшие избыточность и производительность. В RAID 10 зеркалируется два или несколько дисков RAID 0. Если предприятие располагает достаточными средствами, то в целях дополнительной защиты лучше разместить журнал транзакций и TempDB на устройстве RAID 10, а не на RAID 1. Сравнение характеристик различных типов RAID приведено в учебнике по RAID компании Advanced Computer and Network Corporation (http://www.acnc.com/raid.html).