6

We have an in-house application, developed in WinDev, that uses a Microsoft SQL Server 2012 SP2 (build 11.0.5058) database running on Windows Server 2008R2.

We are experiencing performance issues. At the same time Activity Monitor shows a huge number of waits of type "network I/O", where "huge" in this case is a cumulative wait time of 7 million seconds for a server that is up for less than 6 days:

enter image description here

The network connectivity itself is fine, the server is connected with 10G while the client are either physical desktop connected with 1Gb or Remote Desktop servers with 10G. Network monitoring shows no link saturation, nor any other network issue. CPU, RAM and disk I/O are also fine.

From what I read about Network I/O waits, it relates to records returned by a query that are not consumed by the clients. So I tend to think the issue is in the application but I have a hard time having the developers investigate this (not that they are unwilling to do it, but they are quite busy and it seems they don't have any clue about the root cause and how to solve it)

So the questions:

  • Am I right thinking the performance issue is related to those Network I/O waits?

  • what clue could I provide to the dev team to help them identify the cause?

  • apart from fixing the application, is there some fine tuning I can do on the SQL Server itself to alleviate the issue?

  • 2
    Pretty much like you say, receiving side application not consuming the data as fast as SQL delivers it. Supporting content, post by Paul Randal - sqlskills.com/blogs/paul/… – Henrico Bekker May 18 '17 at 12:29
10
  1. Stop using Activity Monitor
  2. Use sp_WhoIsActive to figure out what's actually going on

It could be backups, in which case there's not a whole heck of a lot you can do that doesn't involve hardware upgrades (maybe that 1 Gb iSCSI wasn't such a great idea...)

It could be client-side code consuming data RBAR (think foreach loops for every row coming in), or asking for a lot of rows all at once (this is where paging queries can help).

It could be something else entirely!

To get an idea what your server is waiting on the most, head over to firstresponderkit.org -- full disclosure, I contribute to this open source project -- and grab sp_BlitzFirst (or, heck, grab'em all).

You can run this command to look at your wait stats as a whole, since startup.

EXEC sp_BlitzFirst @SinceStartup = 1

Or this to get a sample of wait stats during a slowdown.

EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

Hope this helps!

  • Thanks for you help, it definitely looks like RBAR. Investigations in progress – JFL May 18 '17 at 13:43
6

Monitor the queries coming from their application, including the waits (you can do this with Extended Events), and then run the same queries from sqlcmd or SSMS (but not with results to grid) and compare. This should help you distinguish between applications that can't consume results fast enough and a network that can't transmit data fast enough.

I don't know if "Network I/O" in Activity Monitor maps only to ASYNC_NETWORK_IO but on a network like yours that wait is almost certainly caused only by delays in client consumption.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.