Entendendo o funcionamento e operações dos Índices Online.

Olá pessoal, tudo bem?

Estou voltando com mais um artigo para vocês, hoje vou dedicar este espaço para abordar um assunto pouco discutido e a meu ver importante para qualquer Administrador de Banco de Dados, os chamados Índices Online ou Índices de Tempo Real.

Introdução

Introduzidos inicialmente no Microsoft SQL Server 2005 na edição Enterprise, os Índices Online, nasceram como uma possibilidade de manter o ambiente do Microsoft SQL Server disponível aos usuários em seus processos de acesso aos dados de uma tabela e seus demais recursos, sem ocasionar qualquer tipo de interrupção.

Os índices online funcionam da mesma forma que um índice normal, seja ele: Clusterizado ou Não-Clusterizado, são recursos que possuem a finalidade de pesquisa, busca, catálogo e armazenamento de dados utilizados como mecanismos chaves para a identificação de registros em uma tabela.

A seguir destaco um pouco do funcionamento deste modelo de índice.

Compreendendo a função um Índice Online

A qualquer momento é possível: criar, excluir, recriar e reconstruir um índice online. Para isso é necessário utilizar a opção Online em conjunto com o comando de manipulação de Índice, sejam eles:

  • CREATE INDEX;
  • ALTER INDEX;
  • DROP INDEX;      e
  • ALTER TABLE.

Através da opção Online, o índice que esta sendo manipulado no momento do processamento da transação, permite que o SQL Server mantenha disponível o acesso aos dados do usuário em suas tabelas ou outros índices que possuem relação e associação com base na operação realizada por este índice online.

 

A grande vantagem deste modelo de índice consiste justamente na liberada que é permite ao usuário realizar seus procedimentos sem sofrer qualquer tipo de impacto, mas principalmente para o Administrador de Banco de Dados que terá a sua disposição a capacidade de realizar procedimentos em tempo real sem necessitar realizar operações de forma off-line para que posteriormente sejam aplicadas em seu ambiente real, o que normalmente poderá chegar possíveis bloqueios de dados tanto na tabela ou índice que estará sendo afetado pela transação.

 

Este grande benefício pode ser ainda mais importante para as grandes corporações que não permitem ou possibilitam qualquer tipo de interrupção em seu ambiente, considerados como missão crítica ou de transações de usuário realizadas 24 horas por, durante os sete dias da semana.

 

Compreendendo o funcionamento e estruturas de um Índice Online

No momento em que um Índice Online encontra-se em uso, algumas operações são executadas pelo Microsoft SQL Server que permitem garantir o funcionamento em tempo real do ambiente, sem gerar ou ocasionar qualquer tipo de impacto ou bloqueio de recursos.

 

As chamadas operações, internamente são tratadas pelo Microsoft SQL Server, como estruturas de atividades unidas, composta como instruções da Linguagem de Definição de Dados de índice DLL.

 

