Índices no SQL Server – parte 01,02

[Black Draguns 0]

Índices no SQL Server – parte 01

 

 

Com que dados eu fundamento isso? Experiência, fiote… experiência… Trabalhei alguns anos com Oracle, já desenvolvi em/para Access e MySQL, além do DB2 e Sybase. Mas eu gostei mesmo é do SQL Server.

Vamos ao assunto do post:

Um recurso que melhora muito a velocidade de acesso aos dados armazenados é o tal de índice. Já tinha publicado esse post no meu site, mas vale a pena rever pois é muito atual.

Primeiro: qual a função de um índice? Melhorar o desempenho em consultas.

Segundo: quais os tipos de índice que temos no SQL Server? Dois: CLUSTERED e NON-CLUSTERED.

Terceiro: o que é um índice? É uma estrutura de árvore (balanceada – um tipo de árvore binária).

Quarto: é mais rápido que pesquisar na tabela, mesmo? Agora sim apareceu A pergunta… vamos começar por ela.

Inicialmente, precisamos definir árvore binária: uma estrutura formada por nós (vértices) e por folhas. Os nós são para determinar a localização de uma informação qualquer (na figura - uma representação simplificada - abaixo, 1-4, 1-2 e 3-4 são nós). As folhas (1, 2, 3 e 4, na figura abaixo) são as informações armazenadas.

Imagine, agora, que minha tabela tenha somente 2 registros: olhando para um deles saberei se é aquele que quero, ou não.

Se tiver 4 registros, devo procurar um máximo de 2 vezes. Veja a figura acima:

 

• 1a. pesquisa: o número está entre 1 e 2 (na verdade, é a leitura do nó que contém 1-2)?
  
• 2a. pesquisa: se o número está entre 1 e 2: o número é 1 (leitura da folha que tem o conteúdo 1)? Se for, ok. Se não for, é o 2. Ou:
  
• 2a. pesquisa: se o número não está entre 1 e 2: o número é 3 (leitura da folha que tem o conteúdo 3)? Se for, ok. Se não for, é o 4.
  

Extrapolando, podemos montar uma tabela como abaixo, já que sabemos ser o número de pesquisas necessárias igual ao expoente a que 2 deve ser elevado para “dar” a quantidade de registros:

QTD REGISTROS LEITURAS até 2 1 2 = 21 até 4 2 4 = 22 até 8 3 8 = 23 … … … até 1.024 10 1.024 = 210 até 1.048.576 20 1.048.576 = 220 Mas, onde esses índices são gravados? Ops… Onde qualquer dado (tabela ou índice) é gravado? A resposta é: numa estrutura chamada PAGE. Uma page (página de dados) tem 8kB (8.192 bytes). Desse valor, tiramos 96 bytes, que são usados para endereçamento interno. O que sobra (8.096 bytes) está disponível para armazenamento de dados.

Uma page pode conter dados de tabelas ou de índices. Outra coisa: uma page somente pode conter dados de uma ÚNICA tabela ou de um ÚNICO índice.

Agora, sim. Podemos dizer que:

 

• ÍNDICE NON-CLUSTERED – tem os nós e folhas gravados em páginas de dados independentes da tabela;
  
• ÍNDICE CLUSTERED – os nós são uma estrutura separada da tabela, mas as folhas são os próprios registros. As duas estruturas (índice e tabela) são mescladas.
  

Para os exemplos seguintes, usaremos a tabela criada com o bloco de comandos abaixo:

CREATE DATABASE EXEMPLO_INDICE

GO

USE EXEMPLO_INDICE

 

CREATE TABLE PESSOA

(

COD INT NOT NULL,

NOME VARCHAR(50),

SEXO CHAR(1)

)

Inserimos alguns registros:

INSERT INTO PESSOA VALUES(1, ‘BRUNO’, ‘M’)

INSERT INTO PESSOA VALUES(2, ‘NETINHO’, ‘M’)

INSERT INTO PESSOA VALUES(3, ‘PEDRO’, ‘M’)

INSERT INTO PESSOA VALUES(4, ‘AGNALDO’, ‘M’)

INSERT INTO PESSOA VALUES(5, ‘MARIA’, ‘F’)

INSERT INTO PESSOA VALUES(6, ‘ANA MARIA’, ‘F’)

INSERT INTO PESSOA VALUES(7, ‘MARIANA’, ‘F’)

INSERT INTO PESSOA VALUES(8, ‘MARINA’, ‘F’)

Agora, vamos fazer alguns selects e ver qual é o plano de execução gerado. Eita nóis… O que é um plano de execução? Plano de execução é um “mapa” gerado pelo mecanismo relacional que informa ao mecanismo de armazenamento onde ler ou gravar no disco, se vai acessar a tabela diretamente ou se vai usar um índice, se a pesquisa é feita por seek ou por scan.

De novo: eita ferro!!! O que são esses mecanismos? Quando instalamos o SQL Server e iniciamos o serviço MSSQLSERVER, temos na memória três elementos:

 

• Mecanismo relacional - responsável por gerar um plano de execução baseado nas estatísticas associadas aos dados das tabelas/índices;
  
• Mecanismo de armazenamento - responsável por acessar uma tabela ou índice, acompanhando o definido no plano de execução;
  
• ODS – open data services – fica “vigiando” uma porta do micro, esperando conexões de clientes. A porta padrão para o SQL Server é a 1433. Esse processo se chama listening.
  

Como vejo um plano de execução? Basta habilitar sua apresentação através das teclas de atalho [CTRL] + [M] ou acessar a opção [include Actual Execution Plan] do menu [Query]. Para testar, podemos selecionar alguns dados da tabela, usando:

SELECT * FROM PESSOA

O plano de execução apresentado é:

Onde Table Scan é o operador que significa que foi feito um acesso sequencial a todos os elementos da tabela Pessoa.

Para qualquer um dos comandos o plano de execução gerado é o mesmo:

SELECT * FROM PESSOA WHERE COD = 1

SELECT NOME FROM PESSOA

SELECT NOME FROM PESSOA WHERE NOME = ‘AGNALDO’

SELECT COD, NOME FROM PESSOA WHERE NOME = ‘AGNALDO’

SELECT * FROM PESSOA WHERE NOME = ‘AGNALDO’

Agora, vamos criar um índice do tipo non-clustered para o campo NOME da tabela, usando o comando:

CREATE NONCLUSTERED INDEX INDEX_PESSOA_NOME ON PESSOA(NOME)

Executando os comandos abaixo, temos planos de execução (uso de operadores) diversos:

SELECT * FROM PESSOA

–-TABLE SCAN

SELECT * FROM PESSOA WHERE COD = 1

–-TABLE SCAN

SELECT NOME FROM PESSOA

–-INDEX SCAN

SELECT NOME FROM PESSOA WHERE NOME = ‘AGNALDO’

–-INDEX SEEK

SELECT COD, NOME FROM PESSOA WHERE NOME = ‘AGNALDO’

–-TABLE SCAN

SELECT * FROM PESSOA WHERE NOME = ‘AGNALDO’

–-TABLE SCAN

Criamos agora, um índice clustered, para o campo COD da tabela:

CREATE CLUSTERED INDEX INDEX_PESSOA_COD ON PESSOA(COD)

E executamos os mesmos comandos:

SELECT * FROM PESSOA

–-CLUSTERED INDEX SCAN

SELECT * FROM PESSOA WHERE COD = 1

–-CLUSTERED INDEX SEEK

SELECT NOME FROM PESSOA

–-INDEX SCAN

SELECT NOME FROM PESSOA WHERE NOME = ‘AGNALDO’

–-INDEX SEEK

SELECT COD, NOME FROM PESSOA WHERE NOME = ‘AGNALDO’

–-INDEX SEEK

SELECT * FROM PESSOA WHERE NOME = ‘AGNALDO’

–-CLUSTERED INDEX SCAN

 

http://www.mxstudio.com.br/sql/indices-no-sql-server-parte-01/

 

Índices no SQL Server – parte 02

 

 

expliquei o que são os índices. Foi uma idéia geral. Mostrei, ainda, um plano de execução simples. Fiz alguns selects que posso agregar nas seguintes tabelas: # COMANDO 01 SELECT * FROM PESSOA 02 SELECT * FROM PESSOA WHERE COD = 1 03 SELECT NOME FROM PESSOA 04 SELECT NOME FROM PESSOA WHERE NOME = ‘AGNALDO’ 05 SELECT COD, NOME FROM PESSOA WHERE NOME = ‘AGNALDO’ 06 SELECT * FROM PESSOA WHERE NOME = ‘AGNALDO’ COMANDO SEM ÍNDICE NON-CLUSTERED CLUSTERED

e

NON-CLUSTERED 01 TABLE SCAN TABLE SCAN CLUSTERED INDEX SCAN 02 TABLE SCAN TABLE SCAN CLUSTERED INDEX SEEK 03 TABLE SCAN INDEX SCAN INDEX SCAN 04 TABLE SCAN INDEX SEEK INDEX SEEK 05 TABLE SCAN TABLE SCAN INDEX SEEK 06 TABLE SCAN TABLE SCAN CLUSTERED INDEX SCAN Nesse ponto, temos que sabe o que é a operação:

 

• SCAN – busca em TODOS os elementos da estrutura (que pode ser uma tabela ou um índice);
  
• SEEK - busca binária nos elementos de um índice.
  

Devemos atentar para a seguinte observação: SCAN é usado quando a tabela não possui índices que atendam ao select ou quando a quantidade de registros que a query retorna (em percentual) é grande. O SEEK é usado quando existe um índice que é adequado e a quantidade de registros retornados é pequena, percentualmente falando.

