contenções no sgbd microsoft sql server 2008 r2

CONTENÇÕES NO SGBD MICROSOFT SQL SERVER 2008 R2
ROMULO RUBENS CUNHA JUNIOR 1
IREMAR NUNES DE LIMA 2
Resumo: Este artigo aborda os tipos de contenções que podem ocorrer no Sistema
Gerenciador de Banco de Dados Microsoft SQL Server 2008 R2. Estas contenções geram
waits (esperas) e afetam diretamente o desempenho de uma instância SQL Server. Será
mostrado como um Administrador de Banco de Dados (DBA) pode identificar estas
contenções e o que deve fazer para minimizar o problema.
Palavras-chave: SQL Server 2008, Banco de Dados, Contenção, Waits.
1 INTRODUÇÃO
Existem vários tipos de contenções que podem ocorrer no SQL Server como, por exemplo:
contenções de I/O, por concorrência, de rede e por configurações incorretas.
As contenções podem trazer vários problemas para aos DBAs pelo o fato de nem sempre
serem de fácil resolução e ter um custo relativamente alto. Por isso, sempre que se pensar em
adquirir um SQL Server deve-se fazer um levantamento de tudo que ira precisar desde a parte
de Hardware até as instâncias que serão instaladas e suas configurações, evitando assim o
1 Especialista em Banco de Dados e Business Inteligence ([email protected]).
2 DBA, mestre em informática e professor do Centro Universitário Newton Paiva ([email protected]). máximo possível de contenções devido a um Banco de Dados mal instalado ou por causa de
uma configuração errada no servidor. As contenções não acontecem apenas na instalação do
banco, elas podem acontecer em vários momentos como, por exemplo, em códigos SQLs mal
formulados.
Desta forma, é importante investigar os waits de um servidor de banco de dados, para
conseguir evitar as contenções que podem ocorrer e para saber o que fazer quando o tempo de
contenção é muito grande ou até mesmo quando acontece o travamento do sistema. Este
artigo aborda alguns tipos de contenções que podem acontecer no SQL Server 2008.
2. TIPOS DE CONTENÇÃO (WAIT)
Existem três tipos de contenções que podem acontecer em um banco de dados. as esperas de
recursos, as esperas de fila e as esperas externas (MSDN, 2011).
•
Esperas de recursos: ocorrem quando é solicitado um acesso a um recurso que no
momento está sendo utilizado e por isto não está disponível ou ainda não foi
disponibilizado. Alguns exemplos que se pode dar de esperas de recurso são: bloqueios
(locks) e espera de I/O de disco.
•
Esperas de fila: acontecem quando uma tarefa está inativa, pois está esperando que lhe
seja atribuído a algum trabalho. Este tipo de tarefa normalmente é vista como tarefas de
segundo plano de sistema, como por exemplo monitor de deadlocks e tarefas de limpeza.
Essas tarefas aguardam as solicitações para serem colocadas em uma fila de trabalho. Este
tipo de espera pode ficar ativo periodicamente mesmo que nenhum outro novo pacote
tenha sido colocado na fila.
•
Esperas Externas: ocorrem quando o SQL Server está aguardando o término de um
evento externo como o extended stored procedures call (chamada de procedimento de
armazenamento estendido) ou o Linked Server Query (Consulta de servidor vinculada).
Lembrando que quando é diagnosticado um problema desse tipo de espera, nem sempre
quer dizer que a tarefa está parada, pois ele pode estar executando algum código externo.
3. ENCONTRANDO WAITS NO SQL SERVER 2008 R2
Sempre que uma query roda e acontece algum tipo de espera, o SQL Server registra os
detalhes sobre o tipo de espera que podem ser observados através da view
SYS.DM_OS_WAITS_STATS. Com esses detalhes pode-se examinar a causa de todas as
contenções. Existem vários tipos diferentes de contenções e a cada atualização do SQL Server
são lançados alguns novos tipos de waits, por isso, é sempre importante enxugar um pouco
quando se vai procurar pelas esperas mais altas. O comando a seguir mostra as dez esperas
que está acontecendo numa instância SQL Server 2008 ordenado por tempo de espera.
Um exemplo de resposta seria:
Como resultado foi listado o tipo de espera classificando-o pelo tempo total. Na pesquisa no
banco de dados apareceu o tipo LOGMGR_QUEUE com o tempo mais alto. O SQL Server
possui como recurso uma procedure para limpar o conteúdo das waits, conforme mostra
figura a seguir, o que se torna excelente para uma análise comparativa. Isto permite ver com
mais segurança onde está acontecendo o maior tipo de contenção.
4. TIPOS MAIS COMUNS DE WAITS
A
seguir
será
analisado
os
tipos
de
contenções
mais
encontradas
na
view
SYS.DM_OS_WAIT_STATS.
4.1 Locks (Bloqueios)
Os LCK_XX são waits que estão relacionados com bloqueios e travamentos. Quando existe
uma contenção muito grande nestes tipos de waits, significa que alguma coisa está impedindo
a execução eficiente dos mecanismos de travamento da engine de armazenamento. Isto pode
acontecer devido às transações estarem muito grandes ou por uma falta de índices adequados,
o que resultaria no bloqueio ou na leitura de mais linhas do que necessário, aumentando assim
o tempo de duração e chances de bloqueios. A configuração de hardware mal feita pode
contribuir para uma espera muito alta no LCK_XX, pois cada ação levará mais tempo para
poder ser concluída. MSQL_XP acontece quando o SQL Server esta a espera de uma store
procedure finalizar, isto pode indicar algum erro no código da sua procedure (ANDREW,
2011).
4.2 Latchs (Travas)
O LATCH_xx e o PAGELATCH_xx são waits que não estão relacionados as atividades
físicas do I/O. As waits LATCH_XX são típicas contenções de recursos internos ou outra
estrutura de buffer pool, especialmente quando estiver usando tipos de dados (text) ou heaps
(pilhas). Comparar o problema associando outros tipos de waits geralmente é importante ao
invés de tratar os LATCHs sozinhos. PAGELATCH_XX são waits que estão ligados
diretamente à contenção na memória cache, incluindo o buffer pool. Uma espera muito alta
pode estar relacionada a vários problemas. Um problema comum é uma pequena quantidade
de memória ou uma memória mal distribuída, o que causa uma grande contenção para páginas
que estão no cache. Nestes casos o ideal é fazer a distribuição correta da memória ou
adicionar mais memória. Outro problema que pode ocorrer é um grande número de inserções
no mesmo intervalo de páginas, especialmente quando resulta em uma divisão de páginas
(split). O problema pode ser ainda maior se houverem muitos SELECTs no mesmo intervalo
de páginas o que é difícil de acontecer devido a exigência de um número muito grande de
transações por segundo. Para resolver este problema tem que se fazer alguns ajustes nos
índices. A PAGELATCH_UP é uma wait que acontece quando uma tarefa está esperando por
desbloqueio de buffer. Geralmente acontece no banco de dados TEMPDB quando este está
muito sobrecarregado. Pode-se resolver este problema criando mais arquivos de dados para o
TEMPDB, o que reduziria a espera ou até mesmo a eliminaria (ANDREW, 2011).
4.3 Rede
Muitas pessoas acham que os problemas de contenção de rede estão relacionados a problemas
físicos da estrutura de rede entre o cliente e o servidor, o que não é correto, apesar de afetar o
tempo de espera do NETWORKIO. O que acontece geralmente é uma falha do próprio
cliente. Por exemplo, se o cliente não consegue processar os resultados tão rápido quanto o
SQL Server, ele começará a observar este tipo de espera. O que também pode ocasionar isto é
quando se tem um aplicativo mal feito rodando e o hardware fica sobrecarregado do lado do
cliente (ANDREW, 2011).
4.4 I / O
As waits IO_COMPLETION, WRITELOG, PAGEIOLATCH_XX e LOGMGR são tipos de
esperas que se encaixam na categoria de I/O. A IO_COMPLETION significa que o sistema
está esperando I/O para a tarefa poder finalizar. A WRITELOG acontece quando está
aguardando a transação de log ser concluída. O que pode causar isto é a freqüência dos pontos
de
verificações
(CHEKPOINT)
e
confirmações
de
transações
(COMMITS).
As
PAGEIOLATCH_XX são waits específicas relativas a transferências do disco para memória,
e quase sempre indica problemas com o subsistema de disco. O subsistema não está
conseguindo acompanhar o volume de solicitações de I/O LOGMGR: este tipo de wait
acontece quando uma tarefa está a espera de algum log pendente de I/O antes de finalizar o
log e fechar o banco de dados. Quando estão envolvidos alguns desses waits é um forte
indicador que o gargalo é no I/O . Temos também o LOGMGR_QUEUE que ocorre quando a
tarefa
de
gravador
de
log
aguarda
uma
solicitação
de
trabalho.
O
LOGMGR_RESERVE_APPEND acontece quando está esperando para verificar se o
truncamento de log vai liberar espaço para os logs, para assim poder ser gravado novos
registros de log (MSDN, 2011).
Existem algumas estratégias para poder reduzir a maioria destas esperas:
•
Ajustar suas consultas para lerem menos dados satisfazendo os pedidos.
•
Aumentar
o
tamanho
do
arquivo
de
log
para
evitar
a
espera
do
LOGMGR_RESERVE_APPEND.
•
Colocar os arquivos de log e de dados em unidades diferentes, para não haver qualquer
tipo de competição para a gravação dos mesmos nas unidades.
•
Alterar os tipos e configurações do dispositivo RAID para melhorar o desempenho (por
exemplo, adicionar mais discos, mudar a matriz de RAID 5 e RAID 10).
4.5 Paralelismo
O wait CXPACKET acontece quando tem um processamento paralelo que não está sendo
eficiente. Acontece quando o SQL Server está aguardando um ou mais processos paralelos se
completarem ou iniciarem. Os valores indicam que não se pode tirar proveito do uso de
múltiplos processadores ou de muitos processadores em paralelos. Se o tempo de espera do
CXPACKET for muito grande a ponto de se tornar um problema deve-se considerar reduzir o
grau de paralelismo (RANDAL, 2011).
5. CONCLUSÃO
As contenções (waits) podem acontecer a todo o momento no SQL Server 2008 R2, o que
pode ocasionar muita lentidão em todo banco de dados. Neste artigo foi mostrado a query
SQL para poder encontrar os waits que estão causando maior contenção no banco de dados e
aqueles que acontecem com maiores freqüência no Microsoft SQL Server 2008 R2.
Cada tipo de contenção tem uma maneira de ser tratada: o ideal é saber qual wait está
acontecendo para assim poder tratá-la de maneira adequada.
REFERÊNCIAS
ANDREW, J. KELLY. Dissecting SQL Server’s Top Waits Disponível em
<http://www.sqlmag.com/article/performance/dissecting-sql-server-s-top-waits> Acessado em
27de maio de 2011
COLLAGE,Rod. SQL Server 2008: Administration In Action. 74°. Greenwich: Ed.
MANNING, 2010.
KNOWS, PEARL by Robert Pearl. SQL Server Waits and Queues Disponivel em
<http://www.sqlservercentral.com/blogs/pearlknows/archive/2010/01/15/sql-server-waitsand-queues.aspx> acessado em 25 de maio de 2011
MSD Magazine, Microsoft. Encontrar dados ocultos para otimizar o desempenho do
aplicativo Disponível em <http://msdn.microsoft.com/pt-br/magazine/cc135978.aspx>
Acessado em 27 de maio de 2011
MSDN,
MICROSOFT
sys.dm_os_wait_stats(Transact-SQL)
Disponível
em
<http://msdn.microsoft.com/pt-br/library/ms179984.aspx> acessado em 28 de maio de 2011
RANDAL, PAUL S. Wait statistics, or please tell me where it hurts. Disponível em
<http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-ithurts.aspx> Acesso em 27 de maio de 2011
SQL Server 2008. Disponível em: <http://social.msdn.microsoft.com>. Acesso em: 22 abril
2011.