Блокирование необходимо для поддержки текущих действий чтения и записи базы данных, но оно может отрицательно влиять не производительность системы, и иногда трудно обнаруживаемыми способами. В данной статье рассматривается оптимизация базы данных SQL Server 2005 и SQL Server 2008 для минимизации блокирования, а также отслеживание состояния системы для лучшего понимания воздействия блокирования на производительность.
Блокирование и укрупнение
SQL Server® выбирает наиболее подходящую грануляцию блокировки на основе количества затрагиваемых записей и существующих в системе одновременных действий. По умолчанию SQL Server использует наименьшую возможную грануляцию блокировки, выбирая крупногранулированные блокировки только в случае возможности более эффективного использования системной памяти. SQL Server укрупняет блокировку, если это повышает общую производительность системы. Как показано на рис. 1, укрупнение происходит в случае, если количество блокировок в определенном просмотре превышает 5 000, или если память, используемая системой для блокировок, превышает доступный объем:
Рис 1 Условия возникновения укрупнения блокировок
- ядром СУБД используется 24 процента памяти не AWE при параметре блокировок - 0;
- ядром СУБД используется 40 процентов памяти не AWE при параметре блокировок, отличном от 0.
Возникающая блокировка всегда является блокировкой таблицы.
Предотвращение появления ненужных блокировок
Блокирование может происходить с любой грануляцией, но ее воздействие увеличивается при возникновении укрупнения. Укрупнение блокировок может свидетельствовать о неэффективной архитектуре, коде или настройке приложения.
Соблюдение основ структуры базы данных (таких как нормализованная схема с узкими ключами и избегание использования массовых операций с данными в транзакционных системах) является важным для предотвращения блокировок. При несоблюдении этих принципов (например, при отделении системы отчетности от транзакционной системы или обработке потоков данных в нерабочее время) настройка системы будет затруднена.
Индексирование может быть ключевым фактором определения количества блокировок, необходимых для доступа к данным. Индекс может уменьшить количество записей, доступных для запроса, путем уменьшения количества внутренних просмотров, которые должны быть выполнены ядром СУБД. Например, при выборе одной строки таблицы в неиндексированном столбце все строки таблицы должны быть временно заблокированы до определения необходимой записи. Если этот столбец был индексирован, потребуется только одна блокировка.
Серверы SQL Server 2005 и SQL Server 2008 содержат динамические административные представления (sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details), отображающие таблицы и столбцы, получающие преимущества от индексов, на основе статистики суммарного использования.
Проблемы производительности также могут быть связаны с фрагментацией в том отношении, что ядру СУБД может требоваться доступ к большему количеству страниц, чем обычно необходимо. Более того, неверная статистика может быть причиной выбора оптимизатором запросов менее эффективного плана.
Следует иметь в виду, что, хотя индексы убыстряют доступ к данным, они могут замедлять изменение данных, поскольку требуется не только изменение базовых данных, но и обновление индексов. Динамическое административное представление sys.dm_db_index_usage_stats показывает частоту использования индексов. Распространенным примером неэффективных индексов являются составные индексы, в которых один столбец индексируется отдельно и вместе. Поскольку SQL Server обращается к индексам слева направо, индекс используется, если самые левые столбцы полезны.
Таблицы секционирования могут оптимизировать систему (уменьшая воздействие блокировки) и поделить данные на отдельные физические объекты, обеспечивая возможность работы с ними по отдельности. Хотя секции по строкам - наиболее очевидный способ секционирования данных, также заслуживает внимание секционирование данных по горизонтали. Можно специально выбрать денормализацию путем разделения таблицы на отдельные таблицы с таким же числом строк и ключей, но различным числом столбцов, чтобы уменьшить возможность того, что отдельные процессы одновременно захотят получить монопольный доступ к одним и тем же данным.
Чем более разнообразны способы доступа приложения к определенной строке данных и чем больше столбцов могут быть включены в эту строку, тем более привлекательным является подход с секционированием столбцов. Иногда этот подход может быть выгоден для очереди приложения и таблиц состояния. В SQL Server 2008 добавлена возможность отключения укрупнений блокировок для отдельных секций (или таблиц, если для таблицы не включены секции).
Оптимизация запроса
Оптимизация запроса играет важную роль в улучшении производительности. Существует три возможных подхода.
Уменьшение транзакции Одним из наиболее важных способов уменьшения блокировок и увеличения общей производительности является обеспечение минимально возможного размера транзакций. Для уменьшения транзакции должны быть выполнены все операции обработки, не являющиеся критическими для целостности транзакции (например, поиск связанных данных, индексирование и чистка данных).
SQL рассматривает все инструкции как неявные транзакции. Если инструкция влияет на большое число строк, одна инструкция может составлять большую транзакцию, особенно при вовлечении большого количества столбцов или если столбцы содержат большой тип данных. Одна инструкция также может вызывать разбиения страницы при высоком коэффициенте заполнения или если инструкция UPDATE заполняет столбец более широким значением, чем выделенное. В таких случаях может быть полезным разделение транзакции на группы строк и их поочередная обработка до полного выполнения. Возможность использования пакетной обработки следует рассматривать только в том случае, если отдельная инструкция или группы инструкций могут быть поделены на пакеты меньшего размера, которые могут считаться полными элементами работы при сбое или успешном выполнении.
Последовательное выполнение транзакции Намеренное последовательное выполнение инструкций транзакции может уменьшить вероятность возникновения блокировки. Необходимо иметь в виду два принципа. Во-первых, доступ к объектам должен осуществляться в одинаковом порядке во всем коде SQL в системе. Несоблюдение порядка может привести к взаимоблокировкам, если конкурирующие процессы обращаются к данным в различном порядке, что приводит к возникновению системной ошибки для одного из процессов. Во-вторых, часто используемые или требующие больших затрат для доступа объекты должны располагаться в конце транзакции. SQL ожидает блокирования объектов до того, как они станут необходимы в транзакции. Задержка доступа к "горячим точкам" позволяет этим объектам удерживать блокировки меньший процент времени.
Использование подсказок блокировки Подсказки блокировки могут использоваться для на уровне сеанса или инструкции для определенной таблицы или представления. Типичным случаем использования подсказки уровня сеанса является пакетная обработка в хранилище данных, если известен процесс, который будет выполняться единственным в определенное время для этого набора данных. С помощью команды SET ISOLATION LEVEL READ UNCOMMITTED в начале хранимой процедуры сервер SQL Server не резервирует никакие блокировки чтения, таким образом уменьшая общие издержки блокировки и повышая производительность.
Типичным случаем использования подсказки уровня инструкции является ситуация известности о возможности безопасного возникновения "грязного" чтения (например, при чтении одной строки таблицы, которая никогда не понадобится остальным параллельным процессам) или неудачи остальных действий по настройке производительности (создание схемы, создание и сопровождение индекса и настройка запроса) и необходимости принудительного использования компилятором определенного вида подсказки.
Подсказки блокировки строк имеют смысл, если в результате наблюдения выяснено возникновение блокировок с большей гранулярностью, а запрос влияет на небольшое количество записей, поскольку это может уменьшить блокирование. Подсказки блокировок таблиц имеют смысл, если в результате наблюдения выяснено, что удерживаются (и не укрупняются) блокировки с меньшей гранулярностью, если запрос влияет практически на все записи в таблице, поскольку это может уменьшить системные ресурсы, необходимые для удержания блокировок. Имейте в виду, что указание подсказки блокировки не гарантирует укрупнение блокировки, если количество блокировок достигает порога для системной памяти. Однако это предотвращает все иные укрупнения.
Настройка конфигурации
Как показано на рис. 2, при настройке системы сервера SQL Server необходимо учитывать ряд факторов.
Рис 2 Определение сервером SQL Server объема памяти, который может использоваться для блокировки
Память Блокировки всегда хранятся в памяти не AWE, поэтому увеличение объема памяти не AWE ведет к увеличению емкости системы для хранения блокировок.
При попытке увеличения емкости блокировок наилучшим вариантом является использование 64-разрядной архитектуры, поскольку 32-разрядная архитектура ограничена 4 ГБ памяти не AWE, тогда как 64-разрядная архитектура не имеет такого ограничения.
В 32-разрядных системах можно использовать дополнительный гигабайт памяти операционной системы для сервера SQL Server путем добавления параметра /3GB к файла Boot.ini.
Параметры конфигурации SQL Server С помощью процедуры sp_configure можно настроить различные параметры, влияющие на блокировку. Параметр блокировок определяет количество блокировок, которое может храниться в системе до возникновения ошибки. Значение этого параметра по умолчанию - 0, что означает, что сервер динамически регулирует количество зарезервированных блокировок с другими процессами, конкурирующими за доступ к памяти. SQL изначально резервирует 2 500 блокировок, а каждая блокировка занимает 96 байт памяти. Выгружаемая память не используется.
Параметры минимального и максимального объема памяти резервируют объем памяти, используемый сервером SQL Server, таким образом настраивая сервер на статическое использование памяти. Поскольку укрупнение блокировок относится к доступной памяти, резервирование памяти от конкурирующих процессов может положительно влиять на возможность возникновения укрупнений.
Параметры соединения По умолчанию для блокирующих блокировок истечение времени ожидания не происходит, но можно использовать параметр @@LOCK_TIMEOUT, вызывающий возникновение ошибки при превышении порога ожидания снятия блокировки.
Флаги трассировки Два флага трассировок используются специально для укрупнений блокировок. Один из них - флаг трассировки 1211, отключающий укрупнения блокировок. Если количество используемых блокировок превышает доступную память, выдается ошибка. Другой флаг трассировки - 1224, отключающий укрупнения блокировок для отдельных инструкций.