In the last few months, I have had my SQL Developer hat on. And admittedly it is a bit difficult to move the needle when one can not just throw hardware at the problem.
I have a query and I am reviewing the Query Plan and noticed a warning indicating “No Join Predicate“.
It is a simple query:
declare @SalesPersonID int SELECT Invoice.OrderNumber , InvoiceDetail.ItemNumber FROM dbo.Invoice INNER JOIN dbo.InvoiceDetail ON Invoice.OrderNumber = InvoiceDetail.OrderNumber WHERE Invoice.SalesPersonID = @SalesPeronID ;
Query Plan – Problematic
I struggled with this all day. There is quite a bit of postings on the Internet. Here are some of the suggestions:
- Re-writing Query
- Rebuilding Indexes & Updating Statistics
Possible Fixes – Optimize for Hint
Add Optimize for Hint:
Add an Optimize for Hint – Indicating a value for the argument\variable.
declare @SalesPersonID int SELECT Invoice.OrderNumber , InvoiceDetail.ItemNumber FROM dbo.Invoice INNER JOIN dbo.InvoiceDetail ON Invoice.OrderNumber = InvoiceDetail.OrderNumber WHERE Invoice.SalesPersonID = @SalesPeronID OPTION (OPTIMIZE FOR (@SalesPersonID = 10191)); ;
Pasted below are the Query Plans which shows that with the introduction of Optimize for we are able to rid ourselves of “No Join Predicate“.
Microsoft Connect Items
Relevant Connect Items
- Missing join predicate – event behavior and description (Bug# 693321)
https://connect.microsoft.com/SQLServer/feedback/details/693321/missing-join-predicate-event-behavior-and-description( will be fixed MS SQL Server 2012 )
Find Similar Queries in Query Plans
Here is a Mat’s answer for digging into our Query Plans and finding queries suffering from “NoJoinPredicate“.
Find “No Join Predicates” in your Query Plans DMV
WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT st.[text] as [actualSQL], pl.query_plan, ps.execution_count, ps.last_execution_time, ps.last_elapsed_time, ps.last_logical_reads, ps.last_logical_writes FROM sys.dm_exec_query_stats ps with (NOLOCK) Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl WHERE pl.query_plan.value('(//Warnings/@NoJoinPredicate)', 'bit') = 1 Order By last_execution_time desc OPTION (RECOMPILE);
- No Join Predicate
- Nested Loops Join no join predicate
- What exactly does no join predicate mean in SQL Server
- Missing Join Predicate Recommendation
- SQL Server Using Optimize for Query Hint
- Implied Predicates and Query Hints