Rastreamento de alterações causando contenção de trava

Estou usando o Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) 15 de junho de 2019 23:15:58 Copyright (c) Microsoft Corporation Standard Edition (64 bits) no Windows Server 2012 R2 Standard 6.3 (Construção 9600:)

O banco de dados tem cerca de 870 GB de tamanho. É SQL Standard e tenho 128 GB de RAM no servidor. O banco de dados está em unidades SSD. O arquivo de dados está em uma unidade diferente do arquivo de log e o Tempdb também possui sua própria unidade SSD. O servidor recebe cerca de 1200 consultas/segundo em média, podendo chegar a 2000 consultas/segundo. A recompilação permanece baixa, apenas 1 a 8 por segundo. A expectativa de vida da página é boa, com média de 61 minutos.

O servidor possui 6 núcleos físicos + hyper threads.

Estamos usando muito o rastreamento de alterações do SQL Server em um sistema em que milhares de dispositivos se conectam e tentam sincronizar as alterações com a chave de rastreamento.

Geralmente roda bem, mas depois, de vez em quando, um dia ou outro, os travamentos do servidor disparam, passando de 0ms para uma média de 60677ms.

Quando verifico quais consultas estão em execução, só vejo as consultas de sincronização, todas bloqueadas, com "PAGELATCH_UP", todas tentando acessar as tabelas de controle de alterações, mais de 300 consultas bloqueadas.

Eu tenho algumas perguntas:

• O SQL Server bloqueia a tabela inteira enquanto procura alterações no controle de alterações?
• Teria melhores resultados com o SQL Entreprise ou não muda nada?
• Alguma ideia de por que o rastreamento de alterações funciona bem na maioria das vezes, mas trava sem motivo aparente de semana para semana?

Esses são o tamanho da minha tabela de rastreamento de alterações. As tabelas que minha consulta bloqueia são as três primeiras, apenas alguns mb de dados.

Eles estão todos esperando no mesmo waitresource.

Waitresource 2:4:88968 está no tempdb. Mas o tempdb é responsável apenas por cerca de 9% das gravações do servidor e 6% das leituras.

Mas minha consulta não usa o tempdb, então acho que é assim por causa da maneira interna como o rastreamento de alterações funciona? Aqui está minha consulta

DECLARE @Id INT; SET @Id = (SELECT Id FROM Users WHERE No=@No); 

SELECT DISTINCT lh.Key1 
FROM ( 
    SELECT Key1 FROM CHANGETABLE(CHANGES dbo.Table1, @TrackingKey) AS CT 
    UNION ALL 
    SELECT Key1 
    FROM dbo.Table2 lhd 
    INNER JOIN (SELECT Key2 FROM CHANGETABLE(CHANGES dbo.Table2, @TrackingKey) AS CT) AS CTLHD ON(CTLHD.Key2=lhd.Key2) 
    UNION ALL 
    SELECT Key1 
    FROM CHANGETABLE(CHANGES dbo.Table3, @TrackingKey) AS CT 
) AS L 
JOIN dbo.Table1 lh ON lh.Key1 = L.Key1 
WHERE lh.Id = @Id AND lh.Date BETWEEN @StartUtc AND @EndUtc 

Meu tempdb tem 10 arquivos e eles são do mesmo tamanho.

O que geralmente acabo fazendo para que o cliente volte ao normal é colocá-lo em tempo de inatividade, depois colocá-lo de volta aos poucos para que todos os dispositivos móveis consigam sincronizar pouco a pouco. Mas nosso sistema é de missão crítica e esta não é uma solução de longo prazo.

Outra solução que tenho pensado é mudar a forma como o sistema funciona com as consultas de controle de alterações. Faça com que o dispositivo móvel sincronize com uma tabela "feita em casa" e preencha essa tabela com uma única alteração de leitura de serviço do rastreamento de alterações. Dessa forma, limitarei as consultas simultâneas às tabelas de controle de alterações, mas temo que apenas mova o problema para a tabela feita em casa.

Algum pensamento sobre isso? Qualquer ajuda será apreciada.

EDITADO: Bloqueador de cabeça

