Optimizer Effort

Overview

The optimizer works to a budget. When it runs out it ships whatever plan it had reached, and it writes down that it did so.

That note is StatementOptmEarlyAbortReason, and it has been in every execution plan SQL Server has produced since 2008. A statement whose plan search was abandoned is a statement where no amount of index tuning is the answer, because the indexes were never going to be examined any harder on the next compile either. There is no other signal anywhere that this has happened.

The Optimizer Effort report: compiling time and running time on one center axis
The center line is zero. Everything to the left of it is time this instance spends choosing a plan; everything to the right is time spent running it. Equal wings mean a statement spends half its life being planned.

The second half of the page is the cost of that work. CompileTime, CompileCPU and CompileMemory are in the plan document too, and nothing in this product or any other has ever added them up. A statement that takes 900 milliseconds to compile and recompiles on every call is spending most of its life in the optimizer, and that is a completely different problem from a slow execution wearing the same clothes.

Where to find it

In the tree, right-click an instance and choose Instance Level Reports → Optimizer Effort. It is a recommended report and appears in the Server Overview list.

Requirements

Requirement Why
SQL Server 2012 or newer The attributes the page reads have been in showplan since 2008, but the plan cache sweep that reads them is not supported on SQL Server 2008 or 2008 R2.
VIEW SERVER STATE For the plan cache reads.
Database compatibility level 110 or newer Lets the whole cache be shredded in one statement. Below it the page reads the plans one at a time instead, and says so.

The version gate is on the instance build and the compatibility level requirement is not. They are different questions, and both are asked. A SQL Server 2019 instance with one database left at compatibility level 100 passes the first and fails the second, so the page runs and reads the plans one at a time. An instance older than 2012 does not get the page at all:

The Optimizer Effort report requires SQL Server 2012 or newer.

The two views

There are three findings and two views, and the reason they do not match is the argument of the page.

A trivial plan is not an abort, and SQL Server does not call it one. It is, though, the other place the optimizer stops before it has finished: a trivial plan is matched and shipped before the cost based search begins. Filed on its own tab it looks like a curiosity. Filed beside the timeouts it is the third value of one column, under a heading that is true of all three.

Stopped early

Every statement where the optimizer did not finish looking.

Stopped because What it means
TimeOut The search hit its stage budget. Usually a very large number of joins, a deeply nested view, or a table valued function chain. The fix is structural.
MemoryLimitExceeded The search ran out of memory to compile in. Rarer, worse, and the compile side of memory pressure rather than the execution side.
Trivial plan A trivial plan above cost 5. Not a problem in itself, and a problem in one specific way: see below.

The last column of this view is written for the person who has to act. TimeOut is a fact; your index will not be looked at any harder next time is the reason the fact matters.

Compile cost

Every statement ranked by what the optimizer spends on it per day, whether its search finished or not.

This is the view that finds the statement running perfectly well and spending half an hour a day being planned. It appears in no CPU by query report anywhere, because compilation time is not execution time and nothing else adds the two together.

Trivial plans never ask for an index

This is the one thing on the page that is worth reading even on a healthy instance.

A trivial plan never generates a missing index request. Missing index requests are produced during the cost based search, and a trivial plan is shipped before that search begins. So a genuinely expensive trivial statement is invisible to every missing index report in this product, in sys.dm_db_missing_index_details, and in every other tool.

The report treats a trivial plan as a finding only above estimated cost 5. That number is not picked to look decisive: it is the default cost threshold for parallelism, which is the line SQL Server itself has used since 1995 to separate a query worth extra effort from one that is not.

Reading the chart

One row per statement, ranked by the whole of its day. Compiling runs left from the center, running runs right, and both wings of every row are measured against one scale shared across the whole chart. Nothing is normalized, and the ticks run outward from zero in both directions.

Color Meaning
Amber wing Time spent compiling
Gray wing Time spent running
Amber stripe The search timed out
Red stripe The search ran out of compile memory
Blue stripe A trivial plan above the cost floor
Green stripe The search finished

