Eu tenho uma tabela grande (300 milhões de linhas), com cerca de 1 milhão de linhas adicionadas por dia. Estes são dados de série temporal, então eu tenho a seguinte configuração:
CREATE TABLE Readings (
Id INT PRIMARY KEY,
TrackerId INT,
DateProcessed DATETIME,
-- About 10 other columns of data
)
CREATE INDEX IX_TrackerId_DateProcessed
ON Readings (
TrackerId,
DateProcessed
) INCLUDE (
-- all the other columns
) WHERE (
TrackerId IS NOT NULL
)
Quando visualizamos os dados, geralmente executo algo como a seguinte consulta:
SELECT Latest.*
FROM Trackers
CROSS APPLY (
SELECT TOP 1 *
FROM Readings
WHERE TrackerId = Trackers.Id
ORDER BY DateProcessed DESC
) Latest
Isso costumava funcionar bem. Agora, leva uma eternidade para gerar o plano de consulta, mesmo quando eu digo explicitamente para usar o índice. A única solução que encontrei até agora é atualizar as estatísticas da tabela com o seguinte comando:
UPDATE STATISTICS Readings
Isso leva cerca de dez minutos para ser executado e, em seguida, a consulta de seleção é executada bem, mas apenas por um dia . Então eu tenho que atualizar as estatísticas novamente. Por que as estatísticas estão desatualizadas tão rapidamente e o que posso fazer para evitar isso?
Pontos que merecem destaque:
- Eu só acrescento à tabela, nunca atualizo ou excluo linhas
- Existem algumas linhas antigas com um valor nulo para o ID do rastreador, mas não serão adicionadas mais
- O banco de dados está sendo executado no nível de compatibilidade 14
- Não consigo habilitar o sinalizador de rastreamento 2371 no Azure SQL
Respostas adicionais para perguntas nos comentários
O plano de consulta estimado antes de atualizar as estatísticas
O plano de consulta estimado após a atualização das estatísticas
Os planos antes e depois de atualizar as estatísticas parecem idênticos para mim. O problema não é que estou recebendo um plano de consulta ruim, mas quanto tempo leva para obter o plano. Antes de atualizar as estatísticas, tive que esperar dois minutos pelo plano. Após atualizar as estatísticas, obteve o plano instantaneamente.
Enquanto escrevia isso, percebi que seria mais rápido esperar pela consulta e deixar o cache fazer o trabalho, em vez de atualizar as estatísticas, mas isso ainda não está resolvendo o problema. Essa consulta é executada de dentro do Entity Framework, com um tempo limite de 30 segundos, portanto, dois minutos ainda é muito tempo.
O tempo gasto na atualização automática das estatísticas é incluído no tempo de compilação. A atualização de estatísticas leva 10 minutos, e o tempo de compilação de uma consulta que aciona uma atualização automática de estatísticas inclui esse tempo - e é por isso que a consulta leva mais de 10 minutos de vez em quando.
O limite de modificação para uma atualização automática de estatísticas, de acordo com a documentação , é
Com seus números (300.000.000 linhas), você atingiu esse limite em 547.722 modificações.
Como 1.000.000 de linhas estão sendo adicionadas todos os dias, você provavelmente receberá uma atualização automática de estatísticas cerca de duas vezes por dia.
Como as estimativas da sua consulta são boas antes e depois da atualização das estatísticas, uma solução possível seria habilitar a
AUTO_UPDATE_STATISTICS_ASYNC
configuração do nível do banco de dados. Isso ainda permite que o SQL Server atualize as estatísticas automaticamente, mas é feito de forma assíncrona (para que as consultas não precisem esperar para acontecer):Observe que isso afeta todo o banco de dados, não apenas essa tabela/estatística.