部分查詢的高 I/O

我有一個很大的查詢，我正在嘗試調整。我寫了很多查詢，但沒有做很多調整。我已經包含了來自 SQL Sentry Plan Explorer Free (SSPEF) 的螢幕截圖：

在計劃的上述部分中，表 pb_WorkRquestLog 包含 229,001 行。但是，查詢計劃顯示大約。3.48 億行（229,001 x 1,520 次迭代）：

沒有 where 子句，因此查詢使用聚集索引掃描。我已經用 FULLSCAN 重建了所有索引並更新了所有統計資訊。

這部分計劃正在執行的程式碼是：

select distinct 
           wrs.ServiceKey 
           , owner.DepartmentName AS GroupName
           , owner.UserName AS UserId
           , owner.WRLCreateDateTime
           , owner.WRLNotes
           , wrx.CreatedDate as WRCreateDateTime
           , wrx.Id
           , wrx.Description 
           , cast(wrx.Notes as varchar(2500)) as Notes
       from    
           prism72ext.dbo.pb_WorkRequestService wrs 
           Join prism72ext.dbo.pb_WorkRequest wrx on (wrs.WorkRequestId = wrx.Id and wrx.Status = 'Incomplete')
           left join (
               select
                   wl.WorkRequestId
                   ,   d.Name AS DepartmentName
                   ,   u.UserName
                   , wl.CreatedDate as WRLCreateDateTime
                   , cast(wl.Notes as varchar(2500)) as WRLNotes
               from
                   (
                       SELECT     
                           MAX( Id ) AS Id
                       FROM          
                               prism72ext.dbo.pb_WorkRequestLog WITH(INDEX(0))
                       GROUP BY 
                           WorkRequestId
                   ) mwl
                   join prism72ext.dbo.pb_WorkRequestLog wl on mwl.Id = wl.Id
                   join prism72Ext.dbo.pb_Department d ON wl.DepartmentId = d.DepartmentId
                   left join prism72Ext.dbo.pb_User u on wl.UserId = u.UserId
               ) owner on wrs.WorkRequestId = owner.WorkRequestId

SSPEF 報告實際數據大小接近 5GB，具有 300 萬次邏輯讀取！SSMS 報告該表的大小僅為 16 MB。

我已將查詢從 4 分 28 秒調低至 1 分 35 秒，但現在我被卡住了。如果有人能指出我處理這個問題的正確方向，我將不勝感激。

編輯：有人建議嘗試“OPTION（HASH JOIN，MERGE JOIN）”。這產生了戲劇性的變化。I/O 大大減少，查詢在 13 秒內執行。

有沒有人看到這個解決方案有任何問題？

如果您還沒有，請創建一個複合非聚集索引prism72ext.dbo.pb_WorkRequestLog(WorkRequestId, Id DESC)並在沒有索引提示的情況下執行查詢。

此外，讓owner派生表的內部選擇返回mwl.WorkRequestId而不是ml.WorkRequestId.

使用 INDEX(0) 提示強制聚集索引掃描。如果你想避免它，你必須刪除提示。表提示：

如果存在聚集索引，則 INDEX(0) 強制執行聚集索引掃描，INDEX(1) 強制執行聚集索引掃描或查找。如果不存在聚集索引，則 INDEX(0) 強制執行表掃描，並且 INDEX(1) 被解釋為錯誤。

您還可以嘗試在 WorkRequestId 上創建一個非聚集索引，並使用 INCLUDE 子句包含 CreatedDate 和 Notes 列 - 請記住，這可能會對插入性能和表的大小產生負面影響。

值得一提的是，在查詢中硬編碼數據庫名稱會使程式碼極難遷移到測試環境，除非您可以為每個伺服器實例提供一個數據庫。

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