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 (http://www.microsoft.com/mspress/books/sampchap/4297a.asp) – achei isso por acaso!


         Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005


 


 


[]s


Luti

Comments (3)

  1. laertejunior says:

    Ltui, antes de mais nada, quero dizer que suas explicações são muito boas, pois incluem a pratica nelas, com os scripts. Seu blog é uma de minhas fonte de estudo.

    1 – Não entendi quando vc disse que tinha

    contenção na Page Free Space (PFS) e na Shared Global Allocation Map (SGAM) com base na informação "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. " Poderia explicar melhor ?

    2 – A alteração que vc fez foi a criação de mais arquivos fisicos para a TEMPDB correto ? Mas ficaram todos no mesmo disco ? Eu li um artigo dizendo que é interssante ter o nro de arquivos fisicos para a TEMPDB conforme o nro de processadores da Maquina. Mas retorno a pergunta, sendo na mesmo disco adianta ?

    3 – Aproveitando a deixa, no servidor de desenvolvimento eu achei estranho uma situação..olhando a sysprocesses muitos writelog e oledb para um determinado DB, pouquissimas vezes com waittypes acima , mas muitos writelogs. Olhei no dbcc sqlperf e a informação sobre o writelog (diga-se que o servidor fazia 2 dias que tinha sido restartado), estava altíssima. O recovery é simple. Mas as consultas começaram a ficar mais lentas. O que essas informações podem me dizer ?

    Agraeço desde já

  2. function showDiv(post, lingua) { if (document.getElementById) { // DOM3 = IE5, NS6 document.getElementById(post