The running wing is deliberately gray. It is the length the finding is measured against rather than a finding of its own, and a color there would make every row look like a verdict on two things when it is a verdict on one.

The chart is the top of the workload; the grid is the finding list. They are different lists on purpose. A statement that stopped early but compiles cheaply has a wing one pixel wide and can be the most alarming row on the page while being nowhere near the top of the chart. Clicking a chart row selects the matching grid row rather than filtering, because a filter would have to mean two different things across two views.

What to do about a row

Click any row. A details pane opens down the right-hand edge of the window and stays open, so you can arrow down the grid and read one statement after another without dismissing anything. It does not open on the page arriving, only when you pick a row, because the chart above needs the width until you have asked a question. Closing it puts it away until the next row you click.

The same pane is on the right-click menu as How to resolve this, and on the right-click menu of a bar in the chart. On a row that is not a finding it reads “Explain this row” instead, because a page that offers to resolve something that is working teaches you to distrust it.

Action What it does
Single click a row Opens the pane on that statement
Double click a row, or press Enter Opens the statement in the Query Advisor
Right-click → Show the execution plan Opens the cached plan in the Plan Viewer

Double clicking gives you the whole statement, not the 90 characters the grid has room for and not the 400 the page keeps in memory. It is fetched from the instance at the moment you ask, and the plan comes with it, so the Plan Analysis button inside the Query Advisor is live rather than hidden.

Show the execution plan is the plan for the statement, not for the batch it sits in. This page already knows where the statement starts and ends inside its batch, and hands those offsets to sys.dm_exec_text_query_plan, so what opens is the plan for the row you clicked. The item is absent on a row whose plan handle could not be read. Where the plan has left the cache since the page was read, you get a message saying so rather than an empty window; refresh the report to work against what is cached now.

The pane opens with the row’s own numbers, not with a paragraph about optimizers. Above everything else is the statement, the finding as a colored chip in the same color the chart drew that row in, and a compiling against running strip: two figures on one shared scale, so a compile bar longer than the run bar means exactly what it looks like. Under that:

Section What it is for
Why it matters What the abort or the compile count actually means for this statement.
How to resolve it Numbered steps in the order they are worth trying, not the order they are easy.
What will not help The wrong instinct, named.
How to tell it worked What should change on this page after the fix.

“What will not help” is the section to read first, and it is tinted so you cannot skim past it. On this page almost everybody arrives with a wrong instinct already formed – add an index, add memory, update statistics – and a list of only the right answers leaves it standing. On a timed out search the pane says plainly that an index will not be examined any more thoroughly next time, and that updating statistics cannot help because the search was cut short before the estimates mattered.

Where a small, cheap query shows the instance level version of the same problem, it is at the bottom of the pane in fixed pitch with a Copy the query button beside it: the compile memory topic gives you the RESOURCE_SEMAPHORE_QUERY_COMPILE wait, and the recompile topic gives you the compilations against re-compilations counters. Where the answer would be another plan cache sweep, the pane says to turn this report’s scan budget up rather than handing you an unbounded shred to run against production, and there is no button to copy, because there is nothing to run.

The statement itself is the last section of the pane. Show the statement and Show the plan open the same two dialogs the grid’s double click and right-click menu do, and Copy the statement puts the whole statement on the clipboard rather than the 400 characters the page is holding.

Copy for a ticket

The pane’s other button hands you the whole thing as one block of T-SQL comment, with the row’s numbers, every section, and the statement underneath. That is the form to paste into a query window beside the code you are about to change, or into a ticket, because every line of it is something the parser will accept.

That block is what this pane used to be on screen. It reads well in a query window and it read badly as a window of its own, so it moved to the clipboard where its argument was always right.

A statement can be two things at once. Where a search was abandoned and the plan is recompiling far more than it should, the pane leads with the abort and appends the recompile advice underneath, because fixing the abort usually takes the compile time down with it. Both show as chips in the header, so you can see it before you scroll.

