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

Откуда: Київ
Сообщений: 10428
Т.е. блокирующий и блокируемый?
18 сен 12, 15:26    [13183222]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Используя sys.dm_tran_locks можно получить текущие блокировки. Используя session_id и request_id через sys.dm_exec_requests получить sql_handle. Используя sys.dm_exec_sql_text получить текст запроса.

Или поставить себе в SSMS Performance Dashboard Reports for SQL Server и наслаждаться прекрасной визуализацией блокировок.

Сообщение было отредактировано: 18 сен 12, 16:06
18 сен 12, 16:06    [13183524]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Пример, собственно. Ткнув в запрос в правом столбце можно получить его план и из какой хп этот запрос.

К сообщению приложен файл. Размер - 125Kb
18 сен 12, 16:13    [13183563]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Данный отчет открывается по клику на столбце Lock в главном отчете дашбоарда. Другие типы ожиданий тоже кликабельны и так же несут кучу полезной информации из DMV, но удобоваримом виде.

К сообщению приложен файл. Размер - 87Kb
18 сен 12, 16:19    [13183603]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
gang
Member

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

+
Инструкция по сбору и просмотру информации о блокировках.

1) Включить на сервере генерацию событий blocked-process-report установив порог времени блокировки в секундах скриптом
sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 10 ;
GO
RECONFIGURE ;
GO

2) Натроить сбор профайлером событий blocked-process-report. Результат можно направить в таблицу
с именем blocked или сохранить как таблицу blocked для дальнейшего парсинга. Экземпляр SQL для хранения результата
должен быть 2008-й.

3) В поле TextData содержится xml-описание события. В принципе если нужно только прочесть XML, то все готово. Но XML описание блокировки очень схоже с аналогичным для события Deadlock Graph, поэтому
слегка переформатировав теги можно добиться отображения графа и для простой блокировки в графическом исполении. Для парсинга и переформатирования TextData выполнить:

declARE @blockedProcessChains TABLE(chainid datetime, chainxml xml)
DECLARE @bprXML xml,
@chainXml xml,
@newResourceXml xml,
@newOwnerXml xml,
@newWaiterXml xml
DECLARE @blockingspid int,
@blockingecid int,
@blockingid varchar(30),
@blockingprocess xml,
@blockedspid int,
@blockedecid int,
@blockedid varchar(30),
@blockedprocess xml,
@chainid datetime,
@waitresource nvarchar(50)

SET NOCOUNT ON

DECLARE bpCursor CURSOR
FOR SELECT cast(TextData as xml), EndTime
FROM blocked --hey, remember to save to a trace table in this database named blocked
WHERE EventClass = 137

--select top 100 * from blocked

OPEN bpCursor

FETCH NEXT FROM bpCursor INTO @bprXML, @chainid
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT
@blockingspid = @bprXML.value('/blocked-process-report[1]/blocking-process[1]/process[1]/@spid', 'int'),
@blockingecid = @bprXML.value('/blocked-process-report[1]/blocking-process[1]/process[1]/@ecid', 'int'),
@blockingprocess = cast(@bprXML.query('/blocked-process-report[1]/blocking-process[1]/process[1]') as xml),
@blockedspid = @bprXML.value('/blocked-process-report[1]/blocked-process[1]/process[1]/@spid', 'int'),
@blockedecid = @bprXML.value('/blocked-process-report[1]/blocked-process[1]/process[1]/@ecid', 'int'),
@blockedprocess = cast(@bprXML.query('/blocked-process-report[1]/blocked-process[1]/process[1]') as xml),
@waitresource = @bprXML.value('/blocked-process-report[1]/blocked-process[1]/process[1]/@waitresource', 'nvarchar(50)')


SET @blockingid = 'process' + CAST(@blockingspid as varchar(10)) + '_' +CAST(@blockingecid as varchar(10))
SET @blockedid = 'process' + CAST(@blockedspid as varchar(10)) + '_' +CAST(@blockedecid as varchar(10))
SET @blockingprocess.modify('insert attribute id {sql:variable("@blockingid")} into (/process[1])')
SET @blockedprocess.modify('replace value of (/process/@id)[1] with sql:variable("@blockedid")')


SET @chainXml = CAST('<deadlock-list><deadlock><process-list></process-list><resource-list></resource-list></deadlock></deadlock-list>' AS xml);
IF EXISTS (select chainid from @blockedProcessChains where chainid = @chainid)
SELECT @chainXml = chainxml
FROM @blockedProcessChains
WHERE chainid = @chainid;
ELSE
INSERT @blockedProcessChains(chainid, chainxml)
VALUES (@chainid, @chainXml);


