Otimizando desempenho no SQL Server 2005 – Parte II

 


O bom desempenho em consultas de bancos de dados vai além da estratégia de índices utilizada nas tabelas pertencentes à consulta. Questões como: projeto de bancos de dados, dimensionamento do hardware e estratégias de otimização são itens que afetam diretamente o tempo de resposta das consultas em geral.

Este artigo propõe-se a discutir aspectos que transcendem a otimização de consultas de bancos de dados. Outras questões como: memória, processador, disco e subsistema de comunicação também podem se tornar fatores de gargalo e afetar o desempenho no nível do sistema. Entende-se por “gargalo” qualquer componente ou atividade que limita o desempenho do sistema. Veremos alguns desses fatores abaixo.

Projeto de bancos de dados

Quando se projeta um banco de dados deve-se ter a certeza de que todas as funcionalidades serão fornecidas de maneira correta e com o menor tempo de resposta possível. Algumas questões de projeto até podem, mas não devem, ser resolvidas após a colocação do banco de dados em produção. Por exemplo, é possível decidir pela criação de constraint de check em uma determinada coluna de uma tabela do banco de dados após o sistema estar no ar. Porém, há questões de desempenho que são resultantes de um projeto de banco de dados ineficaz ou uma modelagem de dados equivocada que tornam sua utilização uma tarefa quase impossível pelos altos tempos de resposta envolvidos. Nesses casos, a solução mais correta é a alteração da estrutura e um novo projeto de banco de dados. Contudo, a solução de reconstrução do projeto, além de drástica, é extremamente custosa.

Questões como: as maiores tabelas do banco e os processos mais complexos da aplicação devem ser identificados tão logo seja possível, bem como considerações sobre os efeitos no desempenho causados pelo aumento do número de usuários que acessam o banco de dados.

Exemplos de mudanças de projeto que podem fornecer melhoria de desempenho:

·         Imagine uma tabela que contenha milhares de registros, das vendas diárias de uma livraria. Suponha que essas vendas devam ser totalizadas para serem usadas em um relatório diário. É possível adicionar uma ou várias colunas nessa tabela que guardem dados previamente agregados para serem usados no relatório ou utilizar uma estratégia de processamento e construção prévia do conjunto de resultados desejado. A idéia aqui é diminuir a sobrecarga de realizar a soma desses valores em tempo de execução. Portanto, colunas calculadas ou consolidação de dados via trigger seriam boas saídas;

·         Bancos de dados excessivamente normalizados podem ter o desempenho de consultas reduzido. Isso se deve, principalmente, ao fato de existirem muitas pequenas tabelas inter-relacionadas. Esse cenário prejudica o desempenho porque o banco terá de processar os dados de todas as tabelas envolvidas e combiná-los para retornar os resultados. Em certas situações um grau controlado de desnormalização é benéfico à aplicação.

 

Considerações de hardware

Geralmente, quanto maior o banco de dados mais recursos de hardware serão necessários para suportar as operações realizadas por seus usuários. Entretanto, vale ressaltar que outros fatores determinantes incluem o número de sessões concorrentes, throughput de transações (quantidade de transações que podem ser realizadas em um mesmo período de tempo) e os tipos de operações que são utilizadas no banco de dados: select, insert, update e delete.

Por exemplo, um sistema de biblioteca que é pouco atualizado terá menos requisitos de hardware do que um data warehouse corporativo altamente acessado pelo departamento de vendas. Da mesma forma, discos maiores para o armazenamento de dados, maior quantidade de memória e processadores mais rápidos seriam fundamentais ao data warehouse, mas não teria um custo benefício interessante para o sistema de biblioteca.

O mecanismo de armazenamento é uma questão chave para qualquer banco de dados relacional e requer bastante atenção no planejamento. Implementações de sucesso requerem um planejamento cuidadoso e rigoroso na etapa de projeto. Esse planejamento deve incluir considerações sobre as seguintes questões:

·         Como armazenar os dados ao longo dos discos usando arquivos e grupos de arquivos;

