Atualização de ajuste de consulta com operador distinto de fluxo de hash

Preciso de ajuda para entender a lentidão em uma das UPDATEdeclarações como abaixo: -

UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK)
on xyz.id=abc.id
WHERE xyz.flag = 0

A Tabela1 tem aprox. 0,5 milhão de linhas e a Tabela 2 tem aprox. 5 milhões de linhas

Plano lento

O operador de fluxo distinto Hash Match mostra um alarme amarelo e a mensagem é:

O operador usou Tempdb para derramar dados para execução com nível de derramamento 4 com 1 encadeamento derramado"

Resíduos de construção:

database.dbo.table2.id como abc.id = database.dbo.table2.id como abc.id

Eu tirei uma captura de tela. Infelizmente, por motivos de segurança, não posso fornecer mais do que isso, nem mesmo um plano anônimo. Da minha estação de trabalho, não consigo acessar a Internet, portanto, não há como fazer com que o explorer de planos seja executado lá.

Geralmente, para um subconjunto menor de linhas, é menos de segundos, como quando temos apenas 10 mil linhas correspondentes ou algo assim. Mas com uma quantidade maior de dados, isso parece ser o ponto de inflexão e o aplicativo não pode permitir 1 minuto de tempo de execução. Do SSMS, recebo 30 segundos, mas do aplicativo, temos o avg. 50 segundos aprox. RCSI está em fase de testes.

Meu bom plano não tem o operador Hash Match Flow Distinct visível como mostrado na minha captura de tela, enquanto o restante do plano permanece o mesmo. Um bom completa menos de 3 segundos ou mais. Como visto, quase 16 segundos são gastos nesse operador. Podemos eliminá-lo por meio de indexação adequada ou reescrita de consulta?

Esquema da tabela

CREATE TABLE dbo.table1
(
    Recid  VARCHAR(128) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    Cdate DATETIME NULL,
    flag BIT   NULL DEFAULT (0),
    Rcode INT NULL,
    EDR VARCHAR(255) COLLATE SQL_Latin1_general_CP1_CI_AS NULL,
    id BIGINT NULL
);

CREATE TABLE dbo.table2
(
    ENID BIGINT IDENTITY(1,1) NOT NULL,
    EID VARCHAR(50) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CID VARCHAR(350) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CDate DATETIME NOT NULL DEFAULT(getdate()),
    id BIGINT NOT NULL,

    CONSTRAINT PK_ENID PRIMARY KEY (ENID ASC, EID ASC),
);

-- table1
CREATE INDEX ix_Cdate on dbo.table1 (Cdate) WITH (FILLFACTOR=100);
CREATE CLUSTERED INDEX ix_Recid on dbo.table1 (Recid) WITH (FILLFACTOR=80);

-- table2
CREATE INDEX ix_ENID_id on dbo.table2 (ENID,id) WITH (FILLFACTOR=100);

Mudanças

Alterações que fiz e alguns números:

• Adicionada dica OPTION (QUERYTRACEON 4138)- avg. execução 7 segundos abaixo dos 50 segundos originais, mas a equipe do aplicativo parece não ter acesso para fazer isso no código. Precisa verificar mais sobre isso.

• OPTION (ORDER GROUP)deu os mesmos resultados de avg. 50 segundos, então não há melhora.

• Índice adicionado conforme sugerido:

CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);

Não há muitas melhorias lá. Média 45 segundos e o plano era semelhante ao anexo nesta questão (plano superior).

Antes e depois de cada teste, verifiquei se o plano não foi gerado a partir do plano anterior em cache.

Plano rápido

Anexar o plano que é mais rápido e sem nenhuma alteração nos dados ou consulta ainda é rápido para a mesma quantidade de linhas em ambas as tabelas. A equipe do aplicativo envia continuamente a consulta acima ao longo do dia para concluir o lote, completando os TOP 100. Há uma mudança de plano com base em algum número de gorjeta e abaixo está a aparência do bom plano:

Edit : - Com tudo inalterado, nenhuma alteração de código ou qualquer índice sendo adicionado, como sugerido quando estou tentando adicionar dica (FORCESEEK) está me dando o erro abaixo

O processador de consultas não pôde produzir um plano de consulta devido às dicas definidas nesta consulta. Reenvie a consulta sem especificar nenhuma dica e sem usar SET FORCEPLAN.