Как посмотреть блокировки в ms sql

В MS SQL блокировки могут возникать на уровне строк, страниц или таблиц, что напрямую влияет на производительность и отклик приложений. Определение источника блокировок требует анализа системных представлений, таких как sys.dm_exec_requests и sys.dm_tran_locks, где фиксируются активные транзакции и ресурсы, занятые блокировками.

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

Важный этап – визуализация зависимостей блокировок через blocking chain, где можно увидеть последовательность сессий, удерживающих и ожидающих ресурсы. Это особенно важно при устранении тупиковых ситуаций (deadlock), где корректное определение лидирующей транзакции позволяет минимизировать потери производительности и предотвратить повторение проблемы.

Использование T-SQL для мониторинга блокировок в реальном времени обеспечивает возможность оперативного реагирования. Запросы, объединяющие sys.dm_exec_requests, sys.dm_exec_sessions и sys.dm_tran_locks, позволяют точно определить тип блокировки, длительность ожидания и идентификаторы процессов, что критично при работе с крупными OLTP-базами и нагрузочными сценариями.

Использование системного представления sys.dm_exec_requests для поиска блокировок

Системное представление sys.dm_exec_requests предоставляет информацию о текущих запросах, выполняемых на сервере, включая данные о блокировках и ожиданиях ресурсов.

Для обнаружения блокировок важно обратить внимание на следующие столбцы:

• session_id – идентификатор сессии, выполняющей запрос.
• blocking_session_id – идентификатор сессии, блокирующей текущую.
• wait_type – тип ожидания; для блокировок это чаще всего LOCK или LCK_M_*.
• wait_time – время ожидания в миллисекундах, позволяет оценить степень блокировки.
• resource_description – описание ресурса, на котором возникла блокировка.

Пример запроса для выявления блокировок:

SELECT
r.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.resource_description,
s.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.blocking_session_id > 0;

Рекомендации при работе с sys.dm_exec_requests:

1. Регулярно проверяйте blocking_session_id для выявления зависших запросов.
2. Используйте wait_time для определения приоритетов устранения блокировок.
3. С помощью sql_handle можно получить текст запроса и оценить, какие операции вызывают конфликты.
4. Для долгих блокировок анализируйте связанные индексы и транзакции в sys.dm_tran_locks.

Использование sys.dm_exec_requests позволяет не только выявлять текущие блокировки, но и получать контекст выполняемых запросов, что критично для оперативного устранения проблем производительности.

Определение блокирующих и заблокированных сессий через sys.dm_tran_locks

Для точного анализа блокировок в MS SQL используйте представление sys.dm_tran_locks. Оно содержит информацию о текущих транзакциях, типах блокировок и идентификаторах ресурсов.

Пример запроса для выявления блокирующих и заблокированных сессий:

SELECT
tl.request_session_id AS blocked_session_id,
wt.blocking_session_id AS blocking_session_id,
tl.resource_type,
tl.resource_database_id,
tl.resource_associated_entity_id,
tl.request_mode,
tl.request_status
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.dm_exec_requests AS wt
ON tl.request_session_id = wt.session_id
WHERE wt.blocking_session_id <> 0
ORDER BY wt.blocking_session_id;

Разбор полей:

• blocked_session_id – ID сессии, ожидающей ресурс.
• blocking_session_id – ID сессии, удерживающей блокировку.
• resource_type – тип объекта: OBJECT, PAGE, KEY и т.д.
• resource_database_id – идентификатор базы данных.
• resource_associated_entity_id – идентификатор таблицы или строки (в зависимости от типа ресурса).
• request_mode – режим блокировки: IX, S, X и др.
• request_status – статус запроса: GRANT или WAIT.

Рекомендации по использованию:

1. Фокусируйтесь на WAIT в request_status для выявления заблокированных сессий.
2. Используйте blocking_session_id для определения причин задержки и при необходимости планирования разрыва блокировок.
3. Сравнивайте resource_associated_entity_id с OBJECT_ID таблиц для точного определения заблокированных объектов.
4. Для долгих блокировок создавайте отчеты с sys.dm_exec_sessions и sys.dm_exec_requests для сопоставления пользователей и выполняемых команд.
5. Применяйте индексы и минимизацию транзакций, чтобы уменьшить вероятность конфликтов.

Просмотр текущих блокировок с помощью sp_who2

Для идентификации блокировок в MS SQL Server используется системная процедура sp_who2. Она возвращает информацию о текущих соединениях, их статусе, заблокированных и блокирующих процессах.

