Lately I’ve heard a few people over-react (in my opinion) to a particular message that can show up in a SQL Server query plan on rare occasions. As an example, consider this query against AdventureWorks2014:

SELECT p.Name, soh.ShipDate, sp.Name StateName,
	SUM((sod.OrderQty * sod.UnitPrice) *
		(1.0 - sod.UnitPriceDiscount))
		over (partition by p.Name, sp.Name) Price,
	th.MostRecentTransactionDate
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail sod
	on sod.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeader soh
	on soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Person.Address addr
	on addr.AddressID = soh.ShipToAddressID
INNER JOIN Person.StateProvince sp
	on sp.StateProvinceID = addr.StateProvinceID
INNER JOIN
(
	SELECT MAX(tha.TransactionDate) MostRecentTransactionDate,
		tha.ProductID 
	FROM Production.TransactionHistoryArchive AS tha 
	GROUP BY tha.ProductID
) th on p.ProductID = th.ProductID
WHERE NOT EXISTS
    (SELECT *
     FROM Production.ProductModel AS pm 
     WHERE p.ProductModelID = pm.ProductModelID
           AND pm.Name LIKE N'%Long%')
OPTION (RECOMPILE);

If we get a query plan on this and look at the properties on the root node, we will see something like this.

Note the “Reason for Early Termination of Statement Optimization” is Time Out. The equivalent in the XML is this snippet (abbreviated for conciseness).

<StmtSimple StatementOptmLevel="FULL"
QueryHash="0xCF50B953FE93513A"
QueryPlanHash="0x80E16631F5292DFD"
StatementOptmEarlyAbortReason="TimeOut"
CardinalityEstimationModelVersion="120">

The comment I’ve heard several times is something to effect that, “Look! SQL Server couldn’t even finish compiling the query! It timed out!”

Here’s the thing. The fact that it timed out optimizing the query is a good thing. As queries become more and more complex, the search space for the optimizer to explore becomes larger at an exponential level. The optimizer will consider various join orders, which indexes may be useful to resolve the query, which of the army of transformation rules at its disposal are applicable and beneficial, parallelization options, and so forth. The number of permutations of query plans at SQL Server’s disposal can quickly become literally astronomical.

If the optimizer were to evaluate every single possibility, the compilation time could easily progress into minutes, hours, even into years at some point. Part of the art of query optimization is knowing when to quit — when the time spent evaluating the myriad of possibilities overwhelms the time to run the query. When this happens, the optimizer will gracefully time out and output its best option so far.

Of course it’s not always perfect — nothing ever is. But it’s usually pretty good.

Yes, the presence of a timeout can be an indication that the query is too complex and that simplification may be in order. But the timeout itself isn’t such a big deal.

It’s really a good thing.