Archive

Archive for April, 2011

Constraints vs Query Performance

Neste post irei demonstrar a importancia de  criar constraints no SQL Server. Graças as Foreign keys e check constraints o optimizer pode criar planos mais eficientes para as querys. Dado o script abaixo da criação das tabelas temos a tabela de Customers e de Orders. Na modelagem proposta que dizer que um Customer pode ter uma ou mais Orders. Notem que o script de criacao da constraint fisica nessa tabela e feita na tabela “filha” no nosso caso a de Orders.
CREATE TABLEdbo.Customers (CustomerID INT  PRIMARYKEY)
CREATE TABLE dbo.Orders(OrderID INT  PRIMARYKEY,CustomerID INT NOT  NULL CONSTRAINT FKOrdersCustomers REFERENCES dbo.Customers(CustomerID)).
Executando a query abaixo, podemos notar que temos duas tabelas na consulta, mas se vc executar a query com o plano de execução o sql server uma apenas o operador fisico para acessar uma das tabelas. Esse compportamento acontece, pois, o otimizador sabe que não é necessario executar um teste de existencia dos registros, pois a FK garante que serao requeridas todas orders para referenciar com o customer. Conforme print abaixo:
SELECT O.OrderID,o.CustomerID
FROM dbo.Orders AS o
WHERE EXISTS(SELECT CustomerID FROM dbo.Customers AS c
WHERE c.CustomerID=o.CustomerID)

Agora o que aconteceria se desabilitarmos a constraint? Vamos aos testes (Amo muito tudo isso..rs)
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT FKOrdersCustomers

Execute novamente  a query, pressione o CTRL+M antes para habilitar o Include Actual Execution Plan. Agora perceba que  o plano de execucao mudou, isso porque a constraint foi desabilitada e o SQL Server nao pode garantir que todas as Orders  tem um customer valido.
SELECT O.OrderID,o.CustomerID
FROM dbo.Orders AS o
WHERE EXISTS(SELECT CustomerID FROM dbo.Customers AS c WHERE c.CustomerID=o.CustomerID)

Para voltarmos ao plano anterior devemos habilitar novamente a constraint, conforme instrução abaixo:
ALTER TABLE dbo.Orders CHECK CONSTRAINT FKOrdersCustomers

Execute a query novamente e veja o plano de execucao. Note que o plano de execucao continua o mesmo de quando a constraint foi desabilitada.

Esse comportamento se da ao devido fato que: O SQL server nao pode garantir que nao foi inserido um registro nao valido enquanto a FK estava desabilitada. Vc pode verificar isso atraves da dmv sys.foreign_keys. A FK foi marcada como “nao confiavel“. Veja a query abaixo:
select name , is_not_trusted from sys.foreign_keys
Com o resultado da query acima  vc vera que a coluna is_not_trusted esta marcada com o valor 1  , indica que a FK nao e confiavel.
O que pode ser feito nesse caso e adicionar a opção WITH CHECK para a clausula alter table que habilita a constraint:

alter table dbo.Orders with check check constraint FKOrdersCustomers

Se a query for executada novamente vera que o plano de execucao sera o visto da primeira implementacao.

Se nesse meio tempo que a FK foi  desabilitada algum registro que nao condiz com a  implementacao da constraint fosse inserido . Uma mensagem de erro seria retornado para o SQL Server. Por exemplo se uma order fosse inserida com um CustomerID NULL.

Referencia: Microsoft Database Developer 2008 TK 70-433

Advertisements

Índices no SQL Server 2000

Esta semana(No ano de 2008) me deparei com um problema de otimização e então li muito a respeito. Hoje falarei de índices.
Antes de falarmos sobre os índices, falaremos como os dados no SQL Server são armazenados e como os mesmo são acessados.

Armazenamento de dados
Uma pilha é uma coleção de páginas de dados:
• Cada página contém 8 quilobytes (KB) de informações.
• Quando os registros são inseridos em uma página, e ela já está cheia, as páginas de dados são divididas.
• Um grupo de 8 páginas adjacentes é chamado de extensão.

Acesso aos Dados
O SQL Server acessa os dados de dois métodos.
• Examinando todas as páginas de dados das tabelas – chamado de exame de tabela. Ao executar um exame de tabela o SQL Server:
• Começa no inicio da tabela.
• Examina todos os registros da tabela, página a página.
• Extrai os registros que satisfazem os critérios da consulta.

• Usando índices. Ao usar um índice, o SQL Server:
• Percorre a estrutura da árvore do índice para localizar os registros solicitados pela consulta.
• Extrai apenas os registros necessários que satisfazem os critérios da consulta

Como Criar Índices?