Estas instruções são executadas durante as operações que o índice online esta realizando.  A seguir destaco as estruturas existentes para qualquer índice online:

 

  • Índices de fonte e preexistentes:
  • Destino:
  • Índice de mapeamento temporário:
    • A fonte é a tabela original ou os dados de índice de clusterizado.
    • Índices preexistentes são quaisquer índices não-clusterizados       associados à estrutura de fonte.
      • Por exemplo, se a operação de índice online estiver recriando um índice        clusterizado que tenha quatro índices não-clusterizados associados, a        fonte será o índice clusterizado existente e os índices preexistentes        serão os índices não-clusterizados.
      • Os índices preexistentes estão disponíveis a usuários simultâneos        para operações de seleção, inserção, atualização, e exclusão. Isto        inclui inserções em massa (aceitas, mas não recomendadas) e atualizações        implícitas por gatilhos e restrições de integridade referenciais.
      • Todos os índices preexistentes estão disponíveis para consultas e        pesquisas. Isto significa que eles podem ser selecionados pelo        otimizador de consulta e, se necessário, especificados pelas dicas de índice.
    • O destino ou destinos são o novo índice (ou heap) ou um conjunto       de novos índices sendo criados ou recriados. As operações de inserção,       atualização, e exclusão do destino são aplicadas pelo Mecanismo de banco       de dados do SQL Server no destino durante a operação de índice.
      • Por exemplo, se a operação de índice online estiver recriando um índice        online, o alvo é o índice clusterizado recriado; o Mecanismo de Banco de        Dados não recria índices não-clusterizados quando um índice clusterizado        é recriado.
      • O índice de destino não é pesquisado enquanto as instruções        SELECT são processadas, até que a operação de índice esteja confirmada.        Internamente, o índice é marcado como somente leitura.
    • As operações de índice online que criam, soltam, ou recriam um índice       clusterizado também requerem um índice de mapeamento temporário.
    • Este índice temporário é usado por transações simultâneas para       determinar quais registros devem ser exclusos nos novos índices sendo       criados, quando as linhas na tabela subjacente são atualizadas ou       exclusas.
    • Este índice não-clusterizado é criado no mesmo passo do novo índice       clusterizado (ou heap) e não requer uma operação de classificação       separada.
    • As transações simultâneas também mantêm o índice de mapeamento       temporário em todas as operações de inserção, atualização, e exclusão.

Atividades

Conforme destacado anteriormente, cada estrutura apresenta algumas atividades específicas para serem utilizadas com os Índices Online. Estas atividades são executadas em três fases:

 

  1. Preparação;
  2. Criação; e
  3. Finalização.

Para tentar ilustrar, as atividades e suas três fases são executadas, apresento a Figura 1.

Figura 1- Processo de Criação de um índice clusterizado online.

Atividades de Índice Online

A Figura 1, apresentada anteriormente demonstra o processo de criação um índice clusterizado online. Podemos notar que o objeto fonte, o heap, não possui nenhum outro índice.

As atividades de estrutura de fonte e destino são mostradas para cada uma das fazes, e as operações de inserção, atualização, e exclusão simultâneas do usuário, também são mostradas.

Nas fases de preparação, criação, e de finalização são indicadas juntas com os modos de bloqueio usados em cada fase.

Atividades da Estrutura Fonte

A seguinte Tabela 1 lista as atividades envolvendo as estruturas fonte durante cada fase da operação de índice, e a estratégia de bloqueio correspondente:

Fase Atividade fonte Bloqueios fonte
Preparação

Fase   muito curta

Preparação dos metadados do sistema para criar uma   nova estrutura de índice vazia.

Um instantâneo da tabela é definido. O que significa   que o controle de versão de linha é usado para fornecer uma consistência de   leitura de nível de transação. Para obter mais informações, consulte   Compreendendo níveis de isolamento com base em controle de versão de linha.

As operações de leitura de usuário simultâneo na fonte   são bloqueadas por um período muito curto.

Operações DLL não simultâneas são permitidas, exceto   na criação de múltiplos índices não-clusterizados.

S   (Shared) na tabela *

IS   (Intent Shared)

INDEX_BUILD_INTERNAL_RESOURCE**

Criar

Fase   principal

Os dados   são digitalizados, classificados, mesclados e inseridos no destino nas   operações de carregamento em massa.

As   operações de exclusão, atualização, inserção, e seleção por usuários   simultâneos, são aplicadas aos índices preexistentes e quaisquer outros novos   índices em criação.

IS (Intent Shared)

INDEX_BUILD_INTERNAL_RESOURCE**

Final

Fase   muito curta

Todas as transações atualizadas não confirmadas   devem ser completadas antes do início dessa fase. Dependendo do bloqueio   adquirido, todas as novas transações de leitura ou gravação de usuário devem   ser bloqueadas por um período muito curto até que essa fase seja completada.

Os metadados do sistema estão atualizados para   substituir a fonte com o destino.

A fonte é solta se for requerido. Por exemplo, após   recriar ou soltar um índice clusterizado.

INDEX_BUILD_INTERNAL_RESOURCE**

S (Shared) na tabela se estiver criando um índice   não-clusterizado. *

