Artigo SQL Magazine 03 - Otimização e Tunning – Parte II

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (0)  (0)

Artigo da Revista SQL Magazine -Edição 3.

Nessa edição daremos continuidade as dicas sobre performance, enfatizando rotinas de reindexação e configurações globais dos servidores SQL Server 2000.

Um caso típico de fragmentação

Com o passar do tempo, as tabelas tendem a adquirir fragmentação – os dados que inicialmente ficavam próximos se tornam “espaçados”. Como analogia, imagine aquele caderninho de telefones, desses que todo mundo tem. Sempre que iniciamos um caderninho novo, temos a mesma idéia em mente: serei organizado o bastante para que esse caderninho não vire uma bagunça. Com o passar do tempo, percebemos que algumas páginas estão praticamente vazias (não existem muitos nomes inciando com a letra “Z”), outras quase totalmente cheias (letra “P”) e várias páginas em fase de “transbordamento” (letra “A”,”C”, etc), sendo necessário o aproveitamento das folhas em branco para acomodar tantos nomes. Um dia, a situação torna-se insustentável – simplesmente não conseguimos achar de maneira eficiente o que procuramos, pois somos obrigados a folhear diversas páginas, num leva-e-traz que consome tempo e paciência. Por fim, decidimos aposentar o atual caderno, substituindo-o por outro. Resta-nos “passar a limpo” todos os endereços.

Num database os problemas não são muitos diferentes; precisamos periodicamente “passar a limpo” as páginas de dados, eliminando a fragmentação. Vejamos como isso acontece.

Conceitos sobre armazenamento de dados

No SQL Server 2000, o armazenamendo é feito em estruturas físicas conhecidas como “páginas”. Páginas constituem a unidade básica de I/O, possuem tamanho fixo de 8KB e são exclusivas para cada objeto (duas tabelas não podem compartilhar a mesma página). Por questões de otimização, páginas são agrupadas em unidades lógicas denominadas “extents”. Uma extent corresponde a 8 páginas (64KB) e normalmente é a unidade utilizada para alocação de espaço para tabelas e índices. Observe que extents são alocadas para um mesmo tipo de página (veja tabela 1); dessa forma, páginas de dados e de índices são alocadas em extents distintas.

Uma extent pode ser compartilhada por mais de um objeto (extents mistas); normalmente um objeto nasce, cresce até 8 páginas em extents mistas, e passa para extents exclusivas. Os principais tipos de páginas encontram-se relacionadas na tabela 1.

Tabela-1: Principais tipos de páginas encontradas num database

Tipo de Página

Função

Data

Armazenam dados de tipos diferentes de text, ntext e image

Index

Chaves dos índices, com ponteiros direcionando para as páginas de dados

Text and Image

Armazenam dados do tipo text, ntext e image

Page Free Space (PFS)

Controla o espaço livre nas páginas.

Global Allocation Map (GAM)

Controla a alocação de extents pelos objetos.

Shared Global Allocation Map (SGAM)

Controla a alocação de extents mistas pelos objetos.

Index Allocation Map (IAM)

Controla as extents utilizadas por “heap tables”ou índices. Todo objeto no momento de sua criação é registrado numa página IAM e em pelo menos uma extent mista.

Tabelas constituem a base do modelo relacional para o armazenamento de informações. São formadas por registros que estão fisicamente alocados em páginas que por sua vez estão alocadas (logicamente) em extents. O tamanho de um registro não pode exceder o tamanho de uma página.

NOTA: Na verdade uma página não comporta um registro de 8192 bytes (=8Kb). Desse montante, devem ser descontados 96 bytes destinados à header da página e 36 bytes para controles de log, resultando em 8060 bytes. Desses 8060 bytes, ainda devem ser descontados 60 bytes para controles internos de colunas de tamanho variável (varchar, nvarchar), chegando então em 8000 bytes.

