O SQL Server encontrou ocorrências de solicitações de E/S demorando mais de 15 segundos

Isso é muito menos frequente um problema de disco e muito mais frequentemente um problema de rede. Você sabe, o N em SAN?

Se você for à sua equipe de SAN e começar a falar sobre os discos serem lentos, eles mostrarão um gráfico sofisticado com latência de 0 milissegundos e apontarão um grampeador para você.

Em vez disso, pergunte sobre o caminho da rede para a SAN. Obtenha velocidades, se for multipath, etc. Obtenha números deles sobre as velocidades que você deve ver. Pergunte se eles têm benchmarks de quando os servidores foram configurados.

Então você pode usar o Crystal Disk Mark ou diskpd para validar essas velocidades. Se eles não se alinharem, novamente, é mais provável que seja a rede.

Você também deve pesquisar no log de erros por mensagens que contenham "FlushCache" e "saturation", pois esses também podem ser sinais de contenção de rede.

Uma coisa que você pode fazer para evitar essas coisas como DBA é garantir que sua manutenção e quaisquer outras tarefas com muitos dados (como ETL) não estejam acontecendo ao mesmo tempo. Isso pode definitivamente colocar muita pressão na rede de armazenamento.

Você também pode verificar as respostas aqui para obter mais sugestões: Ponto de verificação lento e avisos de E/S de 15 segundos no armazenamento flash

Eu escrevi sobre um tópico semelhante aqui: From The Server To The SAN

Temos uma configuração semelhante e recentemente encontramos essas mensagens nos logs. Estamos usando um SAN Compellent da DELL. Aqui estão algumas coisas a serem verificadas ao receber essas mensagens que nos ajudaram a encontrar uma solução

• Revise os contadores de desempenho do Windows para os discos para os quais as mensagens de aviso estão apontando, especificamente:
  • Média de disco tempo de leitura
  • Média de disco tempo de escrita
  • Bytes de leitura de disco/s
  • Bytes de gravação de disco/s
  • Transferências de disco/s
  • Média comprimento da fila de disco
• Os acima são médias. Se você tiver muitos arquivos de banco de dados em uma unidade, essas médias podem distorcer o resultado e mascarar um gargalo em arquivos de banco de dados específicos. Confira esta consulta de Paul S. Randal que retorna a latência média para cada arquivo do dmv sys.dm_io_virtual_file_stats. No nosso caso, a latência média relatada foi aceitável, mas por baixo dos panos tínhamos muitos arquivos com latência média > 200 ms.
• Verifique os horários. Existe algum padrão? Acontece com mais frequência em determinado horário da noite? Em caso afirmativo, verifique se algum trabalho de manutenção está sendo executado naquele momento ou qualquer atividade agendada que possa aumentar a atividade do disco e expor um gargalo no subsistema de E/S.
• Verifique se há erros no visualizador de eventos do Windows. Se seu switch ou SAN estiver sobrecarregado ou não estiver configurado corretamente para seu aplicativo, você poderá encontrar algumas mensagens neste log, e é bom levar essas informações ao administrador da SAN. No nosso caso, estávamos recebendo erros de conexão iSCSI com frequência ao longo do dia, sugerindo o problema.
• Revise seu código do SQL Server. Ao receber essas mensagens, você não deve pensar imediatamente que é um problema do subsistema de E/S e passá-lo para o administrador da SAN. Você precisa fazer sua parte e revisar o banco de dados. Você tem consultas realmente ruins sendo executadas com frequência, passando por toneladas de dados? Indexação ruim? Gravações de log de transações excessivas? Você pode usar algumas consultas de código aberto para obter uma verificação de integridade em seu banco de dados, um exemplo para verificar a aparência do seu plano de consulta é sp_blitzCache
• Não ignore estes. Hoje você pode recebê-los algumas vezes por dia... e vários meses depois, quando sua carga de trabalho aumenta e você esquece de monitorá-los, eles começam a aumentar. Receber muitas dessas mensagens pode impedir que o SQL Server acesse um determinado arquivo e, se for tempdb , isso não é bom. No nosso caso, ficou tão ruim que o SQL Server se desligou.

Nossa solução foi atualizar nosso switch para um switch SAN. Sim, esses são todos os pontos a serem abordados no SQL Server. O que nos levou a descobrir que era o switch foi que estávamos recebendo cerca de 1.500 erros de desconexão de pdu iSCSI no visualizador de eventos do aplicativo Windows no SQL Server todos os dias. Isso levou à investigação de nossos administradores de SAN sobre o switch.

