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

Откуда: Перехлёсток восьми батог
Сообщений: 28147
Сразу признаюсь я не большой специалист в блокировках, но приходится бороться с ними в одном из процессов.

Настроил дополнительный процесс, который следит за блокировками и если таковые появляются то он делает снимок процессов.
На картинке видно что процесс 90 блокирует с десяток других процессов.
Все процессы абсолютно независимые (за исключением 70 и 95 в этом примере).
Т.е. никаких скрытых триггеров или зависимостей между таблицами нет.

Мои предположения могут быть такими:
В процессе вставки в таблицу произошёл рост tempdb и все остальные таблички ждали окончания.

В своём предположении я не уверен, так как и основная база и tempdb достаточно большие и проблем с ростом быть не должно.

Не подскажете-ли что это может быть? Может быть можно как-то очень быстро делать снимок всех локов блокирующей сессии?

Спасибо.

К сообщению приложен файл. Размер - 19Kb
25 фев 13, 18:24    [13978739]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Без приведения информации о ресурсе, на который наложена блокировка, трудно что-то сказать.
25 фев 13, 19:03    [13978962]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
pkarklin
Без приведения информации о ресурсе, на который наложена блокировка, трудно что-то сказать.
Спасибо что откликнулись, но в этом и был мой вопрос: что я должен мониторить что-б увидеть проблему?

Блокироваться должны ТОЛЬКО таблицы в которые происходит вставка. Попутно блокируются таблицы с метаданными.

Пока попытался делать sp_lock, но там я ничего не вижу.
Возможно я просто не успеваю сделать снимок.

Вот например процесс 71 блокируется процессом 66. Я отфильтровал все блокировки типа EXT, KEY, PAG для 66.
Единственная подозрительная блокировка, это которая на tempdb.

К сообщению приложен файл. Размер - 8Kb
25 фев 13, 19:49    [13979248]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Нужны ресурсы с wait_type = 'wait', а не grant.

Сообщение было отредактировано: 25 фев 13, 19:52
25 фев 13, 19:52    [13979266]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
SELECT * into #l
FROM sys.dm_tran_locks 

SELECT TOP 10 * FROM #l
WHERE request_status != 'GRANT'

Сделал такое во время блокировки.

Второй запрос ничего не выдал. Как такое может быть?
25 фев 13, 20:04    [13979331]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
Гость333
Member

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

Для вывода информации "кто, кого, на каком ресурсе блокирует" можно использовать запрос:
select * from sys.dm_os_waiting_tasks where session_id <> blocking_session_id

Условием "session_id <> blocking_session_id" отсекаются процессы, которые не блокированы другими процессами (у таких записей blocking_session_id is null), а также блокировки intra-query parallelism locks (у таких записей session_id = blocking_session_id). Обращение к sys.dm_os_waiting_tasks может быть существенно (на порядки) быстрее, чем к sys.dm_tran_locks (например, при желании в системе могут болтаться миллионы блокировок, из них в режиме ожидания — всего несколько штук).

SandalTree
Второй запрос ничего не выдал. Как такое может быть?

А каким запросом вы получили картинку из первого поста?
26 фев 13, 08:43    [13980564]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
LenaV
Member

Откуда: USA
Сообщений: 6797
автор
картинку генерю с помощью sys.dm_exec_requests.

посмотрите там wait_type, last_wait_type and wait_time
26 фев 13, 21:24    [13985289]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
LenaV
Member

Откуда: USA
Сообщений: 6797
и wait_resource тоже
26 фев 13, 21:27    [13985300]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
SandalTree,

Гм... А не причина ли в самом Вашем запросе на сбор блокировок. Какой он, кстати?
26 фев 13, 21:29    [13985305]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
How to Identify Blocking Problems with SQL Profiler
26 фев 13, 21:51    [13985378]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
pkarklin
SandalTree,

Гм... А не причина ли в самом Вашем запросе на сбор блокировок. Какой он, кстати?


