SQL Server - Management Studio - 客户端统计 - 服务器回复的等待时间与客户端处理时间 [英] 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.

在 Management Studio 中查看客户端统计信息时，服务器回复等待时间约为 8 秒，客户端处理时间约为 1 秒.

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.

我对查询进行了一些更改，现在我在服务器回复上的等待时间约为 250 毫秒，但是，客户端处理时间已增加到约 9 秒，从而使总执行时间稍慢.

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?

推荐答案

'Wait time on server replies' 是最后一个请求数据包离开客户端和服务器返回的第一个响应数据包之间的时间.客户端处理时间"是第一个响应数据包和最后一个响应数据包之间的时间.顺便说一句，我找不到支持这些说法的文件，但我想说，根据我的观察，它们是有效的有根据的猜测.

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

如果您担心客户端会阻止服务器(这可能发生在大型结果集上)，那么您应该调查 sys.dm_exec_requests(还有来自 sys.dm_os_tasks 和 sys.dm_os_workers 对执行调查中的查询的会话很有用.如果我没有记错，服务器等待客户端等待类型是 ASYNC_NETWORK_IO.您还可以检查聚合 sys.dm_os_wait_stats，使用 DBCC SQLPERF("sys.dm_os_wait_stats" , CLEAR) 重置它 然后运行查询，查看 ASYNC_NETWORK_IO 等待类型加起来的时间.当然，请确保测试期间服务器上没有其他活动.

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.

这篇关于SQL Server - Management Studio - 客户端统计 - 服务器回复的等待时间与客户端处理时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！