Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 138

, 'trivial plan'
, 'search 0'
, 'search 1'
, 'search 2'
)
order by [counter]
Once the query has been optimized and cached, subsequent runs will not generate any updates to
the Query Optimizer stats unless you l ush the procedure cache using dbcc freeproccache .
On the machine I am using, the following results were returned from this query against the Query
Optimizer information before I ran the sample query:
COUNTER OCCURRENCE VALUE
optimizations 10059 1
search 0 1017 1
search 1 3385 1
search 2 1 1
trivial plan 5656 1
Here are the results after I ran the sample query:
COUNTER OCCURRENCE VALUE
optimizations 10061 1
search 0 1017 1
search 1 3387 1
search 2 1 1
trivial plan 5656 1
From this you can see that the trivial plan count didn't increment, but the search 1 count did
increment, indicating that this query needed to move onto phase 1 of the optimization process
before an acceptable plan was found.
If you want to play around with this query to see what a truly trivial plan would be, try running the
following:
select lastname
from person.person
The following T-SQL demonstrates what the same plan looks like in text mode:
set statistics profile on
go
select firstname, COUNT (*)
from Person.Person
group by firstname
order by 2 desc
go
set statistics profile off
go