Отслеживание блокировок

Отслеживание блокировокСовсем недавно, у заказчика, возникла необходимость собирать данные о процессах, которые заблокированы более 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 способа без сохранения результатов в файл/таблицу.

 

Какие ещё способы отслеживания блокировок  вам известны?

Блокировки, Дедлоки и прочая чепуха (видео)

Запись опубликована в рубрике Блокировки, Дедлоки и Латчи с метками , , . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: