D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

SQL blocks?

  • 1.  SQL blocks?

    SILVER CONTRIBUTOR
    Posted Jul 03, 2019 12:07 PM
      |   view attached
    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?

    thumbnail image





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


  • 2.  RE: SQL blocks?

    Posted Jul 04, 2019 04:53 AM

    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
    ------------------------------



  • 3.  RE: SQL blocks?

    TOP CONTRIBUTOR
    Posted Jul 04, 2019 02:05 PM
    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
    ------------------------------



  • 4.  RE: SQL blocks?

    Posted Jul 04, 2019 02:44 PM
    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
    ------------------------------



  • 5.  RE: SQL blocks?

    SILVER CONTRIBUTOR
    Posted Jul 05, 2019 01:44 PM
    Thanks Neill!  Great info !

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



If you've found this thread useful, dive deeper into User Group community content by role