2.3.2. Повышение производительности ms sql Server

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

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

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

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

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

В дополнение к оптимизации операционной системы для работы с SQL Server следует повысить отказоустойчивость среды.

Оптимизация производительности SQL Server невозможна без корректной конфигурации базы данных. Для повышения быстродействия базы данных следует отнести основные типы данных, которые предстоит хранить в базе, к отдельным группам файлов. Нужно отделить системные таблицы от пользовательских таблиц, данные от индексов, табличные данные от изображений, текста и n-текста (ntext используется для хранения строк символов Unicode). Применяя эту схему для разделения данных на несколько групп файлов, можно построить чрезвычайно масштабируемую базу данных.

Масштабируемость — это возможность увеличить число обрабатываемых транзакций без снижения производительности. В малых системах можно собрать все файловые группы на одном диске (за исключением журнала транзакций, который следует всегда хранить на другом диске, отдельно от прочих данных). По мере расширения системы, увеличения числа пользователей и объема данных, можно перемещать различные группы файлов на отдельные диски, тем самым распределяя рабочую нагрузку между несколькими дисками. Благодаря разделению базы данных на несколько групп файлов управление резервным копированием упрощается. Можно использовать группы файлов для резервного копирования очень больших баз данных (very large database, VLDB) во временном окне, отведенном для копирования базы данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

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.