Microsoft SQL Server 2014 Query Tuning & Optimization (Database & ERP - OMG)

Author: Benjamin Nevarez
This Month Stack Overflow 1


by anonymous   2017-08-20

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.

  1. Cost of a good enough plan
  2. 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.

  • Good enough plan found
  • Timeout
  • Memory Limit Exceeded.

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

Further Reading