SQL Server Performance

EventClass 80 - Missing Join Predicate

Discussion in 'Performance Tuning for DBAs' started by bbRichbb, Feb 7, 2008.

  1. bbRichbb New Member

    Hi all.
    I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass 80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen during very high traffic. I have isolated the query, included at the bottom of this post (cleaned up). There is very little info on this event class out on the web.
    Issues:
    1. Even though only the value of product_id in the HAVING clause changes, I do not always get the MJP. I would expect that a query without a JP is a query without a JP and it would be all-or-none.
    2. Although it happens maybe 20-30 % of the time in production, I can’t make it happen in testing.
    Questions:
    Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed light? Know of good links, etc?
    Thanks!!
    bbRichbb
    SELECT
    p.Product_Id,
    MIN(ae.Enum_Value) AS color,
    p.Product_Name,
    p.Status_Code,
    ps.Curr_Price,
    s.Section_Id,
    COUNT(ps.SWATCH_STATUS) AS total_available_colors
    FROM
    Attribute_Enum_Value ae
    INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id
    AND ae.Attribute_Type_Id = pae.Attribute_Type_Id
    INNER JOIN Product p
    INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id
    INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id
    INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr
    AND p.Product_Id = ps.PRODUCT_ID
    WHERE
    (pae.Attribute_Type_Id = 500001)
    AND (p.Product_Class_Id = 2)
    AND (p.Status_Code = 'ACTV')
    AND (ps.SWATCH_STATUS = 'ACTV')
    GROUP BY
    p.Sequence_Number,
    p.Product_Id,
    p.Product_Name,
    p.Status_Code,
    ps.Curr_Price,
    s.Section_Id
    HAVING
    (p.Product_Id = 1209645)
    ORDER BY
    p.Sequence_Number,
    p.Product_Id
  2. satya Moderator

    What is the service pack level on SQL ServeR?
    A query that contains a combination of inner and outer joins might result in a NO JOIN PREDICATE warning in the SHOWPLAN_ALL or STATISTICS PROFILE output or the corresponding "Missing Join Predicate" warning in SQL Profiler.
    The problem occurs when the optimizer reorders an OUTER JOIN above an inner join when there is no direct or inferred join condition between the two tables in the resultant inner join. This may cause the query to run slower than usual, with a corresponding increase in system resources to process the query.

Share This Page