Impactos na ordenação de colunas em índices Não-Clusterizados, no Microsoft SQL Server 2005, 2008 e R2.

Pessoal, bom dia!

Tudo bem?

———————————————————————————————————————————————

No artigo de hoje, vou apresentar algo que normalmente não se preocupamos quando estamos criando um índice. Estou falando dos impactos que podem ocorrer em nosso ambiente quando utilizamos índices não-clusterizados compostos.

Naturalmente ao criamos um novo índice, estamos adicionando mais um mecanismo de consulta, pesquisa e organização de dados, alocados em uma tabela. Isso quando estamos criando um índice simples, formado por uma única coluna.

Mas todo e qualquer índice pode conter mais de uma coluna, sendo o mesmo, reconhecido como índice composto. Com base, neste cenário, algumas dúvidas podem surgir, dentre elas se o SQL Server pode sofrer alguns impactos quando estamos fazendo uso deste índice dependendo da ordem de criação e utilização das colunas.

Para começar, vou montar nosso ambiente de Exemplo, composto por suas tabelas: Tabela1 e Tabela2, conforme o Código 1 apresentado abaixo:

Código 1 – Criando as Tabelas fisicamente:

CREATE TABLE dbo.Tabela1

(Coluna1 int NOT NULL,

 Coluna2 int NULL,

 Coluna3 int NULL,

 Coluna4 varchar(50) NULL)

GO

 

CREATE TABLE dbo.Tabela2

(Coluna1 int NOT NULL,

 Coluna2 int NULL,

 Coluna3 int NULL,

 Coluna4 varchar(50) NULL)

GO

Note que ambas as tabelas foram criadas inicialmente sem qualquer tipo de índice, inclusive chave primária, sendo este, o próximo passo, apresentado no Código 2:

Código 2 – Adicionando as Chaves Primárias:

ALTER TABLE dbo.Tabela1

 ADD CONSTRAINT PK_Tabela1 PRIMARY KEY CLUSTERED (Coluna1)

GO

 

ALTER TABLE dbo.Tabela2

 ADD CONSTRAINT PK_Tabela2 PRIMARY KEY CLUSTERED (Coluna1)

GO

Acabamos de adicionar em ambas as tabelas uma chave primária do tipo Clusterizada, com isso, estas tabelas possuem neste momento um índice clusterizado, que será utilizado conforme sua necessidade, mas por padrão o SQL Server vai utilizar a chave primária como mecanismo que garanta a unicidade de nossos dados.

Vamos então adicionar algumas linhas de registros em nossas tabelas, conforme apresenta a seguir o Código 3:

Código 3 – Populando as tabelas:

DECLARE @valor INT

SET @valor=1

 

WHILE @valor < 1000

BEGIN 

   INSERT INTO dbo.Tabela1(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,’Valores’)

   INSERT INTO dbo.Tabela2(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,’Valores’)

   SET @valor=@valor+1

END

GO

Agora com nossas tabelas criadas, com suas respectivas chaves primárias e dados, vou começar a preparar o ambiente para demostrar o pode ocorrer em uma tabela que possui índices compostos não-clusterizados, para isso apresenta o Códiogo 4.

Código 4 – Criando um novo Índice na Tabela 1, composto por duas colunas:

CREATE NONCLUSTERED INDEX IND_Tabela1_Coluna2_Coluna3

ON dbo.Tabela1 (Coluna2,Coluna3)

 WITH (STATISTICS_NORECOMPUTE = OFF,

            IGNORE_DUP_KEY = OFF,

            ALLOW_ROW_LOCKS = ON,

            ALLOW_PAGE_LOCKS = ON)

  ON [PRIMARY]

GO

Nosso ambiente já esta praticamente concluído, e sua estrutura encontra-se distribuída da seguinte forma, conforme apresenta a Figura 1:

Figura 1: Representação da estrutura do ambiente, tabelas e índices criados.

Tudo pronto e preparado para começarmos a brincadeira. Para que tentar aproximar e ilustrar ainda mais o comportamento do SQL Server, por parte dos seus processos de execução e informações estatísticas, vou fazer uso de uma Diretiva SET existente no SQL Server, responsável em apresentar estas informações.

Estou me referindo a Diretiva: SET STATISTICS IO ON, responsável em apresentar o retorno de informações estatísticas existentes no SQL Server conforme as transações são processadas pelo Query Processor. Através do Código 5, vou realizar a ativação deste diretiva.

Código 5 – Ativando a exibição de informações sobre Estatísticas com retorno em Tela:

SET STATISTICS IO ON

 

A partir do momento em que ativamos o retorno de informações estatísitcas, a Guia Messagem apresentada no Result Set do SQL Server, vai apresentar informações sobre o processamento estatístico realizado pelo SQL Server e não informações quantitativas sobre o que foi afetado pelo bloco de transações.

 

O próximo passo será utilizar o comando DBCC DropCleanBuffers, responsável em limpar e remover todos os buffers limpos que possam exisitir dentro do Pool do SQL Server. Para isso, vou utilizar o Código 6.

 

