Попался на одну веселую проблему которую решал, довольно много времени. Вот есть 2 запроса:

SELECT *
            
    
FROM work_Global_Center.dbo._AccumRg5908 as TableTarget --WITH(TABLOCK)
    LEFT JOIN work_Global_Digma.dbo._AccumRg6186 as TableSource
        ON TableTarget._Period=TableSource._Period 
            AND TableTarget._RecorderTRef=(CASE TableSource._RecorderTRef  
                                                        WHEN CONVERT(binary(4),112) then CONVERT(binary(4),107)  
                                                        WHEN CONVERT(binary(4),102) then CONVERT(binary(4),97)  
                                                        WHEN CONVERT(binary(4),181) then CONVERT(binary(4),176)  
                                                        WHEN CONVERT(binary(4),119) then CONVERT(binary(4),114)  
                                                        WHEN CONVERT(binary(4),114) then CONVERT(binary(4),109)  
                                                        WHEN CONVERT(binary(4),142) then CONVERT(binary(4),137)  
                                                        WHEN CONVERT(binary(4),170) then CONVERT(binary(4),165)  
                                                        WHEN CONVERT(binary(4),123) then CONVERT(binary(4),118)  
                                                        WHEN CONVERT(binary(4),153) then CONVERT(binary(4),148)  
                                                        WHEN CONVERT(binary(4),109) then CONVERT(binary(4),104)  
                                                        WHEN CONVERT(binary(4),127) then CONVERT(binary(4),122)  
                                                        WHEN CONVERT(binary(4),182) then CONVERT(binary(4),177)  
                                                        WHEN CONVERT(binary(4),135) then CONVERT(binary(4),130)  
                                                        WHEN CONVERT(binary(4),165) then CONVERT(binary(4),160)  
                                                        WHEN CONVERT(binary(4),111) then CONVERT(binary(4),106)  
                                                        WHEN CONVERT(binary(4),143) then CONVERT(binary(4),138)  
                                                        WHEN CONVERT(binary(4),146) then CONVERT(binary(4),141)  
                                                        WHEN CONVERT(binary(4),154) then CONVERT(binary(4),149)  
                                                        WHEN CONVERT(binary(4),160) then CONVERT(binary(4),155)  
                                                        WHEN CONVERT(binary(4),161) then CONVERT(binary(4),156)  
                                                        ELSE TableSource._RecorderTRef END)
            AND TableTarget._RecorderRRef=TableSource._RecorderRRef
            AND TableTarget._LineNo =TableSource._LineNo
WHERE TableTarget._Period> =DATEADD(DAY,-2,getdate())
        and TableSource._LineNo is null

Этот запрос выполнялся около 2 минут!!!!!!!!!! А этот:

Declare @SelectPeriod datetime;
set @SelectPeriod = DATEADD(DAY,-2,getdate())
SELECT *
            
    
FROM work_Global_Center.dbo._AccumRg5908 as TableTarget --WITH(TABLOCK)
    LEFT JOIN work_Global_Digma.dbo._AccumRg6186 as TableSource
        ON TableTarget._Period=TableSource._Period 
            AND TableTarget._RecorderTRef=(CASE TableSource._RecorderTRef  
                                                        WHEN CONVERT(binary(4),112) then CONVERT(binary(4),107)  
                                                        WHEN CONVERT(binary(4),102) then CONVERT(binary(4),97)  
                                                        WHEN CONVERT(binary(4),181) then CONVERT(binary(4),176)  
                                                        WHEN CONVERT(binary(4),119) then CONVERT(binary(4),114)  
                                                        WHEN CONVERT(binary(4),114) then CONVERT(binary(4),109)  
                                                        WHEN CONVERT(binary(4),142) then CONVERT(binary(4),137) 
                                                        WHEN CONVERT(binary(4),170) then CONVERT(binary(4),165)  
                                                        WHEN CONVERT(binary(4),123) then CONVERT(binary(4),118)  
                                                        WHEN CONVERT(binary(4),153) then CONVERT(binary(4),148)  
                                                        WHEN CONVERT(binary(4),109) then CONVERT(binary(4),104)  
                                                        WHEN CONVERT(binary(4),127) then CONVERT(binary(4),122)  
                                                        WHEN CONVERT(binary(4),182) then CONVERT(binary(4),177)  
                                                        WHEN CONVERT(binary(4),135) then CONVERT(binary(4),130)  
                                                        WHEN CONVERT(binary(4),165) then CONVERT(binary(4),160)  
                                                        WHEN CONVERT(binary(4),111) then CONVERT(binary(4),106)  
                                                        WHEN CONVERT(binary(4),143) then CONVERT(binary(4),138)  
                                                        WHEN CONVERT(binary(4),146) then CONVERT(binary(4),141)  
                                                        WHEN CONVERT(binary(4),154) then CONVERT(binary(4),149) 
                                                        WHEN CONVERT(binary(4),160) then CONVERT(binary(4),155)  
                                                        WHEN CONVERT(binary(4),161) then CONVERT(binary(4),156)  
                                                        ELSE TableSource._RecorderTRef END)
            AND TableTarget._RecorderRRef=TableSource._RecorderRRef
            AND TableTarget._LineNo =TableSource._LineNo
WHERE TableTarget._Period> =@SelectPeriod
        and TableSource._LineNo is null

Меньше секунды........ но что очень весело, при изменении DATEADD(DAY,-2,getdate()) на DATEADD(ww,-2,getdate()), или же месяц давало повышение существенно быстродействие, фактически разница составляла больше 1,5 минуты.