axinthefield: SQL Server Lock Escalation and Blocking

[Blog bot]

Источник: 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