幫助查找無謂詞連接

就像swasheck 提出的一個相關問題一樣，我有一個查詢在歷史上一直存在性能問題。我正在查看 SSMS 上的查詢計劃，並註意到Nested Loops (Inner Join)警告：

無連接謂詞

基於一些倉促的研究（激發了可怕的 DBA和Brent Ozar 的信心），看起來這個警告告訴我我的查詢中有一個隱藏的笛卡爾積。我檢查了我的查詢幾次，沒有看到交叉連接。這是查詢：

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

查詢計劃見：https ://gist.github.com/anonymous/d6ac970b45eb75a88b99

還是我不應該像swasheck 問題中的結論那樣害怕警告，畢竟估計的子樹成本很低，只有 0.05？

---

這個答案似乎也很相關，這意味著這可能是 SQL Server 代表我進行的優化，因為它知道我可以刪除連接。

---

This blog post建議嵌套循環沒有謂詞問題可能是由連接列上的UDF引起的。我沒有在此查詢中引用任何 UDF。

---

這是CurrentConnections視圖的定義：

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

在我的 CTE 中，我缺少一個WITH (NOEXPAND)查詢提示。一旦我添加了這個查詢提示，沒有謂詞的連接就從我的查詢計劃中消失了。

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

引用自：https://dba.stackexchange.com/questions/117506