高“total_worker_time”对于在 SQL Server 2005 中使用 OPENQUERY 的存储过程
[从数据库管理员站点交叉发布，希望它可以在这里获得更好的关注。我将根据需要更新任一站点。]
我在 SQL Server 2005 (SP2) 中有一个存储过程，其中包含如下所示的单个查询（为清楚起见进行了简化）
SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ''Y'' ')
OPTION (MAXDOP 1)
当此过程运行时（并且仅运行一次）我可以看到该计划出现在 sys.dm_exec_query_stats 中，具有较高的“total_worker_time”值（例如 34762.196 毫秒）。这接近经过的时间。然而，在 SQL Management Studio 中，统计数据显示 CPU 时间要低得多，正如我所期望的那样（例如 6828 毫秒）。由于与之通信的服务器速度缓慢，查询需要一些时间才能返回，但它不会返回很多行。
我知道 SQL Server 2005 中的并行查询可能会出现奇怪的 CPU 时间，这就是为什么我尝试使用查询提示关闭任何并行性（尽管我真的不认为任何并行查询中都有任何并行性）案件）。
我不知道如何解释查看 CPU 使用率的两种方法可能不同的事实，也不知道这两种方法中哪一种可能是准确的（我有其他原因认为 CPU 使用率可能是更高的数字，但它是很难测量）。有人可以给我指导吗？
更新：我假设问题出在 OPENQUERY 上，所以我有时尝试查找不使用 OPENQUERY 的长时间运行的查询。在这种情况下，统计数据（通过设置 STATISTICS TIME ON 获得）报告的 CPU 时间为 3315 毫秒，而 DMV 给出的时间为 0.511 毫秒。每种情况下的总经过时间一致。
[Cross posted from the Database Administrators site, in the hope that it may gain better traction here. I'll update either site as appropriate.]
I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity)
SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ''Y'' ')
OPTION (MAXDOP 1)
When this proc is run (and run only once) I can see the plan appear in sys.dm_exec_query_stats with a high 'total_worker_time' value (eg. 34762.196 ms). This is close to the elapsed time. However, in SQL Management Studio the statistics show a much lower CPU time, as I'd expect (eg. 6828 ms). The query takes some time to return, because of the slowness of the server it is talking to, but it doesn't return many rows.
I'm aware of the issue that parallel queries in SQL Server 2005 can present odd CPU times, which is why I've tried to turn off any parallism with the query hint (though I really don't think that there was any in any case).
I don't know how to account for the fact that the two ways of looking at CPU usage can differ, nor which of the two might be accurate (I have other reasons for thinking that the CPU usage may be the higher number, but it's tricky to measure). Can anyone give me a steer?
UPDATE: I was assuming that the problem was with the OPENQUERY so I tried looking at times for a long-running query which doesn't use OPENQUERY. In this case the statistics (gained by setting STATISTICS TIME ON) reported the CPU time at 3315ms, whereas the DMV gave it at 0.511ms. The total elapsed times in each case agreed.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
sys.dm_exec_query_stats中的total_worker_time是累积的 - 它是当前编译的查询版本的所有执行的总执行时间 - 请参阅execution_count代表执行的次数。有关各个执行的计时，请参阅
last_worker_time、min_worker_time或max_worker_time。参考： http://msdn.microsoft.com/en-us/library/ms189741 .aspx
total_worker_timeinsys.dm_exec_query_statsis cumulative - it is the total execution time for all the executions of the currently compiled version of the query - seeexecution_countfor the number of executions this represents.See
last_worker_time,min_worker_timeormax_worker_timefor timing of individual executions.reference: http://msdn.microsoft.com/en-us/library/ms189741.aspx