Registros podem ser gravados de maneira ordenada ou aleatória. Para que os registros sejam gravados fisicamente de forma ordenada (por exemplo, em ordem de nome na tabela “Clientes”), é necessário a construção de um índice especial, conhecido por cluster. O índice cluster é a própria tabela, não existindo portanto uma estrutura à parte para guardar informações relativas a ordenação. Em virtude dessa característica particular, tabelas podem conter somente um índice cluster. Tabelas sem índice cluster são conhecidas tecnicamente por “heaps”.

Por padrão uma página de dados não possui textos ou imagens. Conforme ilustrado na tabela 1, existem páginas específicas para esses tipos de dados. O campo destinado a imagem armazena um ponteiro informando a página inicial onde reside o objeto. Esse mecanismo traz dois benefícios: o primeiro diz respeito à otimização, pois a separação torna o processo de leitura mais eficiente. O segundo diz respeito ao tamanho, pois uma estrutura à parte permite armazenar imagens até um limite de 2GB (várias páginas podem ser alocadas para um único objeto).

O SQL Server 2000 permite, através da opção “text in row”, que sejam gravados imagens ou textos na própria página de dados. Se a maior parte de seus campos BLOB é constantemente acessada e possui tamanho inferior a 8Kb, é possível ganhar performance habilitando essa opção. A linha de comando a seguir ativa a opção de armazenamento de imagens de até 512 bytes na própria página de dados:

exec sp_TableOption Clientes,’text in row’, 512

Este exemplo não limita as imagens a 512 bytes, apenas libera a utilização de páginas de dados para armazenamento até esse tamanho. Imagens que excedam 512 bytes serão armazenadas em páginas específicas, de forma padrão.

Páginas de tabelas com índice cluster são ligadas umas às outras através de informações contidas na header da página (por exemplo, na header da página 1567 estarão identificadas as páginas 1566 e 1568). Em heaps, as páginas alocadas são registradas nas estruturas IAM, sem ordenação prévia. Para varrer uma tabela com índice cluster, o SQL Server 2000 acessa a página inicial, registrada na tabela de sistema sysindexes. Em seguida, as informações contidas na header de cada página direcionam ao restante da leitura. Para heaps, o roteiro de leitura é efetuado através das páginas IAM, num leva-e-traz que, para leituras sequenciais, torna-se menos eficiente.

Fragmentação X Performance: quem vence a batalha?

Agora que conhecemos como o SQL Server 2000 estrutura seus dados, vamos às causas da fragmentação:

  • Ocorrência de “page splits”, termo utilizado para designar uma divisão de página de índice, cluster ou não cluster, para acomodar uma inserção pontual (Figura 1);

Figura 1: Inserção na tab.Clientes, causando split de página

Inserção na tabela clientes

  • Deleção de registros, causando maior espaçamento entre os dados (Figura 2);

    Figura 2: Deleção na tabela Clientes, causando fragmentação

    Deleção de registros

  • Com o crescimento simultâneo das tabelas, é muito provável que as extents pertencentes a uma mesma tabela não estejam alocadas em espaços contíguos.

A recuperação de dados fragmentados requer maior esforço de I/O, portanto devemos trabalhar no sentido de minimizar este problema. O SQL Server 2000 fornece o comando DBCC ShowContig para análise da fragmentação em índices. Sua sintaxe básica é:

DBCC ShowContig (,)

Onde :

  • pode ser obtido pelo comando object_id()
  • pode ser obtido através de query na tabela de sistema sysindexes. Exemplo:

select indid from sysindexes where id=object_id(-da-tabela>)

A sintaxe completa do comando DBCC ShowContig pode ser obtida em http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_46cn.asp

Na listagem 1 vemos o resultado do comando DBCC ShowContig sobre a tabela fictícia “Clientes”, considerando o id do índice cluster igual a 1.

Listagem 1: Execução do comando DBCC ShowContig na tabela “Clientes”

DBCC SHOWCONTIG scanning 'Clientes' table...
 Table: 'Clientes' (1227151417); index ID: 1, database ID: 21
 TABLE level scan performed.
 - Pages Scanned................................: 318
 - Extents Scanned..............................: 45
 - Extent Switches..............................: 54
 - Avg. Pages per Extent........................: 7.1
 - Scan Density [Best Count:Actual Count].....: 72.73% [40:55]
 - Logical Scan Fragmentation ..................: 24.53%
 - Extent Scan Fragmentation ...................: 95.56%
 - Avg. Bytes Free per Page.....................: 784.8
 - Avg. Page Density (full).....................: 90.30%
 DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 