Elapsed time, not worker time

This is the one number on the page that had to be chosen carefully.

The running wing is total_elapsed_time, not total_worker_time. Worker time on a parallel plan is the sum across its threads and exceeds wall clock by roughly the degree of parallelism, so a parallel statement drawn that way would show a running wing eight times too long and make its own compile time look free.

A compile is wall clock. What it is comparable to is wall clock.

Compiles per day, not executions per day

Compiles per day comes from plan_generation_num, which counts how many times the cached plan has been compiled, not how often it has run. They answer different questions, and only this one turns 880 milliseconds of compile time into half an hour a day.

A statement that executes a million times and compiles once is not this report’s subject, and it draws no left wing at all.

The figure understates rather than overstates. A plan evicted from the cache and compiled again from scratch comes back as a new plan with the count reset, so anything recompiling through eviction rather than through invalidation is invisible here. The footer says so.

Both per day figures are clamped for a plan younger than an hour. Without the clamp, a plan created two minutes ago and compiled once reports seven hundred compiles a day and goes straight to the top of the report.

What the sweep reads, and what it costs

Source What it gives
StmtSimple/@StatementOptmLevel TRIVIAL or FULL.
StmtSimple/@StatementOptmEarlyAbortReason TimeOut, MemoryLimitExceeded or GoodEnoughPlanFound.
QueryPlan/@CompileTime Milliseconds for one compile.
QueryPlan/@CompileCPU Processor milliseconds for one compile.
QueryPlan/@CompileMemory Kilobytes. The evidence behind a memory limit row.
sys.dm_exec_query_stats Executions, plan_generation_num, elapsed time, plan age.
sys.dm_exec_text_query_plan The statement’s own plan document, by offset.
sys.dm_exec_sql_text Database, object and statement text.

Why this sweep is cheap

It binds the StmtSimple element once and reads six attributes off it and off the QueryPlan child it already has in hand. Six separate searches would be six walks of every plan in the budget for attributes that all hang off one element.

The element is found by its absolute path, /ShowPlanXML/BatchSequence/Batch/Statements/ StmtSimple, rather than by a // search. A descendant search from the root walks the whole document, and the documents this page cares about most are the enormous ones: a statement with thirty joins in it that timed out has a plan document to match.

Binding once also means the six of them are guaranteed to come from one statement. On a document carrying more than one QueryPlan – an IF, where the condition and each branch have their own – six separate searches could take the compile time from one and the abort reason from another, and the row would be a fact about no statement that exists.

TRY_CONVERT to xml rather than CONVERT, so a plan nested deeper than the 128 levels the xml type allows comes back NULL instead of ending the batch.

The rows are not ordered by the server. An ordered result cannot be sent until all of it exists, so ordering meant every plan in the budget was converted and shredded before the first row reached the screen. Both views and the chart sort on the client anyway, against three different measures.

Three rankings, one budget

Ranking on any single measure misses one of the two views. A statement that compiles for three seconds and runs six times a night is invisible to an execution count ranking, and it is exactly the memory limit row this page exists to find.

Ranking What it finds
By execution count The busiest, where a compile on every call becomes hours a day.
By plan_generation_num The most recompiled, which no other ranking finds.
By total_elapsed_time The longest running, where an abandoned search usually shows up.

Each takes a third of the budget, so “the top 1,000 plans” stays literally true rather than quietly becoming three thousand.

All three come out of one pass over the plan cache, as window functions with an OR between their ranks, and two more window aggregates carry the cache wide totals out with them. Scanning sys.dm_exec_query_stats is not like scanning a table: it walks the plan cache, and doing it once rather than four times is most of what the page can save before it has read a single plan document.

The scan budget

The toolbar offers 250, 500, 1000 and 2500 plans, and the choice is remembered. The subtitle always says how many plans were read, out of how many are in the cache, and what share of cached executions that is.