TRUNCATE TABLE tbl_CollectProcesses
GO
TRUNCATE TABLE tbl_CollectBlocks
GO
SET NOCOUNT ON
DECLARE @Record_Id int, @NextRun datetime = GetDate(), @Message varchar(40), @SQL varchar(100)
while 1=1
BEGIN
  INSERT INTO tbl_CollectBlocks(CNT, NoBlock, Blocked)
		SELECT 
			COUNT(*),
			SUM(CASE blocking_session_id WHEN 0 THEN 1 ELSE 0 END) NoBlock,
			SUM(CASE blocking_session_id WHEN 0 THEN 0 ELSE 1 END) Blocked
		FROM sys.dm_exec_requests 
		where session_id != @@spid and transaction_id != 0
	
	SET @Record_Id = @@IDENTITY
	
	IF EXISTS(SELECT TOP 1 1 FROM tbl_CollectBlocks WHERE Record_Id = @Record_Id and Blocked > 0)
	BEGIN
	BEGIN TRAN
			INSERT INTO tbl_CollectProcesses
		  SELECT c.session_id, r.blocking_session_id as Blk_Id, DB_Name(r.database_id) as [DB_Name], s.login_name, r.command, SUBSTRING(t.text, r.statement_start_offset/2,(CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) as Query_Text, tqp.query_plan AS Query_Plan, qmg.query_cost, r.total_elapsed_time / 60000. as total_time_Min, qmg.request_time as Memory_Request, qmg.requested_memory_kb, qmg.granted_memory_kb, qmg.required_memory_kb, qmg.used_memory_kb, qmg.max_used_memory_kb,	r.status, r.cpu_time, r.total_elapsed_time, r.granted_query_memory as Memory, c.num_reads as cnn_reads, c.last_read, c.num_writes as cnn_writes, c.last_write, t.text as Batch_Text, qp.query_plan AS Batch_Plan, r.reads as request_reads, r.writes as request_writes, r.logical_reads, s.host_name, s.program_name, c.client_net_address,
				@Record_Id as Record_Id, GETDATE() as TimeStamp_Dt, 0 as Blocking
		  FROM sys.[dm_exec_connections] c CROSS APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS t INNER JOIN sys.dm_exec_sessions s on c.session_id = s.session_id LEFT JOIN sys.dm_exec_query_memory_grants qmg on c.session_id = qmg.session_id LEFT JOIN sys.dm_exec_requests r on c.session_id = r.session_id CROSS APPLY sys.dm_exec_query_plan(r.[plan_handle]) AS qp CROSS APPLY sys.dm_exec_text_query_plan(r.[plan_handle],r.statement_start_offset,r.statement_end_offset) AS tqp 
		  ORDER BY c.session_id;
	COMMIT
	END

	WAITFOR DELAY '00:00:01'
END
26 фев 13, 22:28    [13985486]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
LenaV
автор
картинку генерю с помощью sys.dm_exec_requests.

посмотрите там wait_type, last_wait_type and wait_time
Вот их четырёх я как раз и не собирал. На них никогда и внимания даже не обращал.

Сейчас перезапущу гляну что там как.
26 фев 13, 22:33    [13985513]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
Попытался прочитать залоченую страницу, но она пустая и ссылка на 99-й обьект, а системные обьекты на 98-м кончаются.

Единственное пока предположение что сессии дерутся за доступ к прилинкованому серверу.

К сообщению приложен файл. Размер - 38Kb
26 фев 13, 23:30    [13985752]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
LenaV
Member

Откуда: USA
Сообщений: 6797
SandalTree
Попытался прочитать залоченую страницу, но она пустая и ссылка на 99-й обьект, а системные обьекты на 98-м кончаются.

разнесите таблицы в которые вы пишите по разным физическим файлам
или хотя бы создайте несколько фаилов одинакового размера в группе
27 фев 13, 00:52    [13985980]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
LenaV
SandalTree
Попытался прочитать залоченую страницу, но она пустая и ссылка на 99-й обьект, а системные обьекты на 98-м кончаются.

разнесите таблицы в которые вы пишите по разным физическим файлам
или хотя бы создайте несколько фаилов одинакового размера в группе
Предположительно это спасёт меня от блокировок в sysindexes, а если блокировки идут при доступе к прилинкованому серверу то это по идее не поможет. Так?
27 фев 13, 01:36    [13986039]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8910
Может выкинуть select top 1 1 (заменить на select *) и навесить nolock-ов для начала, не?
27 фев 13, 01:37    [13986043]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
SIMPLicity_
Может выкинуть select top 1 1 (заменить на select *) и навесить nolock-ов для начала, не?
Если даже этот запрос вообще убрать, то блокировки всё равно никуда не денутся.

Лена правильную идею дала, проблема в том что это значительно усложнит процесс.
Во первых это не принято, не поймут, а во вторых есть гораздо более лёгкий способ - ограничить количество конкурирующих процессов, но я-то бьюсь за то что-б малой кровью это количество увеличить.
27 фев 13, 02:36    [13986084]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
LenaV
Member

Откуда: USA
Сообщений: 6797
[quot SandalTree]
LenaV
пропущено...

а если блокировки идут при доступе к прилинкованому серверу то это по идее не поможет. Так?

я не вижу у вас блокировки к прилинкованому серверу.
вы наверное про ожидание ОЛЕДБ
это обычно связано со скоростью передачи данных по сети
и размером виртуальной памяти.
27 фев 13, 02:56    [13986087]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
LenaV
Member

Откуда: USA
Сообщений: 6797
SandalTree
Попытался прочитать залоченую страницу, но она пустая и ссылка на 99-й обьект, а системные обьекты на 98-м кончаются.

Единственное пока предположение что сессии дерутся за доступ к прилинкованому серверу.

ссылка на 99-й обьект и номер страницы 3663864 указывают на PFS страницу.
у вас PFS bottleneck.
скажите дба пусть добавит файлы к базе данных.
27 фев 13, 05:48    [13986128]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
SandalTree,

Было бы здОрово увидеть результат

SELECT @@version
27 фев 13, 07:03    [13986190]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
LenaV
SandalTree
Попытался прочитать залоченую страницу, но она пустая и ссылка на 99-й обьект, а системные обьекты на 98-м кончаются.

Единственное пока предположение что сессии дерутся за доступ к прилинкованому серверу.

ссылка на 99-й обьект и номер страницы 3663864 указывают на PFS страницу.
у вас PFS bottleneck.
скажите дба пусть добавит файлы к базе данных.
Так просто файлы распаралелить или таки распихать таблицы по разным файлам?

ЗЫ Ты меня совсем обидеть решила, я-ж и есть ДБА. Вот сижу, экспириенса набираюсь.

2 pkarklin
пишу из дома. Там 2008R2. Или точный релиз что-то скажет?
27 фев 13, 07:10    [13986206]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
LenaV
Member

Откуда: USA
Сообщений: 6797
SandalTree
LenaV
пропущено...

ссылка на 99-й обьект и номер страницы 3663864 указывают на PFS страницу.
у вас PFS bottleneck.
скажите дба пусть добавит файлы к базе данных.
Так просто файлы распаралелить или таки распихать таблицы по разным файлам?

лучше распихать по разным файлам так чтобы файлы были одинакового размера и одинаково заполнены
т.к. скл сервер использует round-robin allocation and proportional fill для заполнения файлов.
как это сделать зависит от размера таблиц, от возможности взять downtime, от индексов и пр.
это вам решать.
если малой кровью - то как вариант - если у вашего дата файла осталось совсем немного свободного места,
то запретите ему расти
и создайте парочку дополнительных файлов в той же файл группе.
скл сервер будет записывать новые данные в новые файлы равномерно.
27 фев 13, 19:03    [13990803]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
LenaV
SandalTree
пропущено...
Так просто файлы распаралелить или таки распихать таблицы по разным файлам?

лучше распихать по разным файлам так чтобы файлы были одинакового размера и одинаково заполнены
т.к. скл сервер использует round-robin allocation and proportional fill для заполнения файлов.
как это сделать зависит от размера таблиц, от возможности взять downtime, от индексов и пр.
это вам решать.
если малой кровью - то как вариант - если у вашего дата файла осталось совсем немного свободного места,
то запретите ему расти
и создайте парочку дополнительных файлов в той же файл группе.
скл сервер будет записывать новые данные в новые файлы равномерно.
Распихивать это не проблема.
Проблема в том что имеет-ли смысл создавать более 8 файлов если на Дев серваке всего 4 кора.

Таблички очень разные, так-что я совсем не понял совета "делать файлы одинакового размера"
Если самые большие/тормознутые таблички: 8/5/3 .... Гб.

В чём смысл "одинакового размера"?
Как я понимаю это важно только когда мы не разбрасываем тамбицы по отдельным файлам.

Сперва протестирую распаралеливание темпдб, а потом уже буду распихивать.
27 фев 13, 20:04    [13990973]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28147
LenaV
SandalTree
Попытался прочитать залоченую страницу, но она пустая и ссылка на 99-й обьект, а системные обьекты на 98-м кончаются.

Единственное пока предположение что сессии дерутся за доступ к прилинкованому серверу.

ссылка на 99-й обьект и номер страницы 3663864 указывают на PFS страницу.
у вас PFS bottleneck.
скажите дба пусть добавит файлы к базе данных.
Кстати, гуглил, ничего не нашёл про 99. Можешь кинуть линк?
27 фев 13, 20:19    [13991004]     Ответить | Цитировать Сообщить модератору
 Re: Странная блокировка  [new]
LenaV
Member

Откуда: USA
Сообщений: 6797
автор
--Проблема в том что имеет-ли смысл создавать более 8 файлов если на Дев серваке всего 4 кора.

на Дев нет, а на Прод сколько?
в любом случае 8-ми обычно хватает.

автор
--В чём смысл "одинакового размера"?
--Как я понимаю это важно только когда мы не разбрасываем тамбицы по отдельным файлам.


скл сервер использует proportional fill механизм для создания новых страниц.
это значит, что если у вас есть файл 1 размером 10ГБ и заполнен на 5ГБ
и вы создали файл 2 пустой размером 10ГБ,
то значительно большее колличество новых страниц будут создаваться на файле 2 до тех пор пока он не достигнет занятости 5ГБ.
и ваша проблема просто перенесется на файл 2 вместо файл 1
и да это все касается только файлов в одной файловой группе.
27 фев 13, 20:34    [13991037]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить