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

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

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!

Confira outros conte�dos:

SQL SUM: somando os valores de uma...

SQL: INNER JOIN

SQL: Introdu��o ao Where

Assista grátis a nossa aula inaugural

Perguntas frequentes

Quem somos?

Por que a programação se tornou a profissão mais promissora da atualidade?

Como faço para começar a estudar?

Em quanto tempo de estudo vou me tornar um programador?

Sim, você pode se tornar um programador e não precisa ter diploma de curso superior!

O que eu irei aprender estudando pela DevMedia?

Principais diferenciais da DevMedia

Qual o investimento financeiro que preciso fazer para me tornar um programador?

Como funciona a forma de pagamento da DevMedia?

Por Devmedia Em 2010

Nossos casos de sucesso

Leonardo Carlos

Eu sabia pouqu�ssimas coisas de programa��o antes de come�ar a estudar com voc�s, fui me especializando em v�rias �reas e ferramentas que tinham na plataforma, e com essa bagagem consegui um est�gio logo no in�cio do meu primeiro per�odo na faculdade.

Lucas Rodrigues

Estudo aqui na Dev desde o meio do ano passado! Nesse per�odo a Dev me ajudou a crescer muito aqui no trampo.
Fui o primeiro desenvolvedor contratado pela minha empresa. Hoje eu lidero um time de desenvolvimento!
Minha meta � continuar estudando e praticando para ser um Full-Stack Dev!

Her�clito J�nior

Economizei 3 meses para assinar a plataforma e sendo sincero valeu muito a pena, pois a plataforma � bem intuitiva e muuuuito did�tica a metodologia de ensino. Sinto que estou EVOLUINDO a cada dia. Muito obrigado!

Julio Cablen

Nossa! Plataforma maravilhosa. To amando o curso de desenvolvimento front-end, tinha coisas que eu ainda n�o tinha visto. A did�tica � do jeito que qualquer pessoa consegue aprender. S�rio, to apaixonado, adorando demais.

Joelberth Sena

Adquiri o curso de voc�s e logo percebi que s�o os melhores do Brasil. � um passo a passo incr�vel. S� n�o aprende quem n�o quer. Foi o melhor investimento da minha vida!

Felipe Nunes

Foi um dos melhores investimentos que j� fiz na vida e tenho aprendido bastante com a plataforma. Voc�s est�o fazendo parte da minha jornada nesse mundo da programa��o, irei assinar meu contrato como programador gra�as a plataforma.

Wanderson Oliveira

Comprei a assinatura tem uma semana, aprendi mais do que 4 meses estudando outros cursos. Exerc�cios pr�ticos que n�o tem como n�o aprender, est�o de parab�ns!

Jos� Lucas

Obrigado DevMedia, nunca presenciei uma plataforma de ensino t�o presente na vida acad�mica de seus alunos, parab�ns!

Eduardo Dorneles

Aprendi React na plataforma da DevMedia h� cerca de 1 ano e meio... Hoje estou h� 1 ano empregado trabalhando 100% com React!

Adauto Junior

J� fiz alguns cursos na �rea e nenhum � t�o bom quanto o de voc�s. Estou aprendendo muito, muito obrigado por existirem. Est�o de parab�ns... Espero um dia conseguir um emprego na �rea.

Ver todos os casos de sucesso

Artigo SQL Magazine 03 - Otimiza��o e Tunning � Parte II

Artigo da Revista SQL Magazine -Edi��o 3.