This is the setting to reach for if the page feels slow. Converting a plan document to XML is the irreducible cost here, and it is paid once per plan in the budget, so halving the budget halves the load. 250 is enough to find the findings on most instances; the larger budgets are for when the subtitle says the share of cached executions read is too small to trust.

The budget is on the page rather than hidden. Silently truncating at a thousand plans and presenting the result as a clean bill of health is what every published version of this kind of script does.

GoodEnoughPlanFound is not a finding

It is by far the most common value the attribute takes, and it means the optimizer found a plan it was satisfied with. That is the system working.

It is counted in the subtitle so a reader can see the sweep found it and chose not to shout, and it appears in neither view. A page that listed it would bury the twenty rows that matter under several thousand that do not.

Messages you may see

N statements ran out of memory to compile in rather than to run in. The compile side of memory pressure. Neither Memory Grants and Spills nor Memory can see it.

N statements spend more of the day being planned than being run. None of them appear in any CPU by query report, because compilation time is not execution time.

N of the plans read carried no optimization level. A statement that is not a StmtSimple – an IF or a WHILE, which carries no statement level optimization information because the optimizer never optimized it as a statement – a module created WITH ENCRYPTION, a plan nested past the 128 levels the xml type allows, or one that left the cache mid read.

This connection landed in a database at compatibility level 100. TRY_CONVERT is refused below level 110 even on a build that has it, so the plans were shredded one at a time. The page is the same and the read is slower.

Every search the optimizer started, it finished. Good news, and the honest empty state. Nothing timed out, ran out of compile memory, or shipped a trivial plan above cost 5.

Nothing here runs anything

Every finding on this page is a structural rewrite of somebody’s query, and which rewrite is right depends on the application rather than on the database. There is no fix button and there is no configuration script.

Copy query produces the two batches behind the page, so they can be run somewhere this application is not installed.

Report Why you would go there
Plan Warnings The same sweep asked the other question: what went wrong with the plan the optimizer did choose.
Parallelism Calibration The other page built on the estimated cost of a cached plan.
Plan Cache What the cache is made of, and how much of it is compiled once and never reused.
CPU by Query Execution cost, which is the half of a statement’s day this page does not measure.
Memory Grants and Spills The execution side of memory pressure, where a memory limit abort sends you next.

Frequently asked questions

The page takes a while to load. Turn the scan budget down to 250 in the toolbar. Converting a cached plan to XML on the server is the cost of this report and it is paid once per plan in the budget, so the budget is the lever. The subtitle tells you what you gave up: if the share of cached executions read is still high, 250 was enough. A very large plan cache also costs a pass to rank, and an instance carrying thousands of enormous plan documents is the slow case.

Nothing stopped early. Is the report working? Yes, and that is good news. Look at the Compile cost view: a healthy instance can still be spending hours a day compiling.

Why is the chart full of rows with no findings? Because the chart is a picture of where the instance’s time goes, and the findings are stripes within it. A chart of the findings alone would draw an empty panel on a healthy instance, which is the moment a reader most needs to see that the page worked.

A statement times out. Should I add an index? No, and this is the whole point of the report. The index would have to be considered by a search that was abandoned before it got there, and it will be abandoned again at the same place next time. Simplify the statement, break it into steps with temporary tables, or unpick the nested views.

What actually causes a MemoryLimitExceeded? An enormous statement being compiled while the instance is under memory pressure. The compile memory column is the evidence. It is a different problem from a query that spills at runtime.

Why does my statement compile every time it is called? A temporary table it creates, a SET option that varies between callers, OPTION(RECOMPILE), or statistics being updated underneath it. The page says when a statement compiles almost as often as it runs.

The compile times all read as 0 ms. Trivial plans compile in microseconds and the plan document rounds. A cache of nothing but singleton lookups genuinely has no compile cost worth reporting.

Why does the cost column disagree with Parallelism Calibration? It should not. Both read the root operator’s EstimatedTotalSubtreeCost off the same plan document. If the two pages read different plans for the same statement, one of them was refreshed after the plan was recompiled.