為什麼第一次查詢掃描和第二次搜尋？

請有人解釋一下為什麼下面的第一個查詢會掃描REFERENCE_VALUES表的每個連接，而第二個查詢會搜尋相同的連接？

第一個查詢確實有其他連接，但我不明白為什麼會導致這種情況發生。join 列RFVAL_REFNO是唯一的聚集索引和 PK。

查詢一：

DECLARE @PATNT_REFNO NUMERIC(10,0) = 515938

SELECT  
'Outpatient' AS TIMELINE_TYPE,
SCHEDULES.SCHDL_REFNO,
ISNULL(SCHEDULES.START_DTTM, SCHEDULES.ARRIVED_DTTM) AS START_DTTM,
REF_VISIT.DESCRIPTION VISIT_TYPE,
SERVICE_POINTS.CODE AS CLINIC_CODE,
SERVICE_POINTS.DESCRIPTION AS CLINIC_DESC,    
SCHEDULES.COMMENTS AS COMMENTS,
SERVICE_POINT_SESSIONS.CODE AS SESSION_CODE,
ISNULL(PROF_CARERS.FORENAME,'') + ' ' + ISNULL(PROF_CARERS.SURNAME,'') AS CLINICIAN,
REF_ATTND.DESCRIPTION AS ATTEND_TYPE,   
CASE REF_ATTND.DESCRIPTION
     WHEN 'Cancelled' THEN 'Cancelled'
     ELSE REF_SCOCM.DESCRIPTION
END AS SCHEDULED_OUTCOME ,  
CASE REF_ATTND.DESCRIPTION
     WHEN 'Cancelled' THEN REF_CANCB.DESCRIPTION 
     ELSE ''
END AS CANCB_DESC,    
[SPECIALTIES].MAIN_IDENT AS SPEC_CODE,
[SPECIALTIES].DESCRIPTION AS SPEC_DESC,
SPECIALTIES.SPECT_REFNO                               
FROM      
[IE_PAS].[dbo].[SCHEDULES]

LEFT JOIN [IE_PAS].[dbo].[PROF_CARERS] ON SCHEDULES.PROCA_REFNO = PROF_CARERS.PROCA_REFNO
LEFT JOIN [IE_PAS].[dbo].[SPECIALTIES] ON SCHEDULES.SPECT_REFNO = SPECIALTIES.SPECT_REFNO


LEFT JOIN [IE_PAS].[dbo].[SERVICE_POINTS] ON SCHEDULES.SPONT_REFNO = SERVICE_POINTS.SPONT_REFNO
LEFT JOIN [IE_PAS].[dbo].[SERVICE_POINT_SESSIONS] ON SCHEDULES.SPSSN_REFNO = SERVICE_POINT_SESSIONS.SPSSN_REFNO
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_VISIT ON SCHEDULES.VISIT_REFNO = REF_VISIT.RFVAL_REFNO               
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_ATTND ON SCHEDULES.ATTND_REFNO = REF_ATTND.RFVAL_REFNO   
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_SCOCM ON SCHEDULES.SCOCM_REFNO = REF_SCOCM.RFVAL_REFNO
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_CANCB ON SCHEDULES.CANCB_REFNO = REF_CANCB.RFVAL_REFNO   


--LEFT JOIN #AuthorisedDNF LETTERS WITH (INDEX(#AuthorisedDNF_IX)) ON SCHEDULES.SCHDL_REFNO = LETTERS.LINK
--LEFT JOIN #CLINICAL_NOTES CLINICAL_NOTES_OP WITH (INDEX(#CLINICAL_NOTES_IX)) ON CAST(SCHEDULES.SCHDL_REFNO AS VARCHAR(10)) = CLINICAL_NOTES_OP.ACTIVITY_REFERENCE   
--                                                                          AND CLINICAL_NOTES_OP.ACTIVITY_TYPE = 'SCHDL_REFNO'
--                                                                          AND CLINICAL_NOTES_OP.AD_HOC_FLAG = 0
WHERE 
SCHEDULES.PATNT_REFNO = @PATNT_REFNO AND
ISNULL(SCHEDULES.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(SERVICE_POINTS.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(SERVICE_POINT_SESSIONS.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(REF_VISIT.ARCHV_FLAG, 'N') <> 'Y' AND          
ISNULL(REF_ATTND.ARCHV_FLAG, 'N') <> 'Y' AND    
ISNULL(REF_SCOCM.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(PROF_CARERS.ARCHV_FLAG, 'N') <> 'Y'

查詢 2：

SELECT  
'Outpatient' AS TIMELINE_TYPE,
SCHEDULES.SCHDL_REFNO,
ISNULL(SCHEDULES.START_DTTM, SCHEDULES.ARRIVED_DTTM) AS START_DTTM,
REF_VISIT.DESCRIPTION VISIT_TYPE, 
SCHEDULES.COMMENTS AS COMMENTS,
REF_ATTND.DESCRIPTION AS ATTEND_TYPE,   
CASE REF_ATTND.DESCRIPTION
     WHEN 'Cancelled' THEN 'Cancelled'
     ELSE REF_SCOCM.DESCRIPTION
END AS SCHEDULED_OUTCOME ,  
CASE REF_ATTND.DESCRIPTION
     WHEN 'Cancelled' THEN REF_CANCB.DESCRIPTION 
     ELSE ''
END AS CANCB_DESC                          
FROM      
[IE_PAS].[dbo].[SCHEDULES]
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_VISIT ON SCHEDULES.VISIT_REFNO = REF_VISIT.RFVAL_REFNO               
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_ATTND ON SCHEDULES.ATTND_REFNO = REF_ATTND.RFVAL_REFNO   
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_SCOCM ON SCHEDULES.SCOCM_REFNO = REF_SCOCM.RFVAL_REFNO
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_CANCB ON SCHEDULES.CANCB_REFNO = REF_CANCB.RFVAL_REFNO      
WHERE 
SCHEDULES.PATNT_REFNO = @PATNT_REFNO AND
ISNULL(SCHEDULES.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(REF_VISIT.ARCHV_FLAG, 'N') <> 'Y' AND          
ISNULL(REF_ATTND.ARCHV_FLAG, 'N') <> 'Y' AND    
ISNULL(REF_SCOCM.ARCHV_FLAG, 'N') <> 'Y'

我重建了索引SERVICE_POINT_SESSIONS，現在 2 個查詢在REFERENCE_VALUES表上搜尋。我選擇了那個來重建，因為執行計劃估計會返回很多行，而實際上返回的行並不多。一些見解仍然會受到讚賞。

您說您重建了索引，現在兩個查詢都根據需要執行索引查找。

這很可能是由於索引重建操作重新創建了受影響列的統計資訊。現在查詢優化器已經更新了統計資訊，它知道查找索引會更有效。

您可能希望確保您有一個按計劃執行的索引重建作業 - 我建議您查看Ola Hallengren 的解決方案。

更準確地說，我要添加另一種可能性是您的索引重建已經更新（而不是重新創建）統計資訊，這導致重新編譯執行計劃（而不是使用記憶體的執行計劃）。

有了這個理論（即更新統計資訊->重新編譯執行計劃），我想說您可能只需要對錶SERVICE_POINT_SESSIONS進行統計資訊更新，如下所示

update statistics SERVICE_POINT_SESSIONS with FULLSCAN, index;

DBA 通常會在重建索引之前先進行統計更新，以檢查查詢性能是否有所提高。原因是統計更新與索引重建相比在成本成本上是輕量級的。

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