• SPID – идентификатор процесса SQL Server.
• Status – состояние соединения, например, RUNNABLE или SLEEPING.
• BlkBy – SPID процесса, который блокирует текущий процесс. Если значение равно 0, блокировок нет.
• CPUTime и DiskIO – помогают определить ресурсоемкие процессы.
• ProgramName и Login – источник запроса, полезно для идентификации пользователя или приложения, создающего блокировку.

Для выявления проблемных блокировок рекомендуется фильтровать результаты: EXEC sp_who2; и смотреть строки, где BlkBy не равен 0. Это позволит быстро определить блокирующие процессы.

Если блокировка вызвана конкретным запросом, SPID можно использовать для дальнейшего анализа через DBCC INPUTBUFFER(SPID), чтобы увидеть точный SQL-запрос, вызывающий блокировку.

Для регулярного мониторинга блокировок удобно сочетать sp_who2 с фильтрацией по Status = ‘RUNNABLE’ и BlkBy > 0, что помогает отслеживать активные задержки в работе базы данных и принимать решения о завершении или оптимизации запросов.

Составление отчета о блокировках с использованием Activity Monitor

Откройте SQL Server Management Studio и подключитесь к необходимому экземпляру сервера. В панели Object Explorer щелкните правой кнопкой мыши на имени сервера и выберите «Activity Monitor».

В верхней части окна Activity Monitor откройте раздел «Processes». Здесь отображаются все активные сеансы, включая идентификатор SPID, имя пользователя, базу данных и состояние запроса.

Для выявления блокировок обратите внимание на колонку «Blocked By». Если в этой колонке указан SPID, это означает, что текущий процесс ожидает завершения другого запроса.

Используйте фильтры в Activity Monitor для ограничения отображения процессов по базе данных, пользователю или состоянию блокировки. Это ускоряет анализ при большом количестве сеансов.

Для детализации блокировок выделите интересующий SPID и нажмите правой кнопкой мыши, выбрав «Details». В открывшемся окне отображается блокируемый объект, выполняемая команда и время ожидания.

Чтобы зафиксировать информацию для отчета, воспользуйтесь функцией экспорта данных Activity Monitor в файл CSV. Выделите строки, щелкните правой кнопкой мыши и выберите «Save Grid As».

Рекомендуется сохранять отчеты с отметкой времени и включать SPID, имя пользователя, базу данных, состояние запроса и источник блокировки. Это позволяет анализировать динамику блокировок и выявлять проблемные сценарии.

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

Применение запросов с JOIN между sys.sysprocesses и sys.dm_exec_sessions для диагностики

Для детального анализа блокировок в MS SQL целесообразно объединять данные из устаревшей представления sys.sysprocesses и современного DMV sys.dm_exec_sessions. Такой подход позволяет сопоставить технические свойства процессов с информацией о сессиях, включая логин, статус и активные запросы.

Пример запроса для диагностики:

SELECT sp.spid, sp.blocked, sp.waittype, sp.waittime, sp.lastwaittype,
s.session_id, s.login_name, s.status, s.host_name, s.program_name
FROM sys.sysprocesses sp
JOIN sys.dm_exec_sessions s ON sp.sid = s.security_id
WHERE sp.blocked <> 0;

В этом примере sp.blocked указывает на ID процесса, который удерживает блокировку. sp.waittype и sp.lastwaittype показывают тип ожидания, а sp.waittime – время ожидания в миллисекундах. Параметры из sys.dm_exec_sessions предоставляют контекст сессии: имя пользователя, хост и программу, инициировавшую запрос.

Для глубокого анализа рекомендуется добавить CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle), чтобы получить текст выполняемого запроса и идентифицировать конкретный ресурс, вызывающий блокировку.

Рекомендуется фильтровать результаты по времени ожидания или типу блокировки для выделения критичных процессов. Также полезно включать host_name и program_name, чтобы понять, какие приложения создают узкие места в производительности.

Использование функции sys.dm_os_waiting_tasks для анализа ожиданий блокировок

Функция sys.dm_os_waiting_tasks позволяет выявлять активные ожидания и блокировки в MS SQL Server. Она возвращает сведения о потоках, которые находятся в состоянии ожидания ресурсов, включая блокировки, на которые они не могут получить доступ.

Основные полезные поля для анализа блокировок:

Пример запроса для анализа текущих блокировок:

SELECT
session_id,
blocking_session_id,
wait_type,
wait_duration_ms,
resource_description
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0;

Рекомендации при работе с результатами:

• Фокусируйтесь на blocking_session_id, чтобы выявить источник блокировки.
• Сортировка по wait_duration_ms позволяет определить самые длительные ожидания.
• С помощью resource_description можно локализовать конкретные объекты базы данных, вызывающие блокировку.
• Для устранения блокировок анализируйте транзакции в sys.dm_tran_session_transactions и идентифицируйте долгоживущие транзакции.

Использование sys.dm_os_waiting_tasks в комбинации с sys.dm_exec_requests и sys.dm_tran_locks позволяет составить полную картину блокировок и принять меры по оптимизации параллельного доступа к данным.

Фиксация и отслеживание блокировок с помощью SQL Trace и Extended Events

SQL Trace позволяет фиксировать блокировки через стандартные события типа Lock:Acquired и Lock:Released. Для точного мониторинга рекомендуется создать трассировку с фильтром по базе данных и уровням блокировок, например, PAGE и KEY. Использование колонки ObjectName позволяет идентифицировать таблицы и индексы, вызывающие блокировки. Для предотвращения перегрузки сервера следует ограничивать трассировку по времени и количеству событий.

Extended Events предоставляют более гибкий инструмент. Сессия должна включать события lock_acquired, lock_released и blocked_process_report. Настройка фильтров по database_id и duration блокировки позволяет сосредоточиться на критических задержках. В качестве целевого хранилища рекомендуется использовать event_file, что позволяет анализировать данные в формате XEL без влияния на производительность.

Для системного анализа блокировок полезно объединять данные SQL Trace и Extended Events. Например, SQL Trace обеспечивает быстрый контроль конкретных объектов, а Extended Events фиксирует долгие блокировки и сложные взаимозависимости между процессами. Рекомендуется регулярно архивировать файлы трассировок и XEL-файлы, чтобы сохранять историю блокировок и создавать отчеты с периодической детализацией по объектам, типам блокировок и пользователям.

Дополнительно, при Extended Events можно настроить action sql_text и session_id, чтобы сразу видеть выполняемый SQL-код и идентификатор сессии, вызвавшей блокировку. Это позволяет точно локализовать проблемные запросы и планировать оптимизацию индексов или перераспределение транзакций.

Вопрос-ответ:

Какие системные представления помогают отследить блокировки в MS SQL?

В MS SQL для анализа блокировок обычно используют представления sys.dm_exec_requests и sys.dm_tran_locks. Первое показывает текущие выполняющиеся запросы, включая информацию о блокировках, которые они держат или ожидают. Второе позволяет получить детальные сведения о типах и уровнях блокировок, а также о связанных сессиях и объектах. Комбинируя эти представления, можно выявить, какие транзакции вызывают задержки и на каких таблицах или строках.

Как определить, какая сессия удерживает блокировку, а какая ожидает её освобождения?

Для этого применяют запрос, который объединяет sys.dm_exec_requests и sys.dm_tran_locks по session_id. В результатах будет видно, какие сессии находятся в состоянии ожидания, а какие держат блокировку. Например, столбец wait_type показывает тип ожидания, а blocking_session_id указывает на идентификатор сессии, вызывающей задержку. Такой подход помогает быстро находить узкие места и оценивать влияние конкретной транзакции на работу базы.

Можно ли посмотреть историю блокировок или только текущее состояние?

Стандартные представления MS SQL показывают только текущее состояние блокировок. Для анализа истории используют расширенные инструменты, такие как SQL Server Profiler или Extended Events. Они позволяют логировать события блокировок и ожиданий, а затем анализировать их после возникновения. Это особенно полезно для выявления периодических проблем с производительностью, которые не фиксируются при обычном мониторинге.

Какие типы блокировок существуют и как их различать в MS SQL?

В MS SQL есть несколько типов блокировок: Shared (S), Exclusive (X), Update (U) и Intent (I). Shared-блокировка разрешает другим транзакциям чтение, но запрещает запись. Exclusive-блокировка полностью блокирует объект для других операций. Update-блокировка используется для предотвращения конфликтов при последующем обновлении. Intent-блокировки указывают на намерение захватить блокировку более низкого уровня, например на строке при блокировке таблицы. Знание этих типов помогает правильно интерпретировать данные из представлений блокировок и принимать решения о снятии или оптимизации транзакций.