O resultado desse comando é interpretado da seguinte forma:

  • Pages Scanned: número de páginas que compõem o índice analisado;
  • Extents Scanned: número de extents; é aproximadamente o resultado da divisão de Pages Scanned por 8 (lembre-se que existem extents mistas);
  • Extents Switches: total de troca (páginas que deveriam estar numa mesma extent estão distribuídas em várias extents). Em condições ideais, deve possuir um valor próximo de Extents Scanned;
  • Avg.Pages per Extent: número médio de páginas por extent. Deve ser próximo de 8;
  • Scan Density [Best Count:Actual Count]: densidade das páginas - quanto mais próximo de 100%, melhor. Um valor igual a 75% indica 25% de fragmentação.
  • Logical Scan Fragmentation: percentual de fragmentação de páginas utilizado SOMENTE para tabelas que possuem índice cluster.
  • Extent Scan Fragmentation: percentual de fragmentação de extents utilizado SOMENTE para tabelas que possuem índice cluster.
  • Avg. Bytes Free per Page: número médio de bytes livres por página; quanto mais próximo de zero melhor *
  • Avg. Page Density (full): densidade (ou preenchimento) médio das páginas; quanto mais próximo de 100% melhor*

(*) Note que espaço livre nas páginas de índice nem sempre é um mal sinal. Embora requeiram esforço adicional de I/O para recuperação dos dados, evitam os custosos page splits que acontecem nos momentos de inserção, pelo fato das páginas não estarem completamente preenchidas. O SQL Server 2000 permite ainda que, na criação ou reestruturação de um índice, seja especificado um percentual de “reserva” para as páginas (conhecido como “fill factor”), diminuindo a incidência de page splits. Esse recurso deve ser avaliado com cautela, pois se por um lado diminui a ocorrência de page splits, por outro aumenta o custo de I/O, pois deixa espaço vago nas páginas do índice. Um fator de preenchimento de 70, indica que a página será preenchida até 70% do tamanho, deixando 30% para inserções pontuais.

Os comandos a seguir especificam duas formas de setar 70% para fill factor:

DBCC dbReindex (‘Clientes’,1,70)
 <p align="left">Create NonClustered Index IX_Clientes on Clientes (Nome) with FillFactor=70

A luta contra dados fragmentados só pode ser combatida com processos de manutenção nos índices, que discutiremos a seguir.

Crie jobs para reindexação periódica de suas tabelas

Uma estratégia fundamental para ganho de performance consiste na reestruturação periódica dos índices. A tabela 2 mostra três maneiras para realizar essa tarefa.

Tabela 2:

Modo

Avaliação

Drop /

Create Index

O inconveniente é manter o script atualizado para recriar todos os índices de um database.

DBCC dbReindex

Encapsula um Drop/Create para todos os índices da tabela, simplificando a rotina de reindexação. Se alguma falha acontecer, a estrutura anterior dos índices será mantida. Atua na desfragmentação INTERNA e EXTERNA, já que reconstrói todas as páginas do índice. Possui a desvantagem de estabelecer bloqueios longos.

DBCC IndexDefrag

Elimina a fragmentação INTERNA nas páginas do índice (não realoca extents). Possui a vantagem de estabelecer bloqueios curtos, sendo possível executá-lo em ambiente de produção.

O batch na listagem 2 executa o comando DBCC dbReindex em todas as tabelas do database corrente. A sintaxe completa dos comandos pode ser obtida em:

Reindex:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_30o9.asp

IndexDefrag:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_30o9.asp

listagem 2: Batch para reindexar todas as tabelas de um database

