Contenção na TempDB (PFS e SGAM)
Em muitos dos casos que ando trabalhando, um problema recorrente que eu vejo é a contenção na TempDB. Vou mostrar como analisar e resolver esse problema. Vale notar que vou colocar os passos que eu fiz para diagnosticar e resolver o problema, mas cada um pode fazer do seu jeito, claro!
Problema e análise
Imagine o cenário:
Um cliente acabou de fazer um super upgrade na infra-estrutura do SQL Server, colocando uma storage poderosa, mais processadores e mais memória. Ele coloca o SQL Server todo feliz no novo servidor e na primeira segunda-feira com o servidor rodando ele começa a ver uma série de problemas acontecendo.
No caso que trabalhei, uma aplicação web podia ser vista com o número do contador “web service:Current ISAPI Extension Requests” batendo 255, que por padrão é o número máximo de threads disponíveis para atender as requisições do IIS. Quando esse número chega ao limite, o IIS começa a negar requisições e retornar erros.
Colocando o contador “Database > Transactions/sec” foi possível verificar que no momento em que o contador do IIS subia, as transações do SQL diminuíam... Locks.
Analisando a saída da sysprocesses no momento em que o problema aparecia, alguns waittypes 0x0046 eram exibidos pela tabela, e estes indicam que existem processos esperando para receber uma worker thread do UMS Scheduler. Normalmente isso acontece devido a bloqueios que fazem as threads não acabarem rapidamente seu serviço.
Dando uma rápida olhada nos waittypes and waitresources, eu vi um número excessivo de recursos iniciados por “2:”. Uma das maneiras em que os waitresources são exibidos é x:y:z. Onde x =banco de dados; y = arquivo; z = página. Em qualquer instalação do SQL Server, o banco de dados com id =2 sempre será o tempBD.
Nesse momento eu já imaginava um problema de contenção na tempdb, e para verificar eu criei o script abaixo e o deixei executando durante 30 minutos. Use o OSQL para disparar o script e capturar a saída em um arquivo texto.
WHILE 1=1
BEGIN
SELECT WaitResource, waittype, lastwaittype, status, count(spid) as Quantity
FROM MASTER..sysprocesses
WHERE WaitResource like '2:%'
AND LAST_BATCH > DATEADD(ss, -30, getdate())
GROUP BY WaitResource, waittype, lastwaittype, status
ORDER BY Quantity
WAITFOR DELAY '00:00:15'
END
O arquivo texto de saída mostrou no topo da lista as páginas 2:1:1 e 2:1:3, com o waittype PAGELATCH_UP. As primeiras páginas de um arquivo são sempre definidas na seguinte ordem: file header, PFS, GAM e SGAM (veja o BOL para mais informações). Portanto temos uma contenção na Page Free Space (PFS) e na Shared Global Allocation Map (SGAM), responsáveis por mapear a quantidade de espaço livre em uma página e se o extent está sendo utilizado como um extent misto ou uniforme.
O banco de dados tempDB é a área de trabalho do SQL Server, e é através dela que o SQL cria tabelas de trabalho para ordenação de dados, hash plans, agrupamentos, cursores, variáveis de tabela e tabelas temporárias, entre outros.
Cruzando as informações: se a tempdb é muito utilizada ela precisa criar muitas worktables, e para saber onde serão criados os novos objetos é necessário consultar e alterar as páginas PFS e SGAM, pois inicialmente é feita a alocação de 8 páginas em extents mistos, somente depois sendo alocados extents uniformes.
Solução
Atualmente o SQL somente possui um arquivo físico de dados por padrão, se forem criados mais três arquivos além das páginas 2:1:1 e 2:1:3, passarão a existir: 2:2:1 e 2:2:3; 2:3:1 e 2:3:3; 2:4:1 e 2:4:3; Assim a contenção que existia será divida em quatro arquivos, aumentando o throughput.
A alteração pode ser feita com o SQL Server rodando que ele passará a utilizar os novos arquivos. É interessante que os tamanhos dos arquivos sejam iguais, para que seja utilizada uma distribuição uniforme, estilo round robin.
FIX: Concurrency enhancements for the tempdb database
Explicando ao cliente
Agora só falta explicar para o cliente a pequena “contradição”, já que uma máquina mais poderosa trouxe um problema de contenção (um pouco estranho a primeira vista, concordo).
· Aumentando o poder da máquina, o SQL Server passou a processar muito mais trabalhos paralelamente, aumentando a utilização da TempDB e criando um ponto de contenção. Na verdade o problema já existia, mas como eram processadas menos consultas, a contenção não era tão grande e passou despercebida.
Leiam o KB sobre as melhorias dos aspectos de concorrência da TempDB, pois no próximo artigo vou analisar em detalhes o trace flag 1118.
Mais referências
- “Managing Extent Allocations and Free Space” no BOL
- Sample chapter from Inside SQL Server 2000 (https://www.microsoft.com/mspress/books/sampchap/4297a.asp) – achei isso por acaso!
[]s
Luti