Sometimes it does feel that a problem is a database problem until proven otherwise.  When this does occur I have had to put a lot of time and effort into demonstrating the hows and whys.  One of the easiest ( and most common ) scenarios of this is related to ASYNC_NETWORK_IO waits.

Simply put ASYNC_NETWORK_IO waits occur when SQL Server is waiting on the client to consume the output that it has ‘thrown’ down the wire.  SQL Server cannot run any faster, it has done the work required and is now waiting on the client to say that it has done with data. 

Naturally there can be many reasons for why the client is not consuming the results fast enough , slow client application , network bandwidth saturation, to wide or to long result sets are the most common and in this blog I would like to show you how I go about diagnosing and demonstrating these issues.

Firstly lets make a query execute 10 times faster with no effort, seriously no effort , a single tick box.  This tick box is “Discard results after execution in SSMS”.

SSMS is surprisingly slow at painting the text data on the screen,  in fact it seems woefully slow.  By using this tick box SQL server does exactly the same work required as without,  throws the data down the wire as normal and waits for the client to responded, again, as normal.   SSMS however will read all the bytes off the wire,  however it will not show you the data.  This will enable you to run queries at the speed that SQL server can run at but be crippled by the client application speed. Yes SSMS is not SQL Server but a client application.

So lets test this theory with the simplest query possible “SELECT * FROM Sales.SalesOrderHeader”…Here is the profiler output running that query 5 times:

An average of ~2.4 ish seconds.

Now, lets turn on ‘Discard results after execution”,  this is the only thing i have done, and re run the query 5 times and…

Running at ~240ms , a 10 times speed improvement, reads and CPU are in the same ballpark, the only difference is SQL Server hasn’t been throttled by SSMS having to present the result set to the user.  If you are trying to performance tune a query , are you taking this into account ?  Maybe the problem is not where you think it is.

So why is  ASYNC_NETWORK_IO wait my favourite ?  Well at the global level we can now find out how much of an effect this is having.

If i clear the waits and use Paul Randal’s “Tell me where it hurts” query when running without discard the ASYNC_NETWORK_IO , SQL Server has ‘waited’ 10.44 seconds.

With discard on:

Nothing, no rows returned.  There were no waits.

Clearly this demonstrates that client application performance is a serious consideration in our scenario here.

Incidentally,  where you are executing a query from will make a hell of a difference,  I was once involved in a performance issue where the DBAs could not reconcile a duration time shown in a profile trace against the application with the same query executing in SSMS.   The big differentiator was that SSMS was being run on the server itself (RDP’d into <facepalm>), the query was fine,  if SSMS had been run off of the saturated network connection that the applications were running over then the same ‘performance problem’ would have been witnessed.  ‘Fair test’ is something we should always be looking to achieve.

SSMS has another often underused feature ( well it has loads ) :  Client Statistics

This is enabled by this icon in SSMS.

What this will enable you to do is compare different result executions over a number of different attributes

As you can see my “Wait time on server replies” the inverse if you like of ASYNC_NETWORK_IO is 3(milliseconds).

Why i particularly like this is the “Bytes Received from server” metric,  self evident what that means but in performance tuning in my experience its a question rarely asked but here we have an exact answer to that.  With this simple number we can now ask  “Well how long would it take to copy a same sized file from server to client ?” When the result set size is gbs,  this  simple question will result in a a fairly rapid re-architecting of a system.

In conclusion ASYNC_NETWORK_IO is my favourite wait state as it means ( fairly mendaciously  ) its not a SQL Server problem, its done its work.  Now, lets talk about doing it right.

If you are interested in more real world stories and hints and tips like this then you’ll hear them in my precon at SQLSaturday Exeter on 11th March 2016, be great to see you there .