views:

676

answers:

2

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 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?

+3  A: 

OK, i found some articles relating to this subject. I hope they might be helpfull. I must say that this has opened all new kinds of doors for me regarding query performace tuning.

SQL Server Wait Events: Taking the Guesswork out of Performance Profiling

Analysing Query Performance in SQL Server 2005

Display SQL Server database waits

INF: Client Effects on SQL Server Throughput

astander
Thanks for these, I'll have a read. I can't post the query I've been running. It's rather large and wouldn't make alot of sense without a lot of background. I'm working on a simple test harness that produces the same result. I'll post it as soon as I'm done.
Robin Day
+1  A: 

'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.

Remus Rusanu
That was what I thought the meanings were for them as well. The key thing is, both queries still have the same outlying sort order and making changes to this sort to include columns that are not indexed at all still results in the same output. I'll have a look into some of the wait type and network io stuff though. Thanks. I'm still working on a test harness to show all this. Hopefully it will make more sense then.
Robin Day