Выбор корректной конфигурации базы данных

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

Выбор корректной конфигурации базы данных

Оптимизация производительности базы данных 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 применяет правило для всех пользователей базы данных. Если ввести правило с помощью приложения, то оно может исчезнуть из будущих версий этой программы или будет не распознано другими приложениями, которые обращаются к тем же данным. Это может привести к нарушениям целостности ссылок и, возможно, искажению данных.

1. Закуски
1. Салат Цезарь
2. Салат из свежих овощей
3. Гренки
4. Сыры
2. Супы
1. Солянка мясная
2. Борщ
3. Лагман

Текстовый процессор WORD