SCH-M (Schema Modification) se qualquer estrutura de   fonte (índice ou tabela) for solta.*

Tabela 1 – Fases, atividades e bloqueios existentes na Estrutura Fonte:


Onde:

* A operação de índice irá esperar que qualquer transação atualizada não confirmada seja concluída, antes de adquirir o bloqueio S-lock ou SCH-M na tabela.

** O bloqueio de recurso INDEX_BUILD_INTERNAL_RESOURCE evita a execução de operações DLL simultâneas na fonte e em estruturas preexistentes, enquanto a operação de índice está em progresso. Por exemplo, esse bloqueio evita a criação simultânea de dois índices na mesma tabela. Embora esse bloqueio de recurso esteja associado ao bloqueio Sch-M, ele não evita as instruções de manipulação de dados.

 

Passos:

 

A Tabela 1 mostra a seqüência de passos que cada atividade dentro da sua fase realizou, durante o processo de criação de um índice online. Destacando:

 

  • O único bloqueio Shared (S) adquirido durante a fase de criação de uma operação de índice online que envolve um único índice.

 

  • Posteriormente, quando os índices clusterizados e não-clusterizados são criados ou recriados em uma única operação de índice online (por exemplo, durante a criação de índice clusterizado inicial em uma tabela que contém um ou mais índices não-cluster), dois bloqueios Shared (S) de curta duração, são adquiridos durante a fase de criação, sendo seguido por bloqueios IS de longa duração.

 

  • Em seguida, um bloqueio S é adquirido primeiramente para a criação de índice clusterizado, e quando a criação do índice clusterizado esteja terminada, um bloqueio S de curta duração é adquirido para a criação dos índices não-clusterizados.

 

  • Por fim, depois que os índices não-clusterizados são criados, o bloqueio S fechadura é desatualizado para um bloqueio IS até a fase final da operação de índice online.

 

Atividades da Estrutura de Destino

 

A Tabela 2 lista as atividades envolvendo as estruturas destino durante cada fase da operação de índice, e a estratégia de bloqueio correspondente:

 

Fase Atividade de destino Bloqueios de destino
Preparação Um novo índice é criado e configurado para apenas   gravação. IS   (Intent Shared)
Criar Os dados   são inseridos a partir da fonte.

As   modificações de usuário (inserções, atualizações, exclusões) aplicadas à   fonte são aplicadas.

Esta   atividade é transparente ao usuário.

IS (Intent Shared)
Final Os metadados de índice são atualizados.

O índice é configurado para o status   leitura/gravação.

S   (Shared)

ou

SCH-M   (Schema Modification)

Tabela 2 – Fases, atividades e bloqueios existentes na Estrutura de Destino:

Passos:

A Tabela 2 mostra a seqüência de passos que cada atividade dentro da sua fase realizou, durante o processo de busca e identificação dos dados. Destacando:

  • O destino não é acessado por instruções SELECT emitidas pelo usuário até que a operação de índice seja concluída.
  • Após a preparação e fase final estarem concluídas, os planos de consulta e atualização armazenados no cache de procedimento são invalidados. Consultas subseqüentes usarão o novo índice.

 

  • O tempo de vida de um cursor declarada em uma tabela que está envolvida em uma operação de índice online está limitada pelas fases de índice online. Os cursores de atualização são invalidados a cada fase. Cursores de somente leitura são invalidados somente após a fase final.

 

Considerações sobre a opção Online

As diretrizes a seguir são aplicáveis ao executar operações de índice online:

  • A tabela subjacente não pode ser alterada, truncada ou descartada quando uma operação de índice online estiver em andamento.
  • É necessário espaço em disco temporário adicional durante a operação de índice. Para obter mais informações, consulte Determinando requisitos de espaço em disco de índice.
  • As operações online podem ser executadas em índices particionados e índices que contenham colunas computadas ou colunas incluídas persistentes.

 

 

Cláusulas ON e OFF

Para se trabalhar com a opção Online, temos duas possibilidades utilizando as cláusulas On ou OFF. Estas cláusulas modificam o comportamento do Microsoft SQL Server no momento da criação, recriação ou alteração de um índice.

