Minha empresa usa um aplicativo que apresenta problemas de desempenho bastante importantes. Há uma série de problemas com o próprio banco de dados que estou trabalhando, mas muitos dos problemas são puramente relacionados ao aplicativo.
Em minha investigação, descobri que existem milhões de consultas no banco de dados do SQL Server que consultam tabelas vazias. Temos cerca de 300 tabelas vazias e algumas dessas tabelas são consultadas até 100-200 vezes por minuto. As tabelas não têm nada a ver com a nossa área de negócios e são essencialmente partes do aplicativo original que o fornecedor não removeu quando foi contratado pela minha empresa para produzir uma solução de software para nós.
Além do fato de suspeitarmos que nosso log de erros do aplicativo está sendo inundado com erros relacionados a esse problema, o fornecedor nos garante que não há impacto no desempenho ou na estabilidade do aplicativo ou do servidor de banco de dados. O log de erros é inundado na medida em que não podemos ver mais de 2 minutos de erros para fazer diagnósticos.
O custo real dessas consultas obviamente será baixo em termos de ciclos de CPU, etc. Mas alguém pode sugerir qual seria o efeito no SQL Server e no aplicativo? Eu suspeitaria que a própria mecânica de enviar uma solicitação, confirmá-la, processá-la, devolvê-la e confirmar o recebimento pelo aplicativo teria um impacto no desempenho.
Usamos SQL Server 2008 R2, Oracle Weblogic 11g para o aplicativo.
@Frisbee- Para encurtar a história, criei uma tabela contendo o querytext que atingiu as tabelas vazias no banco de dados do aplicativo e, em seguida, consultei todos os nomes de tabelas que sei que estão vazios e obtive uma lista muito longa. O maior sucesso foi de 2,7 milhões de execuções em 30 dias de tempo de atividade, tendo em mente que o aplicativo geralmente é usado das 8h às 18h, portanto esses números estão mais concentrados no horário operacional. Várias tabelas, várias consultas, provavelmente algumas relevantes por meio de junções, outras não. O maior sucesso (2,7 milhões na época) foi uma simples seleção de uma única tabela vazia com uma cláusula where, sem junções. Eu esperaria que consultas maiores com junções às tabelas vazias pudessem incluir atualizações para tabelas vinculadas, mas verificarei isso e atualizarei esta pergunta o mais rápido possível.
Atualização: Existem 1000 consultas com uma contagem de execução entre 1043 - 4622614 (mais de 2,5 meses). Terei que cavar mais para descobrir de onde o plano em cache se origina. Isso é apenas para lhe dar uma ideia da extensão das consultas. A maioria é razoavelmente complexa com mais de 20 junções.
@srutzky- sim, acredito que haja uma coluna de data relacionada a quando o plano foi compilado para que seja interessante, então vou verificar isso. Eu me pergunto se os limites de thread seriam um fator quando o SQL Server fica em um cluster VMware? Em breve será um Dell PE 730xD dedicado, felizmente.
@Frisbee - Desculpe pela resposta tardia. Como você sugeriu, executei um select * da tabela vazia 10.000 vezes em 24 threads usando SQLQueryStress (na verdade, 240.000 iterações) e atingi 10.000 solicitações de lote/s imediatamente. Em seguida, reduzi para 1.000 vezes em 24 threads e atingi pouco menos de 4.000 solicitações de lote/seg. Também tentei 10.000 iterações em apenas 12 threads (portanto, 120.000 iterações no total) e isso produziu 6.505 lotes/s sustentados. O efeito na CPU foi realmente perceptível, cerca de 5-10% do uso total da CPU durante cada execução de teste. As esperas de rede foram insignificantes (como 3 ms com o cliente em minha estação de trabalho), mas o impacto da CPU estava lá com certeza, o que é bastante conclusivo no que me diz respeito. Parece resumir-se ao uso da CPU e um pouco de arquivo de banco de dados desnecessário IO. O total de execuções/segundo chega a pouco menos de 3.000, o que é mais do que em produção, porém estou testando apenas uma das dezenas de consultas como esta. O efeito líquido de centenas de consultas atingindo tabelas vazias a uma taxa entre 300 e 4.000 vezes por minuto, portanto, não seria desprezível quando se trata de tempo de CPU. Todos os testes foram feitos em um PE 730xD inativo com dual flash array e 256 GB de RAM, 12 núcleos modernos.
@srutzky- bom pensamento. SQLQueryStress parece usar o pool de conexões por padrão, mas eu dei uma olhada de qualquer maneira e descobri que sim, a caixa para pool de conexões está marcada. Atualização a seguir
@srutzky- O pool de conexões aparentemente não está ativado no aplicativo - ou, se estiver, não está funcionando. Fiz um rastreamento do profiler e descobri que as conexões têm EventSubClass "1 - Nonpooled" para eventos de login de auditoria.
RE: Pool de conexão - verifiquei o weblogics e encontrei o pool de conexão ativado. Executei mais rastreamentos ao vivo e encontrei sinais de agrupamento que não estava ocorrendo corretamente/de forma alguma:
E aqui está o que parece quando executo uma única consulta sem junções em uma tabela preenchida; as exceções diziam "Ocorreu um erro relacionado à rede ou específico da instância ao estabelecer uma conexão com o SQL Server. O servidor não foi encontrado ou não estava acessível. Verifique se o nome da instância está correto e se o SQL Server está configurado para permitir conexões remotas. (provedor: Provedor de pipes nomeados, erro: 40 - Não foi possível abrir uma conexão com o SQL Server)" Observe o contador de solicitações em lote. Fazer ping no servidor durante o tempo em que as exceções são geradas resulta em uma resposta de ping bem-sucedida.
Atualização - duas execuções de teste consecutivas, mesma carga de trabalho (selecione*fromEmptyTable), pool ativado/desativado. Um pouco mais de uso da CPU e muitas falhas e nunca ultrapassa 500 solicitações em lote/seg. Os testes mostram 10.000 lotes/s e nenhuma falha com o pool ativado, e cerca de 400 lotes/s e muitas falhas devido à desativação do pool. Será que essas falhas estão relacionadas a falta de disponibilidade de conexão?
@srutzky- Selecione Count(*) em sys.dm_exec_connections;
Pooling ativado: 37 de forma consistente, mesmo após o teste de carga parar
Pooling desabilitado: 11-37, dependendo se as exceções estão
ocorrendo ou não no SQLQueryStress, ou seja: quando essas depressões aparecem no
gráfico de Lotes/seg, as exceções ocorrem no SQLQueryStress e o
número de conexões cai para 11, depois volta gradualmente para 37 quando os lotes começam a atingir o pico e as exceções não estão ocorrendo. Muito, muito interessante.
Conexões máximas em ambas as instâncias de teste/ativas definidas no padrão de 0.
Verifiquei os logs do aplicativo e não consegui encontrar problemas de conectividade, no entanto, há apenas alguns minutos de log disponíveis devido ao grande número e tamanho dos erros, ou seja: muitos erros de rastreamento de pilha. Um colega do suporte de aplicativos informa que ocorre um número substancial de erros de HTTP relacionados à conectividade. Com base nisso, parece que, por algum motivo, o aplicativo não está agrupando corretamente as conexões e, como resultado, o servidor está ficando sem conexões repetidamente. Vou examinar mais os logs do aplicativo. Gostaria de saber se existe uma maneira de provar que isso está acontecendo na produção do lado do SQL Server?
@srutzky- Obrigado. Vou verificar a configuração do weblogic amanhã e atualizar. Eu estava pensando nas meras 37 conexões - se SQLQueryStress está fazendo 12 threads em 10.000 iterações = 120.000 instruções select não agrupadas, isso não deveria significar que cada select cria uma conexão distinta com a instância sql?
@srutzky- Weblogics estão configurados para agrupar conexões, então deve estar funcionando bem. O pool de conexões é configurado assim, em cada um dos 4 weblogics com balanceamento de carga:
- Capacidade Inicial: 10
- Capacidade Máxima: 50
- Capacidade Mínima: 5
Quando eu aumento o número de threads que executam a seleção da consulta de tabela vazia, o número de conexões atinge o pico em torno de 47. Com o pool de conexões desabilitado, vejo consistentemente um número máximo de solicitações de lote por segundo (de 10.000 para cerca de 400). O que acontecerá sempre é que as 'exceções' em SQLQueryStress ocorrem logo após os lotes/s entrarem em um vale. Está relacionado à conectividade, mas não consigo entender exatamente por que isso está acontecendo. Quando nenhum teste está sendo executado, #connections cai para cerca de 12.
Com o pool de conexões desativado, estou tendo problemas para entender por que as exceções ocorrem, mas talvez seja uma outra pergunta/pergunta stackExchange para Adam Machanic?
@srutzky Eu me pergunto então por que as exceções ocorrem sem o pooling ativado, mesmo que o SQL Server não esteja ficando sem conexões?
Sim, e existem até alguns fatores adicionais, mas o grau em que qualquer um deles está realmente afetando seu sistema é impossível dizer sem analisar o sistema.
Dito isto, você está perguntando o que poderia ser um problema, e há algumas coisas a serem mencionadas, mesmo que algumas delas não sejam atualmente um fator em sua situação particular. Você diz que:
Pode até haver mais, mas isso deve ajudar a ter uma noção das coisas. E lembre-se de que, como a maioria dos problemas de desempenho, tudo é uma questão de escala. Todos os itens mencionados acima não são problemas se forem atingidos uma vez por minuto. É como testar uma alteração em sua estação de trabalho ou no banco de dados de desenvolvimento: sempre funciona com apenas 10 a 100 linhas nas tabelas. Mova esse código para produção e leva 10 minutos para ser executado, e alguém certamente dirá: "bem, funciona na minha caixa" ;-). Ou seja, é apenas devido ao grande volume de chamadas feitas que você está vendo um problema, mas essa é a situação que existe.
Portanto, mesmo com 1 milhão de consultas de linha inúteis e 0, isso equivale a:
mais conexões sendo mantidas que ocupam mais memória. Quanta RAM física não utilizada você tem? essa memória seria melhor usada para executar consultas e/ou cache do plano de consulta. Na pior das hipóteses, você está sem memória física e o SQL Server precisa começar a usar a memória virtual (swap), pois isso torna as coisas mais lentas (verifique o log de erros do SQL Server para ver se você está recebendo mensagens sobre a memória sendo paginada).
E apenas no caso de alguém mencionar, "bem, há um pool de conexões". Sim, isso definitivamente ajuda a reduzir o número de conexões necessárias. Mas com consultas chegando até 200 vezes por minuto, isso é muita atividade simultânea e conexões ainda precisam existir para as solicitações legítimas. Faça um
SELECT * FROM sys.dm_exec_connections;para ver quantas conexões ativas você está mantendo.Se não estou errado sobre o que venho afirmando aqui, então me parece que, mesmo que em pequena escala, este é um tipo de ataque DDoS ao seu sistema, pois está inundando a rede e seu SQL Server com solicitações falsas , evitando que solicitações reais cheguem ao SQL Server ou sejam processadas pelo SQL Server.
Se as tabelas estão sendo atingidas 100-200 vezes por minuto, então elas estão (espero) na memória. A carga no servidor é muito, muito baixa. A menos que você tenha alta CPU ou memória no servidor de banco de dados, isso provavelmente não é um problema.
Sim, as consultas aceitam bloqueios compartilhados, mas esperamos que não estejam bloqueando nenhum bloqueio de atualização nem sendo bloqueadas por nenhum bloqueio de atualização. Você tem alguma atualização, inserção ou exclusão nessas tabelas. Caso contrário, eu simplesmente deixaria para lá - se você está tendo problemas de desempenho, deve haver peixes maiores para fritar do ponto de vista do servidor de banco de dados.
Fiz um teste em 100.000 select count(*) em uma tabela vazia e ele foi executado em 32 segundos e as consultas foram feitas em uma rede. Então 1/3 milissegundo. A menos que sua rede esteja ficando sobrecarregada, isso nem afeta o cliente. Se você estiver tendo grandes problemas de desempenho, essas consultas em branco de 1/3 milissegundos não são o que está matando o aplicativo.
E isso pode ser apenas parte de uma junção à esquerda, capturando alguns dados do tipo estático que não fazem parte do aplicativo atual. Ele pode ser encadeado com outras consultas, portanto, não é uma ida e volta extra. Se sim, é desleixado, mas nem está causando mais tráfego.
Então, voltemos a olhar para as declarações reais. Você está vendo alguma atualização, adição ou exclusão nessas tabelas?
Sim, muitas tabelas vazias e consultas a tabelas vazias são indicação de codificação desleixada. Mas se você está tendo grandes problemas de desempenho, esta não é a causa, a menos que você tenha algumas operações de gravação realmente desleixadas também acontecendo com essas tabelas.
Em geral, em cada consulta, as seguintes etapas são realizadas:
muitas consultas como você mencionou podem causar carga extra em um sistema que já é pesado - carga extra em conexões, CPU, RAM e E/S.