microsoft sql server 2008 r2 - Blog da Newton

OTIMIZADOR DE CONSULTAS EM BANCO DE DADOS
ESTUDO DE CASO: MICROSOFT SQL SERVER 2008 R2
ELISA ALVINA DE MORAES ARCANJO 1
IREMAR NUNES DE LIMA 2
RESUMO: Os bancos de dados armazenam diferentes tipos de informações e são acessados constantemente de diversas formas. Uma consulta SQL quando é
solicitada ao banco de dados é tratada pelo SGBD, que, ao encontrar o melhor caminho, mostra o resultado das informações descritas no comando. A busca
pelo melhor desempenho e retorno rápido dos comandos requer o estudo de técnicas de otimização. O tratamento que o SGBD realiza na execução de consultas em busca de otimização segue por duas linhas diferenciadas: otimização heurística e otimização baseada em custos. Sendo a primeira feita por regras
e a segunda baseada em estatística. O presente trabalho tem como objetivo mostrar as duas formas de otimização de um SGBD e descrever qual a otimização
padrão utilizada pelo SQL Server 2008 R2.
PALAVRAS-CHAVE: Banco de Dados, Otimização, SQL Server 2008 R2.
1 INTRODUÇÃO
informações disponíveis que, talvez, o programador não tenha
Os bancos de dados armazenam diferentes tipos de infor-
acesso, como, por exemplo, os efeitos e quais os índices da
mações e são acessados constantemente de diversas formas,
tabela ou do banco que influenciam no comando e os planos
como, por exemplo, um acesso para consultar certos registros,
de execução já existentes. Assim, este artigo descreve, nos
solicitado por algum software ou mesmo por um DBA (Adminis-
próximos tópicos, como a otimização de consultas é tratadas
trador de Banco de Dados), ou o acesso ao banco para realizar
pelos SGBDs, descreve as técnicas de otimizações heurísticas
alguma alteração em um registro ou a inserção de novos dados.
e baseadas em custos e como funciona a otimização de con-
Seja para consultar, inserir ou alterar, dentre outros coman-
sultas no SQL Server 2008.
dos, o acesso aos dados e a resposta dos comandos deve
O restante deste trabalho está organizado da seguinte for-
ser rápido, contundente com o que está armazenado e com o
ma: na seção 2 é descrito o funcionamento dos otimizadores dos
que foi solicitado. A busca pelo melhor desempenho e retorno
SGBDs e as técnicas de otimização, como a Heurística e base-
rápido dos comandos requer o estudo do termo otimização, o
adas em Custos; na seção 3 é apresentado o funcionamento do
qual consiste, segundo TOMIO (2006), em escolher boas estra-
otimizador do SQL Server 2008 e, na seção 4 a conclusão.
tégias para o processamento de consultas ou comandos com
os objetivos de diminuir o acesso ao disco e a quantidade de
2 OTIMIZAÇÃO DE CONSULTAS DOS SGBDS
memória utilizada. Assim, para certo comando, existe um tipo
Uma consulta SQL quando é solicitada ao banco de dados
de otimização, mas a que será detalhada neste artigo é a otimi-
é tratada pelo SGBD, que ao encontrar o melhor caminho, mos-
zação de consultas SQL (Structured Query Language).
tra o resultado das informações descritas no comando. Este
Segundo BOSCATTO e DAMINELLO (2004, p. 3) “a otimi-
tratamento feito pelo SGBD, também conhecido como proces-
zação da consulta apresenta tanto um desafio como uma opor-
samento de consultas, pesquisa uma expressão equivalente ao
tunidade”, pois a otimização feita pelo programador ou por um
comando SQL com o objetivo de otimizar o tempo de execução
otimizador de banco de dados torna-se um desafio devido ao
da consulta. Para exemplificar a otimização de consultas consi-
grande número de relacionamentos existentes em um banco e
dere o seguinte comando:
a diversidade de estruturas de dados existentes no SGBD. A
SELECT c.nome
oportunidade acontece pelo simples fato de que otimizar con-
FROM clientes c, endereco e
sultas é possível e viável, seja ela como for, feita pelo progra-
WHERE c.codend = e.codend AND e.cidade=
mador humano, pelo DBA ou pelo próprio otimizador do banco.
“BELO HORIZONTE”;
Porém, diante deste contexto, pode ser que o otimizador
do banco consiga melhor desempenho ao executar uma con-
O processamento normal deste comando primeiramente
sulta do que se a otimização for feita pelo programador huma-
realizará o produto cartesiano das tabelas clientes e ende-
no (BOSCATTO, 2004 et all), pois o otimizador do banco possui
reço e depois verificará a satisfação das condições. Então,
PÓS EM REVISTA DO CENTRO UNIVERSITÁRIO NEWTON PAIVA 1/2012 - EDIÇÃO 5 - ISSN 2176 7785 l 121
somente depois do resultado gerado pelo produto cartesiano
representação interna e a mais utilizada é a árvore de sintaxe
e do tratamento das condições é que a projeção da coluna
abstrata ou árvore de consulta (figura 1). Esta árvore repre-
nome da tabela clientes será feita. Este processamento ain-
senta a consulta na linguagem de consulta de sistema. Ou
da não é o mais rápido. Utilizando as técnicas de otimização
seja, o comando é separado em blocos e níveis para que o
dos SGBDs, o processamento seria feito da seguinte forma:
SGBD avalie qual o melhor caminho da árvore a ser percorri-
primeiramente as condições WHERE seriam validadas e dos
do. Considere o comando a seguir e verifique a montagem de
resultados obtidos, a coluna nome seria projetada.
sua árvore de consulta:
Conforme descrito por TOMIO (2006), em geral os passos
seguidos por um otimizador de consultas são:
SELECT cliente.nome FROM cliente, categoria WHERE categoria.nome = “VIP” AND cliente.codcat = categoria.codcat
- Representação interna: a consulta é transformada numa
A árvore da figura 01 representa como um SGBD inter-
10) AND (cliente.nome OR cliente.idade < 30). Assim, diante
preta uma consulta SQL. No primeiro nível da árvore estão os
das diferentes formas, o SGBD deve escolher a melhor ex-
campos que deverão ser retornados pela consulta; no segun-
pressão e eliminar as demais do processo de execução de
do nível, realiza-se o produto cartesiano de categoria e cliente
uma consulta SQL.
de acordo com a condição especificada (a chave estrangeira
Medição de custo: as expressões são consideradas
codcat de cliente deve ser igual à chave primária codcat de
como uma série de operações e para cada operação o SGBD
categoria). No terceiro nível é realizada a filtragem das cate-
avalia os procedimentos necessários para a implementação
gorias que possuem o nome VIP. Sempre ao final da árvore,
da operação e assim, calcula qual o custo da operação;
nas pontas, também chamadas de folhas, encontramos as
tabelas selecionadas no comando.
Expressão equivalente: a representação interna é convertida para uma expressão equivalente com o intuito de re-
Informações do catálogo do sistema: o SGBD verifica as
informações referentes ao estado do banco de dados, como
por exemplo, os índices referentes à consulta inicial, as tabelas relacionadas e suas cardinalidades.
presentar, de maneira mais eficiente, a consulta original. Ou
Planos de consulta: com as informações do catálogo e a
seja, um mesmo comando pode ser representado de diferen-
medição dos custos, o otimizador gera os planos de consulta
tes maneiras, mas gerará o mesmo resultado. Porém, cabe
e em seguida, escolhe o melhor entre eles, ou seja, o de me-
ao SGBD escolher a expressão que melhor representa e que
lhor desempenho (menor custo).
tenha a melhor performance do que o comando original. Por
O tratamento que o SGBD realiza na execução de consul-
exemplo, a condição WHERE cliente.nome = “JOAO” OR
tas em busca de otimização pode ser feito de duas maneiras:
(cliente.idade > 10 AND cliente.idade < 30) também pode
otimização heurística e otimização baseada em custos. Estas
ser descrita como WHERE (cliente.nome OR cliente.idade >
técnicas serão descritas nos próximos tópicos.
122 | PÓS EM REVISTA DO CENTRO UNIVERSITÁRIO NEWTON PAIVA 1/2012 - EDIÇÃO 5 - ISSN 2176 7785
2.1 OTIMIZAÇÃO HEURÍSTICA
muitas tabelas e os relacionamentos forem descritos na forma
Na otimização heurística, segundo TOMIO (2006), algumas
regras são aplicadas para melhorar o desempenho da execução
e da transformação das consultas em diversas expressões equivalentes. As regras são:
de produto cartesiano, mesmo seguindo as regras, a otimização
heurística gerará um resultado, talvez, mais demorado.
A otimização baseada em custos depende de atualizações
periódicas de índices, tuplas, cardinalidade das tabelas e cha-
Executar operações de SELEÇÃO e PROJEÇÃO primeiramente;
ves, e isto necessita de um tratamento por parte de um DBA ou
um desenvolvedor, que devem criar funções que façam tal atu-
A JUNÇÃO só deve ser realizada depois da SELEÇÃO e
PROJEÇÃO;
alização ou eles mesmos façam tal tratamento. Se estes dados
não estiverem de acordo com a realidade do banco podem ge-
Somente os atributos solicitados para o resultado da con-
rar resultados que não sejam os melhores. Porém, se as funções
sulta e os que realmente são necessários em consultas subse-
que armazenam as informações dos índices, chaves, tuplas e
qüentes é que devem ser projetados;
cardinalidade estão atualizadas, a otimização em custos se tor-
Evitar geração de múltiplas tabelas intermediárias;
na bastante vantajosa, uma vez que sua prioridade é eliminação
Pesquisar as subexpressões comuns e processá-las so-
de custos, garantindo ótima performance e resultados rápidos.
mente uma vez;
A utilização dessas regras heurísticas influencia no plano de
3 O OTIMIZADOR DE CONSULTAS DO SQL SERVER 2008 R2
execução da consulta, pois uma ordem de execução das ope-
Conforme o site da MSDN (Microsoft Developer Network)
rações é determinada e também quais os recursos serão utiliza-
ao executar um comando SELECT em um banco de dados,
dos no plano, por exemplo, os índices.
o comando não informa qual o caminho o banco deve seguir
para gerar o resultado no menor tempo e performance possível.
2.2 OTIMIZAÇÃO BASEADA EM CUSTOS
Assim, todo SGBD possui em sua estrutura um otimizador de
A otimização baseada em custos é gerada a partir de fun-
consulta que é responsável por avaliar a instrução a partir do
ções estatísticas dos SGBDs. Estas funções armazenam infor-
comando, do esquema e das estatísticas do banco gerando o
mações referentes ao número de tuplas, aos índices, as chaves
melhor plano de execução.
e a cardinalidade das tabelas. As estatísticas da distribuição dos
O SQL Server 2008 R2 possui em sua estrutura um otimiza-
dados nas colunas das tabelas e que são usadas para estimar
dor de consultas que é baseado em custos. Ou seja, cada plano
os custos, também consideram o uso de CPU e I/O e o tamanho
de execução possui um custo referente aos recursos que utiliza,
das tabelas utilizadas.
como CPU e I/O, e o otimizador é responsável por analisar e
Porém, como este tipo de otimização é baseado em estatísticas e pode gerar um resultado que talvez não seja o melhor.
Isto acontece devido ao SGBD utilizar informações armazenadas que talvez estejam desatualizadas. Assim, é necessário que
as funções que armazenam as informações das tuplas, índices,
chaves e cardinalidade sejam atualizadas periodicamente. Não
escolher o plano mais eficiente, de menor custo e que retorne o
resultado da forma mais rápida.
O processamento de uma consulta no SQL Server 2008 R2,
segundo o site MSDN, segue alguns passos:
Análise do comando SELECT e separação do mesmo em
palavras-chave, expressões, operadores e identificadores.
é viável realizar a atualização todas as vezes que acontece al-
Criação da árvore de consulta do comando.
guma alteração no banco, pois isto implica em alto custo, mas
Análise dos diferentes modos de percorrer a árvore em
atualizando periodicamente, o SGBD manterá valores próximos
busca melhor resultado esperado pelo comando original. Neste
aos reais.
passo, a cada análise, a árvore de consulta é atualizada e sua
versão final é chamada de plano de execução.
2.3 A ESCOLHA DO SGBD
Execução do plano de consulta.
Os SGBDs que optam pela otimização heurística, estão em
Retorno dos resultados ao usuário.
busca de um resultado bem mais confiável do que o resultado
Assim, o otimizador do SQL Server 2008 R2 utiliza a otimi-
da otimização baseada em custos, pois a otimização heurística
zação baseada em custos. Ele realiza otimização em tabelas lo-
segue um padrão de avaliação e execução de uma consulta e
cais, particionadas, distribuídas e em diversos servidores, mas
este padrão ajuda no desempenho e resultado das informações
os passos descritos anteriormente para otimizar uma consulta
do comando original, enquanto que a otimização em custos é
são os mesmos realizados indiferente da arquitetura do banco
baseada em estatísticas. Mas, se a consulta fizer referência a
e das tabelas.
PÓS EM REVISTA DO CENTRO UNIVERSITÁRIO NEWTON PAIVA 1/2012 - EDIÇÃO 5 - ISSN 2176 7785 l 123
4 CONCLUSÃO
Uma consulta quando é solicitada a um banco de dados
não especifica qual a ordem e as etapas de sua execução. Tal
tarefa é de responsabilidade do otimizador de consulta, existente em todo SGBD.
Existem dois tipos de otimização de consulta: heurística e
baseada em custos. A heurística possui passos a seguir para
gerar um plano de execução eficiente, como, por exemplo, SE-
CALUMBY, Rodrigo Tripodi. Otimização Heurística de Consultas. Universidade Estadual de Campinas, 2010. Disponível em: http://www.
ic.unicamp.br/~tripodi/mc526/slides/aula12-otimizacao_de_consultas_
heuristica.pdf. Acesso em: 27 de Maio de 2011.
MSDN – Microsoft Developer Network. Processamento de instruções
SQL – SQL Server 2008 R2. Disponível em: http://msdn.microsoft.com/
pt-br/library/ms190623.aspx. Acesso em: 07 de Junho de 2011.
LEÇÃO e PROJEÇÃO são os primeiros passos desta otimização. A otimização baseada em custos baseia-se em estatísticas
NOTAS DE RODAPÉ
geradas a partir de funções que armazenam informações so-
1 Especialista em Banco de Dados e Business Intelligence (elisaarcanjo@
hotmail.com).
bre índices e chaves, cardinalidade das tabelas e as tuplas do
banco para gerar o plano de execução que gere menor custo e
resultado mais rápido.
O SQL Server 2008 R2 utiliza em seu otimizador a otimiza-
2 DBA, mestre em informática e professor do Centro Universitário Newton
Paiva ([email protected]).
ção baseada em custos e também possui seus próprios passos
na execução de um comando SELECT.
Assim, todo SGBD possui em sua estrutura um otimizador
de consultas, mas cada banco de dados pode escolher qual
estratégia de otimização: heurística ou baseada em custos.
REFERÊNCIAS BIBLIOGRÁFICAS
BOSCATTO, Ronaldo; DAMINELLO, Marcelo Rosalem. Otimização de
Consultas SQL. Instituto Municipal de Ensino Superior de São Caetano
do Sul, 2004. Acesso em 27 de Nov. de 2010.
NOBRE, Wendell. Otimização de Consultas em Banco de Dados
Centralizados. Disponível em: http://docs.google.com/
viewer?a=v&q=cache:4oBqBvQGb-0J:labdist.dimap.ufrn.br/files/3Ma.
ppt+otimizador+de+consultas+sgbd Acesso em: 22 de Nov. de 2010.
Otimização do Desempenho com o SQL Server 2008. Disponível em:
http://fabadas.wordpress.com/dba-banco-de-dados/sql-server/otimizacao-do-desempenho-com-o-sql-server-2008/. Acesso em: 22 de Nov.
de 2010.
TOMIO, Thiago; CAIO, Renato; TSUYOSHI, Arlindo. Banco de Dados
– Álgebra Relacional, Processamento e Otimização de Consultas.
Faculdade de Tecnologia de São Paulo – FATEC-SP, 2006. Disponível
em:http://www.shammas.eng.br/acad/sitesalunos0606/to/otimi.htm.
Acesso em: 22 em Nov. de 2010.
Processamento e Otimização de Consultas. Disponível em: http://homepages.dcc.ufmg.br/~laender/material/ibd-parte7.pdf. Acesso em: 04 de
Maio de 2011.
MACHADO, Javam C.. Otimização de Consultas Relacionais. Universidade Federal do Ceará, 1998. Disponível em: http://www.lia.ufc.
br/~javam/Bd_avancado/otimizacao-consulta.pdf. Acesso em: 05 de
Maio de 2011.
124 | PÓS EM REVISTA DO CENTRO UNIVERSITÁRIO NEWTON PAIVA 1/2012 - EDIÇÃO 5 - ISSN 2176 7785