·         Que projeto de índice utilizar para fornecer desempenho de consulta no acesso aos dados – para mais informações sobre índices consulte o artigo Otimizando o desempenho no SQL Server 2005 – Parte I, publicado na SQL Magazine nºXX;

·         Como configurar os parâmetros do banco de dados para obter o máximo de desempenho;

·         E, caso necessário, qual o tipo de RAID Redundant Array of Independent Disks (Array redundante de discos independentes) de hardware será utilizado.

 

Vejamos a seguir alguns desses itens.

Arquivos e Grupos de Arquivos

Um banco de dados SQL Server é composto de, no mínimo, dois arquivos: um arquivo de dados e um arquivo de log. Esses arquivos, na verdade, só são percebidos pelo sistema operacional uma vez que o SQL Server enxerga o banco como uma unidade indivisível. As informações de dados e log nunca são misturadas no mesmo arquivo. Além disso, existirão arquivos separados para cada banco de dados. A Figura 1 ilustra um banco de dados e seus arquivos.

 

26-12-2007pic01.JPG 

Figura 1. Bancos de dados e seus arquivos

 

Um ou mais arquivos podem ser agrupados em estruturas conhecidas como grupos de arquivos. Os grupos de arquivos facilitam tarefas administrativas como operações de backup e restore. Se por ventura a configuração de hardware possuir diversas unidades de disco, é possível alocar os grupos de arquivos em discos individuais. A Figura 2 ilustra a utilização de dois grupos de arquivos: o primary e um segundo grupo de arquivos definido pelo usuário. O grupo de arquivos primary é default (quando uma determinada tabela é criada, se nada for dito em contrário, ela será criada dentro do primary) do SQL Server e guarda as tabelas do catálogo presentes em cada banco de dados. O grupo de arquivos definido pelo usuário guarda objetos de usuário e não objetos do catálogo do sistema. Portanto, é importante que ao criar um novo banco de dados seja criado, logo em seguida, um grupo de arquivos, definido pelo usuário, para o armazenamento dos objetos. Na Figura 2, as tabelas OrdHistYear1 e OrdHistYear1 são armazenadas no grupo de arquivos definido pelo usuário “User-defined Filegroup”. Os arquivos de log de transações não pertencem a grupos de arquivos e são armazenados em estruturas à parte.

 

26-12-2007pic02.JPG 

Figura 2. Grupos de arquivos

A utilização de arquivos e grupos de arquivos melhora o desempenho do banco de dados porque é possível criar o banco espalhando seus arquivos ao longo de vários discos, controladoras de disco ou conjuntos RAID. Imagine por exemplo que sua máquina possui quatro discos. É possível criar um banco de dados composto de três arquivos de dados e um arquivo de log e armazenar cada arquivo em um disco diferente. Quando os dados forem acessados, os quatro cabeçotes de leitura e gravação podem ler ou gravar os dados, de forma paralela.

Além disso, as tabelas mais freqüentemente acessadas podem ser separadas das demais, sendo armazenadas em grupos de arquivos distintos dos grupos de arquivos das demais tabelas. Vale ressaltar que tais grupos de arquivos devem estar em discos físicos separados para que não seja criada retenção de disco e conseqüentemente perda de desempenho. Outra dica importante é colocar tabelas diferentes, usadas em joins em várias consultas, em grupos de arquivos diferentes. Essa ação melhora o desempenho em função do I/O paralelo para pesquisa dos dados usados no join.

Parâmetros de configuração do servidor

O SQL Server 2005 ajusta automaticamente muitas das opções de configuração do servidor. Entretanto, há configurações que podem ser alteradas pelo administrador. A Microsoft recomenda que tais alterações sejam feitas de forma bastante cautelosa para não incorrer em efeitos contrários à melhoria de desempenho.

 

Otimizando o desempenho do servidor usando opções de configuração de Memória

O gerenciador de memória do SQL Server 2005 elimina a necessidade de gerenciamento manual da memória disponível no SQL Server. Quando o serviço do SQL Server inicia, ele determina dinamicamente a quantidade de memória a ser alocada baseada no total de memória que estiver sendo usada pelo sistema operacional e por outras aplicações.

