Вы все еще используете флаги трассировки 1204 и 1222 для мониторинга взаимоблокировок в SQL Server? Или, возможно, вы полагаетесь на SQL Profiler для захвата событий взаимоблокировок? Если да, то пришло время обновить свои методы мониторинга взаимоблокировок! С появлением Расширенных событий в SQL Server 2012 у вас теперь есть более мощный и эффективный способ мониторинга взаимоблокировок.
Расширенные события, которые были впервые представлены в SQL Server 2008, были интегрированы в SQL Server Management Studio (SSMS) в SQL Server 2012. Эта интеграция позволяет вам легко управлять вашими сеансами Расширенных событий непосредственно из SSMS.
По умолчанию SQL Server создает два сеанса Расширенных событий: “AlwaysOn_health” и “system_health”. Сеанс “system_health” запускается автоматически при запуске службы SQL. Вы можете просмотреть определение этого сеанса, создав скрипт в SSMS.
CREATE EVENT SESSION [system_health] ON SERVER
ADD EVENT sqlclr.clr_allocation_failure( ACTION(package0.callstack,sqlserver.session_id)),
ADD EVENT sqlclr.clr_virtual_alloc_failure( ACTION(package0.callstack,sqlserver.session_id)),
ADD EVENT sqlos.memory_broker_ring_buffer_recorded,
-- more events...
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)),
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
Сеанс “system_health” специально разработан для мониторинга событий взаимоблокировок. Однако вы можете настроить этот сеанс или создать свои собственные сеансы Расширенных событий для захвата других типов событий.
Давайте попробуем создать сценарий взаимоблокировки с помощью следующего скрипта:
-- Создание таблиц
CREATE TABLE a1(a INT)
CREATE TABLE b1(b INT)
-- Вставка данных
INSERT INTO a1 VALUES(1)
INSERT INTO b1 VALUES(1)
-- Выполнение в первом сеансе
SELECT @@SPID
BEGIN TRAN
UPDATE a1 SET a=2
UPDATE b1 SET b=2
-- Выполнение во втором сеансе
SELECT @@SPID
BEGIN TRAN
UPDATE b1 SET b=2
UPDATE a1 SET a=2
Поздравляю, вы создали взаимоблокировку! Вы увидите сообщение об ошибке, подобное следующему:
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Чтобы просмотреть подробности взаимоблокировки, вернитесь в SSMS и дважды щелкните “package0.event_file” в сеансе “system_health”. Это откроет окно, где вы сможете просмотреть все захваченные события.
Щелкните на столбце “Value”, чтобы проверить подробности взаимоблокировки. Здесь вы можете найти информацию о процессах, участвующих в взаимоблокировке, и ресурсах, на которые они ожидали.
В качестве альтернативы, вы можете щелкнуть на вкладке “Deadlock”, чтобы увидеть графическое представление взаимоблокировки.
С помощью Расширенных событий у вас есть более эффективный и мощный инструмент для мониторинга взаимоблокировок в SQL Server 2012. Воспользуйтесь этой функцией, чтобы превентивно выявлять и устранять проблемы с взаимоблокировками в вашей базе данных.