Estamos usando o SQL Server 2008 R2 em um servidor Windows Server 2008.
Temos algumas funções principais que usam alguns procedimentos armazenados que possuem muitas subconsultas dentro dela.
Vou pegar um SP como exemplo; ao executar este SP sequencialmente leva cerca de 500ms, e estamos bem com isso (mais ou menos), ele tem principalmente consultas select, algumas na mesma tabela, também tem algumas tabelas temporárias criadas e preenchidas com outro SP.
Agora estamos no processo de testar essas funções principais em várias solicitações simultâneas e descobrimos imediatamente que o tempo de conclusão do SP salta muito rapidamente para segundos e aumenta...
depois de usar algumas sugestões , notei que mais de um spid está associado a vários bloqueios S e que alguns processos estão se bloqueando.
fora isso, não tenho certeza do que está tornando o desempenho tão ruim.
Não sei se esta descrição é muito vaga, adoraria dar mais informações se necessário, no final gostaria de saber onde você acha que devo procurar para resolver este problema.
EDITAR:
Aqui está uma parte do plano de execução, não parece bom...
Devo apenas adicionar os índices conforme especificado?
o que significam todas as seções de paralelismo no plano de execução de algumas consultas?
isso explica o problema de desempenho?
Observando os planos de consulta, o otimizador de consulta está implorando para que você adicione índices úteis às tabelas. Até que os planos de consulta sejam robustos, esqueça de olhar para bloqueios e E/S.
Para fazer sugestões sólidas de índices, você precisa nos fornecer uma imagem do esquema, todas as consultas e todos os índices existentes nas tabelas. Eu não aceitaria as sugestões de índice que faltam exatamente como são apresentadas. Eles, sem dúvida, ajudarão, mas podem não ser a melhor coisa a fazer (as sugestões geralmente não são ... inteligentes ... por falta de uma palavra melhor).
Os operadores de paralelismo significam que a consulta usou várias CPUs para processar a consulta em paralelo. Isso só é desejável quando você realmente precisa que o SQL Server faça um monte de trabalho - neste caso, o problema é que você está pedindo ao SQL Server para fazer muito trabalho e está fazendo o melhor que pode para satisfazer a consulta no menor tempo possível.
Com um pequeno número de linhas sendo retornadas de cada consulta com o que parecem ser predicados altamente seletivos, eu esperaria que essas consultas voassem com índices úteis aplicados (e nenhum paralelismo seria necessário).
Apenas na área visível, parece que as consultas 9 e 10 são praticamente as mesmas para aproximadamente 35% do custo total cada; relativamente falando, a varredura da tabela
Transactionsdeve ir para praticamente zero. Dependendo do que estiver fazendo, você poderá combinar essas duas consultas em uma.Se isso não for velocidade suficiente, existem outras técnicas envolvendo agregações pré-computadas, mas se você estiver meio que quase bem com 500 ms, não precisará ir tão longe com apenas um conjunto robusto de planos de consulta básicos. Se todas as consultas forem altamente seletivas, os índices sozinhos devem levá-lo facilmente ao intervalo < 5 ms.
Por fim, considere adicionar índices úteis às suas tabelas temporárias. Uma união de loops aninhados com uma varredura de tabela na entrada inferior pode ser bastante cara, dependendo dos dados envolvidos.