Imediatamente após a atualização, os erros de iSCSI desapareceram e a latência média caiu para cerca de 50 ms para todos os arquivos, o que se correlacionou a um melhor desempenho no aplicativo. Com esses pontos em mente, espero que você possa encontrar sua solução.

Por que armazenar os dados em uma SAN? Qual é o ponto? Todo o desempenho do banco de dados está vinculado à E/S de disco e você está usando 3 servidores com apenas um dispositivo para a E/S por trás deles. Isso não faz sentido... e infelizmente tão comum.

Um software de servidor SQL é um software muito avançado, projetado para tirar proveito de quaisquer bits de hardware, núcleos de CPU, cache de CPU, TLB, RAM, controladores de disco, cache de disco rígido... Eles quase incluem toda a lógica do sistema de arquivos. Eles são desenvolvidos em computador comum e comparados em sistemas de ponta. Portanto, um servidor SQL deve ter seus próprios discos. Instalá-los em uma SAN é como "emular" um computador, você perde todas as otimizações de desempenho. SANs são para armazenar backups, arquivos imutáveis ​​e arquivos aos quais você apenas anexa dados (logs).

Os administradores de datacenter tendem a colocar tudo o que podem em SANs porque dessa forma eles têm apenas um pool de armazenamento para gerenciar, é mais fácil do que cuidar do armazenamento em cada servidor. É uma escolha de "não quero fazer o meu trabalho", e muito ruim, porque aí eles têm que lidar com problemas de desempenho e toda a empresa sofre com isso. Basta instalar o software no hardware para o qual foi projetado. Mantenha simples. Cuide da largura de banda de E/S, sobrecarga de cache e alternância de contexto, jitter de recursos (ocorre quando o recurso é compartilhado). Você acabará mantendo 1/10 dos dispositivos para a mesma potência de saída bruta, economizará muitas dores de cabeça para sua equipe de operações, obterá desempenho que deixará seus usuários finais felizes e mais produtivos, tornará sua empresa um lugar melhor para trabalhar e economize muita energia (o planeta agradece).

Você disse nos comentários que está pensando em colocar SSD em seu servidor. Você não reconhecerá sua configuração com SSDs dedicados, em comparação com uma SAN você obterá algo como 500x de melhoria, mesmo com dados e arquivos de log de transações na mesma unidade. Um SQL Server de última geração teria um SSD separado rápido para dados e log de transações em diferentes canais de controladores de hardware (a maioria das placas-mãe do servidor possui vários). Mas comparado à sua configuração atual, estamos falando de ficção científica. Basta dar uma chance ao SSD.

Passei minha vida encontrando plataformas de hardware mal projetadas, onde as pessoas apenas tentam projetar um computador em grande escala. Todo o poder da CPU aqui, todos os discos ali... espero que não exista RAM remota. E o mais triste é que compensam a falta de eficiência desse design com servidores enormes que custam dez vezes mais do que deveriam. Eu vi $ 400k infra mais lento do que um laptop de $ 1k.

Ok, para quem se interessar,

Resolvemos o problema em questão há alguns meses simplesmente instalando unidades SSD conectadas diretamente em cada um dos 3 servidores e movendo dados de banco de dados e arquivos de log da SAN para essas unidades SSD

Aqui resumo sobre o que eu fiz para pesquisar sobre esse problema (usando recomendações de todos os posts desta questão), antes de decidirmos instalar unidades SSD:

1) começou a coletar contadores PerfMon para as seguintes unidades em todos os 3 servidores:

Disk F:é um disco lógico baseado em SAN, contém arquivos de dados MDF
Disk I:é um disco lógico baseado em SAN, contém arquivos de log LDF
Disk T:é um SSD diretamente conectado, dedicado exclusivamente ao tempDB

A imagem abaixo são os valores médios coletados por um período de 2 semanas

Disk I: (LDF)tem uma E/S tão pequena e a latência é muito baixa, então Disco I: pode ser ignorado
Você pode ver que Disk T: (TempDB)tem E/S maior em comparação com Disk F: (MDF), e tem latência muito melhor ao mesmo tempo - 0 ms

Obviamente, algo está errado com o disco F: onde os arquivos de dados residem, ele tem alta latência e fila média de gravação de disco, apesar de baixo IO

2) Latência verificada para bancos de dados individuais usando a consulta deste site

https://www.brentozar.com/blitz/slow-storage-reads-writes/

