SQL-запрос для получения взаимоблокировок в SQL SERVER 2008

Ответ 1

Вы можете использовать deadlock graph и собрать необходимую информацию из файла журнала.

Единственный другой способ, который я мог бы предложить, - это перекопать информацию с помощью EXEC SP_LOCK (скоро будет устаревать), EXEC SP_WHO2 или таблицы sys.dm_tran_locks.

SELECT  L.request_session_id AS SPID, 
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName, 
    P.object_id AS LockedObjectId, 
    L.resource_type AS LockedResource, 
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,        
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

http://www.sqlmag.com/article/sql-server-profiler/gathering-deadlock-information-with-deadlock-graph

http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx

Ответ 2

Чтобы зафиксировать тупиковые графики без использования трассировки (вам необязательно нужен профайлер), вы можете включить флаг трассировки 1222. Это будет записывать информацию об ошибке в журнал ошибок. Тем не менее, журнал ошибок является текстовым, поэтому вы не получите хороших изображений тупикового графика - вам нужно будет прочитать текст тупиков, чтобы понять это.

Я бы установил это как флаг трассировки запуска (в этом случае вам нужно будет перезапустить службу). Однако вы можете запустить его только для текущего запущенного экземпляра службы (который не требует перезапуска, но который не будет возобновлен при следующем перезапуске), используя следующую команду глобального трассировки:

DBCC TRACEON(1222, -1);

Быстрый поиск дал этот урок:

http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/

Также обратите внимание, что если ваша система испытывает множество тупиков, это может действительно забить ваш журнал ошибок и может стать довольно много шума, заглушая другие важные ошибки.

Рассматривали ли вы сторонние инструменты мониторинга? SQL Sentry Performance Advisor, например, имеет гораздо более красивый столбец, показывающий имена объектов/индексов, а также порядок, в котором блокировки были приняты. В качестве бонуса они автоматически захватываются на контролируемых серверах без необходимости настраивать флаги трассировки, запускать собственные трассы и т.д.:

Отказ от ответственности: я работаю для SQL Sentry.