SQL Server 2008: Минимизация блокирования в SQL Server

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

Наблюдение за системой

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

• затрагиваемый объект, грануляция и тип блокировки;
• длительность блокировки и блокирования;
• выполняемая команда SQL (имя хранимой процедуры, инструкция SQL);
• информация о цепочке блокировок, где применимо;
• использование системной доступной емкости блокировок.

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

 Рис 3 Capturing locking stats

SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to 
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode 
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance, 
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id 
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0

DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

SET @SQL = N'
USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions 
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                                     END
                          FROM    sys.allocation_units 
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL
                    END

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
        @SQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT

Также можно отслеживать укрупнения в системе с помощью приложения SQL Profiler (событие Lock:Escalation), динамического административного представления dm_db_index_operational_stats (index_lock_promotion_count) или регулярного опроса информации о блокировках системы. Необходимые сведения, собираемые при отслеживании укрупнений, включают сведения о том, гарантирует ли обработка укрупнение; если укрупнение не гарантируются, соответствующие хранимые процедуры могут указать исходную причину проблем производительности. Таблица с данными большого объема или частым одновременным использованием должна быть основным объектом оценки.

После сбора данных о блокировках, блокированиях и укрупнениях они могут быть проанализированы для определения общего времени блокировки и блокирования (количество возникновений, умноженное на длительность) для каждого объекта. Обычно это может вызвать последовательный цикл настройки производительности, в котором выполняется внесение, отслеживание, анализ и исправление изменений. Иногда требуется только простое изменение, такое как добавление индекса для значительного увеличения производительности и изменения области в системе с наиболее сильным негативным влиянием на производительность.

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