Help finding join without predicate

Question

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]);

Answer 1

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]
)

Answer 2

I have had similar situations and read a while back that the engine will determine if the type of join algorithm is most efficient and will sometimes drop the join predicates in behalf of the efficiency over the written code. By doing no expand, you're telling the engine to no descend to the view and stick with your top layer query block.

Don't be dissuaded if more suggestions are made to prove you have your join predicates. Believe me too, I checked 100s of times and I they were correct. Lol. Thus my contribution.

Try moving the Join up and down the table reference stack, making sure the alias references are in correct dependency order. Meaning if you have 4 joins, I have had success and changed things up moving a less referenced or low selective table join to the last position of the joins and visa versa.

Make sure stats and indexes are up to date. Because I'm not confident with the "Auto Create/Upadate Stats" auto schedule and algorithm that MS is supposed to do, occasionally I will issue the following statements to make sure they are processed lately.

Sp_UpdateStats Sp_CreateStats 'indexonly'

Forcing hints should always be tested and documented, but I wanted to caution and inform what has occurred to me and what my investigation revealed.

The following link is a thread that relates to the same thing... Check for "timeout finding good enough plan" , and other warnings in the properties dialogue.

SQL Central Similar thread

I'm interested if any ideas resolve your issue as it did for me.