Início / dba / Perguntas / 170729
Accepted
Professional Sounding Name
Asked: 2017-04-12 07:12:00 +0800 CST

O plano de consulta inválida do SQL Server 2016 bloqueia o banco de dados uma vez por semana

  • 772

Uma vez por semana, nas últimas 5 semanas, na mesma hora do dia (de manhã cedo, pode ser baseado na atividade do usuário quando as pessoas estão começando a usá-lo), o SQL Server 2016 (AWS RDS, espelhado) começa a expirar muito consultas.

UPDATE STATISTICS em todas as tabelas sempre corrige isso imediatamente.

Após a primeira vez, fiz com que atualizasse todas as estatísticas em todas as mesas todas as noites (em vez de semanalmente), mas ainda aconteceu (cerca de 8 horas após a atualização das estatísticas, mas não todos os dias).

Na última vez, habilitei o Repositório de Consultas para ver se conseguia encontrar qual era o plano de consulta/consulta específico. Acho que consegui resumir em um:

Plano de consulta incorreto

Depois de encontrar essa consulta, adicionei um índice recomendado que estava faltando nessa consulta não usada com frequência (mas que toca em muitas tabelas usadas com frequência).

O plano de consulta ruim estava fazendo uma varredura de índice (em uma tabela com apenas 10 mil linhas). Outros planos de consulta que retornavam em milissegundos costumavam fazer a mesma varredura. O plano de consulta mais recente, após criar o novo índice, faz apenas buscas. Mas mesmo sem esse índice, 99% das vezes, ele estava retornando em alguns milissegundos, mas depois, semanalmente, demorava > 40 segundos.

Isso começou a acontecer depois de mudar para o SQL Server 2016 a partir de 2012.

DBCC CHECKDB não retorna erros.

  1. O novo índice resolverá o problema, fazendo com que ele nunca mais escolha o plano ruim?
  2. Devo "forçar" o plano que funciona bem agora?
  3. Como posso garantir que isso não aconteça com outra consulta/plano?
  4. Isso é um sintoma de um problema maior?

Índices que acabei de adicionar:

CREATE NONCLUSTERED INDEX idx_AppointmetnAttendee_AttendeeType
ON [dbo].[AppointmentAttendee] ([UserID],[AttendeeType])

