SELECT 
	s6.* 
	, st.col_tip_pokazanij
FROM temp_so6_09 s6
LEFT JOIN temp_so1_09 st -- тип показаний
	ON 
	st.link = (SELECT TOP 1 s.link FROM temp_so1_09 s WHERE (s.col_pl_kod = s6.col_ob_pl_kod OR s.col_dogovor = s6.col_ob_pl_kod ) AND s.col34 = s6.col_nomer_pu AND s.col_tip_pokazanij IS NOT NULL
	ORDER BY s.col14 DESC)

create index ... on temp_so1_09 (col_nomer_pu, col14) include (col_pl_kod, col_dogovor, col_tip_pokazanij, link)

create index ... on temp_so1_09 (col_nomer_pu, col14) include (col_pl_kod, col_dogovor, col_tip_pokazanij, link)

create index ... on temp_so1_09 (col34, col14) include (col_pl_kod, col_dogovor, col_tip_pokazanij, link)

create index IDX1 on temp_so1_09 ( col34, col14 ) 
include ( col_pl_kod, col_dogovor, col_tip_pokazanij, link )
where col_tip_pokazanij is not null
go

create index IDX2 on temp_so1_09 ( link ) 
include ( col_tip_pokazanij )
go

select
    s6.*
  , st.col_tip_pokazanij
from temp_so6_09 as s6
    cross apply (
        select top 1 t.link
        from (
            select top 1 s.link, col14
            from temp_so1_09 as s
            where s.col_pl_kod = s6.col_ob_pl_kod 
              and s.col34 = s6.col_nomer_pu
              and s.col_tip_pokazanij is not null
            order by s.col14 desc

            union all
    
            select top 1 s.link, col14
            from temp_so1_09 as s
            where s.col_dogovor = s6.col_ob_pl_kod
              and s.col34 = s6.col_nomer_pu
              and s.col_tip_pokazanij is not null
            order by s.col14 desc
        ) t
        order by t.col14 desc
    )
    left join temp_so1_09 as st on st.link = t.link
    
go

create index IDX1 on temp_so1_09 ( link ) 
include ( col_tip_pokazanij )
go

create index IDX2 on temp_so1_09 ( col34, col_dogovor, col14 ) 
include ( col_tip_pokazanij, link )
where col_tip_pokazanij is not null
go

create index IDX3 on temp_so1_09 ( col34, col_pl_kod, col14 ) 
include ( col_tip_pokazanij, link )
where col_tip_pokazanij is not null
go

--Если temp_so1_09.link уникален для каждой строки из temp_so6_09:
SELECT
  s6.* 
, ( SELECT TOP 1 col_tip_pokazanij
FROM temp_so1_09 s -- тип показаний
WHERE (s.col_pl_kod = s6.col_ob_pl_kod OR s.col_dogovor = s6.col_ob_pl_kod)
  AND s.col34 = s6.col_nomer_pu
  AND s.col_tip_pokazanij IS NOT NULL
ORDER BY s.col14 DESC)
FROM temp_so6_09 s6

--Порядок полей в индексе доработать напильником:
create nonclusterd index XXX on temp_so1_09( col14, col_pl_kod, col_dogovor, col34, col_tip_pokazanij) desc