When a process attempts to initiate a lock on a resource that is already locked by a separate process, blocking occurs. This causes the Microsoft Dynamics AX user screens to freeze for an extended period of time or a batch job to run beyond the normal runtime.
Using the Activity Monitor
In Microsoft SQL Server 2008, the Activity Monitor is an easy tool that is used to detect if extended blocking is occurring. To access the Activity Monitor open SQL Server Management Studio, connect to your SQL instance, right click your instance in the object explorer and select ‘Activity Monitor’.
Once activity monitor is open, click the processes bar to view the SQL processes. It may take a few seconds for anything to load. The default refresh interval is 10 seconds but this is changed by right click the white space in the top right and selecting the refresh interval you desire. If you are trying to filter the processes, pause the refresh if necessary.
Finding Stuck Processes
Once the processes load, you want to look at the ‘Wait Type’ column, ‘Blocked By’ column, and ‘Head Blocker’ columns. The Session Id’s with ‘LCK’ wait types are the processes that are stuck and waiting for another process to release a lock on a resource. The Session Id with a 1 in the ‘Head Blocker’ column is the process that is holding a lock and preventing another process from accessing the same resource.
By right clicking the Head Blocker and selecting ‘View Details’ you get an idea of the process that is running. The statement shown is the current statement being executed and is not always the statement that is holding the lock. This info is used to help identify the overall process/activity.
Once you know the SQL Session Id (SPID) you try to identify the user/process associated to the SPID. In AX, go to Administration -> Common forms -> Online Users. Here you use the SPID column on the form to gather a lot of useful information. You identify the AOS instance, the computer the SPID originated on, the status, session type, and login date/time. All of this information is used to help identify the process causing the blocking.
In an environment with multiple AOS’s, you will only see the SPID’s for the AOS your current AX client session is connected to. You use the ‘Host Name’ column in SQL Activity Monitor to identify what AOS the blocking SPID is on.
If you have questions about detecting blocking and identifying the source in Dynamics AX 2009, contact us.