Совсем недавно, у заказчика, возникла необходимость собирать данные о процессах, которые заблокированы более 30 секунд и, как оказалось, мало кто может сразу выдать готовое решение данной задачи. Именно об это я и хотел сегодня с вами поговорить.
Есть множество способов решить данный вопрос, но начать я хотел бы с самого простого и удобного:
Способ 1 (Extended Events):
Хочу сразу оговориться, что данный способ стал удобным с SQL Server 2012, когда Extended Events добавили в графический интерфейс Management Studio. Раньше данным методом было пользоваться не так удобно и до SQL Server 2012 я бы рекомендовал второй способ.
Как активировать:
1. Первым делом необходимо, на уровне сервера, включить ‘blocked process threshold (s)’.
sp_configure 'blocked process threshold (s)' , 30 GO RECONFIGURE
В мойм примере я установил обнаружение блокировок более 30 секунд. Данный параметр начинает обнаруживать блокировки более установленного времени, но изначально нигде их не регистрирует.
2. Для того, чтобы регистрировать ‘blocked process threshold (s)’ мы создаёт Extended Event сессию и собираем события ‘blocked_process_report’.
3. Это всё, что от нас требуется. Теперь нам остаётся только анализировать собранные данные Extended Event, а благодаря выносу данных событий в графический интерфейс нам достаточно нажать правую кнопку на новой сессии и вызвать меню ‘Watch Live Data’
P.S. Не забываем выключить и blocked process threshold (s) и Extended Events
Способ 2 (SQL Profiler):
Данным способом можно пользоваться с SQL Server 2005. Способ удобен практически так же как и первый, за исключением того, что потребуется либо держать открытым SQL Profiler, либо настраивать трассу в ручную.
Как активировать:
1. Первым делом необходимо, на уровне сервера, включить ‘blocked process threshold (s)’.
sp_configure 'blocked process threshold (s)' , 30 GO RECONFIGURE
Точно так же как и в Способе 1.
2. Далее необходимо открыть SQL Profiler, убрать все счётчики и добавить только один. Его вы найдете в разделе Error and Warnings > Blocked process report
Способ 3 (Сбор snapshot и сохранение в таблицу):
Данный метод мне не очень нравится, так как может упустить ряд блокировок или задание требуется запускать очень часто. Суть заключается в том, чтобы создать таблицу, для сбора данные и писать туда результат выполнения запроса, который будет возвращать информацию о блокировках.
Например можно воспользоваться следующим запросом:
SELECT blocking.session_id AS blocking_session_id , blocked.session_id AS blocked_session_id , waitstats.wait_type AS blocking_resource , waitstats.wait_duration_ms/1000 as wait_duration_sec , waitstats.wait_duration_ms/1000/60 as wait_duration_min , waitstats.wait_duration_ms/1000/60/60 as wait_duration_hour , waitstats.resource_description , blocked_cache.text AS blocked_text , blocking_cache.text AS blocking_text FROM sys.dm_exec_connections AS blocking INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_cache CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_cache INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id
Способ 4 (в реальном времени):
Данный способ очень простой и заключается в использовании 2 или 3 способа без сохранения результатов в файл/таблицу.
Какие ещё способы отслеживания блокировок вам известны?