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

Откуда: Санкт-Петербург
Сообщений: 498
У меня временами в базе появляются блокировки. Появляются приступами в это время вылезают почти на каждом запросе. Запрос тормозит и часто вылетает с ошибкой "Превышено время ожидания блокировки". Причем все остальное время такой же запрос выполняется нормально. Скажите пожалуйста что тут можно предпринять. Как можно собрать статистику? И вообще где про это копать?
Я ловлю блокирующие запросы хранимой процедурой
+
CREATE procedure aip_Blocks 
@inTable int = null
as
    --kill @SPID
	/*
	в ALADA_TEST
begin tran
update schN_ set From_ = 11 where D_Reg='20161114 15:54:35.690'



select top 11 *from schn_ order by 1 desc
*/    
    DECLARE @Table TABLE( SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT)

    INSERT INTO @Table EXEC sp_who2

    --SELECT DISTINCT BlkBy FROM @Table

	--where BlkBy>0


	DECLARE @Res TABLE (SPID int, T varchar(4000))
    DECLARE @SPID int 

	DECLARE curs cursor for
	select
	  BlkBy
	from
	(
	SELECT DISTINCT 
    case 
	  when ltrim(rtrim(p.BlkBy))<>'.' --and  p.BlkBy not like '%[^0-9]%'
	  then cast(ltrim(rtrim(p.BlkBy)) as int)
	  else
	 0	
    end BlkBy
    FROM @Table p
	where DBName = DB_NAME()
	) p
	where BlkBy>0

	open curs
	while 0=0
	begin
	  fetch from curs into @SPID
	  if @@Fetch_Status<>0 break

		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))
        
		if @inTable = 1
		insert tblBlocks(SPID,T,DT) values(@SPID,@line,GetDate())
		else
		insert @Res(SPID,T) values(@SPID,@line)

    end
	close curs
	deallocate curs
    
   if isnull(@inTable,0)<>1
   Select * from @Res


    --SELECT * FROM @Table


но она временами дает странные результаты, например запросы с with (nolock), которые вообще не должны блокировать

Сообщение было отредактировано: 15 фев 17, 16:37
15 фев 17, 16:35    [20214756]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
лолл
Member

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

возможно, длительные транзакции и/или высокий уровень их изоляции, высокая частота параллельных транзакций
15 фев 17, 17:38    [20215058]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33955
Блог
bilov
но она временами дает странные результаты, например запросы с with (nolock), которые вообще не должны блокировать


сначала прочитайте про READUNCOMMITTED/NOLOCK
https://msdn.microsoft.com/ru-ru/library/ms187373.aspx
15 фев 17, 19:04    [20215270]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
Adx
Guest
Критик
bilov
но она временами дает странные результаты, например запросы с with (nolock), которые вообще не должны блокировать


сначала прочитайте про READUNCOMMITTED/NOLOCK
https://msdn.microsoft.com/ru-ru/library/ms187373.aspx


Рекомендую писать в таких советах: "Погуглите". Это короче и приносит ровно столько же пользы.

Для ТС:

Посмотрите, какой ресурс блокируется, и кто его блокирует.
Если ресурс популярный, то достаточно одной блокирующей транзакции, которая будет держать всех.
with (nolock) указывает конкретный ресурс, который нужно читать в режиме READUNCOMMITTED, и не отключает блокировку вообще.
Ну и ловите блокировки совершенно безумной процедурой )
15 фев 17, 20:25    [20215431]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
o-o
Guest
Adx
Если ресурс популярный,
то скорее всего, это порно-ресурс.

Кстати о бездумных процедурах :
у Adx они видимо напротив, задумчивые?
Или вдумчивые, в doom постоянно играют?
15 фев 17, 20:33    [20215442]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
o-o
Guest
bilov
...
но она временами дает странные результаты, например запросы с with (nolock), которые вообще не должны блокировать

Выкладывайте результат, иначе разговор ни о чем.
Запросы с nolock не "не должны блокировать",
они просто не накладывают S-блокировки.
Зато накладывают Sch-S, что несовместимо со Sch-m.
Примеры конфликтов этих двух есть на форуме.
А может, есть еще и у вас
15 фев 17, 20:41    [20215461]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 498
лолл,

Уровень изоляции транзакций не прописывается т.е. по-умолчанию. Транзакции вроде не такие длинные и интенсивные. А главное блокировки то почти сплошные, то их нет. И все это началось где-то месяца 3 назад. Хотя транзакции всегда были одинаковы. И я не нашел чтобы когда есть блокировки были бы особенно длинные и тяжелые транзакции
15 фев 17, 21:13    [20215533]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 498
Adx,

