The query looks slow, but the plan offers no missing-index suggestion. A trivial plan is one reason SQL Server can skip the broader optimization search. Read the optimization level before treating the absence of a suggestion as proof that the current access path is ideal.
Separate Planning Effort From Execution Work
The optimizer does not spend the same effort on every statement. A simple query with an obvious acceptable plan can qualify for trivial optimization. More involved statements enter broader cost-based exploration. TRIVIAL and FULL describe those planning paths, not the duration or IO of execution.
I check optimization level when a plan seems surprisingly sparse. The label explains why certain optimizer-generated information is absent. It does not tell me whether the query returns the correct rows or reads an appropriate amount of data. Those questions still need the plan and execution evidence.
A trivial query can read a large table if the obvious access path is a scan. Simplicity of syntax does not make the data small. Conversely, full optimization can choose an excellent plan for a complicated statement. The optimizer's planning budget and the workload's actual cost are related, but they are not interchangeable measurements.
Build a Small Trivial Plan You Can Inspect
Run the following setup in a disposable database. The example has a simple table and a narrow lookup. Enable the actual execution plan in SSMS, then run the SELECT. Open the SELECT operator's properties and look at Optimization Level. The plan XML exposes the same value as StatementOptmLevel.
In my test on SQL Server 2025 this SELECT showed TRIVIAL, and the join in the later block showed FULL. The example is a candidate for the shortcut, not a demand that SQL Server must produce TRIVIAL on every build and configuration. Statistics, indexes, settings, and optimizer behavior affect eligibility. If the result is FULL, inspect the actual output rather than pretending the label is different.
Use the sample to learn where the property lives. Then examine the real slow statement. A reproduction with a tiny temporary structure does not automatically explain the production plan. I keep the exact query text and parameter values beside the plan so the planning context remains visible during the review.
CREATE TABLE dbo.TrivialDemo(ID int NOT NULL PRIMARY KEY,ValueText varchar(40) NULL);
INSERT dbo.TrivialDemo VALUES(1,'First'),(2,'Second');
SELECT ValueText FROM dbo.TrivialDemo WHERE ID=1;Read the Cached Optimization Level With XML
sys.dm_exec_query_plan returns XML for cached plans where available. The XML namespace must be declared before querying Showplan elements. The following query extracts optimization level and statement text from StmtSimple nodes. It keeps the result bounded to make a manual review practical.
The DMV requires appropriate server permissions. Cached entries can disappear between reads, and some plans are unavailable or too deeply nested for this XML function. Missing output does not establish that a statement never executed. Query Store or a captured actual plan can provide another source of evidence.
The extracted value belongs to a statement node, not necessarily the entire batch. One batch can contain several statements with different optimization levels. Do not label a stored procedure trivial because one SELECT inside it has that value. Read the relevant node and its text, then compare it with the request under investigation.
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (50) cp.objtype,
n.s.value('(@StatementOptmLevel)[1]','nvarchar(20)') AS OptimizationLevel,
n.s.value('(@StatementText)[1]','nvarchar(4000)') AS StatementText
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS n(s)
WHERE qp.query_plan IS NOT NULL;
Compare a Statement That Requires More Choices
A join gives the optimizer additional choices about access paths and join strategy. Run the next setup and SELECT in the same test database. Inspect the optimization level again. Compare the complete plans rather than looking only for one new icon.
Adding an extra predicate or join can move a statement to full optimization, but only when the resulting query still requires those choices after simplification. A redundant join that the optimizer removes does not reliably create the intended comparison. The second table here supplies a real matching value.
What changed in the decision space? SQL Server now has to combine two inputs and choose their access and join behavior. That explains the demonstration. It does not justify adding a pointless join to a production query merely to provoke FULL. Query shape should express the required result. Optimizer behavior is evidence to understand, not a label to collect.
CREATE TABLE dbo.TrivialLookup(ID int NOT NULL PRIMARY KEY,Category varchar(20));
INSERT dbo.TrivialLookup VALUES(1,'Open'),(2,'Closed');
SELECT d.ValueText,l.Category
FROM dbo.TrivialDemo AS d
JOIN dbo.TrivialLookup AS l ON l.ID=d.ID
WHERE l.Category='Open';Know What a Trivial Plan Leaves Out
Trivial optimization skips much of the cost-based search. Missing-index requests are associated with that broader work, so their absence in this planning path is unsurprising. Such plans also do not use the normal full optimization path for considering parallel alternatives.
A missing-index suggestion is advisory even when it appears. It does not account for every write cost or existing overlapping index. Its absence is equally limited. Review predicates, estimated and actual rows, logical reads, and the table's current indexes directly.
Do not use an undocumented setting to force trivial optimization. There is no supported request that tells SQL Server to produce this shortcut directly for your statement. You can inspect its eligibility and change the legitimate query or schema design, but the optimizer chooses the planning path. A simple statement usually benefits from avoiding unnecessary compilation effort.
Tune the Access Path That Actually Matters
For the real query, capture STATISTICS IO and an actual execution plan. Check whether the predicate can use an existing index, whether a conversion changes that predicate, and whether the returned columns require expensive lookups. Fix the concrete access problem instead of chasing the optimization label.
Retest with representative parameters and enough data to expose the workload pattern. Keep the result set unchanged when comparing alternatives. A faster query that drops required rows is not a tuning result. Also include compilation cost when an alternative depends on repeated recompilation.
Clean up the demonstration tables after the experiment in the disposable database. Preserve the captured plans and the reason for any production index or query change. A trivial plan explains how SQL Server approached planning. Your own measured execution evidence explains whether the selected path serves the workload well.
Related reading on this blog: Execution Plans and Indexing Strategies: Quick Guide and Is Query from Cache? Execution Plan Property.
A trivial plan is not a performance verdict, it is a planning shortcut for a simple statement.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
