sys.dm_exec_query_optimizer_info“超时”记录表示什么？

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:

在调查某些客户端计算机失去与SQL Server 2005的连接时,我在Web上遇到了以下代码:

Select * FROM sys.dm_exec_query_optimizer_info WHERE counter = 'timeout'

选择* 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

超时 - 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.

据我所知,这意味着查询优化器在尝试优化针对我们的服务器运行的查询时超时 - 9100次。但是,我们没有在SQL Server错误日志中看到任何超时错误,并且我们的最终用户没有报告任何超时特定错误。

Can anyone tell me what this number of “occurrences” means? Is this an issue we should be concerned about?

谁能告诉我这些“事件”的含义是什么?这是一个我们应该关注的问题吗?

3 个解决方案

#1

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

SQL Server不会花费太多时间来尝试编译最好的计划(至少不使用跟踪标志)。

It calculates two values at the beginning of the optimisation process.

它在优化过程开始时计算两个值。

Cost of a good enough plan

一个足够好的计划的成本

Maximum time to spend on query optimisation (this is measured in number of transformation tasks carried out rather than clock time).

花在查询优化上的最长时间(这是根据执行的转换任务的数量而不是时钟时间来衡量的)。

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.

早期优化完成的原因显示在StatementOptmEarlyAbortReason属性的执行计划中。实际上有三个可能的值。

Good enough plan found

找到了足够好的计划

Timeout
Memory Limit Exceeded.

内存限制超出。

A timeout will increment the counter you ask about in sys.dm_exec_query_optimizer_info.

超时将增加您在sys.dm_exec_query_optimizer_info中询问的计数器。

Further Reading

Reason for Early Termination of Statement

提前终止陈述的原因

Microsoft SQL Server 2014 Query Tuning & Optimization

Microsoft SQL Server 2014查询调优和优化

#2

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

#3

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)

根据其他链接,我怀疑这是内部引擎超时(例如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.

客户端超时也不会记录在SQL中,因为客户端会中止批处理,从而停止SQL处理。

Please do you have more details?

请告诉你更多细节?

#1