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

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

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

Заметка! Блокировки в 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 для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.

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

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.