set nocount on
 DECLARE tabelas CURSOR fast_forward 
 FOR select name from sysobjects where type = 'u'
 DECLARE @nome varchar(80)
 OPEN tabelas
 FETCH NEXT FROM tabelas INTO @nome
 WHILE (@@fetch_status <> -1)
 BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
              select '[][][] Reindexando a tabela: '+@nome
              exec( 'dbcc dbreindex ( ''' + @nome + ''')')
      END
     FETCH NEXT FROM tabelas INTO @nome
 END
 CLOSE tabelas
 DEALLOCATE tabelas

Após a execução da rotina de reindexação, aplique o comando DBCC ShowContig para a mesma tabela Clientes (listagem 3). Compare a execução atual com a listagem 2.

Listagem 3: DBCC ShowContig na tabela “Clientes” após reindexação

DBCC SHOWCONTIG scanning 'Clientes' table...
 Table: 'Clientes' (1227151417); index ID: 1, database ID: 21
 TABLE level scan performed.
 - Pages Scanned................................: 289
 - Extents Scanned..............................: 37
 - Extent Switches..............................: 36
 - Avg. Pages per Extent........................: 7.8
 - Scan Density [Best Count:Actual Count]....: 100.00% [37:37]
 - Logical Scan Fragmentation ..................: 2.08%
 - Extent Scan Fragmentation ...................: 13.51%
 - Avg. Bytes Free per Page.....................: 51.4
 - Avg. Page Density (full).....................: 99.37%
 DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Se a reindexação de todas as tabelas for custosa (devido ao tamanho, por exemplo), você pode optar por reindexar somente as tabelas que possuem fragmentação elevada (Scan Density < 60%, por exemplo).

Heaps não se beneficiam de processos de reindexação. Reduzir fragmentação em heaps, portanto, significa mover dados para uma área temporária, dropar a tabela, recriá-la e proceder a importação dos dados.

Customizações na configuração padrão

O SQL Server 2000 é bastante otimizado em suas configurações globais. Existem, contudo, alguns parâmetros que podem ser alterados na sua configuração default para efeito de tunning.

O comando sp_configure na Figura 3 fornece uma visão detalhada das configurações passíveis de alteração. A sintaxe detalhada pode ser obtida em http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_0put.asp

Figura 3: Execução do comando sp_Configure

execução do comando sp_configure

Para que possamos alterar uma configuração, devemos informar o nome do parâmetro seguido do novo valor. O comando reconfigure with OverRide efetiva a alteração, conforme exemplo abaixo:

Sp_Configure ‘show advanced options’,true
 Reconfigure with OverRide

NOTA: Algumas opções exigem stop/start no serviço do SQL Server.

Abaixo, algumas opções que podem ser customizadas:

  • Max Worker Threads : pool de threads disponíveis pelo SO (Microsoft Windows NT® 4.0 ou Windows® 2000 ) para os processos relacionados ao SQL Server. Possui valor padrão de 255, que se adapta bem para grande parte das instalações. Se o número de conexões ativas exceder esse limite, uma thread passará a atender mais de uma conexão (thread pooling). Fique atento para a ocorrência da mensagem “... The working thread limit of 255 has been reached ...”. Como sugestão, se o número médio de usuários ativos for superior a 255, altere essa opção e avalie o desempenho do servidor.
  • Priority Boost: se o servidor não é dedicado ao SQL Server, habilite essa configuração para aumentar a prioridade das threads do SQL Server em relação às outras aplicações.
  • LightWeight Pooling: diz respeito ao escalonamento das threads no processador. O SQL Server 2000 possui um algoritmo otimizado para schedule de threads em máquinas multiprocessadas (também conhecido por “fiber mode”), que pode ser utilizado em oposição ao “default thread based scheduling model” do SO. Habilite essa opção somente se possuir mais de um processador e estiver vivenciando carga elevada de processamento. Nesse caso, a melhora de performance pode ser considerável.

Conclusão

Efetuar tunning em um servidor de banco de dados não é um processo simples; devemos atacar em várias frentes para produzir resultados eficientes. Se, por exemplo, nos concentrarmos em otimização de queries e nos esquecermos de desfragmentar as tabelas, o resultado será modesto.

Neste número demos continuidade ao assunto tunning, focando rotinas para desfragmentação de dados e configurações globais de um servidor SQL Server 2000. Por hoje é só. Até a próxima!

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?