Why does the first query scan and the second seek?

Question

Please could somebody explain why the first query below scans on every join to the REFERENCE_VALUES table but the second query seeks on the same joins?

The first query does have other joins, but I don't see why that should cause this to happen. The join column RFVAL_REFNO is a unique clustered index and PK.

Query 1:

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'

Query 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'

I rebuilt the index on SERVICE_POINT_SESSIONS and now the 2 queries seek on the REFERENCE_VALUES table. I picked that one to rebuild as the execution plan was estimating lots of rows would be returned when not many were actually being returned. A bit of insight would still be appreciated.

Answer 1

You said you rebuilt the index and now both queries are performing index seeks, as desired.

This is most likely due to the index rebuild operation recreating the statistics for the affected columns. Now that that query optimizer has updated statistics, it knows it would be more efficient to seek the index.

You may want to ensure you have an index rebuild job that runs on a schedule - I'd recommend looking at Ola Hallengren's solution for that.

Answer 2

To be more accurate, I would add another possibility is that your index rebuild has updated (not recreating) the statistics, and this caused recompilation of the execution plans (instead of using the cached execution plans).

With this theory (i.e. statistics updated -> execution plans recompiled), I'd say you may just need to do an statistics update on your table SERVICE_POINT_SESSIONS, like the following

update statistics SERVICE_POINT_SESSIONS with FULLSCAN, index;

It is common for DBAs to do a statistics update first before rebuilding index to check whether a query performance is improved. The reason is stats update is light-weight compared to index rebuild in overhead cost.