SQL Wait ASYNC_NETWORK_IO on NAV Production

sarmasarma Posts: 7Member
Hi All,

While we are looking into NAV SQL Performance issue, we found that one of the top wait is "ASYNC_NETWORK_IO".As per my understading the NAV App server is not consuming the data generated by DB quickly.

Our Prod Setup is:

-2 Terminal services for NAV client, and they are load balanced.
-2 App Servers are load balanced
-1 DB server in AG group with Sync

Can someone help me how to trouble shoot this issue with NAV client? or some one come across this type of issue before.When i asked my Infrastructure team, they told me that every thing is fine, and nothing they suspect from NAV App server or Terminal server.

Is there any setting with in NAV defines Client memory size etc.? or is it down to NAV coding etc.?

Any help would be appreciate.

Regards
Sarma

Answers

  • Slawek_GuzekSlawek_Guzek Posts: 713Member
    Hi

    "Everything's fine" may simply mean everything works, not necessarily "everything works at fastest possible pace" :)

    Since you have an infrastructure team I guess you have quite decent and expensive hardware around, including all network gear. Yet it still may be worth to check a few things. If you google around for "TCP/IP offloading SQL Server problem" you will find a good few articles about how to not to setup SQL server's network cards to avoid SQL Server performance problems.

    Massive ASYNC_NETWORK_IO does not always mean problems. The real meaning of it is that "the NAV App server is not consuming the data generated by DB quickly enough.". That's it. It does not have to consume the data slowly, it just means that the SQL Server is quicker delivering it. Which is good in fact, as long as users are not complaining about the slow NAV.

    If they are, then the sad truth is that it is almost always down to NAV coding - investing time in NAV code and table structure/indexing optimisations usually give the best pay-offs.

    Memory-wise as far as I know you can only play with the cache size settings on the NST server, I am not aware of any RTC client side settings. They would not make much sense or impact anyway as the data flows between the NST and SQL server, the RTC clients are involved in data processing.

    Regards,
    Slawek
  • David_SingletonDavid_Singleton Posts: 5,416Member
    sarma wrote: »
    Hi All,

    While we are looking into NAV SQL Performance issue, we found that one of the top wait is "ASYNC_NETWORK_IO".As per my understading the NAV App server is not consuming the data generated by DB quickly.
    ...

    Had the same problem in NAV 2013.

    How many CPU cores do you have on your SQL server? And what is you MAXDOP? if you have MAXDOP set to 4 or 8 then setting it to 1 may give you a quick fix whilst you sort out the root cause. But this will most likely slow down the system, but at least it wont lock out new users logins.

    Ultimately take a look at your worker threads, as this is the most likely cause. But don't set them too high if you don't have enough CPU cores.

    David Singleton
  • David_SingletonDavid_Singleton Posts: 5,416Member
    ...
    Massive ASYNC_NETWORK_IO does not always mean problems. The real meaning of it is that "the NAV App server is not consuming the data generated by DB quickly enough.". That's it. It does not have to consume the data slowly, it just means that the SQL Server is quicker delivering it. Which is good in fact, as long as users are not complaining about the slow NAV. ...

    Noit always a problem but if it reaches the limit, then the server will refuse new connections and they will have to restart the service tier, which is never a good thing.

    Ideally you want your Middle tier matched to the work load and the SQL server so that everything runs smoothy, and it is best to address these issues before the whole system grinds to a halt.

    David Singleton
  • Slawek_GuzekSlawek_Guzek Posts: 713Member
    Are you sure that the ASYNC_NETWORK_IO wait can force the server to refuse connections? Personally I can't see why it would or could cause that. And how dropping the MAXDOP could help.

  • David_SingletonDavid_Singleton Posts: 5,416Member
    Are you sure that the ASYNC_NETWORK_IO wait can force the server to refuse connections? Personally I can't see why it would or could cause that. And how dropping the MAXDOP could help.

    How many CPU cores do you have? And how many worker threads?
    David Singleton
  • Slawek_GuzekSlawek_Guzek Posts: 713Member
    edited 2017-10-12
    @sarma didn't say but I guess minimum sensible configuration is 4 cores, as you can't bus SQL Server license for less than 4 cores.

    Default worker threads for 4 or less CPU core is 512

    And?
  • David_SingletonDavid_Singleton Posts: 5,416Member
    sarma wrote: »
    Any help would be appreciate.

    Need more info please.

    David Singleton
  • David_SingletonDavid_Singleton Posts: 5,416Member

    Default worker threads for 4 or less CPU core is 512
    Are you getting the same error as Sarma?
    David Singleton
  • Slawek_GuzekSlawek_Guzek Posts: 713Member
    Are you getting the same error as Sarma?
    Have you read his post at all?

    He is not getting errors. He is complaining about high level of ASYNC_NETWORK_IO waits, that's it.
  • sarmasarma Posts: 7Member
    Hi Guys,

    We have server in AWS environment,

    The DB server has 1 CPU and 16 cores.
    The SQL Memory 100GB
    The MAX_WORKING_THREAD is default one i.e. 0(Internally SQL calculating 700+ threads)
    The MAXDOP value 1
    The Always on group with FULL Sync mode.

    We are getting this issue intermittent and one of the NAV session consumes 400+ threads(even after MAXDOP to 1).We are unable to identify root cause.Can some one helps me that would be great.

    Regards


Sign In or Register to comment.