Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Sandra Rudloff]

We’ve discovered that occasionally, but at least weekly, we have users complaining of slowness or locking up, and that we have blocking in the system. 
Specifically

the one below.  If we end it, all goes back to normal.  Wondering if anyone else has had this happen, and what have you done to trace it back to the cause? 

——————————
Sandra Rudloff
Vice President Business Systems
Pivot Interiors, Inc.
Santa Clara CA
——————————

[Neill Riordan]

Hello Sandra,

 

Currently, Microsoft has published this guidance

 

Scenario: Users report slow performance when using the system. One issue could be a blocking statement. Blocking by itself is typical in a healthy system and is only a problem when it becomes excessive or starts degrading business activities.

1. Go to the Live View tab and check if there are any blocking statements. If there is a blocking statement, copy the blocking query ID.
2. Open the Queries tab and select the Current Blocking Tree query. This will return the root blocker that is blocking the SQL operation.
3. To resolve the issue, you can either let it run and clear naturally, or end the process for the lead blocker, which will roll work back. Typically, you should only end the lead blocker process if you think that it will not clear naturally (such as a bad query plan), or in situations where a critical process is unable to run and needs to complete immediately.
4. Confirm that it’s okay to terminate the statements that are currently being executed.
5. Open the Actions tab and select the End SQL Process action and pass in the root blocker query ID. This will execute a query against the SQL database to terminate the blocking statement.
6. Go to the Queries tab and run Current blocking query to verify if the blocking statement was terminated.
7. You can also check the Environment History page to see details on what process was terminated.
8. To avoid this issue in the future, you should use indexes or plan guides, or turn off lock escalation, or use page locks if processes are blocking each other while operating on different records. If processes are operating on the same records, the only way to avoid blocking is by refactoring or rescheduling the processes to not operate on the same records at the same time.

 

That last point is a big “sort your self out statement”.  The reality is it takes some digging and we found the most helpful area now you understood the statement is to go to the performance metrics.  This query should be poorly performing and should show in this area.  If you have any concerns this could be custom code please get your dev team to review the indexes and best practice information around performance.  Outside of that sometimes an execution plan run by Microsoft is not always best so we have before request via support detail of the execution plan being used for the query as we may have to insert our own through lcs to stop the issue.

 

We have also found that the indexing routines used by MS sometimes are not robust enough so as part of the above steps make sure you understand the state of the indexes used by the process that is blocking.

——————————
Neill Riordan
Dynamics 365 Solution Architect
IBM
——————————
——————————————-

[Zvika Rimalt]

Hi Neil – do you have a link to these guidelines? I want to distribute it to our technical people, so I want them to have the original (and possibly a place to review more guidelines)

——————————
Zvika Rimalt
Functional Consultant
Vancouver BC
——————————
——————————————-

[Neill Riordan]

Sure here is the link
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/lifecycle-services/monitoring-diagnostics

You will find the performance link at the bottom of this page

——————————
Neill Riordan
Dynamics 365 Solution Architect
IBM
Watford
——————————
——————————————-

[Sandra Rudloff]

Thanks Neill!  Great info !

——————————
Sandra Rudloff
Vice President Financial Operations
Pivot Interiors, Inc.
Santa Clara CA
——————————
——————————————-