AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 25.06.2011, 20:14   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
axinthefield: SQL Server Lock Escalation and Blocking
Источник: http://blogs.msdn.com/b/axinthefield...-blocking.aspx
==============

Back in November 2010 I talked about four SQL Server trace flags that I found to be helpful in improving performance for Dynamics AX.

http://blogs.msdn.com/b/axinthefield/archive/2010/11/04/sql-server-trace-flags-for-dynamics-ax.aspx.

One of the trace flags I mentioned was 1224. This is the one that changes the default behavior for lock escalation. Since lock escalation is just one of many reasons you might be experiencing blocking, I just wanted to add some details around how you can identify if lock escalation is in fact an issue for you. Here are the steps I take to identify if lock escalation is causing blocking.



Step 1: Set the Blocked Process Threshold.

Set the blocked process threshold to 5 on the SQL Server hosting your AX database.

sp_configure 'show advanced options',1 ;

GO

RECONFIGURE;

GO

sp_configure 'blocked process threshold',5 ;

GO

RECONFIGURE;

GO

For more information on this option see the following MSDN article on the topic:

http://msdn.microsoft.com/en-us/library/ms181150.aspx



Step 2: Collect Lock Escalation and Blocked Process Report Events.

Lock escalation and blocked process report events are not automatically captured by SQL Server. In order to know if these events are happening, we need to tell SQL Server to record them. Our team uses the Performance Analyzer for Microsoft Dynamics tool to gather that information. Check out this post by Rod Hansen for more information on the tool and how to collect blocking details with it. If you just want to use SQL Server Profiler, the events you would need to collect are shown below:



For more information on these two events see the following MSDN articles:

Blocked Process Report Event Class: http://msdn.microsoft.com/en-us/library/ms191168.aspx

Lock Escalation Event Class: http://msdn.microsoft.com/en-us/library/ms190723.aspx



Step 3: Review the Trace in SQL Server Profiler.

There are two main indicators that will tell you if the blocking is related to lock escalation.

First, you see a series of lock escalation events immediately preceding the blocked process report events. Below is an example taken from a trace produced by the Performance Analyzer for Microsoft Dynamics tool. This is one thing to look for in the trace, but this alone doesn’t mean lock escalation is causing the blocking.





To verify that the blocking is in fact related to lock escalation, you need to look at the blocked process report details. In the TextData section look for waitresource (see the screenshot below). If waitresource starts with OBJECT, we know the blocked statement is waiting on a table level lock to be released before it can proceed. If waitresource starts with KEY or PAG instead of OBJECT, then lock escalation isn’t involved in that specific block. Lock escalation will always increase the scope of a lock to OJBECT regardless of where it starts.



If you see these two things together, it's a pretty good bet that lock escalation is causing the blocking and you would probably benefit from implementing SQL Server trace flag 1224.




Источник: http://blogs.msdn.com/b/axinthefield...-blocking.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
Теги
4199

 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
axinthefield: Capture Database Blocking with Performance Analyzer for Microsoft Dynamics Blog bot DAX Blogs 0 03.03.2011 21:11
axinthefield: SQL Server Trace Flags for Dynamics AX Blog bot DAX Blogs 0 23.11.2010 21:11
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

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

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 15:26.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.