sp_BlitzCache™ Result: Trivial Execution Plans
Trivial Plans occur when a query is so simple, there’s no reason for SQL Server to optimize the statement. Think of something like
SELECT * FROM dbo.Users WHERE UserId = 1. When UserID is the clustered key, there’s nothing to do apart from seek to that record and return the data.
Normally, this isn’t an issue. On a very busy SQL Server, seeing trivial plans as problems in the plan cache means that these queries are being run frequently or that a query has a large number of plans. No matter how you look at it, you shouldn’t be seeing queries with trivial plans in the top results of sp_BlitzCache™.
In some circumstances, trivial plans may prevent an optimal plan from being created. Trivial plans won’t be parallel, and don’t generate missing index requests.
How to Fix the Problem
You need to locate the queries using trivial plans and determine the source. Once you know the source of these queries, you’ll need to determine why they’re becoming a problem. This can include adding caching to your application, parameterizing the queries, or reducing the frequency of query execution.
You can find queries with trivial execution plans using the following query:
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT TOP 50 *
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE query_plan.exist('//p:StmtSimple[@StatementOptmLevel[.="TRIVIAL"]]/p:QueryPlan/p:ParameterList') = 1