Boas Praticas Otimizacao Artigo SQL

8/4/2019 Boas Praticas Otimizacao Artigo SQL

http://slidepdf.com/reader/full/boas-praticas-otimizacao-artigo-sql 1/11

Boas Práticas para Otimização de Consultas a Bancos de

Dados usando SQL Server 2005

LEONARDO FERREIRA SOARES* [email protected]

IREMAR NUNES DE LIMA** [email protected]

RESUMO Este trabalho apresenta algumas das formas utilizadas para o aprimoramento ou

otimização das consultas a bancos de dados utilizando o SQL Server 2005.

Palavras-chave: Banco de Dados. Otimização

*Graduando em Tecnologia em Informática pelo Unicentro Newton Paiva **Professor/Orientador - Unicentro Newton Paiva

1. Introdução Em meio a um mercado extremamente competitivo as empresas vêem cada vez

mais apostando em sistemas informatizados que dêem apoio à melhoria de seus

processos. Dentro desse contexto os Sistemas Gerenciados de Bancos de Dados

(SGBD`s) vêem com o intuito de armazenar e gerenciar as informações garantindo sua

disponibilidade de forma rápida e eficaz. Mas, em geral, SGBD`s não são ferramentas auto

suficientes no que diz respeito a otimização de consultas a banco de dados. Exitem vários

fatores que podem influenciar o desempenho de consultas submetidas aos SGBD`s

Esse artigo tem como objetivo descrever quais variáveis podem afetar o

desempenho de consultas a bancos de dados utilizando o Microsoft SQL Server 2005.

Antes de abordar alguns pontos sobre a otimização das consultas a banco de dados

é necessário ter em mente que não existe uma fórmula exata para obter sucesso na



otimização de um banco de dados. Existem sim, algumas regras de boa conduta

que deverão ser seguidas para que o objetivo final seja cumprido aproximando-se ao

máximo do que se pode chamar de um banco de dados otimizado. Serão tratados assuntos como indexação de tabelas, fator de preenchimento

(fillfactor) e análise do plano de execução de consultas SQL.

2. Otimização de Consultas

A metodologia para a otimização de um banco de dados envolve identificar as

consultas que consomem mais recursos, e então otimizá-las. Em geral, um número

pequeno de consultas pode são responsáveis pela maior parte das atividades que ocorrem

em um banco de dados (Linha de Código, 2006). É preciso lembrar que toda e qualquer consulta deve primeiro atender os requisitos

de quem a usa garantindo a disponibilidade, escalabilidade e segurança das informações

(Linha de Código, 2006). A seguir será mostrado algumas ferramentas importantes que ajudam na

otimização das consultas.

2.1 Índices

Segundo Paulo Riberio, índices são estruturas que possuem algoritmos otimizados

para acessar dados em um banco de dados. Tais estruturas constituem uma ferramenta

poderosa para o projetista do Banco de Dados no intuito de auxiliá-lo na melhora do

desempenho das consultas. O principal papel de um índice é reduzir o número de operações de I/O (leitura e

escrita) necessários para localizar os dados solicitados por uma consulta. Sendo assim, ao

fazer uso dos índices, o SQL Server 2005 rapidamente localizará e disponibilizará os

dados de uma consulta através de um número de I/O muito menor. Ao não utilizar índices,

o SQL Server precisa realizar uma operação conhecida como Table Scan , para localizar os

dados solicitados. Uma operação Table Scan é uma leitura seqüencial de todos osregistros da tabela, o que implica muito mais operações de I/O no disco. As operações de



I/O em disco são normalmente bastante lentas quando comparadas com operações

de leitura e escrita em memória (BATTISTI, 2005).

Os índices podem ser criados em qualquer coluna da tabela, inclusive em uma

coluna com valores calculados. O corpo de um índice é formado pelas colunas de uma

tabela cujos dados se deseja classificar seguido de uma referencia conhecida como

“ponteiro”, que serve para localizar a página de dados da tabela (BATTISTI, 2005).

2.2 Index Clustered Um clustered index armazena os dados ordenados de acordo com os valores do

campo onde o índice foi definido. Ao definir um clustered index, o mesmo organiza os

dados da página de acordo com sua chave. Neste caso, o índice está alterando a ordem

aleatória da tabela. Este tipo de índice é bastante rápido eficiente para agilizar as

operações de localização de registros. Somente podemos ter um clustered index por

tabela, pois só poderemos armazenar os dados em ordem de um determinado critério

(RIBEIRO).

Embora o índice melhore o desempenho das consultas, existe um pequeno

“overhead” para operações de atualização, inserção e exclusão de registro, pois estas

operações podem fazer com que a ordem dos registros seja alterada, e que estes tenham

que ser reposicionados para manter a ordem definida pelo índice (BATTISTI, 2005).

2.3 Non-Clutered Index Ao se criar um índice não-cluster cria-se na verdade uma estrutura que, através de

ponteiros, ligará a linha do índice à correspondente página da tabela sem alterar suaordem. Os registros estarão armazenados em uma ordem aleatória, porém poderão ser

facilmente localizados através do ponteiro (RIBEIRO). A utilização desse tipo de índice é indicada quando os dados podem ser

pesquisados por diferentes critérios, uma vez que podemos criar vários nonclustered index

em uma tabela. Uma novidade do SQL Server 2005 é a possibilidade de ampliar a funcionalidade

de um nonclustered index , através de colunas que não são chaves, como parte do último



nível ou camada de nós da folha de dados. Esta opção poderá melhorar

consideravelmente o desempenho das consultas (BATTISTI, 2005). O SQL Server 2005 usa a chave clusterizada na página de índice para colocar o

ponto do índice, e uma chave armazenando o local da informação. Tabelas que contém

índices cluster e índices não cluster são muito comuns. O melhor nessas situações é criar

o índice cluster primeiro (que irá organizar os dados e o índice em ordem ascendente) e

depois criar o índice não clusterizado nas colunas que forem necessárias como FK`s, ou

colunas muito acessadas. Deve-se tomar cuidado com a utilização dos dois tipos de

índices em uma única tabela. Nesse caso o SQL acaba por utilizar os dois meios de busca

gerando um I/O muito grande.

2.4 Index Keys Index Keys são as colunas utilizadas para a definição de um índice. A chave do

índice é um valor que permite que o registro correspondente seja facilmente localizado.

Nesse caso o valor a ser procurado será exatamente igual ao valor da chave (RIBEIRO).

2.5 Index Uniqueness Nesse tipo de índice não há unicidade nos valores armazenados. Um índice não

único permite valores repetidos para a chave, porém será proporcionalmente mais efetivo

quanto mais variarem os valores para o campo (RIBEIRO).

2.6 FILL FACTOR A opção fill factor determina qual a porcentagem de uma página de dados deve ser

preenchido com o índice e quanto deve ser mantido em branco, reservado para inclusões

e alterações. Por exemplo, se utilizarmos o fill factor igual a 80%, então o SQL Server irá

apenas preencher 80% de cada página com os índices. Se alterarmos ou incluirmos

dados, o SQL consegue reorganizar os índices de uma maneira mais rápida, pois ele tem

20% de espaço em branco em cada página de dados para poder preencher (Linha de

Código, 2006).

A decisão de usar ou não fill factor depende do número de inclusões e alterações



que se espera receber em uma tabela. Se o banco for utilizado somente para consulta,

então a melhor coisa a se fazer é colocar o fill factor 100%, pois não há necessidade de

deixar espaço em branco nas páginas de índices, já que eles jamais serão reorganizados

(RIBEIRO). Imagine que uma tabela tem 1.000.000 de registros e que, diariamente, 10.000

novos registros são adicionados ou modificados. Se colocado o fill factor igual a 90%,

muito provavelmente em dez dias o espaço em branco que o SQL Server reservou já foi

tomado. Nesse contexto haverá uma queda de desempenho na hora de incluir registros na

tabela. Agora, se colocado o fill factor igual a 70%, não teremos queda de performance

durante um mês (Linha de Código, 2006). Quanto maior o fill factor maior será o tempo entre manutenções do índice do

banco de dados. O fill factor ideal de um índice será aquele que causar menos fragmentação para

um determinado período de analise

2.8 DBCC – Database Consistency Cheker No SQL Server 2005, através da linguagem transacional, temos uma série de

comando que podem ser de grande utilidade na manutenção de tabelas e índices.

Entre os vários comando DBCC existentes, alguns merecem atenção maior quando

falamos de otimização de consultas. Abaixo veremos quais são eles divididos por quatro

categorias.

2.8.1 Comandos de Manutenção Comandos para manutenção preventiva ou corretiva no banco de dados:

DBCC DBREINDEX Reconstrói os índices de uma tabela. Muito útil para manutenção de

índices. DBCC DBREPAIR Apaga um banco corrompido. Use DRP DATABASE ao invés de DBCC

DBREPAIR. DBCC Desfragmenta um ou mais índices de uma tabela. Melhora a



Fonte: PICHILIANI 2.8.2 Comandos Gerais

Possuem diversas funcionalidades, como alocação de tabela na memória, ajuda

sobre outros comandos DBCC e ‘pinagem’ de tabela:

Fonte: PICHILIANI 2.8.3 Comandos de Status

Fazem algumas verificações de algumas configurações do banco de dados:

INDEXDEFRAG performance do índice.

DBCC HELP Retorna a sintaxe de algum outro comando DBCC DBCC PINTABLE ‘Pina’ a tabela , ou seja , faz o SQL Server não liberar da memória

algumas informações de uma tabela. Se utilizado com cuidado , há

ganho de performance. DBCC UNPINTABLE Faz o SQL Server liberar da memórias algumas informações de uma

tabela que foi ‘pinada’ como comando DBCC PINTABLE. DBCC ROWLOCK Simplesmente incluída por compatibilidade. A funcionalidade que este

comando proporcionava já é embutida automaticamente no SQL Server

2000 DBCC TRACEON Habilita um flag de trace que é necessário para outros comandos

DBCC.

DBCC TRACEOFF Desabilita um flag de trace setado como comando DBCC TRACEON

DBCC OPENTRAN Mostra informações sobre a transação mais velha ( mais tempo

executando ) em um banco de dados. DBCC SHOWCONTIG Mostra várias informações sobre os índices de uma tabela , inclusive

o nível de fragmentação do índice. DBCC

SHOW_STATISTICS Mostra as informações sobre as estatísticas de uma tabela.

Estatísticas são muito importantes para a melhora de performance.



Fonte: PICHILIANI 2.8.4. Comandos de validação

Checagem de alguns objetos do banco de dados como tabelas, valores das

colunas IDENTITY e constraints:

Fonte: PICHILIANI

DBCC TRACESTATUS Mostra a situação dos flags de trace que foram setados com o

DBCC TRACEON. Estes traces controlam configurações internas do

SQL Server

DBCC CHECKALLOC Verifica o espaço para as estruturas de alocações internas do

SQL Server. Permite alguns reparos em caso de erro. DBCC CHECKCATALOG Somente checa a consistência de algumas tabelas de sistema do

SQL Server ( que compõem o Database Catalog ). Não faz

reparos DBCC

CHECKCONSTRAINTS Checa os relacionamentos de uma determinada constraint no

banco de dados. Não faz reparos. DBCC CHECKDB Verifica erros de alocação e de consistência em vários objetos do

banco de dados. Pode efetuar reparos importantes em caso de

erro. DBCC CHECKFILEGROUP Muito parecido com o DBCC CHECKDB , porém só faz a

verificação no nível do filegroup de um database. Não faz reparos DBCC CHECKIDENT Chega e corrige , caso necessário , valores de colunas que

possuem a propriedade IDENTITY. Pode inclusive resetar o valor

inicial ( seed ) da coluna que possui a propriedade IDENTITY. DBCC CHECKTABLE Checa e corrige a integridade das páginas de dados , índices ,

ntext , text e image para uma tabela ou uma indexed view DBCC NEWALLOC Idêntica à DBCC CHECKALLOC. Foi mantida por

compatibilidade.



2.9 SQL Server Profiler Profiler é uma ferramenta que monitora a atividade de uma ou mais instâncias de

um servidor SQL Server. Entende-se por atividade desde uma conexão efetuada por um

usuário até comandos executados através de uma conexão. Com a utilização do Profiler temos a possibilidade de capturar e uma série de

comandos enviados por uma determinada instância do SQL Server e armazena-las para

testes futuros.

Imaginando que uma aplicação faça uso de constantes consultas ao banco de

dados, pode-se então armazená-las e após a criação ou reestruturação de índices, ou

definição de diferentes porcentagens de fill factor, teremos a possibilidade de fazer a

analise em conjunto das alterações realizadas facilitando a tomada de decisões. À captura destes comandos ou eventos dá-se o nome de trace. O trace poderá ser

configurado de acordo com as necessidades do administrador do banco.

2.9.1 Configurando o Trace Após a execução da ferramenta Profiler, clicar no menu FILE NEW TRACE.

Surgirá uma janela perguntando qual instância do servidor SQL Server deseja-se monitorar

conforme figura 1.

Após a seleção do Servidor surge a janela de propriedades do trace. Nesta janelapodemos definir qual o modelo de trace será utilizado e personalizar algumas



características, conforme indicado na figura 1.2.

Para iniciar a captura dos eventos será necessário um duplo clique no botão Run .

Na figura 1.3 pode-se ver o evento de consulta Select executado no banco de dados.

2.10 Estatisticas de I/O As estatísticas de I/O podem ser um ótimo indicador de desempenho no processo

de criação de uma consulta. Para fazer a verificação das estatísticas de uma consulta, basta, antes da



execução do comando transacional SQL, inserir o comando “Set Statistcis IO ON”. Dessa

forma, após a execução da consulta estarão detalhadas as informações de estatística da

mesma. Segue algumas das informações fornecidas:

- Logical Reads : informa o número de páginas lidas em memória. (RIBEIRO, 2006).

- Physical Reads: número de páginas lidas em disco. Se as páginas requeridas por um

comando não estão em memória, devem ser lidas do disco para a memória. Quando você

executa um comando pela primeira vez, podem ocorrer leituras físicas. Se você executar o

mesmo comando repetidas vezes, irá notar que leituras físicas são “convertidas” em

leituras lógicas. As leituras físicas “desaparecem”, permanecendo somente as leituras

lógicas (RIBEIRO, 2006).

- Read Ahead Reads: páginas lidas por antecipação. O SQL Server 2000 lê páginas

adicionais para efeito de otimização, mantendo-as em cache para agilizar sua utilização

por outras queries (RIBEIRO, 2006). - Scan Count: número de vezes que a tabela foi acionada. Dependendo da maneira como

escrevemos a query, o mesmo pelo modêlo de join utilizado, uma mesma tabela pode ser

acessada repetidas vezes – exemplo: uma subquery na linha do select exige um acesso

para cada linha lida na tabela principal, portanto o scan count das tabelas presentes na

subquery será igual ao número de linhas retornadas pelo select (RIBEIRO, 2006). Tais informações também poderão ser verificadas através da ferramenta SQL

Server Profiler. 3.0 Conclusão

O SQL Server 2005 possui um conjunto de soluções vastas e complexas para a

otimização de consultas a bancos de dados. Porém, entendendo alguns conceitos sobre

criação de índices, análise de estatísticas, fator de preenchimento das páginas dos índices

e outros comandos ou ferramentas apresentadas ao longo do artigo, já se pode obter um

grande ganho na otimização das consultas a banco. Seja na construção ou verificação de consultas já desenvolvidas por outras



pessoas, percebe-se que, apesar de ser o objetivo central das consultas, não basta

apenas obter as informações desejadas. necessário que as informações sejam

retornadas no menor tempo possível para que se atinja um nível de satisfação aceitável

com os usuários de sistemas informatizados.

REFERÊNCIAS

BATTISTI, Júlio SQL Server 2005: Administração e Desenvolvimento: Curso Completo.

Janeiro: Axcel Books, 2005. Linha de Código: SQL Server – Índices. Disponível em

http://www.linhadecodigo.com.br/artigos.asp?id_ac=619. Acesso em 02 Out. 2006. RIBEIRO, Paulo: SQL Magazine; Entendendo e utilizando índices na otimização de queries

no SQL Server. RIBEIRO, Paulo: SQL Magazine; Otimização e Tunning – Parte 1. 2ª Edição. RIBEIRO, Paulo: SQL Magazine; Otimização e Tunning – Parte 2. 2ª Edição RIBEIRO, Paulo: SQL Magazine; Tuning – Estatísticas de I/O. Disponível em

http://www.sqlmagazine.com.br/Colunistas/PauloRibeiro/07_Tunning_Estatisticas_IO.asp .

Acesso em 18 Nov. 2006. PICHILIANI, Mauro: IMaster; Comandos DBCC no SQL Server. Disponível em

http://www.imasters.com.br/artigo/255/sql_server/comandos_dbcc_no_sql_server. Acesso

em 18 Nov. 2006.

Documents

Boas Praticas Otimizacao Artigo SQL