Tempos limite do SQLS - Altas leituras no Profiler

Question

Auditei um servidor SQLS2008 com o Profiler por um dia ... a sobrecarga não pareceu incomodar esse novo cliente que minha empresa possui. Eles estão usando um aplicativo herdado do VB6 como front-end. Eles estão passando por tempo limite quando o uso de RAM do SQLS é alto. Atualmente, o servidor está executando x64 sqls2008 em uma VM com quase 9 GB de RAM. A 'opção de memória máxima do servidor' do SQL Server está atualmente definida como 6 GB.

Coloquei os resultados do rastreamento em uma tabela e os consultei usando essa consulta.

SELECT TextData, ApplicationName, Lidas
FROM [TraceWednesday] WHERE textdata não é nulo e EventClass = 12            GROUP BY TextData, ApplicationName, leituras ORDER BY Lê DESC

Como eu esperava, alguns valores são muito altos.

Top Reads, in pages.
2504188
1965910
1445636
1252433
1239108
1210153
1088580
1072725

Estou correto em pensar que o top (2504188 páginas) é 20033504 KB, que é aproximadamente ~ 20'000 MB, 20GB? Essas consultas geralmente são executadas e podem levar algum tempo para serem executadas. Eventualmente, a RAM é usada por causa da engorda do cache, e os tempos limite ocorrem quando o SQL não consegue 'respingar' páginas no buffer pool. Custos aumentam. Estou correto no meu entendimento?

Eu li que eu deveria ajustar o T-SQL associado e criar índices apropriados. Obviamente, reduzir a E / S faria com que o SQL Server usasse menos RAM. Ou, talvez, isso possa retardar o processo de mastigar toda a RAM. Se muito menos páginas forem lidas, talvez tudo funcione muito melhor mesmo quando o uso for alto? (menos troca de tempo, etc.)

Atualmente, nossa única opção é reiniciar o SQL uma vez por semana quando o uso de RAM é alto, de repente os tempos limite desaparecem. O SQL respira novamente.

Eu tenho certeza que muitos DBAs estão nesta situação. Eu estou perguntando antes de começar a cavar todo o T-SQL ruim e colocar índices aqui e ali, existe algo mais que eu possa fazer? Qualquer conselho, exceto pelo que eu sei (não muito ainda ..)

Muito apreciado.
Leo.

    

Answer 1

Em geral, o SQL Server alocará a quantidade de memória permitida, sem usar o arquivo de paginação. Essa memória é usada como um cache para páginas de dados. (Há também um cache de plano e algumas outras coisas, mas acho que está fora do escopo desta questão.) Essa alocação tem o efeito de "esgotar" toda a memória livre no servidor e muitas vezes preocupa as pessoas que não são familiarizado com o SQL Server.

Não se preocupe com a quantidade de memória, preocupe-se com leituras de páginas físicas (que vêm do disco) e leituras de páginas lógicas (que vêm da memória. Listas de páginas lógicas não são tão problemáticas quanto leituras de páginas físicas porque ocorrem muito mais rapidamente, mas eles ainda demoram um tempo finito.Mesmo que você tivesse um terabyte de RAM e todo o seu banco de dados estivesse armazenado em cache na memória, ainda assim levaria um tempo finito para os processadores caçarem todos os seus dados. É sempre melhor se o SQL Server tiver menos dados para examinar, mesmo que seja totalmente armazenado em cache na RAM.

O acesso aos dados fica mais lento quando os dados não se encaixam na RAM e o servidor precisa lê-los no disco.

Na sua situação, parece que o SQL está lendo 20 GB de dados. Isso não caberá em 6 GB de cache de dados, portanto, o SQL Server reutilizará a memória carregando dados do disco por cima dos dados "antigos" que considera desnecessários. Se outra consulta, de outro usuário, aparecer 1 segundo depois, o SQL pode ter que voltar ao disco para reler os dados "antigos" de volta.

Ajustar consultas e melhorar a indexação deve fazer com que o SQL Server tenha menos dados para examinar, o que significa que é mais provável que os dados que devem ser examinados permaneçam no cache de dados, na RAM, e menos dados precisem ser lido a partir do disco, no caso de os dados necessários não estarem no cache de dados.

Aumentar a RAM ("jogar hardware no problema") também significa que há uma melhor chance de manter esses dados na RAM também, MAS você pode geralmente obter uma melhoria maior (talvez um fator de 10x ou mais, se você tem sorte e a indexação é muito ruim para começar - eu vi melhorias de 60x e melhor) do que você pode aumentar a quantidade de RAM disponível para dados de cache (geralmente é impossível aumentar a quantidade de RAM em mais de 2x ou 3x sem um novo servidor ou colocando muita tensão que não tenha sido planejada em seu ambiente de VM.) Concedido, geralmente é mais fácil / rápido conectar novos chips de RAM do que ajustar as consultas, mas algumas vezes você não tem alguma escolha.

Outra coisa que vale a pena fazer é certificar-se de que suas estatísticas de índice estão sendo atualizadas regularmente. Em ambientes sem DBA, essa manutenção costuma ficar de lado. Estatísticas desatualizadas podem fazer com que o mecanismo de consulta use planos ineficientes, e isso é particularmente visível / doloroso em tabelas grandes.

Uma última observação: Reiniciar o SQL Server irá liberar todos os dados do cache, e esses dados terão que ser recarregados a partir do disco conforme as consultas chegam depois que o SQL Server é reiniciado. Normalmente, isso significa que um SQL Server é mais lento após uma reinicialização, não mais rápido. Servidores SQL que são mais rápidos após uma reinicialização geralmente estão experimentando bloqueio excessivo (a reinicialização do servidor elimina todas as conexões bloqueadas e bloqueadas) ou eles têm visto uma tempestade de consultas que estão fazendo com que ele leia muitos e muitos dados do disco (Muitas vezes, essas consultas são causadas por usuários com problemas que reenviam a mesma consulta várias vezes, com alguns segundos ou minutos, porque as coisas estão "demorando demais").