As operações executadas nas tabelas/índices foram:

 

• TABLE SCAN – Busca em todos os elementos da tabela, de forma seqüencial;
  
• INDEX SCAN -Busca em todos os elementos de um índice non-clustered, de forma seqüencial;
  
• CLUSTERED INDEX SCAN – Busca em todos os elementos de um índice clustered, de forma seqüencial;
  
• INDEX SEEK – Busca binária num índice non-clustered;
  
• CLUSTERED INDEX SEEK – Busca binária num índice non-clustered.
  

Agora, por que o SQL Server criou planos de execução tão diferentes? Porque ele “sabe” (usando as estatísticas – assunto que abordarei em outro post) qual tipo de acesso tem maior probabilidade de retornar os dados pedidos no menor tempo possível.

E como o SQL Server escreve os índices no disco? Vamos lá.

Para essa explicação, vamos dropar o database que estávamos usando e recriá-lo, junto com a tabela pessoa. Após isso, vamos inserir as 8 pessoas (como fizemos no post anterior).

Lembro que, para dropar um banco, usamos os comandos:

USE MASTER

DROP DATABASE EXEMPLO_INDICE

Após a criação da tabela, fazemos um select na tabela indexes do schema sys (que vou chamar, a partir de agora, de sys.indexes):

SELECT *

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID(‘PESSOA’)

Vemos que existe uma linha na sys.indexes, mesmo PESSOA não tendo índice. Na realidade, sys.indexes armazena dados dos índices (quando existem) e das tabelas. Sabemos que a tabela não possui índice clustered porque seu type_desc é HEAP.

Nesse ponto, podemos executar os comandos abaixo, que vão nos mostrar como estão alocadas as páginas de dados para essa tabela:

DECLARE @DB_ID INT,

@OBJECT_ID INT

SELECT @DB_ID = DB_ID(‘EXEMPLO_INDICE’),

@OBJECT_ID = OBJECT_ID(‘PESSOA’)

 

DBCC IND(@DB_ID, @OBJECT_ID, -1)

Onde:

 

• PagePID – é o número da página de dados onde a informação está escrita;
  
• IndexID - é o tipo de estrutura:
  
  • 0 – página de dados;
    
  • 1 – índice clustered;
    
  • 2 a 255 – índices non-clustered;
    

   

  [*]PageType – é o tipo do dado armazenado:

  • 1 – página de dados;
    
  • 2 – página de índice;
    
  • 10 – mapa de alocação de índices;
    

   

Executando o comando abaixo e fornecendo o valor de PagePID obtido com o script acima:

DECLARE @DB_ID INT

SELECT @DB_ID = DB_ID(‘EXEMPLO_INDICE’)

 

DBCC TRACEON(3604)

DBCC PAGE(@DB_ID, 1, 152, 3)

DBCC TRACEOFF(3604)

Temos a descrição do conteúdo das página em questão.

Agora, recriamos o índice non-clustered, usando o mesmo comando do post anterior. Em seguida fazemos o select na sys.objects que, nesse momento, nos mostra a existência do índice non-clustered.

Executando o DBCC IND (acima) vemos que foram alocadas páginas para o índice.

Com o DBC PAGE (acima) vemos o conteúdo da página – temos que substituir o valor 152 (página de dados da tabela) por 154 (página que armazena os dados do índice).

Vemos, na figura acima, que existe uma coluna (HEAP RID) que aponta para o endereço do registro na página de dados.

Recriamos o índice clustered. Em seguida executamos os mesmos comandos mostrados anteriormente (select na sys.indexes, DBCC IND e DBCC PAGE para o índice non-clustered, trocando o número da página, claro – no meu servidor, 158).

Note que o type_desc do select mudou de HEAP para CLUSTERED.

A coluna que aponta para endereço do registro não é mais o HEAP RID e sim COD, que é a chave do índice clustered. Por isso que uma consulta por cod, nome (o select número 5) na tabela pessoa faz um table scan quando a tabela somente tem o índice non-clustered e index seek quando a tabela tem índices clustered e non-clustered.

Com esses dois posts temos uma visão (simplificada) de como os índices do Microsoft SQL Server 2005 funcionam.

 

http://www.mxstudio.com.br/sql/indices-no-sql-server-parte-02/

 

 

Creditos Ao: MxStudio

[Pedroddcunha 3]

Obrigado pelo tutorial

 

 

- Aprovado -

[jackespelber 0]

Na Onde Baixa O Sql Server?

[torolecarte 0]

vc pode baixar as versões Express que são gratuitas, tem algumas limitações, mas acho que nd que atrapalhe o uso.

 

http://www.microsoft.com/express/download/

 

 

:yes:

[bonde116 0]

Tuto bem legal pra quem não sabe usá-lo obrigado aew pelo tuto

[Sorcerer of luz 0]

Ondi é que baixa?

ah é bello topico.