IF (@chainXml.exist('//process-list/process[@ecid = sql:variable("@blockedecid") and @spid = sql:variable("@blockedspid")]') = 1)
SET @chainXml.modify('delete //process-list/process[@ecid = sql:variable("@blockedecid") and @spid = sql:variable("@blockedspid")]')
SET @chainXml.modify('insert sql:variable("@blockedprocess") into (//process-list)[1] ')


IF NOT (@chainXml.exist('//process-list/process[@ecid = sql:variable("@blockingecid") and @spid = sql:variable("@blockingspid")]') = 1)
SET @chainXml.modify('insert sql:variable("@blockingprocess") into (//process-list)[1] ')


IF NOT (@chainXml.exist('//resource-list/unknownlock[@resource=sql:variable("@waitresource")]') = 1)
BEGIN
SET @newResourceXml = CAST('<unknownlock><owner-list /><waiter-list /></unknownlock>' as xml);
SET @newResourceXml.modify('insert attribute resource {sql:variable("@waitresource")} into (/unknownlock[1])');
SET @chainXml.modify('insert sql:variable("@newResourceXml") into (//resource-list)[1] ');
END


IF NOT(@chainXml.exist('//unknownlock[@resource=sql:variable("@waitresource")]//owner[@id=sql:variable("@blockingid")]')= 1)
BEGIN

SET @newOwnerXml = CAST('<owner />' as xml);
SET @newOwnerXml.modify('insert attribute id {sql:variable("@blockingid")} into (/owner[1])');
SET @chainXml.modify('insert sql:variable("@newOwnerXml") into (//unknownlock[@resource=sql:variable("@waitresource")]/owner-list)[1]');
END


IF NOT(@chainXml.exist('//unknownlock[@resource=sql:variable("@waitresource")]//waiter[@id=sql:variable("@blockedid")]')= 1)
BEGIN

SET @newWaiterXml = CAST('<waiter requestType="wait" />' as xml);
SET @newWaiterXml.modify('insert attribute id {sql:variable("@blockedid")} into (/waiter[1])');
SET @chainXml.modify('insert sql:variable("@newWaiterXml") into (//unknownlock[@resource=sql:variable("@waitresource")]/waiter-list)[1]');
END


UPDATE @blockedProcessChains
SET chainxml = @chainXml
WHERE chainid = @chainid

FETCH NEXT FROM bpCursor INTO @bprXML, @chainid
END
CLOSE bpCursor;
DEALLOCATE bpCursor;


WITH rankedEvents AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY EndTime ORDER BY Duration DESC) AS myRank ,
CAST (TextData AS XML) AS TextData,
EndTime
FROM blocked --hey, remember to save to a trace table in this database named blocked
WHERE EventClass = 137
),
blockers AS
(
select EndTime,
'process' +
re.TextData.value('(//blocking-process/process/@spid)[1]', 'varchar(10)') +
'_' +
re.TextData.value('(//blocking-process/process/@ecid)[1]', 'varchar(10)') as processId
from rankedEvents re
where myRank = 1
)
UPDATE @blockedProcessChains
SET chainxml.modify('insert attribute victim {sql:column("processId")} into (//deadlock)[1]')
FROM @blockedProcessChains bpc
JOIN blockers b ON bpc.ChainId = b.EndTime

SELECT * FROM @blockedProcessChains;

4) Скопировать поле chainxml для конкретного события и сохранить в файле с расширением .xdl
5) Открыть *.xdl файл менеджмент студией.



Как-то так.
18 сен 12, 16:21    [13183615]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
pkarklin
Данный отчет открывается по клику на столбце Lock в главном отчете дашбоарда. Другие типы ожиданий тоже кликабельны и так же несут кучу полезной информации из DMV, но удобоваримом виде.


не вижу и писец.. где этот столбец Lock на главной странице?
18 сен 12, 18:24    [13184447]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
все, нашел...

Другое неясно: как проанадизирвоать блокировки, если оин были 10 минут назад?
Этот отчет показывает текущее состояние, а уже блокировки ушли с выбором жертв...
18 сен 12, 18:49    [13184542]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Winnipuh
Этот отчет показывает текущее состояние, а уже блокировки ушли с выбором жертв...


Так я и думал... С "выбором жертв" уходят мертвые блокировки (deadlocks):