Tentei determinar quem é o bloqueador de cabeça e o que está esperando, mas é uma tarefa difícil. Parece que tenho muitos "bloqueadores de cabeça".

Todas as consultas estão executando o mesmo SELECT, quase todas divididas em 4 threads, para algumas consultas elas não estão bloqueadas, mas aguardando "MISCELLANEOUS", mas para algumas consultas, pelo menos parte das threads são bloqueadas por outras consultas.

Por exemplo, agora, 294 tópicos estão sendo exibidos.

A consulta 202 é dividida em 4 threads e uma de suas threads está bloqueada por 123, mas as outras threads não estão bloqueadas. Os três encadeamentos estão aguardando em "MISCELLANEOUS" e o encadeamento bloqueado está aguardando em "PAGELATCH_UP"

Quanto à consulta 123, ela não está bloqueada, são 4 threads aguardando "DIVERSOS"

Ou, por exemplo, a consulta 219 é bloqueada pela consulta 140 em um encadeamento e bloqueada por 69 nos outros três encadeamentos.

69 está bloqueado por 193 e 193 está em execução, aguardando "MISCELLANEOUS" novamente. 140 não está mais na lista, por isso expirou ou foi concluído.

Meu limite de custo para paralelismo é 70.
Bloqueios 0
Grau máximo de paralelismo 3
Espera de consulta -1

O nível de isolamento do instantâneo não está habilitado no banco de dados. As consultas não estão usando o nível de isolamento de instantâneo.

Também verifiquei as estatísticas nas tabelas e até nas tabelas sys.change_tracking. Os índices nas tabelas não são fragmentados (menos de 10%) para as tabelas consultadas.

Executei uma ou duas consultas e geralmente o resultado da consulta é de 4 linhas, que se tornam apenas uma linha por causa da cláusula DISTINCT. Portanto, não é como se estivesse retornando milhares de linhas.

Quando executo a consulta no SSMS, ela é rápida e não bloqueia, mesmo que atualmente veja centenas de consultas bloqueadas no servidor na mesma consulta. Então eu acho que pode estar relacionado ao sniffing de parâmetros, talvez?

Aqui estão as estatísticas de E/S quando executo no SSMS.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 92 ms, elapsed time = 92 ms.
Table 'Users'. Scan count 0, logical reads 2, physical reads 0, read-ahead  reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscommittab'. Scan count 3, logical reads 555, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'change_tracking_62623266'. Scan count 1, logical reads 3364, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 0, logical reads 34281, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'change_tracking_46623209'. Scan count 1, logical reads 1152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'change_tracking_78623323'. Scan count 1, logical reads 1077, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 296 ms,  elapsed time = 435 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Completion time: 2020-01-22T14:18:25.9480651-05:00

Aqui está meu plano de consulta https://www.brentozar.com/pastetheplan/?id=By7k-7UWI

Mas, novamente, essa é a versão que não bloqueia. Eu tenho o repositório de consultas habilitado para que eu pudesse tentar obter a versão que bloqueia, só não estou 100% como fazê-lo.

EDITADO: QUERY STORE INFO

Minha consulta é a primeira nas "consultas regredidas" do Query Store.

De acordo com a loja Query, aqui está o "plano ruim" https://www.brentozar.com/pastetheplan/?id=ryqKGQUbL

E aqui está o "bom plano" https://www.brentozar.com/pastetheplan/?id=rknnGQ8WL

Devo apenas "forçar" o bom plano?

EDITADO Minha solução

Ok, então eu usei o sp_blitzcache de Brent Ozar ( https://www.brentozar.com/blitz/ ) com "expert_mode" em 1 para poder recuperar o identificador do "plano ruim" e poder limpá-lo de cache (sem limpar mais nada).

DBCC FREEPROCCACHE (0x06000800155A5106F08F632F1C00000001000000000000000000000000000000000000000000000000000000);

Meu servidor voltou ao estado normal novamente e todas as centenas de consultas bloqueadas desapareceram. Eu acho que é sniffing de parâmetros então? Esperando que não aconteça novamente. Gostaria de encontrar uma maneira para que isso não aconteça novamente.