CREATE NONCLUSTERED INDEX [idx_appointment_start] ON [dbo].[Appointment]
(
    [ProjectID] ASC,
    [Start] ASC
)
INCLUDE (   [ID],
    [AllDay],
    [End],
    [Location],
    [Notes],
    [Title],
    [CreatedByID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Texto completo da consulta:

https://pastebin.com/Z5szPBfu (gerado por LINQ, posso/devo ser capaz de otimizar as colunas selecionadas, mas deve ser irrelevante para este problema)

sql-server execution-plan

1 respostas

  1. Best Answer

    Vou responder suas perguntas em uma ordem diferente da que você fez.

    4. Isso é um sintoma de um problema maior?

    O novo estimador de cardinalidade no SQL Server 2016 pode estar contribuindo para o problema. O SQL Server 2012 usa o CE herdado e você não teve seu problema nessa versão. O novo estimador de cardinalidade faz suposições diferentes sobre seus dados e pode gerar planos de consulta diferentes para o mesmo SQL. Você pode experimentar um desempenho melhor para algumas consultas com o CE legado, dependendo de sua consulta e seus dados. Portanto, algumas partes do seu modelo de dados podem não ser a melhor combinação para o novo CE. Tudo bem, mas você pode precisar contornar o novo CE por enquanto.

    Eu também estaria preocupado com o desempenho inconsistente da consulta, mesmo com atualizações diárias de estatísticas. Uma coisa importante a ser observada é que a coleta de estatísticas em todas as tabelas eliminará efetivamente todos os planos de consulta do cache, portanto, você pode ter um problema com as estatísticas ou pode ter a ver com a detecção de parâmetros. É difícil fazer uma determinação sem muitas informações sobre seu modelo de dados, taxa de alteração de dados, políticas de atualização de estatísticas, como você está chamando seu código etc. O SQL Server 2016 oferece algumas configurações de nível de banco de dados para detecção de parâmetros que podem ser úteis , mas isso pode afetar todo o seu aplicativo em vez de apenas uma consulta problemática.

    Vou jogar fora um cenário de exemplo que pode levar a esse comportamento. Você disse:

    Alguns usuários podem ter 1 registro de permissão, alguns, até 20k.

    Suponha que você colete estatísticas em todas as tabelas que eliminem todos os planos de consulta. Dependendo dos fatores mencionados acima, se a primeira consulta do dia for contra um usuário com apenas 1 registro de permissão, o SQL Server pode armazenar em cache um plano que funciona bem para usuários com 1 registro, mas funciona muito mal com usuários com 20 mil registros. Se a primeira consulta do dia for contra um usuário com 20 mil registros, você poderá obter um bom plano para 20 mil registros. Quando o código é executado em um usuário com 1 registro, pode não ser a consulta ideal, mas ainda pode terminar em ms. Realmente soa como sniffing de parâmetros. Isso explica por que você nem sempre vê o problema ou por que às vezes leva horas para aparecer.

    1. O novo índice resolverá o problema, fazendo com que ele nunca mais escolha o plano ruim?

    Acho que um dos índices que você adicionou evitará o problema porque acessar os dados necessários por meio do índice será mais barato do que fazer uma verificação de índice clusterizado na tabela, especialmente quando a verificação não puder ser encerrada antecipadamente. Vamos ampliar a parte ruim do plano de consulta:

    plano de consulta ruim

    O SQL Server estima que apenas uma linha será retornada da junção em [Permission]e [Project]. Para cada linha na entrada externa, ele fará uma varredura de índice clusterizado no [Appointment]. Todas as linhas serão verificadas nesta tabela, mas apenas aquelas que corresponderem à filtragem [Start]serão retornadas ao operador de junção. Dentro do operador de junção, os resultados são ainda mais reduzidos.

    O plano de consulta descrito acima pode funcionar se realmente houver apenas uma linha enviada para a entrada externa da junção. No entanto, se a estimativa de cardinalidade da junção estiver errada e obtivermos, digamos, 1.000 linhas, o SQL Server fará 1.000 varreduras de índice clusterizado em arquivos [Appointment]. O desempenho do plano de consulta é muito sensível a problemas de estimativa.

    A maneira mais direta de nunca mais obter esse plano de consulta seria criar um índice de cobertura na [Appointment]tabela. Algo como um índice [ProjectId]e [Start]deve fazê-lo. Parece que este é exatamente o [idx_appointment_start]índice que você criou para resolver o problema. Outra maneira de desencorajar o SQL Server de escolher o plano de consulta é corrigir a estimativa de cardinalidade da junção [Permission]e [Project]. As maneiras típicas de fazer isso incluem alterar o código, atualizar estatísticas, usar o CE herdado, criar estatísticas de várias colunas, fornecer ao SQL Server mais informações sobre variáveis ​​locais, como uma RECOMPILEdica, ou materializar essas linhas em uma tabela temporária. Muitas dessas técnicas não são uma boa abordagem quando você precisa de tempo de resposta em nível de ms ou precisa escrever código por meio de um ORM.

    O índice que você criou [AppointmentAttendee]não é uma maneira direta de resolver o problema. No entanto, você obterá estatísticas de várias colunas no índice e essas estatísticas podem desencorajar o plano de consulta ruim. O índice pode fornecer uma maneira mais eficiente de acessar os dados, o que também pode desencorajar o plano de consulta ruim, mas não acho que haja qualquer tipo de garantia de que isso não acontecerá novamente apenas com o índice em [AppointmentAttendee].

    3. Como posso garantir que isso não aconteça com outra consulta/plano?

    Entendo por que você está fazendo essa pergunta, mas é extremamente ampla. Meu único conselho é tentar entender melhor a causa raiz da instabilidade do plano de consulta, validar se você tem os índices corretos criados para sua carga de trabalho e testar e monitorar cuidadosamente sua carga de trabalho. A Microsoft tem alguns conselhos gerais sobre como lidar com regressões de plano de consulta causadas pelo novo CE no SQL Server 2016:

    O fluxo de trabalho recomendado para atualizar o processador de consultas para a versão mais recente do código é:

    1. Atualize um banco de dados para o SQL Server 2016 sem alterar o nível de compatibilidade do banco de dados (mantenha-o no nível anterior)

    2. Habilite o repositório de consultas no banco de dados. Para obter mais informações sobre como habilitar e usar o repositório de consultas, consulte Monitorando o desempenho usando o repositório de consultas.

    3. Aguarde tempo suficiente para coletar dados representativos da carga de trabalho.

    4. Altere o nível de compatibilidade do banco de dados para 130

    5. Usando o SQL Server Management Studio, avalie se há regressões de desempenho em consultas específicas após a alteração do nível de compatibilidade

    6. Para casos em que há regressões, force o plano anterior no repositório de consultas.

    7. Se houver planos de consulta que não sejam forçados ou se o desempenho ainda for insuficiente, considere reverter o nível de compatibilidade para a configuração anterior e, em seguida, acionar o Suporte ao Cliente da Microsoft.

    Não estou dizendo que você precisa fazer o downgrade para o SQL Server 2012 e começar de novo, mas a técnica geral descrita pode ser útil para você.

    2. Devo "forçar" o plano que funciona bem agora?

    Depende inteiramente de você. Se você acredita que tem um plano de consulta que funciona bem para todos os parâmetros de entrada possíveis, está confortável com a funcionalidade do repositório de consultas e quer a tranquilidade de forçar um plano de consulta, então vá em frente. Afinal, forçar planos de consulta que tiveram regressões faz parte da política de atualização recomendada da Microsoft para o SQL Server 2016.

    • 17

relate perguntas