Início / dba / Perguntas / 250649
Accepted
jitbit
Asked: 2019-10-10 02:23:54 +0800 CST

O SQL Server continua revertendo para o plano ineficiente (Verificação de índice clusterizado) uma vez por semana

  • 772

Tenho uma consulta muito simples:

INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
ORDER BY CommentID DESC

contra esta tabela:

CREATE TABLE [dbo].[Comments] (
    [CommentID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [CommentDate] datetime NOT NULL DEFAULT (getdate()),
    [UserID] int NULL ,
    [Body] nvarchar(max) NOT NULL,
--a couple of other int and bit cols, no indexes on them
)

Eu tenho um índice simples na UserIDcoluna (sem colunas incluídas) e tudo funciona bem e super-rápido.

Mas uma vez a cada 5-8 dias, vejo tempos limite nessa parte do aplicativo. Então vou investigar no Query Store e vejo que o servidor para de usar meu índice e reverte para uma estúpida "varredura em cluster". Remover a tabela temporária não ajuda.

por que, meu Deus, por quê???

Para resolver isso - redefino o cache do plano para essa consulta específica (apenas para registro, veja como faço isso)

select plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
where text like '%SELECT TOP 50 CommentID FROM hdComments%'
--blahblahblah skipped some code
DBCC FREEPROCCACHE (@plan_handle)

E depois volta a funcionar normalmente.

Planos de execução: lento rápido

Estou coçando a cabeça há dias... Alguma idéia?

sql-server index

3 respostas

  1. Best Answer

    Seu índice no UserID não é o ideal para essa consulta. Ele deixa o otimizador a opção de usá-lo e precisar de uma classificação adicional por CommentID ou varrer a tabela (para trás) para obter as linhas já classificadas por commentID e filtradas dinamicamente pela cláusula where e pelo operador top. Embora a coluna PK clusterizada esteja incluída em cada coluna não clusterizada, ela é apenas um ponteiro, portanto não pode ser usada para classificação.

    A melhor maneira de evitá-lo para uma consulta crítica como você descreve é ​​fornecer um índice ideal, para que o otimizador o escolha sempre. Com base nas informações que você forneceu, seu índice deve ser um índice composto não clusterizado em (UserID, CommentID DESC) Isso permitirá acesso direto às linhas de usuários e também as primeiras 50 linhas podem ser verificadas em ordem de CommentID, deixando-o o ideal escolha, independentemente de estatísticas e seletividade.

    O servidor SQL é inteligente o suficiente para perceber isso. Experimente... HTH

    • 4

  2. Se a resposta do SQLRaptor não funcionar para você, uma outra coisa drástica que você pode tentar é usar a dica de consulta FORCESEEK. Isso essencialmente força o otimizador a sempre usar um plano que faça uma busca de índice em vez de uma varredura de índice (se possível).

    Uma razão pela qual não é a primeira opção é porque limita o número de planos de consulta que o otimizador pode optar por usar e, em certos casos, apresentará erros ao dizer que não há plano disponível para essa dica de consulta. Normalmente, as dicas de consulta são mais uma correção de bandaid de último recurso (exceto em casos de borda específicos), mas sem dúvida menos drásticas do que executar o comando DBCC FREEPROCCACHE e provavelmente menos drásticas do que sempre recompilar a consulta com a dica OPTION RECOMPILE da resposta de KumarHarsh também.

    (Isso acabou sendo a melhor solução para um cenário específico que encontrei recentemente com uma tabela que tinha bilhões de registros e o otimizador estava sempre tentando usar uma verificação de índice clusterizado, mas havia um índice não clusterizado que era mais aplicável à consulta e foi realmente sempre mais rápido como uma busca.)

    Consulte a seção FORCESEEK do documento da Microsoft para obter mais informações: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

    • 4

  3. Eu já vi isso antes em um sistema mal projetado onde a chave primária era composta e uma coluna dela era atualizada com bastante frequência no dia em que foi criada. Isso levou à fragmentação do índice à noite (era um sistema 24 horas por dia, 7 dias por semana) horas antes que as recompilações de índice fossem agendadas. Nesse ponto, o SQL parou de usar a melhor consulta e diminuiu drasticamente, embora a consulta usando o PK real ainda fosse mais rápida. Quando os índices foram reconstruídos, é claro, o SQL voltou ao plano de consulta sensato.

    Uma solução para isso foi fornecer à consulta uma dica de índice. Você pode fazer isso por:

    select …..
from tableA a with(index(pk_tableA)) -- any table index allowed
inner join tableB b on b.Id = a.BId
where etc etc

    Não parecia uma boa solução - idealmente teríamos redesenhado a mesa e a maneira como ela foi usada - mas orçamentos.

    Nota (como Jonathon me lembra nos comentários) O índice foi reconstruído online em vez de offline. Isso precisa ser especificado no comando de reconstrução.

    O abaixo de MSSqlTips.com

    ALTER INDEX [IX_Test] ON [dbo].[Test] REBUILD WITH (ONLINE = ON);

    Esta opção não está disponível se:

    • o índice é um índice XML
    • o índice é um índice espacial
    • o índice está em uma tabela temporária local
    • o índice é agrupado e a tabela contém uma(s) coluna(s) de banco de dados LOB
    • o índice não está agrupado e o próprio índice contém uma(s) coluna(s) de banco de dados LOB

    Além disso, como menciona Denis Rubashkin, a opção online está apenas na versão corporativa do SQL Server.

    • 0

relate perguntas