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!

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

[]s

Luti

Comments

• Anonymous
  March 03, 2007
  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á

• Anonymous
  August 02, 2008
  function showDiv(post, lingua) { if (document.getElementById) { // DOM3 = IE5, NS6 document.getElementById(post

• Anonymous
  May 29, 2009
  PingBack from http://paidsurveyshub.info/story.php?title=luti-microsoft-conten-o-na-tempdb-pfs-e-sgam