Microsoft BI Professionals Denmark

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

View My Drafts

Back to discussions
Expand all | Collapse all

Connecting to NAV via Odata result in deadlock on SQL server and NAV service tier shutdown

  • 1.  Connecting to NAV via Odata result in deadlock on SQL server and NAV service tier shutdown

    Posted Nov 08, 2018 08:02 AM
    Dear Community,

    I am experiencing issues when connecting to NAV via Odata feed.

    With no specific pattern, the SQL database log the following error and afterwards all the NAV services lose connection to the SQL database.
    "All schedulers on Node 0 appear deadlocked due to a large number of worker theads waiting on ASYNC_NETWORK_IO"

    The issue is observed when updating from Power BI desktop and when using scheduled update via the datagateway on Power BI Service and on two different environments.

    Have anyone experienced the same issues and did you find a solution?

    ------------------------------
    Mathias Andersen
    ------------------------------


  • 2.  RE: Connecting to NAV via Odata result in deadlock on SQL server and NAV service tier shutdown

    Bronze Contributor
    Posted Nov 09, 2018 07:22 AM
    It sounds like you should look into MAXDOP on the server. However you probably don't want to modify the server setup just because Power Query is putting to much pressure on it.

    Some other ways to do it, could be implementing wait states in your Power Queryies in order to minimize the concurrency.
    Or you could use the Incremental Reresh, should you be so fortunate to have a Premium licens at hand.

    Are you using some of the Standard Apps? Or is it something your build yourself in your organisation?

    Also bear in mind that your production system is trying to tell you that might be doing operations on it it is not scaled for. NAV is not OLAP and you might benefit from a datawarehouse/lake/seperate system (call it want you want) that is build for your purpose.

    /Henrik

    ------------------------------
    Henrik Zacher Molbech
    R&D Manager
    Molbech
    ------------------------------

    Original Message


  • 3.  RE: Connecting to NAV via Odata result in deadlock on SQL server and NAV service tier shutdown

    Posted Nov 12, 2018 02:22 AM
    The excessive ASYNC_NETWORK_IO wait stats typically indicates that your client (Power BI Desktop and Power BI scheduled update) is not able to consume the dataset fast enough when updating. The reason for that might be that the update ask for a very large dataset. Another reason for getting the excessive ASYNC_NETWORK_IO stats might be the Network itself but that's normally not very likely. And since you are using OData any filtering of your data might be handled locally and not on your server.

    Maybe this might help:

    OData filter pass-through

    BTW if you have problems with MAXDOP you should look for excessive CXPACKET Wait Type stats on your server which might indicate problems with your queries using parallel execution plans.

    HTH
    Bobby Henningsen





    ------------------------------
    Bobby Henningsen
    Consultant
    4D A/S
    Copenhagen
    33147144
    ------------------------------

    Original Message