А как узнать что блокируется и чем блокируется. Безумная процедура хотя бы давала текст запроса если специально повесишь транзакцию
15 фев 17, 21:20    [20215546]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 498
o-o,

Нет обычная учетно-тоговая, складская прога. Где-то 100 юзеров одновременно
15 фев 17, 21:26    [20215557]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
лолл
Member

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

Еще может помочь snapshot isolation, если есть блокировки читателей, созданных писателями
16 фев 17, 10:28    [20216689]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
aleksrov
Member

Откуда:
Сообщений: 948
А как узнать что блокируется и чем блокируется. Безумная процедура хотя бы давала текст запроса если специально повесишь транзакцию

+

select DB_Name(r.database_id) as DB, 
       s.login_name, 
           s.memory_usage, 
           s.login_time,             
       S.host_name,  
           S.program_name, 
       s.session_id, 
           r.request_id, 
           r.start_time, 
           s.status as 'Status Ses',  
           r.status as 'Status Req',  
           r.command,            
           r.reads, 
           r.writes,  
           r.logical_reads,  
           s.row_count, 
           r.total_elapsed_time/1000 as total_time,  
           r.blocking_session_id,  
           r.wait_time/1000 as wait_time, 
           r.last_wait_type, 
           r.transaction_isolation_level, 
           r.transaction_id,             
(SELECT SUBSTRING(text, statement_start_offset/2 + 1, 
(CASE WHEN statement_end_offset = -1 
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 
ELSE statement_end_offset 
END - statement_start_offset)/2) 
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, 
(select query_plan from sys.dm_exec_query_plan(plan_handle)) as PlanQuery  
from sys.dm_exec_sessions AS S 
inner JOIN sys.dm_exec_requests r 
ON s.session_id = r.session_id 
WHERE s.is_user_process = 1 
--and r.blocking_session_id > 0 
order by s.status 




SELECT request_session_id , 
  DB_NAME(resource_database_id) AS DatabaseName , 
  resource_type , 
  resource_subtype, 
  resource_description , 
  resource_associated_entity_id , 
  request_mode , 
  request_status, 
  request_session_id 
  FROM sys.dm_tran_locks AS dtl 
  --WHERE --request_session_id IN ( 52, 53 ) and 
  --resource_type NOT IN ( 'DATABASE', 'METADATA' ) 




  SELECT 
    [owt].[session_id] AS [SPID], 
    [owt].[exec_context_id] AS [Thread], 
    [ot].[scheduler_id] AS [Scheduler], 
    [owt].[wait_duration_ms] AS [wait_ms], 
    [owt].[wait_type], 
    [owt].[blocking_session_id] AS [Blocking SPID], 
    [owt].[resource_description], 
    CASE [owt].[wait_type] 
        WHEN N'CXPACKET' THEN 
            RIGHT ([owt].[resource_description], 
                CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1) 
        ELSE NULL 
    END AS [Node ID], 
    [eqmg].[dop] AS [DOP], 
    [er].[database_id] AS [DBID], 
    CAST ('https://www.sqlskills.com/help/waits/' + [owt].[wait_type] as XML) AS [Help/Info URL], 
    [eqp].[query_plan], 
    [est].text -- запрос SPID 
FROM sys.dm_os_waiting_tasks [owt] 
INNER JOIN sys.dm_os_tasks [ot] ON 
    [owt].[waiting_task_address] = [ot].[task_address] 
INNER JOIN sys.dm_exec_sessions [es] ON 
    [owt].[session_id] = [es].[session_id] 
INNER JOIN sys.dm_exec_requests [er] ON 
    [es].[session_id] = [er].[session_id] 
FULL JOIN sys.dm_exec_query_memory_grants [eqmg] ON 
    [owt].[session_id] = [eqmg].[session_id] 
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est] 
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp] 
WHERE 
    [es].[is_user_process] = 1 
ORDER BY 
    [owt].[session_id], 
    [owt].[exec_context_id]; 
GO

16 фев 17, 10:54    [20216815]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
Adx
Guest
o-o
то скорее всего, это порно-ресурс.

Кстати о бездумных процедурах :
у Adx они видимо напротив, задумчивые?
Или вдумчивые, в doom постоянно играют?


Уважаемый o-o выдал очередную полезную идею.

o-o
Зато накладывают Sch-S, что несовместимо со Sch-m.
А может, есть еще и у вас

