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 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
CROSS APPLY qp.query_plan.nodes('//p:RelOp/p:Warnings[(@NoJoinPredicate[.="1"])]') AS q(n) ;