No Join Predicate

sp_BlitzCache™ Finding: No Join Predicate

Top poorly performing queries are throwing warnings that there are operators without join predicates. That means somewhere in your query, you’ve logically written a CROSS JOIN without thinking about it. Maybe you’re using an old style join syntax (SELECT * FROM a, b, c) or maybe you really did intend to write this code. Either way, a lot of rows can be produced as a result of this operation. It’s best to make sure this was your intent.

How to Fix the Problem

Re-write your query so you are always applying a join predicate.

You can find the offending queries using the following T-SQL:

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  st.text,
        qp.query_plan
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
CROSS APPLY qp.query_plan.nodes('//p:RelOp/p:Warnings[(@NoJoinPredicate[.="1"])]') AS q(n) ;

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

SELECT st.text,

qp.query_plan

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

CROSS APPLY qp.query_plan.nodes('//p:RelOp/p:Warnings[(@NoJoinPredicate[.="1"])]') AS q(n) ;