alt=capaSQL12.JPG hspace=0 src="/loja/img/Capa_SQL42_G.gif" border=0>

Clique aqui para ler todos os artigos desta edição

Analise de compilação e recompilação no SQL Server 2005

 

Quando estamos trabalhando com bases de dados com um grande volume de informação é extremamente necessário tomar cuidados especiais com pequenas operações que, muitas vezes, isoladamente não parecem degradar o desempenho de nossas aplicações.

Neste artigo, vamos explicar de maneira prática como o SQL Server 2005 gerencia os planos de execução em cache para aumentar a reutilização dessas rotinas gerando um ganho de desempenho nas operações. Vamos também mostrar maneiras de identificar as compilações, recompilações de queries e caches armazenados.

O que é recompilação?

O SQL Server, inclusive nas suas outras versões, utiliza-se do recurso de compilar rotinas como queries, stored procedures e triggers, em um plano de execução visando seu reaproveitamento.

Dizer que um plano de execução está em cache significa dizer que o SQL Server analisará a rotina (ler Nota 1) compilada e armazenada em cache para ver a possibilidade do reaproveitamento do processamento anterior, ou seja, evitar uma recompilação de um procedimento e usar uma informação já existente em cache.

 

Nota 1. Rotina

Uma rotina SQL ou TSQL (no caso do SQL Server) pode conter uma ou mais instruções Select, Insert, Update ou Delete associadas a outras instruções específicas como SET, WHILE, DECLARE, ou até mesmo instruções DDL, como Create, Drop, comandos que alteram permissões como GRANT, DENY ou REVOKE ou agora, a partir da versão 2005, comandos CRL (rotinas, assemblies escritos em código .Net que podem ser armazenados e executados dentro do SQL Server).

 

Esses planos de execução são armazenados em um local no servidor SQL Server chamado Plan Cache. Essa área reservada da memória é constantemente analisada para possíveis reutilizações. Esta análise é feita através da verificação do que está sendo executado contra o que o SQL Server possui armazenado em seu cache. Se um plano de execução em cache for reutilizado, o SQL Server não precisará compilar/recompilar a instrução, diminuindo o custo de processamento.

Para explicar tecnicamente o processo de compilação imaginemos, por exemplo, uma consulta “Select Campos From Tabela”. O SQL Server analisa a melhor forma de executar esse comando. Depois, armazena a instrução no cache (compilado) para que na próxima vez que um comando igual seja executado, ele aproveite esse cache. Caso o comando seja diferente da definição previamente armazenada, o SQL Server terá que recompilar e armazenar essa instrução, para que possivelmente em uma próxima execução ela seja reaproveitada. Recompilação não é de todo ruim. Apesar de degradar o desempenho, ela existe para garantir a confiabilidade do retorno das informações ou execução das instruções dentro do SQL Server.

Recompilações: SQL 2000 x SQL 2005

A grande diferença que encontramos entre um servidor SQL Server versão 2000 e a nova versão 2005 no que diz respeito a recompilações é a maneira como a engine do banco de dados (mecanismo de processamento de instruções do SQL Server) trata a recompilação. Por exemplo, se temos uma instrução SQL em um servidor SQL Server 2000 que possui várias Ad-hoc queries (Select, Insert, Update) e o servidor que está analisando os planos armazenados em cache identifica a necessidade de recompilação, toda a rotina é recompilada. O SQL Server 2005 faz a recompilação em partes, aproveitando pedaços compilados e armazenados, diminuindo o tempo de locks (recurso de banco de dados que bloqueia a utilização de uma tabela, por exemplo, por algum tempo garantindo a consistência dos dados) e reduzindo o consumo de recursos do servidor.

Analisando o cache armazenado

Como já comentado anteriormente, um cache armazenado nada mais é do que uma rotina ou instrução SQL / TSQL armazenada e disponibilizada como referência às outras rotinas ou instruções.

Vamos analisar como isto ocorre na prática utilizando o Management Studio, nova ferramenta do SQL Server 2005 que une os recursos do Enterprise Manager e Query Analiser do SQL 2000. Vamos então fazer o Login no servidor SQL Server utilizando a autenticação integrada ao Windows. Após logar no servidor, veremos a tela apresentada na Figura 1.

 

Figura 1. Tela Principal do Management Studio

 

Utilizaremos para nossos exemplos um banco de dados que será criado especificamente para nossas análises. No Management Studio, selecionamos a opção New Query, e digitaremos o script apresentado na Listagem 1.

 

Listagem 1. Script para criação do banco de dados, tabelas e dados de exemplo.

Create Database Caches -- Nome do banco de dados de exemplo

Go –- Usado para separar instruções de banco de dados

 

Use Caches -- Comando para se posicionar no banco de exemplo

Go

 

Create Table Nomes -- Tabela de exemplos

(

   Idx_Nomes Int Identity(1,1),

   Nome_Nomes Varchar(30)

)

Go

 

-- Esta rotina ira popular nossa tabela de nomes com 500 registros

Declare @Start Int

 

Set Nocount On

 

Select @Start = 1

 

While @Start <= 500

Begin

 Insert Into Nomes (Nome_Nomes)

 Values ('Nome ' + Convert (Varchar (3), @Start))

 Set @Start = @Start + 1

End

 

Depois de executado o script da Listagem 1, teremos um banco de dados chamado “Cache” com uma tabela chamada “Nomes”. Nesta tabela são incluídas 500 linhas. Feito isso, iremos fazer um Select simples nesta tabela e veremos como o cache de consulta é armazenado. Veja exemplo na Listagem 2.

 

Listagem 2. Select simples na tabela Nomes

 Select Idx_nomes, Nome_Nomes

 From Nomes

 Where Idx_Nomes >= 100

Order By Idx_Nomes

 

Executada a query da Listagem 2, o SQL Server 2005 armazena a estrutura do comando no seu cache, deixando a consulta compilada. Podemos perceber isso na Figura 2. Uma observação importante é que o armazenamento das compilações segue um padrão, ou seja, ele armazena exatamente a instrução em case sensitive e space sensitive (maiúsculas / minúsculas e espaçamentos, respectivamente).

Outro ponto que devemos estar atentos é o uso de consultas parametrizadas (Auto Parameterized Queries). Estas possuem uma variação de parâmetro na cláusula Where, e por isso elas são compiladas com um parâmetro (variável). Veja Listagem 3 ...

Quer ler esse conteúdo completo? Tenha acesso completo