Tendo como finalidade, especificar se as tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF.

 

ON: Bloqueios de tabela de longa duração não são mantidos pelo tempo de operação do índice. Durante a fase principal da operação de índice, apenas um bloqueio Tentativa Compartilhada é mantido na tabela de origem.

 

Ele permite o prosseguimento de consultas ou atualizações feitas na tabela e nos índices subjacentes. No início da operação, um bloqueio Compartilhado (S) é mantido no objeto de origem por um período muito curto.

 

Ao término da operação, por um curto período de tempo, um bloqueio S (Compartilhado) será adquirido na origem se um índice não clusterizado estiver sendo criado; ou um bloqueio de modificação de esquema (SCH-M) será adquirido quando um índice clusterizado for criado ou descartado online e quando um índice clusterizado ou não clusterizado estiver sendo recriado.

 

Não será possível definir ONLINE como ON quando um índice estiver sendo criado em uma tabela temporária local.

 

OFF: Os bloqueios de tabela são aplicados enquanto durar a operação de índice. Uma operação de índice off-line que cria, recria ou cancela um índice clusterizado ou recria ou cancela um índice não clusterizado, adquire um bloqueio de esquema de modificação (Sch-M) na tabela.

 

Isso evita o acesso de todos os usuários à tabela subjacente enquanto durar a operação. Uma operação de índice off-line que cria um índice não clusterizado adquire um bloqueio compartilhado (S) na tabela. Ele impede a realização de atualizações na tabela subjacente, mas permite operações de leitura, como instruções SELECT.

 

Para um índice XML ou índice espacial, só há suporte para ONLINE = OFF e, se ONLINE for definido como ON, um erro será gerado.

 

Utilizando um Índice Online

 

A seguir destaque um simples exemplo que ilustra a praticidade e facilidade de como podemos utilizar os Índices Online. Neste exemplo, todos os índices na tabela Product do banco de dados de exemplo AdventureWorks2008R2 são recriados online.

 

USE AdventureWorks2008R2;

GO

 

ALTER INDEX ALL ON Production.Product

REBUILD WITH (ONLINE = ON);

GO

 

Observações: Por se tratar de uma operação realizada diretamente em seu ambiente em tempo real, a utilização dos Índices Online, devem ser programadas e estudadas de acordo com a sua necessidade e disponibilidade.

 

A possibilidade de se fazer uso de operações de manutenção um ambiente em tempo real, oferecido pelo Índice Online, esta disponível nas edições: Enterprise, Developer e Evaluation do Microsoft SQL Server.

 

Sua utilização é bastante simples e não necessita de qualquer tipo de mudança de configuração ou ativação de recurso, fazendo somente uso da opção Online, conforme destacado anteriormente.

 

Conclusão

Como podemos perceber a utilização do Índice Online consiste na utilização da opção Online em conjunto com a instrução Transact – SQL que esta sendo processada.

A sua forma de utilização é bastante simples e prática, o que favorece ainda mais o seu uso, sem necessitar de qualquer tipo de configuração ou mudança de recursos do Microsoft SQL Server.

Os benefícios que este tipo de recurso pode trazer para seu ambiente esta o além de manutenções em tempo real, na verdade os índices online, possibilitam ao Administrador de Banco de Dados re-planejar sua forma de trabalho e possibilitar que o seu ambiente continue disponível mesmo nos momentos que algumas manutenções estão sendo aplicados.

Para o usuário a utilização deste tipo de recurso é transparente, seus dados continuam disponíveis e acessíveis em tempo real, mesmo que atividades de manipulação dos objetos que estão vinculados com estes índices no momento em que algumas das fases processadas por um índice online venham a ser executada.

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 MSDN, Mundo SQL Server, Scripts, SQL Server, TechNet, VIRTUAL PASS BR, Windows e marcado , , , , , , , , . Guardar link permanente.

Uma resposta para Entendendo o funcionamento e operações dos Índices Online.

  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