SQL Server - Capturing Missing Join Predicate for queries using Extended Events

missing_join_predicate , one of the event  -  " Occurs when an executed query is missing a join predicate. Use this event to identify a query that may perform slowly because of the missing predicate. This event only occurs if both sides of the join return more than one row " .

SELECT * FROM sys.dm_xe_objects WHERE name = 'missing_join_predicate'

--Create Session to track missing_join_predicate event with below mentioned Actions

CREATE EVENT SESSION missing_join_predicate
ON SERVER
ADD EVENT sqlserver.missing_join_predicate
(
ACTION (
        sqlserver.database_id,
        sqlserver.server_instance_name,
        sqlserver.transaction_id ,
        sqlserver.session_id ,
        sqlserver.nt_username ,
        sqlserver.server_principal_name ,
        sqlserver.sql_text ,
        sqlserver.tsql_stack
        )
       
)
--Below mentioned path location will hold the captured informations by the above created Session
ADD TARGET package0.event_file(
SET filename='D:\ExtendedEvents\missing_join_predicate.xel')
GO

If we execute the below query and notice the Execution plan - (CTRL + L) Display Estimated Execution Plan , we will notice Warning - No Join Predicate as shown below :

USE AdventureWorks2012
GO
SELECT *
FROM Sales.SalesOrderHeader AS SOH
,Sales.SalesOrderDetail AS SOD
, Sales.SalesOrderHeaderSalesReason AS SOHS

--Start the session
ALTER EVENT SESSION missing_join_predicate ON SERVER
STATE=START
GO

Note : As soon as , you start the event , you will notice target file created  .

Now , execute the below query :

USE AdventureWorks2012
GO
SELECT *
FROM Sales.SalesOrderHeader AS SOH
,Sales.SalesOrderDetail AS SOD
, Sales.SalesOrderHeaderSalesReason AS SOHS

Cancel Executing Query  ( Alt + Break )

To analyze the captured event data , execute below queries :

SELECT CAST(event_data AS XML) AS 'event_data_XML',*
FROM sys.fn_xe_file_target_read_file('D:\ExtendedEvents\missing_join_predicate_0_130321942091940000.xel', NULL, NULL, NULL)
GO 

SELECT RN,
    i.value('@name', 'NVARCHAR(MAX)') Event,
    i.value('@timestamp', 'NVARCHAR(MAX)') Timestamp,
    j.value('@name', 'NVARCHAR(MAX)') Action,
    k.value('.', 'NVARCHAR(MAX)') Value
FROM (SELECT row_number()OVER(ORDER BY module_guid) RN, object_name, CAST(event_data AS XML) event_data
FROM sys.fn_xe_file_target_read_file  ( 'D:\ExtendedEvents\missing_join_predicate_0_130321942091940000.xel',  NULL, NULL, NULL )) tmp
    CROSS APPLY tmp.event_data.nodes('event') x(i)
    CROSS APPLY i.nodes('action') y(j)
    CROSS APPLY j.nodes('value') z(k)

--To stop the event session
ALTER EVENT SESSION missing_join_predicate ON SERVER
STATE=STOP
GO
--To drop the event session
DROP EVENT SESSION missing_join_predicate
ON SERVER
GO

See Also :

• SQL Server - Miscellaneous
• Missing Join Predicate Event Class