SQL Server - Management Studio - Client Statistics - Wait time on server replies vs Client processing time

I have a slow running query that I've been working on optimising.

When looking at the Client Statistics in Management Studio it was taking about 8 seconds wait time on server replies and about 1 second on Client processing time.

I have always thought that the Wait time on server replies was the number to work on and Client processing time was generally bandwidth or large data size related.

I have made a number of changes to the query and now my Wait time on server replies is开发者_Python百科 around 250ms, however, the Client processing time has increased to about 9 seconds making the Total execution time slightly slower.

The result set being returned is exactly the same.

Can someone shed any light on what exactly the difference between these two numbers is and what would cause such a result?

'Wait time on server replies' is the time between the last request packet left the client and the very first response packet returned from the server. 'Client processing time' is the time between first response packet and last response packet. Btw, I couldn't find the documentation to back these claims, but I'd say, based on my observations, that they are valid educated guess.

If you run a query with a large 'wait time on server replies' it means the server took long time to produce the very first row. This is usual on queries that have operators that need the entire sub-query to evaluate before they proceed (typical example is sort operators).

On the other hand a query with a very small 'wait time on server replies' means that the query was able to return the first row fast. However a long 'client processing time' does not necessarily implies the client spent a lot of time processing and the server was blocked waiting on the client. It can simply mean that the server continued to return rows from the result and this is how long it took until the very last row was returned.

What you see is the result of changes in the query plan that probably removed an operator that was blocking execution (most probably a sort) and the new plan uses a different strategy that produces the first result faster (probably uses an index that guarantees the requested order so no sort i needed), but overall last longer.

If you are worried about the client holding back the server (it can happen on large result sets) then you should investigate the wait_type in sys.dm_exec_requests (also info from sys.dm_os_tasks and sys.dm_os_workers is usefull) for the session executing the query under investigation. If I'm not mistaken the server waiting on client wait type is ASYNC_NETWORK_IO. You can also check the aggregate sys.dm_os_wait_stats, reset it using DBCC SQLPERF("sys.dm_os_wait_stats" , CLEAR) then run the query, see how long the ASYNC_NETWORK_IO wait type adds up. Of course, make sure no other activity occurs on the server during the test.