Ao criar índices, leve em conta dois fatores: a natureza dos dados e a natureza das consultas realizada nas tabelas.
Natureza dos dados – Quando me refiro a natureza de dados, sempre levo em conta o tipo da coluna em que colocarei um índice, é recomendável que índices estejam em colunas do tipo: int, char, bigint, tinyint, smallint e datetime.
Natureza das consultas – As naturezas das consultas se referem em qual campo da minha tabela devo criar um índice, exemplo: Imagine um cenário que tenho que criar uma consulta que traga os pedidos emitidos por data de emissão e que a situação seja somente os pedidos liberados. Naturalmente na minha tabela de pedidos eu teria o campo data de emissão e situação do pedido. Essas são colunas aconselháveis para a criação de índices.
Os índices são úteis, porém consomem espaço em disco e acarretam custos de manutenção e sobrecarga. Não crie um índice que não será usado com freqüência.

Índice Clustered ou Agrupamento
Esse tipo de índice é útil para as colunas pesquisadas com freqüência ou em busca de chave de valores. Ao criar um índice desse tipo, considere as seguintes diretrizes:
• Cada tabela só pode ter um índice clustered.
• A ordem física dos registros da tabela e a ordem dos registros do índice são iguais. Primeiramente é aconselhável que seja criado o indice clustered antes do índice não clusterizado.
• Quando um registro é excluído, o espaço é restaurado e torna-se disponível para outro registro.
• Quando se cria uma coluna como PRIMARY KEY, automaticamente essa coluna torna-se um índice clustered.
• O tamanho médio de um índice clustered é aproximadamente 5% do tamanho da tabela. No entanto, esse tamanho varia dependendo do tamanho da coluna indexada.

Índice No-Clustered ou Sem Agrupamento
Um índice não clusterizado é eficiente quando os usuários precisam de vários critérios de pesquisa. Por exemplo, um vendedor pode pesquisar na tabela de pedidos pelo número do pedido, data de emissão, cliente e o representante deste cliente. Ao criar um índice não clusterizado, considere os seguintes fatos:
• A ordem das páginas no nível folha se um índice não clusterizado é diferente da ordem
• Pode existir até 249 índices não clusterizado por tabela.
• O SQL Server recria automaticamente os índices não clusterizados existentes quando ocorre uma das situações a seguir:
• Um índice clustered é criado.
• Um índice clustered é descartado.
• A opção DROP_EXISTING é usada para alterar as colunas que definem o índice de agrupamento.

Colunas que devem ser indexadas.
Crie índices em colunas pesquisadas com freqüências como:
• Chaves primárias (PK).
• Chaves externas (FK) ou colunas usadas frequentemente para unir tabelas.
• Colunas pesquisadas para a localização de faixa de valores de chave. (quando usamos a clausula IN, NOT IN, BETWEEN).
• Colunas acessadas na ordem de classificação (ORDER BY)
• Colunas usadas durante a agregação. (GROUP BY)

Colunas que NÃO devem ser indexadas.
Não referencie colunas que:
• Raramente são usadas em um consulta.
• Contenham poucos valores únicos. Por exemplo, um índice em uma coluna com dois valores, Masculino e feminino, retorna uma alta porcentagem de registros.
• Sejam definidas com os tipos de dados text, ntext, varchar e image. Não é possível indexar colunas com esses tipos de dados.

Criando índices.
Ao criar índice em uma tabela considere os fatos:
• Você deve ser proprietário da tabela.
• Quando se cria uma constraint do tipo PRIMARY KEY e UNIQUE, automaticamente o SQL Server cria índices clustered para elas.
• Você pode criar índices em VIEWS
• Você não pode criar índice em colunas que já existam os mesmos.
• Você pode criar índice na hora de criação de sua tabela.

Sintaxe:
CREATE NONCLUSTERED INDEX nomeDoIndice ON Tabela(CampoIndexado)
Apagando índice
Ao apagar um índice você deve levar em conta as seguintes diretrizes:
• Você não pode apagar os índices criados pelas restrições PRIMARY KEY e UNIQUE.
• Você não pode apagar os índices das tabelas do sistema.
• Você deve está no banco que reside o índice para apagá-lo.

Sintaxe:
DROP INDEX Tabela.NomeDoIndice

Na prática:
DROP INDEX DptoEmpresa.IdxEmpresaID

Até a próxima e espero que todos tenham gostado.
Obs: Esse post retirei de um site que havia publicado a muito tempo atras quando comecei a trabalhar com SQL Server, futuramente irei adicionar a questão de indicec om as features do SQL Server 2008

Ola Mundo SQL!

Ola, galera. Bom dia! Esse é o primeiro “post” que estou fazendo no blog. Pq a idéia de ter criado o blog com uma tagline “Aprendendo SQL Server”? A idéia veio no decorrer desta semana  que estou participando de um curso de SQL Server Internals em SP lecionado pelo Luti (neste momento o [Luti] está falando sobre deadlocks..rs.). Uma coisa muito importante que aprendi neste treinamento é que tenho q estudar muitooooooooooooooo mais, desde que sentei na sala e vi o Luciano falar quero me dedicar mais ainda. Pois o “mundo SQL Server” é extenso , fascinante e nao da pra saber tudo.  Então fica a lição: Se você acha que sabe muito e não precisa realizar cursos,treinamentos e ler mais livros! Preocupe-se provavelmente você está equivocado.  Por hoje é só( Voltando á aula)….

Ta-ta for Now

Categories: Pessoal