Poucos bancos de dados ativos no servidor primário tinham latência de leitura de 150-250 ms e latência de gravação de 150-450 ms
O que é interessante, os arquivos de banco de dados mestre e msdb tinham latência de leitura de até 90 ms, o que é suspeito devido ao pequeno tamanho de seus dados e baixa E/S - outra indicação de que algo está errado com SAN

3) Não houve horários específicos

Durante as quais mensagens "SQL Server encontrou ocorrências..." apareceram
Não houve manutenção ou ETL pesado em disco em execução quando essas mensagens foram registradas

4) Visualizador de eventos do Windows

Não mostrou nenhuma outra entrada que indique o problema, exceto "SQL Server encontrou ocorrências..."

5) Começou a verificar as 10 principais consultas

De sp_BlitzCache (cpu, leituras, etc.) e otimizando sempre que possível
Nenhuma consulta pesada de E/S super que geraria toneladas de dados e afetaria fortemente o armazenamento, embora
a indexação em bancos de dados seja OK, eu a mantenho

6) Não temos equipe SAN

Temos apenas 1 administrador de sistema que ajuda ocasionalmente
Caminho de rede para SAN - é multipath, cada um dos 3 servidores tem 2 cabos de rede que levam aos switches e depois à SAN, e deve ser de 1 Gigabyte / seg

7) Não houve resultados do CrystalDiskMark

Ou qualquer outro resultado de teste de benchmark de quando os servidores foram configurados, então não sei quais deveriam ser as velocidades, e não é possível fazer benchmark neste momento para ver quais são as velocidades atualmente, pois isso afetaria a Produção

8) Configure a sessão Extended Events no evento de checkpoint para o banco de dados em questão

A sessão XE ajudou a descobrir que durante as mensagens "SQL Server encontrou ocorrências...", o ponto de verificação acontecia muito devagar (até 90 segundos)

9) Log de erros do SQL Server

Continha entradas "FlushCache" "Saturação"
Elas deveriam aparecer quando o tempo do ponto de verificação para um determinado banco de dados excede as configurações do intervalo de recuperação

Os detalhes mostraram que a quantidade de dados que o checkpoint está tentando liberar é pequena e está demorando muito para ser concluída, e a velocidade geral é de cerca de 0,25 MB / s ... estranho

10) Por fim, esta imagem mostra o gráfico de solução de problemas de armazenamento:

Parece que simplesmente temos um "Problema de hardware: - Trabalhe com o administrador do sistema/fornecedor de hardware para corrigir qualquer configuração incorreta da SAN, drivers, controladores, firmware, etc., antigos/defeituosos."

Em outra pergunta "Ponto de verificação lento..." Ponto de verificação lento e avisos de E/S de 15 segundos no armazenamento flash Sean tinha uma lista muito boa de quais itens devem ser verificados em nível de hardware e software para solucionar problemas

Nosso administrador de sistema não pôde verificar todas as coisas da lista, então simplesmente optamos por lançar algum hardware nesse problema - não era nada caro

Resolução:

Encomendamos drives SSD de 1 TB e instalamos diretamente nos servidores

Como temos grupos de disponibilidade, migramos arquivos de dados de banco de dados de SAN para SSD em réplicas secundárias, depois fazemos failover e migramos arquivos no antigo primário Isso permitiu um tempo de inatividade total mínimo - menos de 1 minuto

Agora, cada servidor tem uma cópia local dos dados do banco de dados, e backups completos/dif/log são feitos na SAN mencionada
. reconstruções de índice, consultas etc. aumentaram significativamente

Quanto desempenho em termos de latência de E/S melhorou desde que migramos arquivos de banco de dados para SSD?

Para avaliar o impacto, use os logs de desempenho do Windows Performance Monitor 2 semanas antes da migração e 4 semanas após a migração:

Também abaixo está a comparação de estatísticas de latência de nível de banco de dados (utilizou estatísticas de arquivos virtuais capturados do SQL Server antes e depois da migração)

Resumo

Valeu a pena a migração de SAN para SSDs locais conectados diretamente
Ele teve um grande impacto na latência do armazenamento e melhorou bem mais de 90% em média (especialmente operações WRITE), e não temos mais picos de 20 a 50 segundos no IO

A mudança para o SSD local resolveu não apenas os problemas de desempenho de armazenamento, mas também a segurança de dados com a qual eu estava preocupado (se a SAN falhar, todos os 3 servidores perderão seus dados ao mesmo tempo)