Dado:
- Ambiente de produção
- Servidores de aplicativos usando o Hibernate
- SQL Server Studio Manager v17.5
- SQL Server 2016 em uma configuração de HAG em cluster
- SQL Servers NÃO têm o recurso Repositório de Consultas habilitado
- O autor desta pergunta é um engenheiro de software com conhecimento suficiente do SQL Server para ser categorizado como principalmente inofensivo
atualização 1
- Configurações de crescimento do banco de dados: Ilimitado, 1024.000 KB, somente dados
- instant_file_initialization_enabled - Sim
- is_auto_update_stats_async_on - Não
atualização 2
- O servidor tem 4 núcleos de CPU
- Há picos de tarefas em espera de mais de 3.000.000. Ainda não faço ideia do que sejam. Este deve ser o motivo dos grandes tempos de 'bloqueio'.
- Esses picos ocorrem a cada 10 ou 15 segundos. Eu tenho o seguinte gráfico atualizando uma vez por segundo:
O problema:
A raiz do problema é que, em momentos aparentemente aleatórios de um dia agitado, algumas consultas SQL expiram, no entanto, para os propósitos desta pergunta, estou interessado em saber se a captura de tela é indicativa de um problema em si. Talvez isso seja subjetivo, mas não tenho experiência com esse valor.
Ação:
As falhas em si não apontam diretamente para uma questão concreta e, portanto, estou reunindo evidências e tentando um processo de eliminação sempre que possível. Atualmente estou investigando se tempos de espera excessivos e uma 'tempestade perfeita' de consultas podem causar uma cascata de bloqueios e, portanto, um tempo limite de consulta.
Evidências Recolhidas:
- Várias consultas estão resultando em varreduras completas de índice ou varreduras completas de tabela.
- Várias capturas de tela com planos de execução mostrando varreduras de tabela. A inspeção superficial mostra que os índices existem - ainda não usados. Eu posso ser capaz de higienizar as capturas de tela se elas forem úteis.
- A captura de tela abaixo mostra um grande tempo de espera.
Pergunta:
Que outras informações ajudariam a determinar se os tempos de bloqueio e espera podem ser a causa dos tempos limite de consulta? Por exemplo, eu tenho a seguinte captura de tela do monitor de atividade do gerenciador de estúdio do sql server. O valor me pareceu surpreendente.
As esperas de bloqueio da captura de tela mostram 18.024.389 ms / s como o tempo de espera recente (médio) nos últimos minutos. Isso significa que para cada segundo de "tempo do relógio de parede", há 18.000 segundos (5 horas?!) de esperas de bloqueio acumuladas por consultas. Isso é tão tremendamente ruim que me pergunto se é apenas um bug na interface do usuário do Activity Monitor.
Dependendo de quantos núcleos o servidor possui e de quantas consultas estão sendo executadas simultaneamente, mesmo o número menor (2,5 segundos de esperas de bloqueio por segundo do tempo do relógio de parede) não é o ideal.
Essas esperas podem implicar em uma cadeia de bloqueio (você pode usar
sp_WhoIsActivepara identificar o bloqueador de leads e tentar corrigir por que está bloqueando tudo). Independentemente disso, eles definitivamente podem contribuir para esses tempos limite do lado do cliente que você descreveu - cada segundo que uma consulta aguarda bloqueios é um segundo que não está progredindo na consulta real que está sendo executada.