missing_join_predicate event: behavior and description

missing_join_predicate event: behavior and description by Ana Mihalj

Closed

as Duplicate Help for as Duplicate

Type:	Bug
ID:	693321
Opened:	10/6/2011 3:51:08 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Description of missing_join_predicate event is: "This event only occurs if both sides of the join return more than one row."

From BOL (http://msdn.microsoft.com/en-us/library/ms175146(v=SQL.110).aspx): This event is produced only if both sides of the join return more than one row.

But missing_join_predicate event is produced if one side of join returns one row; it will be produced even when both side of the join returns only one row.

So behavior or documentation isn't correct.

Details (expand)

English

SQL Server Denali CTP 3

SQL Engine

Windows Server 2008 R2 Standard

English

-- Create XEvent session
CREATE EVENT SESSION [missing_join_predicate] ON SERVER
ADD EVENT sqlserver.missing_join_predicate(
ACTION(sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'D:\MissingJoinPredicate.xel')
--Start XEvent session
ALTER EVENT SESSION [missing_join_predicate]
ON SERVER
STATE = START
GO

--run query in AdventureWorks2008R2 database
select sod.SalesOrderID, th.TransactionID
from Sales.SalesOrderDetail sod
inner join Production.TransactionHistory th
on sod.SalesOrderID=th.ReferenceOrderID
where th.ReferenceOrderID=43660

Event session missing_join_predicate collected missing_join_predicate warning, but one side of join return only 1 row (from TransactionHistory) and the other side of join return 2 rows (SalesOrderDetail).

You can also delete one row from SalesOrderDetail:
delete from Sales.SalesOrderDetail
where SalesOrderID=43660 and SalesOrderDetailID=13

If you run select again, both side of the join returns only one row but event missing_join_predicate is collected.

In Steps to reproduce.

missing_join_predicate warning isn't collected.

0 attachments

Dear Ana,

Thanks for the suggestion. We'll change the code to ignore the number of rows and issue the warning if there is no join predicate regardless of cardinality estimate. This will appear in a future relase after SQL Server 2012. We've also updated the documentation to remove the statement that "This event only occurs if both sides of the join return more than one row."

Best regards,
Eric Hanson
Program Manager
SQL Server Query Processing