Detecting and Ending Deadlocks

Сообщение было отредактировано: 18 сен 12, 20:45
18 сен 12, 20:44    [13185030]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Col
Member

Откуда: Торонто
Сообщений: 180
Winnipuh
Другое неясно: как проанадизирвоать блокировки, если оин были 10 минут назад?

Алерт настроить в агенте, он и отловит и пришлет все что нужно.
19 сен 12, 04:54    [13185931]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
gang
Member

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

Блокировки и дедлоки разные вещи. Для дедлока есть соответствующие трейс-флаги для вывода инфы в errorlog или событие Deadlock Graph для отлова профайлером.
19 сен 12, 08:52    [13186148]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
ага, понял, спасибо. А вот еще вопрос:

Вот здесь на картинках в blocking process report событии есть внутри хмл запросы. которые блокируют или блокируются...
Как получить такое же?
Я получаю такое событие, но какой именно запрос там нету.

http://www.simple-talk.com/sql/sql-tools/how-to-identify-blocking-problems-with-sql-profiler/
19 сен 12, 11:43    [13187502]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
pkarklin
Member

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

А как же на картинке запрос SELECT * FROM Sales.SalesOrder?
19 сен 12, 11:51    [13187587]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
gang
Member

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

посмотрите повнимательнее, что у Вас в этом XML-нике заключено в теги <executionStack> и <inputbuf> возможно там будет ссылка на кусок кода из проц-ры.
19 сен 12, 11:57    [13187668]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
gang
Winnipuh,

посмотрите повнимательнее, что у Вас в этом XML-нике заключено в теги <executionStack> и <inputbuf> возможно там будет ссылка на кусок кода из проц-ры.


ну вот пара стеков... и что там можно понять?

   <executionStack>
    <frame line="104" stmtstart="6450" stmtend="6646" sqlhandle="0x030006000d7c2f410b2e1a01cca0000001000000000000000000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
Proc [Database Id = 6 Object Id = 1093630989]   </inputbuf>


   <executionStack>
    <frame line="275" stmtstart="23814" stmtend="24358" sqlhandle="0x03000600f8f6140156a41101cfa0000001000000000000000000000000000000000000000000000000000000"/>
    <frame line="110" stmtstart="7270" stmtend="7444" sqlhandle="0x03000600bc00616669511d01cfa0000001000000000000000000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
Proc [Database Id = 6 Object Id = 1717633212]   </inputbuf>
19 сен 12, 12:09    [13187814]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Winnipuh
ну вот пара стеков... и что там можно понять?

Proc [Database Id = 6 Object Id = 1093630989] - вот это не понятно что ли ?
19 сен 12, 12:16    [13187892]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Glory
Winnipuh
ну вот пара стеков... и что там можно понять?

Proc [Database Id = 6 Object Id = 1093630989] - вот это не понятно что ли ?



Это понятно, там процедура в 1000 строк.
Вопрсо был о запросе, ведь процедура не занимается блокировками, а запросы внутри...
19 сен 12, 12:19    [13187916]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Winnipuh
Это понятно, там процедура в 1000 строк.

И каждая из них в точности использует все объекты других ?
Т.е. 1000 одинаковых select-ов ?
19 сен 12, 12:20    [13187929]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Glory
Winnipuh
Это понятно, там процедура в 1000 строк.

И каждая из них в точности использует все объекты других ?
Т.е. 1000 одинаковых select-ов ?


нет, конечно, в процедуре создаются временные таблицы, селекты, апдейты ....
19 сен 12, 12:24    [13187970]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
gang
Member

Откуда:
Сообщений: 1394
Winnipuh
Glory
пропущено...

И каждая из них в точности использует все объекты других ?
Т.е. 1000 одинаковых select-ов ?


нет, конечно, в процедуре создаются временные таблицы, селекты, апдейты ....

И что, вот это вот место:
<frame line="275" stmtstart="23814" stmtend="24358"

ни на какие мысли не наводит?
19 сен 12, 14:15    [13189038]     Ответить | Цитировать Сообщить модератору
 Re: Как в профайлере выловить запросы из блокировок?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
gang
Winnipuh
пропущено...


нет, конечно, в процедуре создаются временные таблицы, селекты, апдейты ....

И что, вот это вот место:
<frame line="275" stmtstart="23814" stmtend="24358"

ни на какие мысли не наводит?


вы думаете что это оно и есть, то что мне надо?
очень похоже ...
19 сен 12, 14:26    [13189206]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить