Trivial Plans

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  st.text,
        qp.query_plan,
        qs.*
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    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

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)

SELECT st.text,

qp.query_plan,

qs.*

FROM (

SELECT TOP 50 *

FROM sys.dm_exec_query_stats

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