Estou tendo contenção de tempdb (eu acho, pelo menos) em um dos servidores (SQL Server 2008 R2).
Tanto o bloqueador quanto os garçons (10 deles) têm o tipo de espera PAGELATCH_EX no banco de dados tempdb e o recurso de espera de todas as sessões (bloqueador e garçons) é "2:1:122".
Verifiquei os códigos SQL e vi que as variáveis de tabela são criadas na maioria das sessões e suspeito que isso possa ser o culpado.
Qualquer sugestão sobre como diagnosticar melhor e aliviar esse problema é bem-vinda.
PAGELATCH_XX - Isso indica que o SQL está aguardando o acesso a uma página do banco de dados, mas a página não está passando por E/S física. Essa classe de problema é causada por um grande número de spids tentando acessar a mesma página física ao mesmo tempo. O wait_resource é o número da página (o formato é dbid:file:pageno) que está sendo acessada. Se estiver diagnosticando em um servidor ativo, os relatórios do Painel de desempenho executarão DBCC PAGE e exibirão a saída para informar a qual objeto e tipo de página se destina a contenção (alocação, dados, texto, ...)
Como as páginas que o SQL está esperando com mais frequência estão no banco de dados tempdb (número da página em dbid 2), você pode estar enfrentando uma disputa de trava de alocação de tempdb.
A contenção de trava de página de alocação de tempdb pode ocorrer com cargas de trabalho que criam e destroem objetos temporários (incluindo tabelas de trabalho/arquivos de trabalho para operações de classificação ou hash) centenas ou milhares de vezes por segundo.
Resolução -
1.Implemente o sinalizador de rastreamento -T1118.
2. Aumente o número de arquivos de dados no tempdb para maximizar a largura de banda do disco e reduzir a contenção nas estruturas de alocação. Como regra geral, se o número de processadores lógicos for menor ou igual a 8, use o mesmo número de arquivos de dados como processadores lógicos. Se o número de processadores lógicos for maior que 8, use 8 arquivos de dados e, se a contenção continuar, aumente o número de arquivos de dados em múltiplos de 4 (até o número de processadores lógicos) até que a contenção seja reduzida a níveis aceitáveis ou faça mudanças na carga de trabalho/código.