Clever Geek Handbook

What does the sys.dm_exec_query_optimizer_info "timeout" entry indicate?

While researching some client machines that have lost connectivity with SQL Server 2005, I came across the following line of code on the Internet:

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

When I run this query on our server, we get the following results:

counter - appearance - value

timeout - 9100 - 1

As far as I can tell, this means the query optimizer is disabled when trying to optimize queries running on our server - 9100 times. However, we do not see timeout errors in the SQL Server error log, and our end users do not report any timeout errors.

Can anyone tell me what the number of "occurrences" is? Is this a problem we should be worried about?

+1


source to share


3 answers


This counter has nothing to do with your connection problems.

SQL Server will not waste forever trying to compile the best possible plan ( at least without using trace flags ).

It calculates two values ​​at the beginning of the optimization process.

  • The cost of a good enough plan.
  • The maximum time to optimize queries (this is measured by the number of conversion tasks performed, not hours).

If a plan with a cost below the threshold is found, then it should not continue to optimize. Also, if it exceeds the number of scheduled tasks, the optimization will end as well and it will return the best plan found so far.



The reason the optimization finished earlier is displayed in the execution plan in the attribute StatementOptmEarlyAbortReason

. There are actually three possible meanings.

  • Good enough plan.
  • Time-out
  • Memory limit exceeded.

Timeout will increment the counter you are asking about c sys.dm_exec_query_optimizer_info

.

additional literature

+2


source


The occurrence column indicates the number of times the counter was incremented, and the value column is the internal column for that counter.



See here

0


source


Sorry, the documentation says this is only internal.

Based on another link, I suspect this refers to internal timeouts (for example SET QUERY_GOVERNOR_COST_LIMIT

)

The client timeout is also not logged in SQL because the client aborts the batch, stopping the SQL processing.

Do you have more details please?

0


source






More articles:


All Articles