Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
Есть ли лучшая возможность прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х секунд?
Может есть специальная опция или что то поизящней того, как я это делал:
+
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

declare @sessionid int = 779   -- !!!! SESSION ID TO WATCH !!!!

declare @killsql nvarchar(100) = 'kill ' + cast(@sessionid as nvarchar(10))
set nocount on;
while 1=1
begin

	if exists
	(
		SELECT 	
		  *
		FROM 
					sys.dm_exec_requests req with(nolock)	
		INNER JOIN  sys.dm_exec_sessions ses with(nolock) on req.session_id=ses.session_id
		CROSS APPLY sys.dm_exec_sql_text(sql_handle)  AS sqltext 	
		where 1=1
		and req.blocking_session_id=@sessionid
		and req.start_time < DATEADD(second,-10,getdate())       
	)
	begin	    
		exec sp_executesql @killsql
		exec msdb.dbo.sp_send_dbmail @recipients='v.pupkin@pupkin.gov', @body='session killed: Ground:1'
		break
	end	
	
    waitfor delay '00:00:01'
end
21 мар 17, 15:31    [20318119]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
MacaronicTragedy
Member

Откуда:
Сообщений: 99
Alexander Us,

я б завернул это дело в джоб

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

declare @sessionid int = 779   -- !!!! SESSION ID TO WATCH !!!!
declare @killsql nvarchar(100) = 'kill ' + cast(@sessionid as nvarchar(10))

if exists
(
select 1 from sys.dm_exec_requests
where blocking_session_id = @sessionid
and wait_time > 10000
)
begin
	exec @killsql
	exec msdb.dbo.sp_send_dbmail @recipients='v.pupkin@pupkin.gov', @body='session killed: Ground:1'
end


затем создал бы алерт object - general statistics, counter - proccesses blocked
response - запуск ранее созданного джоба.

получаем, когда количество заблокированных процессов станет больше 0, отрабатывает джоб, который проверяет твою сессию и длительность лока итд итп.
21 мар 17, 15:44    [20318191]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
MacaronicTragedy
Member

Откуда:
Сообщений: 99
Alexander Us,

чет у тебя много лишних джоинов и nolock. если поставил уровень изоляции в read uncommitted, то не нужно после каждой таблицы писать NOLOCK.
21 мар 17, 15:45    [20318196]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
MacaronicTragedy,

автор
получаем, когда количество заблокированных процессов станет больше 0
это какой-то синг юзер получится
21 мар 17, 15:45    [20318197]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
MacaronicTragedy
Member

Откуда:
Сообщений: 99
TaPaK,

не понял. что за синг юзер? пой пользователь?
21 мар 17, 15:47    [20318201]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
MacaronicTragedy,

Спасибо за исправления и пояснения, нахожу их очень хорошими.

Что касается примениния:
раньше применял этот скрипт, чтобы мониторить/прибивать (второстепенный но затратный) процесс на сервере.
теперь хотел бы применить ту же логику из .NET приложения.
получается, придётся запускать вторую сессию чтобы наблюдать/прибивать первую.
21 мар 17, 16:07    [20318293]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
MacaronicTragedy
Member

Откуда:
Сообщений: 99
Alexander Us,

Подозреваю, что да. Но тут я точно не советчик))
В сиквел встроена "обратная" возможность - SET LOCK_TIMEOUT. Т.е. если запрос не получил блокируемый другим запросом ресурс в течение определенного времени, то он завершается.

Мне кажется вполне допустимым вариант который я предложил выше, правда я так и не понял, что Тапку не понравилось.
21 мар 17, 16:21    [20318347]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
Idol_111
Member

Откуда:
Сообщений: 633
MacaronicTragedy
правда я так и не понял, что Тапку не понравилось.

ему не понравилось, что это будет почти single mode.
22 мар 17, 00:07    [20319525]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Alexander Us,

с чего бы прерывать запрос? Пишите запрос так, чтобы не мешал другим. А то периодически здесь появляются танцоры. которым транзакции мешают.
22 мар 17, 01:53    [20319609]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
MacaronicTragedy
Member

Откуда:
Сообщений: 99
Idol_111,

Single user mode? Он тут при чем?
Я действительно не въезжаю о чем вы
22 мар 17, 08:21    [20319725]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
Владислав Колосов
с чего бы прерывать запрос?
Это ответ вопросом на вопрос или Вам действительно хочется знать?
Владислав Колосов
Пишите запрос так, чтобы не мешал другим
Сделано в первую очередь. Оказалось недостаточно.
22 мар 17, 10:15    [20320115]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Подход в виде костыльков. Если реально запрос - запросище, будем всегда срубать? Как вы будете определять приоритеты?
Джоб или (службу) написать делов то на 5-ть минут. Только вот видел такие поделки в жизни. Работа одной компании остановилась на пол дня. Местный админ запустил такой джоб но только не учел что со временем да и в силу сезонности запросы могут выполняться больше чем обычно. В результате 50% запросов килялись и пользователи запускали их заново не имея никакой возможности на успех(создавая доп. нагрузку что опять замедляло их выполнение).
22 мар 17, 16:51    [20322080]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
МуМу
Подход в виде костыльков. Если реально запрос - запросище, будем всегда срубать? Как вы будете определять приоритеты?
Джоб или (службу) написать делов то на 5-ть минут. Только вот видел такие поделки в жизни. Работа одной компании остановилась на пол дня. Местный админ запустил такой джоб но только не учел что со временем да и в силу сезонности запросы могут выполняться больше чем обычно. В результате 50% запросов килялись и пользователи запускали их заново не имея никакой возможности на успех(создавая доп. нагрузку что опять замедляло их выполнение).

Тут ситуация наоборот, чем у вашего админа:
Я использую этот трюк при выполнении ручных разовых акций, которые выполняются несколько часов/дней.
Скрипт для акции написан так, что при перезапуске он продолжает начатое.
Основная цель, чтобы скрипт не тормозил работу пользователей/клиентов.
Если у клиентов возникают тормоза, скрипт отрубается, клиеты счастливо работают дальше.
22 мар 17, 19:25    [20322637]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Есть например такая вещь как ресурс говернер.(на английском все забываю) Правда там не все гладко отрабатывает.
23 мар 17, 20:52    [20327484]     Ответить | Цитировать Сообщить модератору
 Re: Прервать выполнение запроса, если он вызывает блокировку других запросов более чем Х сек.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
МуМу
Есть например такая вещь как ресурс говернер.(на английском все забываю) Правда там не все гладко отрабатывает.
Например то, что ему пофигу на блокировки?
23 мар 17, 22:55    [20327733]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить