Как посмотреть блокировки в ms sql

Решение проблем MS SQL Server с блокировками

Возможный признак блокировки — не отрабатывает простой запрос к таблице (при этом к другим таблицам запросы проходят быстро).

Проверяем есть ли блокированные запросы:

select cmd,* from sys.sysprocesses where blocked > 0

Если есть, то смотрим в колонку blocked (это spID процесса, захватившего ресурсы) и завершаем этот процесс:

kill 54

Выполняется эта команда с сервера из под администратора.

Также можно посмотреть, что это за запрос (точнее последний запрос, выполненный в рамках данного SPID).

DBCC INPUTBUFFER(61) GO 

Поиск блокировок в MS SQL Server

13.10.2022

itpro

SQL Server

Один комментарий

Блокировки в SQL Server позволяют обеспечивать целостность данных при одновременном изменении несколькими пользователя. SQL Server блокирует объекты в таблице при начале транзакции и снимает блокировку при ее завершении. В этой статье мы научимся искать блокировки в базе данных MS SQL Server и удалять их.

Можно сымитировать блокировку одной из таблиц с помощью незакрытой транзакции (которая не завершена через rollback или commit). Например, выполните такой SQL запрос:

USE tesdb1
BEGIN TRANSACTION

DELETE TOP(1) FROM tblStudents

SQL Server перед внесением изменений сначала заблокирует таблицу. Попробуйте открыть SQL Server Management Studio и выполнить простой SQL запрос на выборку:

SELECT * FROM tblStudents

Запрос зависнет в состоянии ( Executing query ) пока не отвалится по таймауту. Дело в том, что запрос SELECT пытается обратиться к данным в таблице, которая заблокирована SQL Server-ом.

В SQL Server можно настроить блокировку на уровне строки или на уровне всей таблицы.

Чтобы вывести список заблокированных запросов в MSSQL Server, выполните команду:

select cmd,* from sys.sysprocesses
where blocked > 0

Либо вывести список блокировок для конкретной базы данных:
SELECT * FROM master.dbo.sysprocesses
WHERE
dbid = DB_ID(‘testdb12’) and blocked <> 0
order by blocked

В колонке Blocked указан идентификатор процесса PID процесса, который заблокировал ресурсы. Здесь же видно и время ожидания для данного запроса (waittime в милисекундах). Можно использовать это поле для поиска наиболее старых блокировок.

В некоторых случаях блокировка может быть вызвана целым деревом процессов. Чтобы найти процесс-первоисточник блокировки нужно использовать следующий запрос для по SPID до тех пор, пока не найдете процесс со значением blocked=0 (это и будет процесс источник блокировки).

select * FROM
master.dbo.sysprocesses
where 1=1
—and blocked <> 0
and spid = 59

По SPID процесса можно получить код последнего SQL запроса, выполнено в рамках данного процесса (транзакции):

Для принудительного завершения процесса и снятия блокировки, выполните команду:

Например, в моем случае это:

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

CREATE PROCEDURE PrintCurrentCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end — @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
print @line

Теперь для вывод кода SQL запроса, который заблокировал таблицу, нужно указать только его SPID:
Exec PrintCurrentCode 51

Также код запроса можно получить по sql_handle процесса блокировки. Например:

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

Для поиска блокировок в MS SQL Server можно использовать Microsoft SQL Server Management Studio. Вы можете использовать один из следующих методов:

• Щелкните правой кнопкой по северу, запустите Activity Monitor и разверните Processes. Список запросов, ожидающих освобождения ресурсов указан со статусом SUSPENDED.
• Выберите базу данных -> Reports -> All Blocking Transactions. Здесь также видно список заблокированных запросов и SPID источника блокировки.

Предыдущая статья Следующая статья

Определение запросов, содержащих блокировки

Администраторам баз данных часто нужно определить источник блокировок, приводящих к ухудшению производительности базы данных.

Например, может возникнуть подозрение о том, что проблемы с производительностью сервера вызваны блокировками. При выполнении запроса к sys.dm_exec_requests обнаруживается несколько приостановленных сеансов с типом ожидания, указывающим, что ожидаемым ресурсом является блокировка.

Результаты выполнения запроса к представлению sys.dm_tran_locks показывают наличие многих необработанных блокировок, но в представлении sys.dm_exec_requests у сеансов, которым были предоставлены блокировки, никаких активных запросов нет.

