sys.dm_exec_query_optimizer_info“超时”是什么意思? 记录表明?
在调查某些客户端计算机与 SQL Server 2005 失去连接的过程中,我在网络上遇到了以下代码行:
Select * FROM sys.dm_exec_query_optimizer_info WHERE counter = 'timeout'
当我在服务器上运行此查询时 - 我们是得到以下结果:
计数器 - 出现 - 值
超时 - 9100 - 1
据我所知,这意味着查询优化器在尝试优化针对我们的服务器运行的查询时超时 - 9100 次。 但是,我们在 SQL Server 错误日志中没有看到任何超时错误,并且我们的最终用户也没有报告任何特定于超时的错误。
谁能告诉我这个“出现次数”意味着什么? 这是我们应该关心的问题吗?
During an investigation of some client machines losing their connection with SQL Server 2005, I ran into the following line of code on the web:
Select * FROM sys.dm_exec_query_optimizer_info WHERE counter = 'timeout'
When I run this query on our server - we are getting the following results:
counter - occurrence - value
timeout - 9100 - 1
As far as I can determine, this means that the query optimizer is timing out while trying to optimize queries run against our server – 9100 times. We are however, not seeing any timeout errors in the SQL Server error log, and our end-users have not reported any timeout specific errors.
Can anyone tell me what this number of “occurrences” means? Is this an issue we should be concerned about?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
该计数器与您的连接问题无关。
SQL Server 不会永远尝试编译最佳计划(至少不使用跟踪标志)。
它在优化过程开始时计算两个值。
如果发现成本低于阈值的计划,则无需继续优化。 此外,如果超出预算的任务数量,优化也将结束,并将返回迄今为止找到的最佳计划。
优化提前完成的原因显示在执行计划的
StatementOptmEarlyAbortReason
属性中。 实际上存在三个可能的值。超时会增加您在 sys.dm_exec_query_optimizer_info 中询问的计数器。
进一步阅读
This counter is nothing to do with your connection issues.
SQL Server won't spend forever trying to compile the best possible plan (at least without using trace flags).
It calculates two values at the beginning of the optimisation process.
If a plan with a cost lower than the threshold is found then it needn't continue optimising. Also if it exceeds the number of tasks budgeted then optimisation will also end and it will return the best plan found so far.
The reason that optimisation finished early shows up in the execution plan in the
StatementOptmEarlyAbortReason
attribute. There are actually three possible values.A timeout will increment the counter you ask about in
sys.dm_exec_query_optimizer_info
.Further Reading
出现列将告诉您计数器已递增的次数,而值列是该计数器的内部列。
参见此处
The occurence column will tell you the number of times that counter has been incremented and the value column is an internal column for this counter.
See here
抱歉,文档说这仅供内部使用。
根据另一个链接,我怀疑这是内部引擎超时(例如
SET QUERY_GOVERNOR_COST_LIMIT
)客户端超时也不会记录在 SQL 中,因为客户端中止批处理,从而停止 SQL 处理。
请问您有更多详细信息吗?
Sorry, the documentation say this is internal only.
Based on the other link, I suspect this is for internal engine timeouts (eg
SET QUERY_GOVERNOR_COST_LIMIT
)A client timeout will also not be logged in SQL because the client aborts the batch, ths stopping SQL processing.
Please do you have more details?