Как посмотреть блокировки в Microsoft SQL Server

Всем привет! Сегодня мы поговорим о том, как посмотреть блокировки в Microsoft SQL Server, в материале представлен готовый скрипт на T-SQL, который показывает информацию о блокировках в удобном и понятном виде.

Заметка! Блокировки в Microsoft SQL Server – что это такое и для чего нужны.

Введение

В Microsoft SQL Server посмотреть блокировки можно несколькими способами, например:

• с помощью системных хранимых процедур
• с помощью Dynamic Management Views (DMV)

К числу системных хранимых процедур, с помощью которых можно посмотреть блокировки и текущие процессы, можно отнести:

• sp_lock
• sp_who
• sp_who2

Однако данные процедуры уже немного устарели, даже сам Microsoft для просмотра блокировок рекомендует использовать Dynamic Management Views (DMV – динамические административные представления).

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

Dynamic Management Views (DMV) – это динамические административные представления, возвращающие данные о состоянии сервера, которые можно использовать для контроля исправности экземпляра SQL Server, диагностики проблем и настройки производительности.

Заметка! Типы блокировок в Microsoft SQL Server.

Просмотр блокировок в Microsoft SQL Server

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

Ниже представлен запрос, который отображает Ваши (по ORIGINAL_LOGIN) текущие заблокированные запросы.

Примечание! Если Вам необходимо посмотреть все свои блокировки, включая те, которые не ожидают ресурсов, то Вы можете убрать условие по blocking_session_id. Или, если Вы хотите посмотреть блокировки других пользователей, или еще как-то отфильтровать данные, то Вы легко можете поиграть с условиями в WHERE.

Описание столбцов результирующего набора данного запроса, а также описание источников, представлено чуть ниже, после самого запроса.

  
  SELECT 
    [Status]            = TL.request_status,
    [LockType]          = TL.request_mode,
    [DataBase]          = DB.name,
    [TableName]         = OBJECT_NAME(P.object_id),
    [IndexName]         = I.name,
    [ResourceType]      = TL.resource_type,
    [TranStart]         = TAT.transaction_begin_time,
    [LockDuration]      = CONVERT(VARCHAR, DATEADD(MS, WT.wait_duration_ms, 0), 108), -- hh:mm:ss
    [SessionId]         = TL.request_session_id,
    [BlockingSessionId] = WT.blocking_session_id,
    [LoginName]         = ES.original_login_name,
    [BlockingLoginName] = ESB.original_login_name,
    [SQLText]           = RST.text,
    [BlockingSQLText]   = BST.text,
    [ProgramName]       = ES.program_name
  FROM       sys.dm_tran_locks               AS TL
  INNER JOIN sys.databases                   AS DB  ON DB.database_id      = TL.resource_database_id
  INNER JOIN sys.dm_exec_sessions            AS ES  ON ES.session_id       = TL.request_session_id
  LEFT  JOIN sys.partitions                  AS P   ON P.hobt_id           = TL.resource_associated_entity_id
  LEFT  JOIN sys.indexes                     AS I   ON I.object_id         = P.object_id 
                                                   AND I.index_id = P.index_id
  LEFT  JOIN sys.dm_os_waiting_tasks         AS WT  ON WT.resource_address = TL.lock_owner_address
  LEFT  JOIN sys.dm_exec_sessions            AS ESB ON ESB.session_id      = WT.blocking_session_id
  LEFT  JOIN sys.dm_exec_connections         AS EC  ON EC.session_id       = TL.request_session_id
  LEFT  JOIN sys.dm_exec_connections         AS ECB ON ECB.session_id      = WT.blocking_session_id
  LEFT  JOIN sys.dm_tran_active_transactions AS TAT ON TL.request_owner_id = TAT.transaction_id 
                                                   AND TL.request_owner_type = 'TRANSACTION'
  OUTER APPLY sys.dm_exec_sql_text (EC.most_recent_sql_handle)  AS RST
  OUTER APPLY sys.dm_exec_sql_text (ECB.most_recent_sql_handle) AS BST
  WHERE (
         ES.original_login_name  = ORIGINAL_LOGIN() -- Свои процессы
         OR
         ESB.original_login_name = ORIGINAL_LOGIN() -- Процессы, которые заблокировали мы сами
	 )
	  AND TL.resource_database_id = DB_ID()     -- В рамках текущей база дынных
	  AND WT.blocking_session_id IS NOT NULL    -- Только заблокированные процессы
  ORDER BY [LockDuration] DESC
  GO

Описание столбцов:

• [Status] – текущее состояние запроса. WAIT означает, что запрос заблокирован и ожидает ресурса
• [LockType] – тип блокировки
• [DataBase] – имя базы данных
• [TableName] – имя таблицы
• [IndexName] – имя индекса
• [ResourceType] – тип ресурса, на который накладывается блокировка
• [TranStart] – время начала транзакции
• [LockDuration] – время нахождения процесса в заблокированном состоянии в формате hh:mm:ss
• [SessionId] – идентификатор сессии, которая запустила инструкцию
• [BlockingSessionId] – идентификатор сессии, которая заблокировала инструкцию
• [LoginName] – имя входа (по ORIGINAL_LOGIN), которое запустило инструкцию
• [BlockingLoginName] – имя входа (по ORIGINAL_LOGIN), которое заблокировало инструкцию
• [SQLText] – текст SQL инструкции
• [BlockingSQLText] – текст SQL инструкции, которая блокирует текущую инструкцию
• [ProgramName] – имя приложения, откуда пришел запрос

Заметка! План выполнения запроса в Microsoft SQL Server – что это такое и для чего он нужен.

Описание источников:

• dm_tran_locks – возвращает сведения об активных в данный момент в SQL Server ресурсах диспетчера блокировок
• sys.databases – системное представление, возвращающее информацию о базах данных на сервере
• sys.dm_exec_sessions – возвращает сведения обо всех активных подключениях пользователей и внутренних задачах
• sys.partitions – отображает информацию о секциях всех таблиц и большинства типов индексов базы данных. Считается, что все таблицы и индексы в SQL Server содержат как минимум одну секцию, даже если они явно не секционированы
• sys.indexes – отображает информацию об индексах
• sys.dm_os_waiting_tasks – возвращает сведения об очереди задач, ожидающих освобождения определенного ресурса
• sys.dm_exec_connections – возвращает сведения о соединениях, установленных с данным экземпляром SQL Server
• sys.dm_tran_active_transactions – возвращает данные о транзакциях для экземпляра SQL Server
• sys.dm_exec_sql_text – табличная функция, возвращающая текст SQL пакета, который идентифицируется по sql_handle.

Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.

На сегодня это все, надеюсь, данный скрипт будет Вам полезен!