Por que a consulta no ETL via SSIS é lenta, mas por meio de um procedimento armazenado local é rápida?

Estou vendo o clássico problema 'executa rápido no gerenciador de estúdio, mas lento no aplicativo'. Parece que pode ser sniffing de parâmetros. No entanto, minha experiência com ETL e SSIS é zero.

Do DBA recebi a seguinte consulta e ela termina com um ? em vez de um parâmetro. Aqui está uma amostra ofuscada da consulta:

SELECT 
tablex.x_id, 
tablex.create_ts, 
tablex.update_ts, 
tablex.myStatus, 
tablex.x_type, 
tablex.ami_uploaded, 
tablex.work_id,
tablex_capture_ts,
[column1],
[column2],
[column3],
[column4] 
FROM     sqltable..tablex 
INNER JOIN 
sqltable..tableWork ON tablex.work_id = tableWork.work_id
WHERE  
(tablex.update_ts >= ?)

• De acordo com o DBA, o ponto de interrogação é substituído por um argumento de 'hora/data' que está uma hora no passado.
• Quando executo essa mesma consulta localmente a partir de um procedimento armazenado, passando um parâmetro que está há uma hora, ele retorna em menos de um segundo. (o que para mim significa que 'pode' usar o índice existente)
• Observando esta execução a partir do ETL, leva alguns minutos e o plano de execução mostra as varreduras de tabela.
• Existe um índice update_ts.

O mecanismo de consulta recomenda um segundo índice update_ts com várias colunas de inclusão. Eu gostaria de evitar isso, se possível, pois aumentará a pressão da memória e não estou convencido de que isso resolva o problema real. Pensamentos?

Isso parece um caso em que as estatísticas de consulta estão distorcidas e, quando o mecanismo de consulta detecta o parâmetro, ele evita usar o índice existente porque o número estimado de linhas está além do limite.

Minhas perguntas:

1. Como o ? na consulta SSIS são tratadas pelo sql server? Eu sei que o sniffing de parâmetros é uma questão complexa. Eu tenho estudado isso: http://www.sommarskog.se/query-plan-mysteries.html
2. Se for o mecanismo de consulta farejando o parâmetro (de uma hora no passado) e pensando que o número estimado de linhas está além do ponto de disparo, o que faço para corrigir isso? O DBA recusou a dica OPTIMIZE for RECOMPILE como uma opção e não posso dizer que discordo. (Ele tem um ponto em relação ao histórico de bugs) No entanto, essas consultas acontecem SOMENTE do ETL nos horários programados e talvez isso seja motivo suficiente para usar o HINT independente do possível bug??

Além disso, este é um longo problema com o qual tenho lutado. Todas essas postagens estão relacionadas a esse mesmo problema. Que viagem de descoberta:

Este é um tempo de 'Bloqueio' excessivamente grande e é indicativo de um problema?

SQL Server - Posso remover cirurgicamente um plano de consulta em cache incorreto ou estou perseguindo a ideia errada?

Qualquer conselho é muito apreciado.

Este deve ser o plano de execução real da versão do procedimento armazenado local. Esta versão retorna em 1 segundo e exibe o comportamento QUE EU DESEJO que o ETL tenha:

https://www.brentozar.com/pastetheplan/?id=ry4wy6dBO

Agora, esta é uma captura de tela da versão ETL que leva minutos para ser concluída. Desculpe, não posso fornecer mais detalhes sobre esta consulta específica:

Esta é uma captura de tela de um rastreamento de perfil feito durante um período de uma hora. Acho que é assim que os comandos ETL estão sendo executados. Eu ainda não sei porque todos têm o mesmo tempo. Eu também preciso encontrar o preparar também. Olhe para essas colunas de CPU, leituras e duração!