В этом примере показано, как определить, какой запрос взял блокировку, план запроса и стек Transact-SQL во время выполнения блокировки. Этот пример также демонстрирует использование цели «Попарное разбиение событий» в сеансе расширенных событий.

Выполнение этой задачи включает использование редактора запросов в SQL Server Management Studio для выполнения следующей процедуры.

В примере используется база данных AdventureWorks.

Определение запросов, удерживающих блокировки

1. В редакторе запросов выполните следующие инструкции.

-- Perform cleanup. IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FindBlockers') DROP EVENT SESSION FindBlockers ON SERVER GO -- Use dynamic SQL to create the event session and allow creating a -- predicate on the AdventureWorks database id. -- DECLARE @dbid int SELECT @dbid = db_id('AdventureWorks') IF @dbid IS NULL BEGIN RAISERROR('AdventureWorks is not installed. Install AdventureWorks before proceeding', 17, 1) RETURN END DECLARE @sql nvarchar(1024) SET @sql = ' CREATE EVENT SESSION FindBlockers ON SERVER ADD EVENT sqlserver.lock_acquired (action ( sqlserver.sql_text, sqlserver.database_id, sqlserver.tsql_stack, sqlserver.plan_handle, sqlserver.session_id) WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0) ), ADD EVENT sqlserver.lock_released (WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0 )) ADD TARGET package0.pair_matching ( SET begin_event=''sqlserver.lock_acquired'', begin_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'', end_event=''sqlserver.lock_released'', end_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'', respond_to_memory_pressure=1) WITH (max_dispatch_latency = 1 seconds)' EXEC (@sql) -- -- Create the metadata for the event session -- Start the event session -- ALTER EVENT SESSION FindBlockers ON SERVER STATE = START 

-- -- The pair matching targets report current unpaired events using -- the sys.dm_xe_session_targets dynamic management view (DMV) -- in XML format. -- The following query retrieves the data from the DMV and stores -- key data in a temporary table to speed subsequent access and -- retrieval. -- SELECT objlocks.value('(action[@name="session_id"]/value)[1]', 'int') AS session_id, objlocks.value('(data[@name="database_id"]/value)[1]', 'int') AS database_id, objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' ) AS resource_type, objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint') AS resource_0, objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint') AS resource_1, objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint') AS resource_2, objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)') AS mode, objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)') AS sql_text, CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml) AS plan_handle, CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', 'varchar(MAX)') AS xml) AS tsql_stack INTO #unmatched_locks FROM ( SELECT CAST(xest.target_data as xml) lockinfo FROM sys.dm_xe_session_targets xest JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address WHERE xest.target_name = 'pair_matching' AND xes.name = 'FindBlockers' ) heldlocks CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks) -- -- Join the data acquired from the pairing target with other -- DMVs to return provide additional information about blockers -- SELECT ul.* FROM #unmatched_locks ul INNER JOIN sys.dm_tran_locks tl ON ul.database_id = tl.resource_database_id AND ul.resource_type = tl.resource_type WHERE resource_0 IS NOT NULL AND session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0) AND tl.request_status='wait' AND REPLACE(ul.mode, 'LCK_M_', '' ) = tl.request_mode 

DROP TABLE #unmatched_locks DROP EVENT SESSION FindBlockers ON SERVER 

Предыдущие примеры кода Transact-SQL выполняются в локальной среде SQL Server, но могут не работать в базе данных SQL Azure. Основные части примера, непосредственно связанные с событиями, например ADD EVENT sqlserver.lock_acquired работа с базой данных SQL Azure. Тем не менее, для выполнения этого примера необходимо заменить предварительные элементы, такие как sys.server_event_sessions , на их аналоги из базы данных SQL Azure, например sys.database_event_sessions . Дополнительные сведения об этих незначительных различиях между локальным экземпляром SQL Server и базой данных SQL Azure см. в следующих статьях:

• Расширенные события в Базе данных SQL Azure
• Системные объекты, которые поддерживают расширенные события

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

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

Введение

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

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

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

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

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

Dynamic Management Views (DMV) – это динамические административные представления, возвращающие данные о состоянии сервера, которые можно использовать для контроля исправности экземпляра 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] – имя приложения, откуда пришел запрос

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

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

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