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:
Sent: 11-08-2018 08:02 AM
From: Mathias Andersen
Subject: Connecting to NAV via Odata result in deadlock on SQL server and NAV service tier shutdown
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
------------------------------