Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate".

But unfortunately it does not seem to be as easy as that.

For example, very simple situation:

create table #temp1(i int);
create table #temp2(i int);
Select * from #temp1, #temp2 option (recompile);

There is no data in the tables, there is no warning either, although it obviously has no join predicate.

If I take a look at documentation for SQL Server 2005 (the same link, just other server version), there is an extra sentence: "This event is produced only if both sides of the join return more than one row." This would make perfect sense in the previous situation. There is no data, so both sides returns 0 rows and no warning. Insert rows, get warning. Ok, cool.

But for the next confusing situation, I insert the same values in both tables:

Insert into #temp1 (i) values (1)
Insert into #temp1 (i) values (1)
Insert into #temp2 (i) values (1)
Insert into #temp2 (i) values (1)

And I get:

-- no warning:
Select * from #temp1 t1 
    inner join #temp2 t2 on t1.i = t2.i 
option (recompile)
-- has warning:
Select * from #temp1 t1 
    inner join (select 1 i union all select 1) t2 on t1.i = t2.i 
option (recompile)

Why is this so?

Note: some scripts I used to detect these bad queries on my server.

  1. of course, execution plan of procedures
  2. used default server trace to find warnings

    Declare @trace nvarchar(500);
    Select @trace = cast(value as nvarchar(500))
    From sys.fn_trace_getinfo(Null)
    Where traceid = 1 and property = 2;
    
    Select t.StartTime, te.name, *
    From sys.fn_trace_gettable(@trace, 1) t
        Inner join sys.trace_events te on t.EventClass = te.trace_event_id
        where EventClass = 80
    order by t.StartTime desc
    
  3. execution plan cache, to find those plans with warnings (like this one)

    WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT
        Cast('<?SQL ' + st.text + ' ?>' as xml) sql_text,
        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)[1]', 'bit') = 1
    Order By last_execution_time desc
    OPTION (RECOMPILE);
    
share|improve this question
3  
The reason the recent documentation does not mention the "more than one row" text is that the behaviour is being changed and the description was not always correct anyway. –  Paul White Feb 20 '13 at 12:41
    
@PaulWhite So there is possible to get situation, when I have one-to-many relationship and I get this warning? –  Jānis Feb 21 '13 at 6:55

1 Answer 1

up vote 8 down vote accepted

Your question is similar to this one. Sometimes SQL Server can remove a join predicate from the original query.

In the case where you see a join predicate warning SQL Server detects at compile time that the table of constants only has one distinct value and that value is 1 so rewrites the query as

SELECT *
FROM   (SELECT *
        FROM   #temp1 t1
        WHERE  t1.i = 1) t1
       CROSS JOIN (SELECT 1 i
                   UNION ALL
                   SELECT 1) t2 

There is a predicate on the table scan of #temp as follows [tempdb].[dbo].[#temp1].[i] =(1)

The join predicate of on t1.i = t2.i can't be removed in this way at compile time when using two tables or if the table of constants contains more than one distinct value.

share|improve this answer
1  
+1 Though some people are confused by the idea of SQL Server rewriting queries in SQL. It does not do that, it transforms the logical query tree, of which the SQL shown here might be a representation. –  Paul White Feb 20 '13 at 12:44
1  
@PaulWhite - Good clarification, and of course more info about this can be found in your Query Optimizer Deep Dive series. –  Martin Smith Feb 20 '13 at 12:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.