Cláusula WHERE Compare VarChar(n) Coluna = VarChar(MAX) Variável Resultados em Comparação de Desigualdades e Baixo Desempenho de Consulta

Hoje cedo eu estava trabalhando em um Reporting Stored Procedureque teve um desempenho ruim. Consegui corrigir o problema alterando uma estática variableque continha uma string de dez caracteres ou menos de VARCHAR(MAX)para VARCHAR(10). Estou procurando entender por que isso teria feito a diferença.

Detalhes Adicionais Se Necessário

A consulta original é muito maior do que o que precisa ser analisado, mas eu vi as coisas abaixo acontecendo, ao juntar o fundamental tablea um Key-Value Pair table, estávamos retornando uma tonelada a mais rowsdo que era realmente necessário (em alguns casos, mais de 14 bilhões de linhas para uma saída final de ~60.000 linhas) e tivemos algumas operações extras (particularmente o Lazy Table Spool). A imagem dessa parte do Plano de Consulta está abaixo:

Eu podia ver que uma parte estranha do plano de execução era como @ProgramID variableestava sendo tratado. Essa variável é usada como [Column] = @ProgramIDe Columnfaz parte das tabelas Non-Clustered Indexe é um arquivo VarChar(10). Mas ainda estamos fazendo um Inequality Searchem vez de um Equality Search.

Depois de modificar isso parameter/variablede VarChar(Max)para VarChar(10). O execution timetempo caiu de várias horas para cerca de 30 segundos e o plano tinha uma estrutura bem diferente. Olhando para um trecho semelhante do plano de execução, não estávamos retornando tantas linhas, não temos as operações extras e estávamos fazendo um equality searchon [Column] = @ProgramID(novamente Columnfaz parte das tabelas Non-Clustered Indexe é um VarChar(10)).

Abaixo está uma consulta simplificada que mostra um pouco desse comportamento (não temos todas as etapas extras, mas a diferença Seek Predicate ainda está lá): ( Paste The Plan )

DECLARE @CompanyID VarChar(10) = 'RxCRoads'
        ,@ClientID VarCHar(10) = 'Amgen'
        ,@ProgramID VarChar(MAX) = 'Foundation'
        ,@StartDate DATETIME = '2020-01-01'
        ,@EndDate DATETIME = '2020-12-07'
        ,@RxOnly INT = 9

SELECT PC.CompanyID,
PC.ClientID,
PC.ProgramID,
PC.PatientID,
PC.CaseID,
RxOnly.[Value] AS RxOnly
FROM PATIENTCASES PC
    LEFT OUTER JOIN PatientCaseDetail RxOnly
        ON RxOnly.CompanyId = PC.CompanyID
        AND RxOnly.ClientId = PC.ClientID
        AND RxOnly.ProgramId = PC.ProgramID
        AND RxOnly.PatientID = PC.PatientID
        AND RxOnly.CaseID = PC.CaseID
        AND RxOnly.PatientCaseAdditionalElementId = @RxOnly
WHERE PC.CompanyID = @CompanyID
AND PC.ClientID = @ClientID
AND PC.ProgramID = @ProgramID
AND PC.CaseCreateDateTime >= @StartDate
AND PC.CaseCreateDateTime < @EndDate

Apenas alterar o @ProgramID VarChar(MAX) = 'Foundation')para @ProgramID VarChar(10) = 'Foundation'retorna um plano mais ágil e melhor desempenho (mesmo que não seja realmente perceptível nesta consulta específica). ( Colar o plano )

Existe algum motivo específico pelo qual essa mudança no tamanho do tipo de dados resultaria na mudança no plano de execução? Executar a mesma consulta com @ProgarmId VarChar(8000)em vez de @Program VarChar(10)não tem diferença perceptível de @Program VarChar(10). ( Colar o plano )

Meu palpite é que VarChar(MAX)tem regras de comparação diferentes devido ao seu tamanho, e isso é apenas um estado do que VarChar(MAX)é. Mas eu não sabia se alguém tinha uma resposta melhor/mais técnica do que eu para que eu possa aplicar melhor essa lição no futuro.