As opções de configuração min server memory, max server memory, max worker threads, index create memory, min memory per query podem ser usadas para configurar a utilização de memória e ajustar o desempenho do servidor. Todas essas opções de configuração são em nível de servidor e são configuradas através da stored procedure sp_configure. A seguir uma rápida descrição sobre cada uma delas.

·         max server memory: estabelece o limite superior de memória a ser usado por uma instalação SQL Server;

·         min server memory: estabelece o limite inferior de memória a ser usado por uma instalação SQL Server;

·         max worker threads: permite a configuração do número de threads de trabalho disponíveis para o SQL Server;

·         index create memory: permite controlar o total de memória usado em operações de criação de índices;

·         min memory per query: essa opção permite especificar a quantidade mínima de memória em (em kilobytes) que será alocada para execução de uma query.

 

Otimizando o desempenho do servidor usando opções de configuração de E/S

A opção de configuração de servidor recovery interval pode ser gerenciada para configurar o uso de Entrada e Saída (E/S) e prover melhoria de desempenho do servidor. Essa opção controla quando o SQL Server 2005 realiza checkpoint (períodos de tempo em que as transações concluídas que estão no log de transações são gravadas definitivamente no disco) em cada banco de dados. Por padrão o SQL Server determina o melhor momento para fornecer operações de checkpoint.

Entretanto, utilizando o Performance Monitor, é possível monitorar se essa opção está configurada a contento através da verificação de atividade de escrita nos discos do servidor no banco de dados. A configuração desta opção pode ser realizada através da stored procedure de sistema sp_configure.

Picos de atividade de utilização de disco que alcançam 100% afetam o desempenho, obviamente, de forma negativa. Alterar a freqüência de ocorrência do processo de checkpoint pode melhorar o desempenho geral. Vale ressaltar que o desempenho deve ser constantemente medido para determinar se o novo valor teve um efeito positivo no desempenho.

RAID

Soluções de armazenamento, baseadas em hardware, que objetivem disponibilidade e tolerância a falhas são tipicamente implementadas usando RAID - Redundant Array Of Independent Disks (Array redundante de discos independentes) níveis 0, 1, 0 + 1 e 5. O RAID utiliza controladoras de disco e arrays de discos para minimizar a perda de dados, em caso de falha da mídia, além de fornecer melhoria de desempenho nas operações de leitura e escrita.

As diferenças nos níveis de RAID basicamente estão na forma de implementação da tolerância a falhas e melhoria no desempenho do acesso aos dados. Por exemplo:

·         RAID nível 0 - Também chamado de Stripping (faixas de disco). Divide os arquivos em “fatias” e escreve cada uma delas em um disco do array. Esse nível de RAID não fornece tolerância a falhas, apenas permite um aumento de desempenho na medida em que as operações de leitura e escrita serão realizadas em paralelo. Desvantagem: se um dos discos do conjunto for danificado, as informações armazenadas em todo o conjunto são perdidas. Observe na Figura 3 que os arquivos são escritos ao longo do conjunto que, nesse exemplo, é composto por quatro discos.


26-12-2007pic03.JPG 

Figura 3. O RAID 0 “fatia” o arquivo e armazena cada parte em um disco diferente

 

·         RAID nível 1 – Também chamado de Espelhamento. As informações escritas em um disco são ao mesmo tempo escritas em outro disco. O RAID 1 suporta tolerância a falhas, mas não possui um desempenho tão bom quanto no RAID 0 já que o arquivo é gravado como um bloco único no disco. A grande desvantagem é o custo. Para cada disco será necessário outro para que o espelhamento seja realizado. Na ocorrência de falhas, o desempenho do sistema não é afetado, mas é necessária a substituição do disco defeituoso o quanto antes sob pena da perda do dado caso um evento subseqüente de falha aconteça. Após a substituição do disco defeituoso, o desempenho é degradado em função da reconstrução dos dados no novo disco. Após o término desse processo, o desempenho volta ao normal. A Figura 4 ilustra a utilização do RAID 1 ao longo dos discos 1 e 2. Observe que cada arquivo é gravado nos dois discos. Assim, o arquivo “A” existe tanto no disco 1 quanto no disco 2.

 

