Sql-server – Help finding join without predicate

azure-sql-databasejoin;performancequery-performancesql server

Much like a related question by swasheck, I have a query that historically has suffered from performance problems. I was looking through the query plan on SSMS and noticed a Nested Loops (Inner Join) with the warning:

No Join Predicate

Based on some hasty research (confidence inspiring Scary DBA, and Brent Ozar) it looks like this warning is telling me I have a hidden Cartesian product in my query. I've checked my query a few times and don't see the cross join. Here is the query:

DECLARE @UserId INT; -- Stored procedure input
DECLARE @Now DATETIME2(7) = SYSUTCDATETIME();
;WITH AggregateStepData_CTE AS -- Considering converting this CTE into an indexed view
(
    SELECT 
        [UA].[UserId] -- FK to the UserId
        , [UA].[DeviceId] -- FK to the push device's DeviceId (int)
        , SUM(ISNULL([UA].[LatestSteps], 0)) AS [Steps]
    FROM [User].[UserStatus] [UA]
        INNER JOIN [User].[CurrentConnections] [M] ON 
           [M].[Monitored] = [UA].[UserId] AND [M].[Monitor] = @UserId
    WHERE
        [M].[ShareSteps] = 1 -- Only use step data if we are allowed to see.
        AND
        CAST([UA].[ReportedLocalTime] AS DATE) = 
          CAST(DATEADD(MINUTE, DATEPART(TZOFFSET, [UA].[ReportedLocalTime]), @Now) AS DATE)
          -- Aggregate the steps for today based on the device's time zone.         
    GROUP BY
        [UA].[UserId]
        , [UA].[DeviceId]
)
SELECT
    [UA].[UserId] -- FK to the UserId
    , [UA].[ReportedLocalTime]
    , CASE WHEN [M].[ShareLocation] = 1 THEN [UA].[Latitude] ELSE NULL END AS [Latitude]
    , CASE WHEN [M].[ShareLocation] = 1 THEN [UA].[Longitude] ELSE NULL END AS [Longitude]
    , CASE WHEN [M].[ShareLocation] = 1 THEN [UA].[LocationAccuracy] ELSE NULL END 
         AS [LocationAccuracy]
    , CASE WHEN [M].[ShareSteps] = 1 THEN ISNULL([SD].[Steps], 0) ELSE NULL END AS [Steps]
    , CASE WHEN [M].[ShareBattery] = 1 THEN [UA].[BatteryPercentage] ELSE NULL END 
         AS [BatteryPercentage]
    , CASE WHEN [M].[ShareBattery] = 1 THEN [UA].[IsDraining] ELSE NULL END 
         AS [IsDraining]
    , [PD].[DeviceName]
FROM [User].[LatestUserStatus] [UA]
    INNER JOIN [User].[CurrentConnections] [M] WITH (NOEXPAND) ON 
      [M].[Monitored] = [UA].[UserId] AND [M].[Monitor] = @UserId
    INNER JOIN [User].[PushDevice] [PD] ON [PD].[PushDeviceId] = [UA].[DeviceId]
    LEFT JOIN [AggregateStepData_CTE] [SD] ON 
      [M].[Monitored] = [SD].[UserId] AND [SD].[DeviceId] = [UA].[DeviceId]
ORDER BY        
    [UA].[UserId]
    , [UA].[ReportedLocalTime] DESC

Query plan can be found at: https://gist.github.com/anonymous/d6ac970b45eb75a88b99

Or should I just not be afraid of the warning as was the conclusion in swasheck's question, after all the estimated sub-tree cost is quite low at 0.05?


This answer also seems relevant, which implies this is probably an optimization that SQL Server is making on my behalf, because it knows I can drop a join.


This blog post suggests that the nested loops no predicate issue could be caused by a UDF on a join column. I'm not referencing any UDF's in this query.


Here is the definition of the CurrentConnections view:

CREATE VIEW [User].[CurrentConnections]
WITH SCHEMABINDING
AS 
    SELECT 
        [M].[Monitor] -- FK to the UserId
        , [M].[Monitored] -- FK to the UserId
        , [M].[MonitoringId]
        , [M].[ShareBattery]
        , [M].[ShareLocation]
        , [M].[ShareSteps]
        , [M].[ShowInSocialFeed]
        , [M].[Created] AS [RelationshipCreated]
        , [AT].[AlertThresholdId]
        , [AT].[EffectiveStartTime]
        , [AT].[EndTime]
        , [AT].[OverNightRedThreshold]
        , [AT].[SendBatteryAlerts]
        , [AT].[SendGeneralAlerts]
        , [AT].[StartTime]
        , [AT].[ThresholdInMinutes]
        , [AT].[Threshold]
        , [U_Monitored].[ProfilePhoto] AS [Monitored_ProfilePhoto]
        , [U_Monitored].[DisplayName] AS [Monitored_DisplayName]
        , [U_Monitored].[Fullname] AS [Monitored_FullName]
        , [U_Monitored].[PhoneNumber] AS [Monitored_PhoneNumber]
    FROM [User].[Monitoring] [M]
        INNER JOIN [User].[AlertThreshold] [AT] ON [AT].[MonitoringId] = [M].[MonitoringId]
        INNER JOIN [User].[User] [U_Monitored] ON [U_Monitored].[UserId] = [M].[Monitored]
    WHERE
        [M].[ArchivedOn] IS NULL
        AND
        [AT].[ArchivedOn] IS NULL

GO

CREATE UNIQUE CLUSTERED INDEX [IDX_User_CurrentConnections_Monitor_Monitored] ON 
   [User].[CurrentConnections]([Monitor], [Monitored]);
GO
CREATE NONCLUSTERED INDEX [IDX_User_CurrentConnections_Monitored] ON 
  [User].[CurrentConnections]([Monitored]) 
  INCLUDE ([Monitor], [ShareBattery], [ShareLocation], [ShareSteps]);

Best Answer

In my CTE I was missing a WITH (NOEXPAND) query hint. Once I added this query hint the join without predicate disappeared from my query plan.

;WITH AggregateStepData_CTE AS
(
    SELECT
        [UA].[UserId]
        , [UA].[DeviceId]
        , SUM(ISNULL([UA].[LatestSteps], 0)) AS [Steps]
    FROM [User].[UserStatus] [UA]
        INNER JOIN [User].[CurrentConnections] [M] WITH (NOEXPAND) -- Added query hint here
          ON [M].[Monitored] = [UA].[UserId] AND [M].[Monitor] = @UserId
    WHERE
        [M].[ShareSteps] = 1 -- Only use step data if we are allowed to see.
        AND
        CAST([UA].[ReportedLocalTime] AS DATE) = 
          CAST(DATEADD(MINUTE, DATEPART(TZOFFSET, [UA].[ReportedLocalTime]), @Now) AS DATE) 
          -- Aggregate the steps for today based on the device's time zone.         
    GROUP BY
        [UA].[UserId]
        , [UA].[DeviceId]
)