Código 6 – Removendo todos os buffers limpos do Pool de buffers do SQL Server:

DBCC DROPCLEANBUFFERS

GO

O objetivo de limparmos o Pool de buffers no SQL Server é apresentar o retorno de informações estatísticas da forma mais precisa de acordo com a sua quantidade de leituras e escritas.

Nossa brincadeira esta ficando boa, vamos então, por um pouco mais de açúcar neste bolo e verificar através do retorno de informações estatísticas o que pode acontecer quando estamos fazendo de um índice não-clusterizado composto, com base, no código 7, apresentado a seguir:

Código 7 – Consultando os Dados na Tabela 1, através da Coluna3:

SELECT * FROM dbo.Tabela1

WHERE Coluna3=99

GO

Este simples Select esta fazendo a consulta de dados com base no Coluna3, limitando o resultado de registros que possuam valor igual 88, mas o que importa neste cenário é o retorno estatístico que o SQL Server nos apresenta na guia Message, consolidado na Tabela 1 apresentada abaixo:

Retorno – Estatísticos – Código 7

Row(s)

Scan Count

Logical Reads

Physical Reads

Read-ahead reads

1

1

6

5

4

 

O próximo passo será fazer a execução de um Select similar ao apresentado no Código 7, mas com uma pequena mudança, ao invés de utilizar a Coluna3 vamos utilizar a Coluna2, conforme apresenta o Código 8:

Código 8 – Consultando os Dados na Tabela 1, através da Coluna2:

–Removendo todos os buffers limpos do Pool de buffers do SQL Server–

DBCC DROPCLEANBUFFERS

GO

 

–Consultando dados–

SELECT * FROM dbo.Tabela1

WHERE Coluna2=99

GO

Retorno – Estatísticos – Código 8

Row(s)

Scan Count

Logical Reads

Physical Reads

Read-ahead reads

1

1

4

4

0

A princípio já podemos observar que existe uma diferença de retorno estatístico de dados na execução do Código 8 em relação ao Código 7, nos indicando uma diferença considerável de leituras Lógicas e Físicas entre eles.

Mas o que pode ter acontecido para ocorrer esta diferença de valores estatísticos entre estes códigos?

Para tentar responder esta pergunta, vou utilizar o Plano de Execução apresentado pelo SQL Server, executando novamente os Código 7 e Código 8.

Após executar novamente o Código 7, nos deparemos com o seguinte Plano de Execução, conforme apresenta a Figura 2:

Figura 2 – Plano de Execução – Código 7.

Podemos notar que o SQL Server realizou uma busca direta de registros fazendo uso de nosso índice Clusterizado que se encontra amarrado a nossa chave primária. O que normalmente nos oferece um ganho de performance.

Após executarmos novamente o Código 8, nos deparemos com o seguinte Plano de Execução, conforme apresenta a Figura 3:

Figura 3 – Plano de Execução – Código 8.

Observando este Plano de Execução, já conseguimos identificar facilmente o que temos de diferença em relação ao Plano de Execução apresentado no Código 7. O Query Optimizer identificou como melhor caminho para processo o Código 8, a utilização de dois operadores para encontrar o dado desejado, neste caso: Index Seek(Non-Clustered) e Key Lookup para depois consolidar estes dados no operador Nested Loops.

Então, parece ser algo de outro mundo esta mudança radical de comportamento do SQL Server, mas não é. Muito pelo contrário é algo bastante simples de se entender, como criamos um índice não-clusterizado composto e a primeira coluna declarada na composição deste índice foi a Coluna 2, o Query Optimizer entende que ao usar esta Coluna que faz parte de um índice e a mesma é a primeira coluna do índice.

Neste cenário, o Query Optimizer faz uso do operador Key Lookup para vasculhar dados no índice Clusterizado que em conjunto com este operador utiliza o operador Nested Loops. Em contra partida o operador Index Seek(Non-Clustered) realiza a pesquisa de dados sobre a coluna utilizada no índice não-clusterizado.

Conclusão

Podemos concluir que o uso de índices em nossas tabelas pode de alguma forma melhor a performance no nosso ambiente, mas por outro lado apresentar um comportamento totalmente diferente em relação ao que entendemos ser o mais indicado. Sendo assim, utilizar muitos índices em uma tabela ou muitas colunas pode acabar impactando em nosso ambiente.

———————————————————————————————————————————————

Mais uma vez quero agradecer a sua visita.

Nos encontramos em breve.

Anúncios

Sobre Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes, bem como, Professor Titular na Fatec São Roque. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulações e Reconhecimentos: Microsoft MVP, MCC, MSTC e MIE.
Esse post foi publicado em Dicas, Mundo SQL Server, Notícias, Scripts, SQL Server, VIRTUAL PASS BR e marcado , , , , , , . Guardar link permanente.

Uma resposta para Impactos na ordenação de colunas em índices Não-Clusterizados, no Microsoft SQL Server 2005, 2008 e R2.

  1. Pingback: Índices – Fundamentos, Estruturas e Usabilidade « Alex Souza

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s