26-12-2007pic04.JPG 

Figura 4. RAID 1, realiza um “espelhamento”, dos dados gravados no disco 1, no disco 2

 

·         RAID nível 5 - Faixas de disco com paridade. Nesse nível de RAID as informações são distribuídas em faixas e gravadas nos discos juntamente com uma informação de paridade que é usada para reconstruir os dados em caso de perda de um dos discos. A informação sobre a paridade é armazenada em cada um dos discos de forma alternada, ou seja, a cada momento é gravada em um disco diferente. O RAID nível 5 traz o melhor custo benefício, uma vez que combina tolerância a falhas com desempenho satisfatório nos acessos para operações de leitura e escrita. Entretanto, na ocorrência de falhas o desempenho ficará ligeiramente degradado uma vez que o RAID realizará o cálculo de paridade para reconstruir o dado presente no disco que foi danificado. É o nível de RAID mais utilizado no mercado. A Figura 5 mostra a estrutura do RAID nível 5 com cinco discos.

 

26-12-2007pic05.JPG 

Figura 5. RAID 5, armazena a informação de paridade, de forma alternada, em cada um discos do conjunto

 

·         RAID 0 + 1 - É o nível de RAID que traz o melhor desempenho e suporte tolerância a falhas. Combina o RAID 0 (Stripping) com RAID 1 (espelhamento). Sua desvantagem está no altíssimo custo de operacionalização.



Utilizando grupos de arquivos com RAID

É possível combinar grupos de arquivos com soluções de RAID para melhorar o desempenho no acesso e a segurança dos dados. Inicialmente é necessário configurar a distribuição do RAID e, depois, usar os grupos de arquivos para distribuir os dados em vários discos.

A Figura 6 mostra, além da controladora do sistema operacional e da controladora do log de transações, duas controladoras que apontam para dois conjuntos de RAID. Quatro arquivos estão associados a cada RAID. Um grupo de arquivos é composto de oito arquivos, nomeados de “A” a “H”, e está distribuído ao longo dos RAIDs. Essa estratégia é interessante porque distribui os dados de maneira uniforme em todos os discos e ao mesmo tempo mantém a facilidade de administração. Portanto, a união dos conceitos de RAID e grupos de arquivos trazem além de melhoria de desempenho, melhoria da administração do banco de dados em nível geral. Por exemplo, as operações de backup e restore se beneficiam dessa arquitetura e tornam-se muito mais rápidas do que em arquiteturas que não utilizam tais recursos.

 

26-12-2007pic06.JPG 

Figura 6. RAID 5, armazena a informação de paridade, de forma alternada, em cada um discos do conjunto

 

Portanto, usar tal configuração fornece acesso paralelo aos dados, além de distribuir a carga entre vários discos para reduzir a perda de desempenho durante operações de leitura e escrita (retenção de disco).

Conclusão

Foram abordados ao longo desse artigo aspectos teóricos relacionados a itens que podem melhorar o desempenho de servidores de bancos de dados SQL Server 2005. Os dois artigos da série servem para dar uma visão geral dos aspectos que podem afetar o desempenho de bancos de dados SQL Server.

É importante ressaltar que o desempenho geral de um sistema pode ser afetado por questões relativas ao desempenho: das aplicações cliente; do banco de dados; do SQLServer e das configurações do hardware. Cobrimos apenas os 3 últimos itens. A arquitetura das aplicações cliente é outro fator que pode impactar diretamente no desempenho geral do sistema.

Referências

070-431 MSPress - Maintaining Microsoft SQL Server 2005 Database (MOC 2780A).

Stanek, William – Microsoft SQL Server 2005 Guia de Bolso do Administrador. Bookman, 2006.

Battisti, Júlio - SQL Server 2005 Administração e Desenvolvimento Curso Completo.
Axcel Books, 2005.

Mcts Self-Paced Training Kit (Exam 70-431) - Microsoft SQL Server 2005 Implementation And Maintenance.