Да-да, это самый вероятный вариант - блокировка Sch-m ))
Но я все же рекомендую сначала рассмотреть более тривиальные причины.
16 фев 17, 11:29    [20216964]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Adx
o-o
то скорее всего, это порно-ресурс.

Кстати о бездумных процедурах :
у Adx они видимо напротив, задумчивые?
Или вдумчивые, в doom постоянно играют?


Уважаемый o-o выдал очередную полезную идею.

o-o
Зато накладывают Sch-S, что несовместимо со Sch-m.
А может, есть еще и у вас

Да-да, это самый вероятный вариант - блокировка Sch-m ))
Но я все же рекомендую сначала рассмотреть более тривиальные причины.


А разве он не прав, у ПорноХаба в день полтора ляма посещений и в 4 раза больше просмотра видео (слабаки, до 5 видео мало кто дотягивает). Интересно, как они с блокировками борятся?

Конечно причина скорее всего банальна и быстро обнаружится если правильно ее искать (а не через ту хранимку)
16 фев 17, 11:37    [20216993]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Adx,
автор
Да-да, это самый вероятный вариант - блокировка Sch-m ))
Но я все же рекомендую сначала рассмотреть более тривиальные причины.

куда ещё тривиальней??? туча любителей делать DROP CREATE ALTER TABLE в процедурах
16 фев 17, 11:48    [20217048]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
Adx
Guest
aleksrov
Adx
пропущено...


Уважаемый o-o выдал очередную полезную идею.

пропущено...

Да-да, это самый вероятный вариант - блокировка Sch-m ))
Но я все же рекомендую сначала рассмотреть более тривиальные причины.


А разве он не прав, у ПорноХаба в день полтора ляма посещений и в 4 раза больше просмотра видео (слабаки, до 5 видео мало кто дотягивает). Интересно, как они с блокировками борятся?

Конечно причина скорее всего банальна и быстро обнаружится если правильно ее искать (а не через ту хранимку)


Увы, Ваш вариант тоже вряд ли одобрят, в Doom он не играет )
У ТС проблема, он просит помощи. Хочешь помочь - напиши что-то полезное, не хочешь - какой смысл писать ерунду?
Высоконагруженные системы - это отдельная тема, там нужно начинать с правильного проектирования.
Там есть что-то от RTS, возможные блокировки нужно рассмотреть заранее.

PS.
Для тех, кому непонятно. Брать результат sp_who2 и затем обрабатывать курсором в процедуре несколько ... странно.
Рекомендую все же запросы от aleksrov
16 фев 17, 11:54    [20217092]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
londinium
Member

Откуда: Киев
Сообщений: 1176
автор
туча любителей делать DROP CREATE ALTER TABLE в процедурах

Зачем? Есть какие-то объективные причины или только собственная злобность?
16 фев 17, 12:10    [20217157]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
bilov
А как узнать что блокируется и чем блокируется. Безумная процедура хотя бы давала текст запроса если специально повесишь транзакцию
Не нужно изобретать велосипедов.
Есть проверенное временем решение - sp_WhoIsActive
Вот по результату ее выполнения во время "тормозов" можно будет сказать что-то конкретное.
16 фев 17, 12:11    [20217170]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
londinium
автор
туча любителей делать DROP CREATE ALTER TABLE в процедурах

Зачем? Есть какие-то объективные причины или только собственная злобность?

желания самые разные, но вполне обыденный truncate даст Sch-M и посадит всех с NOLOCK
16 фев 17, 12:51    [20217353]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 498
aleksrov,

Во время блокирования запустил Ваш первый запрос и он дал мне запрос который блокировал больше всего. Я его оптимизировал где-то раз в 100 и пока все спокойно. Огромное спасибо
16 фев 17, 14:17    [20217817]     Ответить | Цитировать Сообщить модератору
 Re: Массовые блокировки на базе  [new]
Adx
Guest
TaPaK
Adx,
автор
Да-да, это самый вероятный вариант - блокировка Sch-m ))
Но я все же рекомендую сначала рассмотреть более тривиальные причины.

куда ещё тривиальней??? туча любителей делать DROP CREATE ALTER TABLE в процедурах


Вообще для модификации схемы нужны особые права. Давать их рядовым пользователям как-бы не нужно.
Есть любители вообще всем давать роль sysadmin, но тогда нужно начинать с исправления этой ситуации, а не с поиска блокировок.
16 фев 17, 16:28    [20218435]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить