Guia de design e arquitetura de índice

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Projetar índices eficientes é fundamental para alcançar um bom desempenho de banco de dados e aplicativos. A falta de índices, a superindexação ou índices mal projetados são as principais fontes de problemas de desempenho do banco de dados.

Este guia descreve a arquitetura e os fundamentos do índice e fornece práticas recomendadas para ajudá-lo a projetar índices eficazes para atender às necessidades de seus aplicativos.

Para obter mais informações sobre os tipos de índice disponíveis, consulte Índices.

Este guia abrange os seguintes tipos de índices:

Formato de armazenamento principal	Tipo de índice
Armazenamento de linha baseado em disco
	Clustered
	Nonclustered
	Unique
	Filtered
Columnstore
	Columnstore agrupado
	Columnstore não clusterizado
Memory-optimized
	Hash
	Não clusterizado com otimização de memória

Para obter informações sobre índices XML, consulte Índices XML (SQL Server) e Índices XML seletivos (SXI).

Para obter informações sobre índices espaciais, consulte Visão geral de índices espaciais.

Para obter informações sobre índices de texto completo, consulte Preencher índices de Full-Text.

Noções básicas de índice

Pense em um livro comum: no final do livro, há um índice que ajuda a localizar rapidamente as informações dentro do livro. O índice é uma lista ordenada de palavras-chave e ao lado de cada palavra-chave há um conjunto de números de página apontando para as páginas onde cada palavra-chave pode ser encontrada.

Um índice de armazenamento de linhas é semelhante: é uma lista ordenada de valores e, para cada valor, há ponteiros para as páginas de dados onde esses valores estão localizados. O índice em si também é armazenado em páginas, referidas como páginas de índice. Em um livro normal, se o índice se estende por várias páginas e você tem que encontrar ponteiros para todas as páginas que contêm a palavra SQL , por exemplo, você teria que folhear desde o início do índice até localizar a página de índice que contém a palavra-chave SQL. A partir daí, você segue as indicações para todas as páginas do livro. Isso pode ser otimizado ainda mais se, no início do índice, você criar uma única página que contenha uma lista alfabética de onde cada letra pode ser encontrada. Por exemplo: "A a D - página 121", "E a G - página 122" e assim por diante. Esta página extra eliminaria a etapa de folhear o índice para encontrar o local de partida. Essa página não existe em livros comuns, mas existe num índice rowstore. Esta única página é referida como a página raiz do índice. A página raiz é a página inicial da estrutura de árvore usada por um índice. Seguindo a analogia da árvore, as páginas finais que contêm ponteiros para os dados reais são referidas como "páginas de folha" da árvore.

Um índice é uma estrutura no disco ou na memória associada a uma tabela ou exibição que acelera a recuperação de linhas da tabela ou exibição. Um índice rowstore contém chaves criadas a partir dos valores em uma ou mais colunas na tabela ou exibição. Para índices de armazenamento de linhas, essas chaves são armazenadas em uma estrutura de árvore (árvore B+) que permite que o Mecanismo de Banco de Dados localize as linhas associadas aos valores de chave de forma rápida e eficiente.

Um índice de armazenamento de linhas armazena dados organizados logicamente como uma tabela com linhas e colunas e armazenados fisicamente em um formato de dados em linha chamado rowstore¹. Há uma maneira alternativa de armazenar dados em colunas, chamada columnstore.

O design dos índices certos para um banco de dados e sua carga de trabalho é um ato de equilíbrio complexo entre a velocidade da consulta, o custo de atualização do índice e o custo de armazenamento. Índices estreitos de armazenamento de linhas baseados em disco, ou índices com poucas colunas na chave de índice, exigem menos espaço de armazenamento e uma sobrecarga de atualização menor. Índices amplos, por outro lado, podem melhorar mais consultas. Você pode ter que experimentar vários designs diferentes antes de encontrar o conjunto mais eficiente de índices. À medida que o aplicativo evolui, os índices podem precisar ser alterados para manter o desempenho ideal. Os índices podem ser adicionados, modificados e removidos sem afetar o esquema do banco de dados ou o design do aplicativo. Portanto, você não deve hesitar em experimentar diferentes índices.

O otimizador de consulta no Mecanismo de Banco de Dados geralmente escolhe os índices mais eficazes para executar uma consulta. Para ver quais índices o otimizador de consulta usa para uma consulta específica, no SQL Server Management Studio, no menu Consulta , selecione Exibir Plano de Execução Estimado ou Incluir Plano de Execução Real.

Nem sempre iguale o uso do índice com um bom desempenho e um bom desempenho com o uso eficiente do índice. Se o uso de um índice sempre ajudasse a produzir o melhor desempenho, o trabalho do otimizador de consulta seria simples. Na realidade, uma escolha incorreta do índice pode causar um desempenho inferior ao ideal. Portanto, a tarefa do otimizador de consulta é selecionar um índice, ou uma combinação de índices, somente quando ele melhora o desempenho e evitar a recuperação indexada quando ele prejudica o desempenho.

Um erro de design comum é criar muitos índices especulativamente para "dar opções ao otimizador". A superindexação resultante retarda as modificações de dados e pode causar problemas de simultaneidade.

¹ Rowstore tem sido a maneira tradicional de armazenar dados de tabelas relacionais. Rowstore refere-se a uma tabela onde o formato de armazenamento de dados subjacente é um heap, uma árvore B+ (índice clusterizado) ou uma tabela com otimização de memória. O armazenamento de linhas baseado em disco exclui tabelas com otimização de memória.

Tarefas de design de índice

As seguintes tarefas compõem nossa estratégia recomendada para projetar índices:

1. Compreender as características da base de dados e da aplicação.

   Por exemplo, em um banco de dados OLTP (processamento de transações online) com modificações de dados frequentes que devem sustentar uma alta taxa de transferência, alguns índices de armazenamento de linha estreitos direcionados para as consultas mais críticas seriam um bom design de índice inicial. Para uma taxa de transferência extremamente alta, considere tabelas e índices otimizados para memória, que fornecem um design sem bloqueios e travas. Para obter mais informações, consulte Diretrizes de design de índice não clusterizado com otimização de memória e Diretrizes de design de índice de hash neste guia.

   Por outro lado, para um banco de dados OLAP (analytics ou data warehousing) que deve processar conjuntos de dados muito grandes rapidamente, o uso de índices columnstore clusterizados seria especialmente apropriado. Para obter mais informações, consulte Columnstore indexes: overview ou Columnstore index architecture neste guia.

2. Compreender as características das consultas mais utilizadas.

   Por exemplo, saber que uma consulta usada com freqüência une duas ou mais tabelas ajuda a determinar o conjunto de índices para essas tabelas.

3. Compreender a distribuição de dados nas colunas usadas nos predicados de consulta.

   Por exemplo, um índice pode ser útil para colunas com muitos valores de dados distintos, mas menos para colunas com muitos valores duplicados. Para colunas com muitos NULLs ou aqueles que têm subconjuntos de dados bem definidos, você pode usar um índice filtrado. Para obter mais informações, consulte Diretrizes de design de índice filtrado neste guia.

4. Determine quais opções de índice podem melhorar o desempenho.

   Por exemplo, a criação de um índice clusterizado em uma tabela grande existente pode se beneficiar da ONLINE opção de índice. A ONLINE opção permite que a atividade simultânea nos dados subjacentes continue enquanto o índice está sendo criado ou reconstruído. Utilizar compactação de dados linha-a-linha ou em página pode melhorar o desempenho ao reduzir a carga de I/O e o espaço de memória do índice. Para obter mais informações, consulte CREATE INDEX.

5. Examine os índices existentes na tabela para evitar a criação de índices duplicados ou muito semelhantes.

   Muitas vezes, é melhor modificar um índice existente do que criar um índice novo, mas principalmente duplicado. Por exemplo, considere adicionar uma ou duas colunas adicionais incluídas a um índice existente, em vez de criar um novo índice com essas colunas. Isso é particularmente relevante quando você ajusta índices não clusterizados com sugestões de índice ausentes ou se usa o Orientador de Otimização do Mecanismo de Banco de Dados, onde podem ser oferecidas variações semelhantes de índices na mesma tabela e colunas.

Diretrizes gerais de design do índice

Compreender as características do banco de dados, consultas e colunas de tabela pode ajudá-lo a projetar índices ideais inicialmente e modificar o design à medida que seus aplicativos evoluem.

Considerações sobre o banco de dados

Ao criar um índice, considere as seguintes diretrizes de banco de dados:

• Um grande número de índices numa tabela afeta o desempenho de INSERT, UPDATE, DELETE, e MERGE instruções porque os dados em índices podem ter que mudar à medida em que os dados na tabela mudam. Por exemplo, se uma coluna for usada em vários índices e você executar uma UPDATE instrução que modifique os dados dessa coluna, cada índice que contém essa coluna também deverá ser atualizado.

  • Evite a indexação excessiva de tabelas muito atualizadas e mantenha os índices estreitos, ou seja, com o menor número possível de colunas.

  • Você pode ter mais índices em tabelas com poucas modificações de dados, mas grandes volumes de dados. Para essas tabelas, uma variedade de índices pode ajudar no desempenho da consulta enquanto a sobrecarga de atualização do índice permanece aceitável. No entanto, não crie índices especulativamente. Monitore o uso do índice e remova os índices não utilizados ao longo do tempo.

• A indexação de tabelas pequenas pode não ser ideal porque o Mecanismo de Banco de Dados pode levar mais tempo para percorrer o índice em busca de dados do que para executar uma verificação de tabela base. Portanto, os índices em tabelas pequenas podem nunca ser usados, mas ainda devem ser atualizados à medida que os dados na tabela são atualizados.

• Os índices em visualizações podem fornecer ganhos de desempenho significativos quando a exibição contém agregações e/ou junções. Para obter mais informações, consulte Criar vistas indexadas.

• Os bancos de dados em réplicas primárias no Banco de Dados SQL do Azure geram automaticamente recomendações de desempenho do supervisor de banco de dados para índices. Opcionalmente, você pode ativar o ajuste automático do índice.

• O Repositório de Consultas ajuda a identificar consultas com desempenho abaixo do ideal e fornece um histórico de planos de execução de consultas que permitem ver os índices selecionados pelo otimizador. Você pode usar esses dados para tornar as alterações de ajuste de índice mais impactantes, concentrando-se nas consultas mais frequentes e que consomem recursos.

Considerações sobre a consulta

Ao criar um índice, considere as seguintes diretrizes de consulta:

• Crie índices não agrupados nas colunas que são usadas com freqüência em predicados e junte expressões em consultas. Estas são as suas colunas SARGable . No entanto, você deve evitar adicionar colunas desnecessárias aos índices. Adicionar muitas colunas de índice pode afetar negativamente o espaço em disco e o desempenho da atualização do índice.

  O termo SARGable em bancos de dados relacionais refere-se a um predicadocapaz de Search ARGque pode usar um índice para acelerar a execução da consulta. Para obter mais informações, consulte SQL Server e Azure SQL index architecture and design guide.

  Tip

  Certifique-se sempre de que os índices criados são realmente usados pela carga de trabalho de consulta. Remova índices inutilizados.

  As estatísticas de utilização do índice estão disponíveis em sys.dm_db_index_usage_stats e sys.dm_db_index_operational_stats.

• A cobertura de índices pode melhorar o desempenho da consulta porque todos os dados necessários para atender aos requisitos da consulta existem dentro do próprio índice. Ou seja, apenas as páginas de índice, e não as páginas de dados da tabela ou índice clusterizado, são necessárias para recuperar os dados solicitados; portanto, reduzindo a E/S geral do disco. Por exemplo, uma consulta de colunas A e B em uma tabela que tem um índice composto criado em colunas A, Be C pode recuperar os dados especificados apenas do índice.

  Note

  Um índice de cobertura é um índice não clusterizado que satisfaz todos os dados acessados por uma consulta diretamente sem acessar a tabela base.

  Esses índices têm todas as colunas SARGable necessárias na chave de índice e colunas não SARGable como colunas incluídas . Isso significa que todas as colunas necessárias para a consulta, sejam nas cláusulas WHERE, JOIN e GROUP BY ou nas cláusulas SELECT ou UPDATE, estão presentes no índice.

  Há potencialmente muito menos E/S para executar a consulta, se o índice for estreito o suficiente, quando comparado com as linhas e colunas da própria tabela, ou seja, é um pequeno subconjunto de todas as colunas.

  Considere cobrir índices ao recuperar uma pequena porção de uma tabela grande e onde essa pequena porção é definida por um predicado fixo.

  Evite criar um índice de cobertura com muitas colunas, pois isso diminui seu benefício enquanto infla o armazenamento do banco de dados, a E/S e o espaço ocupado pela memória.

• Escreva consultas que inserem ou modifiquem o maior número possível de linhas em uma única instrução, em vez de usar várias consultas para atualizar as mesmas linhas. Isso reduz a sobrecarga de atualização do índice.

Considerações sobre a coluna

Ao criar um índice, considere as seguintes diretrizes de coluna:

• Mantenha o comprimento da chave de índice curto, especialmente para índices clusterizados.

• As colunas dos tipos de dados ntext, text, image, varchar(max), nvarchar(max), varbinary(max), json e vetor não podem ser especificadas como colunas de chave de índice. No entanto, colunas com esses tipos de dados podem ser adicionadas a um índice não clusterizado como colunas de índice não chave (incluídas). Para obter mais informações, consulte a seção Usar colunas incluídas em índices não clusterizados neste guia.

• Examine a exclusividade da coluna. Um índice exclusivo em vez de um índice não exclusivo nas mesmas colunas de chave fornece informações adicionais para o otimizador de consulta que tornam o índice mais útil. Para obter mais informações, consulte Diretrizes exclusivas de design de índice neste guia.

• Examine a distribuição de dados na coluna. Criar um índice em uma coluna com muitas linhas, mas poucos valores distintos, pode não melhorar o desempenho da consulta, mesmo que o índice seja usado pelo otimizador de consulta. Como analogia, uma lista telefônica física ordenada alfabeticamente no nome da família não agiliza a localização de uma pessoa se todas as pessoas na cidade forem chamadas Smith ou Jones. Para obter mais informações sobre distribuição de dados, consulte Estatísticas.

• Considere o uso de índices filtrados em colunas que tenham subconjuntos bem definidos, por exemplo, colunas com muitos NULLs, colunas com categorias de valores e colunas com intervalos distintos de valores. Um índice filtrado bem projetado pode melhorar o desempenho da consulta, reduzir os custos de atualização do índice e reduzir os custos de armazenamento armazenando um pequeno subconjunto de todas as linhas na tabela, se esse subconjunto for relevante para muitas consultas.

• Considere a ordem das colunas de chave de índice se a chave contiver várias colunas. A coluna usada no predicado de consulta em uma igualdade (=), desigualdade (>,>=,<,),<= ou BETWEEN expressão, ou participa de uma junção, deve ser colocada em primeiro lugar. As colunas adicionais devem ser ordenadas com base no seu nível de distinção, ou seja, da mais distinta para a menos distinta.

  Por exemplo, se o índice for definido como LastName, FirstName, o índice será útil quando o predicado de consulta na cláusula WHERE for WHERE LastName = 'Smith' ou WHERE LastName = Smith AND FirstName LIKE 'J%'. No entanto, o otimizador de consulta não usaria o índice para uma consulta pesquisada apenas no WHERE FirstName = 'Jane', ou o índice não melhoraria o desempenho dessa consulta.

• Considere a indexação de colunas computadas se elas estiverem incluídas em predicados de consulta. Para obter mais informações, consulte Índices em colunas computadas.

Características do índice

Depois de determinar que um índice é apropriado para uma consulta, você pode selecionar o tipo de índice que melhor se adapta à sua situação. As características do índice incluem:

• Agrupados ou não agrupados
• Único ou não único
• Coluna única ou coluna múltipla
• Ordem crescente ou decrescente para as colunas de chave no índice
• Todas as linhas ou filtradas, para índices não clusterizados
• Armazenamento em colunas ou armazenamento em linhas
• Hash ou não clusterizado para tabelas com otimização de memória

Posicionamento de índice em grupos de arquivos ou esquemas de partições

Ao desenvolver sua estratégia de design de índice, você deve considerar o posicionamento dos índices nos grupos de arquivos associados ao banco de dados.

Por padrão, os índices são armazenados no mesmo grupo de arquivos que a tabela base (índice clusterizado ou heap) na qual o índice é criado. Outras configurações são possíveis, incluindo:

• Crie índices não clusterizados em um grupo de arquivos diferente do grupo de arquivos da tabela base.

• Particione índices agrupados e não clusterizados para abranger vários grupos de arquivos.

Para tabelas não particionadas, a abordagem mais simples geralmente é a melhor: criar todas as tabelas no mesmo grupo de arquivos e adicionar quantos arquivos de dados forem necessários para utilizar todo o armazenamento físico disponível.

Abordagens mais avançadas de posicionamento de índice podem ser consideradas quando o armazenamento hierárquico estiver disponível. Por exemplo, você pode criar um grupo de arquivos para tabelas acessadas com freqüência com arquivos em discos mais rápidos e um grupo de arquivos para arquivar tabelas em discos mais lentos.

Você pode mover uma tabela com um índice clusterizado de um grupo de arquivos para outro descartando o índice clusterizado e especificando um novo grupo de arquivos ou esquema de partição na MOVE TO cláusula da DROP INDEX instrução ou usando a CREATE INDEX instrução com a DROP_EXISTING cláusula.

Índices particionados

Você também pode considerar o particionamento dos heaps baseados em disco, índices agrupados e não agrupados em vários grupos de arquivos. Os índices particionados são particionados horizontalmente (por linha), com base em uma função de partição. A função partition define como cada linha é mapeada para uma partição com base nos valores de uma determinada coluna que você designa, chamada coluna de particionamento. Um esquema de partição especifica o mapeamento de um conjunto de partições para um grupo de arquivos.

O particionamento de um índice pode fornecer os seguintes benefícios:

• Torne os grandes bancos de dados mais gerenciáveis. Os sistemas OLAP, por exemplo, podem implementar ETL com reconhecimento de partição que simplifica muito a adição e remoção de dados em massa.

• Faça com que certos tipos de consultas, como consultas analíticas de longa duração, sejam executados mais rapidamente. Quando as consultas usam um índice particionado, o Mecanismo de Banco de Dados pode processar várias partições ao mesmo tempo e ignorar (eliminar) partições que não são necessárias para a consulta.

Advertência

O particionamento raramente melhora o desempenho da consulta em sistemas OLTP, mas pode introduzir uma sobrecarga significativa se uma consulta transacional precisar acessar muitas partições.

Para obter mais informações, consulte Tabelas e índices particionados.

Diretrizes de conceção para a ordenação de índices

Ao definir índices, considere se cada coluna de chave de índice deve ser armazenada em ordem crescente ou decrescente. Ascendente é o padrão. A sintaxe das instruções CREATE INDEX, CREATE TABLE e ALTER TABLE suporta as palavras-chave ASC (ascendente) e DESC (descendente) em colunas individuais em índices e restrições.

Especificar a ordem na qual os valores-chave são armazenados em um índice é útil quando as consultas que fazem referência à tabela têm ORDER BY cláusulas que especificam direções diferentes para a coluna ou colunas de chave nesse índice. Nesses casos, o índice pode remover a necessidade de um operadorSort no plano de consulta.

Por exemplo, os compradores do departamento de compras da Adventure Works Cycles têm de avaliar a qualidade dos produtos que compram aos fornecedores. Os compradores estão mais interessados em encontrar produtos enviados por fornecedores com uma alta taxa de rejeição.

Conforme mostrado na consulta a seguir no banco de dados de exemplo AdventureWorks, a recuperação dos dados para atender a esse critério exige que a RejectedQty coluna na Purchasing.PurchaseOrderDetail tabela seja classificada em ordem decrescente (grande a pequena) e a ProductID coluna seja classificada em ordem crescente (pequena a grande).

SELECT RejectedQty,
       ((RejectedQty / OrderQty) * 100) AS RejectionRate,
       ProductID,
       DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;

O plano de execução a seguir para esta consulta mostra que o otimizador de consulta usou um operador Sort para retornar o conjunto de resultados na ordem especificada pela ORDER BY cláusula.

Se um índice de armazenamento de linha baseado em disco for criado com colunas de chave que correspondem às da cláusula ORDER BY na consulta, o operador Classificar no plano de consulta será eliminado, o que torna o plano de consulta mais eficiente.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);

Depois que a consulta é executada novamente, o plano de execução a seguir mostra que o operador Sort não está mais presente e o índice não clusterizado recém-criado é usado.

O Mecanismo de Banco de Dados pode verificar um índice em qualquer direção. Um índice definido como RejectedQty DESC, ProductID ASC ainda pode ser usado para uma consulta na qual as direções de classificação das colunas na ORDER BY cláusula são invertidas. Por exemplo, uma consulta com a ORDER BY cláusula ORDER BY RejectedQty ASC, ProductID DESC pode usar o mesmo índice.

A ordem de classificação pode ser especificada apenas para as colunas de chave no índice. A vista de catálogo sys.index_columns informa se uma coluna de índice é armazenada em ordem ascendente ou descendente.

Diretrizes de design de índice agrupado

O índice clusterizado armazena todas as linhas e todas as colunas de uma tabela. As linhas são classificadas na ordem dos valores de chave de índice. Só pode haver um índice agrupado por tabela.

O termo tabela base pode referir-se a um índice agrupado ou a um heap. Uma pilha é uma estrutura de dados não classificada no disco que contém todas as linhas e todas as colunas de uma tabela.

Com algumas exceções, cada tabela deve ter um índice clusterizado. As propriedades desejáveis do índice agrupado são:

Propriedade	Description
Estreito	A chave de índice clusterizado faz parte de qualquer índice não clusterizado na mesma tabela base. Uma chave estreita, ou uma chave em que o comprimento total das colunas de chave é pequeno, reduz o armazenamento, a E/S e a sobrecarga de memória de todos os índices em uma tabela. Para calcular o comprimento da chave, adicione os tamanhos de armazenamento para os tipos de dados usados pelas colunas de chave. Para obter mais informações, consulte Categorias de tipos de dados.
Único	Se o índice clusterizado não for exclusivo, uma coluna exclusiva interna de 4 bytes será adicionada automaticamente à chave de índice para garantir exclusividade. Adicionar uma coluna exclusiva existente à chave de índice clusterizada evita a sobrecarga de armazenamento, E/S e memória da coluna uniqueifier em todos os índices de uma tabela. Além disso, o otimizador de consulta pode gerar planos de consulta mais eficientes quando um índice é exclusivo.
Cada vez maior	Num índice cada vez maior, os dados são sempre adicionados na última página do índice. Isso evita divisões de página no meio do índice, o que reduz a densidade da página e diminui o desempenho.
Imutável	A chave de índice clusterizado faz parte de qualquer índice não clusterizado. Quando uma coluna de chave de um índice clusterizado é modificada, uma alteração também deve ser feita em todos os índices não clusterizados, o que adiciona uma CPU, registro, E/S e sobrecarga de memória. A sobrecarga é evitada se as colunas de chave do índice clusterizado forem imutáveis.
Tem apenas colunas não anuláveis	Se uma linha tiver colunas anuláveis, ela deve incluir uma estrutura interna chamada bloco NULL, que adiciona de 3 a 4 bytes de armazenamento por linha em um índice. Tornar todas as colunas do índice clusterizado não anuláveis evita essa sobrecarga.
Tem apenas colunas de largura fixa	As colunas que usam tipos de dados de largura variável, como varchar ou nvarchar , usam 2 bytes adicionais por valor em comparação com tipos de dados de largura fixa. O uso de tipos de dados de largura fixa, como int , evita essa sobrecarga em todos os índices na tabela.

Satisfazer o maior número possível dessas propriedades ao projetar um índice clusterizado torna mais eficientes não apenas o índice clusterizado, mas também todos os índices não clusterizados na mesma tabela. O desempenho é melhorado evitando despesas de armazenamento, E/S e memória.

Por exemplo, uma chave de índice clusterizada com uma única coluna int ou bigint não anulável tem todas essas propriedades se for preenchida por uma IDENTITY cláusula ou uma restrição padrão usando uma sequência e não for atualizada depois que uma linha for inserida.

Por outro lado, uma chave de índice clusterizada com uma única coluna uniqueidentifier é mais ampla porque usa 16 bytes de armazenamento em vez de 4 bytes para int e 8 bytes para bigint, e não satisfaz a propriedade cada vez maior , a menos que os valores sejam gerados sequencialmente.

Tip

Quando você cria uma PRIMARY KEY restrição, um índice exclusivo que suporta a restrição é criado automaticamente. Por padrão, esse índice é agrupado; No entanto, se esse índice não satisfizer as propriedades desejadas do índice clusterizado, você poderá criar a restrição como não clusterizado e criar um índice clusterizado diferente.

Se não criar um índice clusterizado, a tabela será armazenada como um heap, o que geralmente não é recomendado.

Arquitetura de índice clusterizado

Os índices Rowstore são organizados como árvores B+. Cada página em uma árvore de índice B+ é chamada de nó de índice. O nó superior da árvore B+ é chamado de nó raiz. Os nós inferiores no índice são chamados de nós de folha. Quaisquer níveis de índice entre a raiz e os nós foliares são coletivamente conhecidos como níveis intermediários. Em um índice clusterizado, os nós folha contêm as páginas de dados da tabela subjacente. Os nós de nível raiz e intermediário contêm páginas de índice contendo linhas de índice. Cada linha de índice contém um valor de chave e um ponteiro para uma página de nível intermediário na árvore B+ ou uma linha de dados no nível de folha do índice. As páginas em cada nível do índice são vinculadas em uma lista duplamente vinculada.

Os índices clusterizados têm uma linha em sys.partitions para cada partição usada pelo índice, com index_id = 1. Por padrão, um índice clusterizado tem uma única partição. Quando um índice clusterizado tem várias partições, cada partição tem uma estrutura de árvore B+ separada que contém os dados para essa partição específica. Por exemplo, se um índice clusterizado tiver quatro partições, há quatro estruturas de árvore B+, uma em cada partição.

Dependendo dos tipos de dados no índice clusterizado, cada estrutura de índice clusterizado tem uma ou mais unidades de alocação nas quais armazenar e gerenciar os dados para uma partição específica. No mínimo, cada índice clusterizado tem uma IN_ROW_DATA unidade de alocação por partição. O índice clusterizado também tem uma LOB_DATA unidade de alocação por partição se contiver colunas de objeto grande (LOB), como nvarchar(max). Ele também tem uma ROW_OVERFLOW_DATA unidade de alocação por partição se contiver colunas de comprimento variável que excedam o limite de tamanho de linha de 8.060 bytes.

As páginas na estrutura da árvore B+ são ordenadas no valor da chave de índice clusterizada. Todas as inserções são feitas na página onde o valor da chave na linha inserida se encaixa na sequência de ordenação entre as páginas existentes. Dentro de uma página, as linhas não são necessariamente armazenadas em qualquer ordem física. No entanto, a página mantém uma ordenação lógica de linhas usando uma estrutura interna chamada matriz de slots. As entradas na matriz de slots são mantidas na ordem das chaves de índice.

Esta ilustração mostra a estrutura de um índice clusterizado em uma única partição.

Diretrizes de design de índice não agrupado

A principal diferença entre um índice clusterizado e um índice não clusterizado é que um índice não clusterizado contém um subconjunto das colunas na tabela, geralmente classificadas de forma diferente do índice clusterizado. Opcionalmente, um índice não clusterizado pode ser filtrado, o que significa que ele contém um subconjunto de todas as linhas na tabela.

Um índice não clusterizado de armazenamento de linha baseado em disco contém os localizadores de linha que apontam para o local de armazenamento da linha na tabela base. Você pode criar vários índices não clusterizados em uma tabela ou modo de exibição indexado. Geralmente, os índices não agrupados devem ser projetados para melhorar o desempenho de consultas usadas com freqüência que, de outra forma, precisariam verificar a tabela base.

Semelhante à maneira como você usa um índice em um livro, o otimizador de consulta procura um valor de dados pesquisando o índice não clusterizado para encontrar o local do valor de dados na tabela e, em seguida, recupera os dados diretamente desse local. Isso torna os índices não agrupados a escolha ideal para consultas de correspondência exata porque o índice contém entradas que descrevem o local exato na tabela dos valores de dados que estão sendo pesquisados nas consultas.

Por exemplo, para consultar a HumanResources.Employee tabela para todos os funcionários que se reportam a um gerente específico, o otimizador de consulta pode usar o índice IX_Employee_ManagerIDnão clusterizado, que tem ManagerID como primeira coluna de chave. Como os ManagerID valores são ordenados no índice não clusterizado, o otimizador de consulta pode localizar rapidamente todas as entradas no índice que correspondem ao valor especificado ManagerID . Cada entrada de índice aponta para a página e linha exatas na tabela base, onde os dados correspondentes de todas as outras colunas podem ser recuperados. Depois que o otimizador de consulta encontrar todas as entradas no índice, ele poderá ir diretamente para a página e linha exatas para recuperar os dados em vez de verificar toda a tabela base.

Arquitetura de índice não clusterizado

Os índices não clusterizados de armazenamento de linha baseados em disco têm a mesma estrutura de árvore B+ que os índices clusterizados, exceto pelas seguintes diferenças:

• Um índice não clusterizado não contém necessariamente todas as colunas e linhas da tabela.

• O nível de folha de um índice não agrupado é composto por páginas de índice em vez de páginas de dados. As páginas de índice no nível de folha de um índice não agrupado contêm colunas chave. Opcionalmente, eles também podem conter um subconjunto de outras colunas na tabela como colunas incluídas, para evitar recuperá-las da tabela base.

Os localizadores de linha em linhas de índice não clusterizadas são um ponteiro para uma linha ou são uma chave de índice clusterizada para uma linha, descrita da seguinte forma:

• Se a tabela tiver um índice clusterizado ou se o índice estiver em uma exibição indexada, o localizador de linha será a chave de índice clusterizada para a linha.

• Se a tabela for um heap, o que significa que não possui um índice agrupado, o localizador de linhas é um ponteiro para a linha. O ponteiro é criado a partir do identificador de arquivo (ID), número de página e número da linha na página. O ponteiro inteiro é conhecido como ID de linha (RID).

Os localizadores de linha também garantem exclusividade para linhas de índice não clusterizadas. A tabela a seguir descreve como o Mecanismo de Banco de Dados adiciona localizadores de linha a índices não clusterizados:

Tipo de tabela base	Tipo de índice não agrupado	Localizador de linhas
Heap
	Nonunique	RID adicionado às colunas principais
	Unique	RID adicionado às colunas incluídas
Índice clusterizado exclusivo
	Nonunique	Chaves de índice clusterizadas adicionadas às colunas de chave
	Unique	Chaves de índice clusterizadas adicionadas às colunas incluídas
Índice clusterizado não exclusivo
	Nonunique	Chaves de índices clusterizados e uniqueifier (quando presente) adicionados às colunas de chave
	Unique	Chaves de índices clusterizadas e identificador único (quando presente) adicionados às colunas incluídas

O Mecanismo de Banco de Dados nunca armazena uma determinada coluna mais de uma vez em um índice não clusterizado. A ordem da chave de índice especificada pelo usuário quando ele cria um índice não clusterizado é sempre honrada: todas as colunas do localizador de linha que precisam ser adicionadas à chave de um índice não clusterizado são adicionadas no final da chave, seguindo as colunas especificadas na definição do índice. Os localizadores de linha de chave de índice clusterizados em um índice não clusterizado podem ser usados no processamento de consultas, independentemente de serem explicitamente especificados na definição de índice ou adicionados implicitamente.

Os exemplos a seguir mostram como os localizadores de linha são implementados em índices não clusterizados:

Índice agrupado	Definição de índice não agrupado	Definição de índice não agrupado com localizadores de linha	Explanation
Índice clusterizado exclusivo com colunas de chave (A, B, C)	Índice não exclusivo não clusterizado com colunas de chave (B, A) e colunas incluídas (E, G)	Colunas chave (B, A, C) e colunas incluídas (E, G)	O índice não clusterizado não é exclusivo, portanto, o localizador de linha precisa estar presente nas chaves de índice. As colunas B e A do localizador de linhas já estão presentes, portanto, apenas a coluna C é adicionada. A coluna C é adicionada ao final da lista de colunas principais.
Índice clusterizado exclusivo com coluna de chave (A)	Índice não exclusivo não clusterizado com colunas de chave (B, C) e coluna incluída (A)	Colunas-chave (B, C, A)	O índice não clusterizado não é exclusivo, portanto, o localizador de linha é adicionado à chave. A coluna A ainda não está especificada como uma coluna de chave, por isso é adicionada ao final da lista de colunas de chaves. A coluna A agora está na chave, portanto, não há necessidade de armazená-la como uma coluna incluída.
Índice clusterizado exclusivo com coluna de chave (A, B)	Índice não clusterizado exclusivo com coluna de chave (C)	Coluna chave (C) e colunas incluídas (A, B)	O índice não clusterizado é exclusivo, portanto, o localizador de linha é adicionado às colunas incluídas.

Os índices não clusterizados têm uma linha em sys.partitions para cada partição usada pelo índice, com index_id > 1. Por padrão, um índice não clusterizado tem uma única partição. Quando um índice não clusterizado tem várias partições, cada partição tem uma estrutura de árvore B+ que contém as linhas de índice para essa partição específica. Por exemplo, se um índice não clusterizado tiver quatro partições, há quatro estruturas de árvore B+, uma em cada partição.

Dependendo dos tipos de dados no índice não clusterizado, cada estrutura de índice não clusterizado tem uma ou mais unidades de alocação nas quais armazenar e gerenciar os dados para uma partição específica. No mínimo, cada índice não clusterizado tem uma IN_ROW_DATA unidade de alocação por partição que armazena as páginas da árvore de índice B+. O índice não clusterizado também tem uma LOB_DATA unidade de alocação por partição se contiver colunas de objeto grande (LOB), como nvarchar(max). Além disso, ele tem uma ROW_OVERFLOW_DATA unidade de alocação por partição se contiver colunas de comprimento variável que excedam o limite de tamanho de linha de 8.060 bytes.

A ilustração a seguir mostra a estrutura de um índice não clusterizado em uma única partição.

Usar colunas incluídas em índices não clusterizados

Além das colunas chave, um índice não agrupado também pode ter colunas não-chave armazenadas no nível da folha. Essas colunas não-chave são chamadas de colunas incluídas e são especificadas INCLUDE na cláusula da CREATE INDEX instrução.

Um índice com colunas não-chave incluídas pode melhorar significativamente o desempenho da consulta quando cobre a consulta, ou seja, quando todas as colunas usadas na consulta estão no índice como colunas chave ou não-chave. Os ganhos de desempenho são alcançados porque o Mecanismo de Banco de Dados pode localizar todos os valores de coluna dentro do índice; a tabela base não é acessada, resultando em menos operações de E/S de disco.

Se uma coluna precisar ser recuperada por uma consulta, mas não for usada nos predicados, agregações e classificações de consulta, adicione-a como uma coluna incluída e não como uma coluna chave. Isto tem as seguintes vantagens:

• As colunas incluídas podem usar tipos de dados não permitidos como colunas de chave de índice.

• As colunas incluídas não são consideradas pelo Mecanismo de Banco de Dados ao calcular o número de colunas de chave de índice ou o tamanho da chave de índice. Com colunas incluídas, você não está limitado pelo tamanho máximo de chave de 900 bytes. Você pode criar índices mais amplos que abrangem mais consultas.

• Quando você move uma coluna da chave de índice para colunas incluídas, a compilação de índice leva menos tempo porque a operação de classificação de índice se torna mais rápida.

Se a tabela tiver um índice clusterizado, a coluna ou colunas definidas na chave de índice clusterizado serão adicionadas automaticamente a cada índice não clusterizado não exclusivo na tabela. Não é necessário especificá-los na chave de índice não clusterizada ou como colunas incluídas.

Diretrizes para índices com colunas incluídas

Considere as seguintes diretrizes ao criar índices não clusterizados com colunas incluídas:

• As colunas incluídas só podem ser definidas em índices não agrupados em tabelas ou exibições indexadas.

• Todos os tipos de dados são permitidos, exceto de texto, ntext e de imagem.

• Colunas computadas que são determinísticas e precisas ou imprecisas podem ser incluídas colunas. Para obter mais informações, consulte Índices em colunas computadas.

• Tal como acontece com as colunas chave, as colunas calculadas derivadas dos tipos de dados image, ntext e text podem ser usadas como colunas incluídas, desde que o tipo de dados da coluna calculada seja permitido numa coluna incluída.

• Os nomes das colunas não podem ser especificados na INCLUDE lista nem na lista de colunas principais.

• Os nomes das colunas não podem ser repetidos na INCLUDE lista.

• Pelo menos uma coluna de chave deve ser definida em um índice. O número máximo de colunas incluídas é 1.023. Este é o número máximo de colunas da tabela menos 1.

• Independentemente da presença de colunas incluídas, as colunas de chave de índice devem seguir as restrições de tamanho de índice existentes de no máximo 16 colunas de chave e um tamanho total de chave de índice de 900 bytes.

Recomendações de design para índices com colunas incluídas

Considere redesenhar índices não clusterizados com um tamanho grande de chave de índice, de modo que apenas as colunas utilizadas em predicados, agregações e ordenações de consulta se tornem colunas-chave de índice. Faça com que todas as outras colunas que cobrem a consulta incluam colunas não-chave. Dessa forma, você tem todas as colunas necessárias para cobrir a consulta, mas a chave de índice em si é pequena e eficiente.

Por exemplo, suponha que você deseja criar um índice para cobrir a consulta a seguir.

SELECT AddressLine1,
       AddressLine2,
       City,
       StateProvinceID,
       PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';

Para cobrir a consulta, cada coluna deve ser definida no índice. Embora você pudesse definir todas as colunas como colunas de chave, o tamanho da chave seria de 334 bytes. Como a única coluna usada como critério de pesquisa é a PostalCode coluna, com um comprimento de 30 bytes, um design de índice melhor definiria PostalCode como a coluna chave e incluiria todas as outras colunas como colunas não-chave.

A instrução a seguir cria um índice com colunas incluídas para cobrir a consulta.

CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Para validar se o índice cobre a consulta, crie o índice e exiba o plano de execução estimado. Se o plano de execução mostrar um operador Index Seek para o IX_Address_PostalCode índice, a consulta será coberta pelo índice.

Considerações de desempenho para índices com colunas incluídas

Evite criar índices com um número muito grande de colunas incluídas. Mesmo que o índice possa estar cobrindo mais consultas, seu benefício de desempenho é reduzido porque:

• Menos linhas de índice cabem em uma página. Isso aumenta a E/S do disco e reduz a eficiência do cache.

• É necessário mais espaço em disco para armazenar o índice. Em particular, adicionar tipos de dados varchar(max), nvarchar(max), varbinary(max) ou xml em colunas incluídas pode aumentar significativamente os requisitos de espaço em disco. Isso ocorre porque os valores da coluna são copiados para o nível da folha do índice. Portanto, eles residem no índice e na tabela base.

• O desempenho da modificação de dados diminui porque muitas colunas devem ser modificadas na tabela baseada e no índice não clusterizado.

Você precisa determinar se os ganhos no desempenho da consulta compensam a diminuição no desempenho de modificação de dados e o aumento nos requisitos de espaço em disco.

Diretrizes para o projeto de índice único

Um índice exclusivo garante que a chave de índice não contenha valores duplicados. A criação de um índice exclusivo só é possível quando a exclusividade é uma característica dos próprios dados. Por exemplo, se você quiser certificar-se de que os valores na coluna NationalIDNumber da tabela HumanResources.Employee são únicos, quando a chave primária for EmployeeID, crie uma restrição UNIQUE na coluna NationalIDNumber. A restrição rejeita qualquer tentativa de introduzir linhas com números de identificação nacionais duplicados.

Com índices exclusivos de várias colunas, o índice garante que cada combinação de valores na chave de índice seja exclusiva. Por exemplo, se um índice exclusivo for criado em uma combinação de LastName, FirstNamee MiddleName colunas, nenhuma linha na tabela poderá ter os mesmos valores para essas colunas.

Os índices agrupados e não agrupados podem ser exclusivos. Você pode criar um índice clusterizado exclusivo e vários índices não clusterizados exclusivos na mesma tabela.

Os benefícios dos índices únicos incluem:

• As regras de negócios que exigem exclusividade de dados são impostas.
• Informações adicionais úteis para o otimizador de consulta são fornecidas.

A criação de uma restrição PRIMARY KEY ou UNIQUE cria automaticamente um índice único nas colunas especificadas. Não há diferenças significativas entre criar uma UNIQUE restrição e criar um índice exclusivo independente de uma restrição. A validação de dados ocorre da mesma maneira e o otimizador de consulta não diferencia entre um índice exclusivo criado por uma restrição ou criado manualmente. No entanto, você deve criar uma restrição UNIQUE ou PRIMARY KEY na coluna quando a imposição de regras de negócios for o objetivo. Ao fazer isso, o objetivo do índice é claro.

Considerações exclusivas sobre o índice

• Não é possível criar um índice exclusivo, uma UNIQUE restrição ou uma PRIMARY KEY restrição se existirem valores de chave duplicados nos dados.

• Se os dados forem exclusivos e você quiser que a exclusividade seja imposta, a criação de um índice exclusivo em vez de um índice não exclusivo na mesma combinação de colunas fornecerá informações adicionais para o otimizador de consulta que pode produzir planos de execução mais eficientes. Neste caso, recomenda-se a criação de uma UNIQUE restrição ou de um índice exclusivo.

• Um índice não clusterizado exclusivo pode conter colunas não-chave incluídas. Para obter mais informações, consulte Usar colunas incluídas em índices não clusterizados.

• Ao contrário de uma PRIMARY KEY restrição, uma UNIQUE restrição ou um índice exclusivo pode ser criado com uma coluna anulável na chave de índice. Para efeitos de aplicação da unicidade, dois NULLs são considerados iguais. Por exemplo, isso significa que, em um índice exclusivo de coluna única, a coluna pode ser NULL apenas para uma linha na tabela.

Diretrizes de design de índice filtrado

Um índice filtrado é um índice não clusterizado otimizado, especialmente adequado para consultas que exigem um pequeno subconjunto de dados na tabela. Ele usa um predicado de filtro na definição de índice para indexar uma parte das linhas na tabela. Um índice filtrado bem projetado pode melhorar o desempenho da consulta, reduzir os custos de atualização do índice e reduzir os custos de armazenamento do índice em comparação com um índice de tabela completa.

Os índices filtrados podem oferecer as seguintes vantagens em relação aos índices de tabela completa:

• Melhor desempenho da consulta e qualidade do plano

  Um índice filtrado bem projetado melhora o desempenho da consulta e a qualidade do plano de execução porque é menor do que um índice não clusterizado de tabela completa. Um índice filtrado tem estatísticas filtradas, que são mais precisas do que as estatísticas de tabela completa porque cobrem apenas as linhas no índice filtrado.

• Custos reduzidos de atualização do índice

  Um índice é atualizado somente quando instruções DML (linguagem de manipulação de dados) afetam os dados no índice. Um índice filtrado reduz os custos de atualização do índice em comparação com um índice não clusterizado de tabela completa porque é menor e só é atualizado quando os dados no índice são afetados. É possível ter um grande número de índices filtrados, especialmente quando eles contêm dados que são afetados com pouca frequência. Da mesma forma, se um índice filtrado contiver apenas os dados frequentemente afetados, o tamanho menor do índice reduz o custo de atualização das estatísticas.

• Custos reduzidos de armazenamento de índice

  A criação de um índice filtrado pode reduzir o armazenamento em disco para índices não clusterizados quando um índice de tabela completa não é necessário. Talvez seja possível substituir um índice não clusterizado de tabela completa por vários índices filtrados sem aumentar significativamente os requisitos de armazenamento.

Os índices filtrados são úteis quando as colunas contêm subconjuntos de dados bem definidos. São exemplos:

• Colunas que contêm muitos "NULLs".

• Colunas heterogêneas que contêm categorias de dados.

• Colunas que contêm intervalos de valores como quantidades, hora e datas.

Os custos de atualização reduzidos para índices filtrados são mais percetíveis quando o número de linhas no índice é pequeno em comparação com um índice de tabela completa. Se o índice filtrado incluir a maioria das linhas na tabela, a manutenção poderá custar mais do que um índice de tabela completa. Nesse caso, você deve usar um índice de tabela completa em vez de um índice filtrado.

Os índices filtrados são definidos em uma tabela e suportam apenas operadores de comparação simples. Se precisar de uma expressão de filtro que tenha lógica complexa ou faça referência a várias tabelas, crie uma coluna computada indexada ou uma exibição indexada.

Considerações de design de índice filtrado

Para projetar índices filtrados eficazes, é importante entender quais consultas seu aplicativo usa e como elas se relacionam com subconjuntos de seus dados. Alguns exemplos de dados que têm subconjuntos bem definidos são colunas com muitos NULLs, colunas com categorias heterogêneas de valores e colunas com intervalos distintos de valores.

As considerações de design a seguir fornecem vários cenários para quando um índice filtrado pode fornecer vantagens em relação aos índices de tabela completa.

Índices filtrados para subconjuntos de dados

Quando uma coluna tem apenas alguns valores relevantes para consultas, você pode criar um índice filtrado no subconjunto de valores. Por exemplo, quando a coluna é principalmente NULL e a consulta requer apenas valores não-NULL, você pode criar um índice filtrado contendo as linhas não-NULL.

Por exemplo, o banco de dados de exemplo AdventureWorks tem uma Production.BillOfMaterials tabela com 2.679 linhas. A EndDate coluna tem apenas 199 linhas que contêm um valor não-NULL e as outras 2480 linhas contêm NULL. O índice filtrado a seguir abrange consultas que retornam as colunas definidas no índice e que exigem apenas linhas com um valor não-NULL para EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

O índice filtrado FIBillOfMaterialsWithEndDate é válido para uma consulta a seguir. Exiba o Plano de Execução Estimado para determinar se o otimizador de consulta usou o índice filtrado.

SELECT ProductAssemblyID,
       ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
      AND ComponentID = 5
      AND StartDate > '20080101';

Para obter mais informações sobre como criar índices filtrados e como definir a expressão de predicado de índice filtrado, consulte Criar índices filtrados.

Índices filtrados para dados heterogêneos

Quando uma tabela tem linhas de dados heterogêneas, você pode criar um índice filtrado para uma ou mais categorias de dados.

Por exemplo, os Production.Product produtos listados na tabela são atribuídos a um ProductSubcategoryID, que por sua vez estão associados às categorias de produtos Bicicletas, Componentes, Vestuário ou Acessórios. Essas categorias são heterogêneas porque seus valores de Production.Product coluna na tabela não estão estreitamente correlacionados. Por exemplo, as colunas Color, ReorderPoint, ListPrice, Weight, Class, e Style têm características únicas para cada categoria de produto. Suponhamos que há consultas frequentes para acessórios, que têm subcategorias entre 27 e 36 inclusive. Você pode melhorar o desempenho de consultas de acessórios criando um índice filtrado nas subcategorias de acessórios, conforme mostrado no exemplo a seguir.

CREATE NONCLUSTERED INDEX FIProductAccessories
ON Production.Product (ProductSubcategoryID, ListPrice)
INCLUDE (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;

O índice FIProductAccessories filtrado abrange a consulta a seguir porque os resultados da consulta estão contidos no índice e o plano de consulta não requer acesso à tabela base. Por exemplo, a expressão do predicado de consulta ProductSubcategoryID = 33 é um subconjunto do predicado de índice filtrado representado por ProductSubcategoryID >= 27 e ProductSubcategoryID <= 36, as colunas ProductSubcategoryID e ListPrice no predicado de consulta são ambas colunas chave no índice, e o nome é armazenado no nível de folha do índice como uma coluna incluída.

SELECT Name,
       ProductSubcategoryID,
       ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33
      AND ListPrice > 25.00;

Colunas chave e colunas incluídas em índices filtrados

É uma prática recomendada adicionar um pequeno número de colunas em uma definição de índice filtrada, somente conforme necessário para que o otimizador de consulta escolha o índice filtrado para o plano de execução da consulta. O otimizador de consulta pode escolher um índice filtrado para a consulta, independentemente de cobrir ou não a consulta. No entanto, é mais provável que o otimizador de consulta escolha um índice filtrado se ele cobrir a consulta.

Em alguns casos, um índice filtrado cobre a consulta sem incluir as colunas na expressão de índice filtrada como chave ou colunas incluídas na definição de índice filtrado. As diretrizes a seguir explicam quando uma coluna na expressão de índice filtrada deve ser uma chave ou uma coluna incluída na definição de índice filtrado. Os exemplos referem-se ao índice filtrado, FIBillOfMaterialsWithEndDate que foi criado anteriormente.

Uma coluna na expressão de índice filtrada não precisa ser uma chave ou coluna incluída na definição de índice filtrado se a expressão de índice filtrada for equivalente ao predicado de consulta e a consulta não retornar a coluna na expressão de índice filtrada com os resultados da consulta. Por exemplo, FIBillOfMaterialsWithEndDate abrange a consulta a seguir porque o predicado de consulta é equivalente à expressão de filtro e EndDate não é retornado com os resultados da consulta. O FIBillOfMaterialsWithEndDate índice não precisa de EndDate como chave ou coluna incluída na definição de índice filtrado.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Uma coluna na expressão de índice filtrada deve ser uma chave ou uma coluna incluída na definição de índice filtrada se o predicado de consulta usar a coluna em uma comparação que não seja equivalente à expressão de índice filtrada. Por exemplo, FIBillOfMaterialsWithEndDate é válido para a consulta a seguir porque seleciona um subconjunto de linhas do índice filtrado. No entanto, ele não cobre a consulta a seguir porque EndDate é usado na comparação EndDate > '20040101', que não é equivalente à expressão de índice filtrada. O processador de consultas não pode executar essa consulta sem examinar os valores de EndDate. Portanto, EndDate deve ser uma chave ou coluna incluída na definição de índice filtrado.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate > '20040101';

Uma coluna na expressão de índice filtrada deve ser uma chave ou uma coluna incluída na definição de índice filtrada se a coluna estiver no conjunto de resultados da consulta. Por exemplo, FIBillOfMaterialsWithEndDate não cobre a consulta a seguir porque retorna a EndDate coluna nos resultados da consulta. Portanto, EndDate deve ser uma chave ou coluna incluída na definição de índice filtrado.

SELECT ComponentID,
       StartDate,
       EndDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

A chave de índice clusterizada da tabela não precisa ser uma chave ou coluna incluída na definição de índice filtrado. A chave de índice clusterizada é incluída automaticamente em todos os índices não clusterizados, incluindo índices filtrados.

Operadores de conversão de dados no predicado do filtro

Se o operador de comparação especificado na expressão de índice filtrado do índice filtrado resultar em uma conversão de dados implícita ou explícita, ocorrerá um erro se a conversão ocorrer no lado esquerdo de um operador de comparação. Uma solução é escrever a expressão de índice filtrada com o operador de conversão de dados (CAST ou CONVERT) no lado direito do operador de comparação.

O exemplo a seguir cria uma tabela com colunas de diferentes tipos de dados.

CREATE TABLE dbo.TestTable
(
    a INT,
    b VARBINARY(4)
);

Na seguinte definição de índice filtrado, a coluna b é implicitamente convertida em um tipo de dados inteiro para compará-la com a constante 1. Isso gera a mensagem de erro 10611 porque a conversão ocorre no lado esquerdo do operador no predicado filtrado.

CREATE NONCLUSTERED INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = 1;

A solução é converter a constante no lado direito para ser do mesmo tipo que a coluna b, como visto no exemplo a seguir:

CREATE INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = CONVERT (VARBINARY(4), 1);

Mover a conversão de dados do lado esquerdo para o lado direito de um operador de comparação pode alterar o significado da conversão. No exemplo anterior, quando o operador CONVERT foi adicionado à direita, a comparação alterou-se de uma comparação int para uma comparação varbinary.

Arquitetura de índice Columnstore

Um índice columnstore é uma tecnologia para armazenar, recuperar e gerenciar dados usando um formato de dados colunar, chamado columnstore. Para obter mais informações, consulte Columnstore indexes: overview.

Para obter informações da versão e descobrir as novidades, visite Novidades nos índices de columnstore.

Conhecer esses conceitos básicos facilita a compreensão de outros artigos sobre columnstore que explicam como usar essa tecnologia de forma eficaz.

O armazenamento de dados usa columnstore e rowstore

Ao discutir índices columnstore, usamos os termos rowstore e columnstore para enfatizar o formato do armazenamento de dados. Os índices Columnstore usam ambos os tipos de armazenamento.

• Um columnstore são dados organizados logicamente como uma tabela com linhas e colunas e armazenados fisicamente em um formato de dados em coluna.

  Um índice columnstore armazena fisicamente a maioria dos dados no formato columnstore. No formato columnstore, os dados são compactados e descompactados como colunas. Não há necessidade de descompactar outros valores em cada linha que não são solicitados pela consulta. Isso torna mais rápido digitalizar uma coluna inteira de uma tabela grande.

• Um armazenamento de linhas são dados organizados logicamente como uma tabela com linhas e colunas e, em seguida, armazenados fisicamente em um formato de dados em linha. Essa tem sido a maneira tradicional de armazenar dados de tabelas relacionais, como um índice de árvore B+ agrupado ou um heap.

  Um índice columnstore também armazena fisicamente algumas linhas em um formato rowstore chamado deltastore. O deltastore, também chamado delta rowgroups, é um local de retenção para linhas que são insuficientes para serem compactadas no columnstore. Cada grupo de linhas delta é implementado como um índice de árvore B+ clusterizado, que é um armazenamento de linhas.

As operações são executadas em grupos de linhas e segmentos de coluna

O índice columnstore agrupa linhas em unidades gerenciáveis. Cada uma dessas unidades é chamada de grupo de linhas. Para obter o melhor desempenho, o número de linhas em um grupo de linhas é grande o suficiente para melhorar a taxa de compactação e pequeno o suficiente para se beneficiar em operações de memória.

Por exemplo, o índice columnstore executa estas operações em grupos de linhas:

• Compacta grupos de linhas no columnstore. A compactação é realizada em cada segmento de coluna dentro de um grupo de linhas.

• Mescla grupos de linhas durante uma operação ALTER INDEX ... REORGANIZE, incluindo a remoção de dados excluídos.

• Recria todos os grupos de linhas durante uma ALTER INDEX ... REBUILD operação.

• Relatórios sobre integridade e fragmentação de grupos de linhas nas visualizações de gerenciamento dinâmico (DMVs).

O deltastore é composto de um ou mais rowgroups chamados delta rowgroups. Cada grupo de linhas delta é um índice de árvore B+ agrupado que armazena pequenas cargas em massa e insere até que o grupo de linhas contenha 1.048.576 linhas, momento em que um processo chamado tuple-mover compacta automaticamente um grupo de linhas fechado no columnstore.

Para obter mais informações sobre os status do grupo de linhas, consulte sys.dm_db_column_store_row_group_physical_stats.

Tip

Ter demasiados grupos de linhas pequenos diminui a qualidade do índice columnstore. Uma operação de reorganização mescla grupos de linhas menores, seguindo uma política de limite interno que determina como remover linhas excluídas e combinar os grupos de linhas compactados. Após uma mesclagem, a qualidade do índice é melhorada.

No SQL Server 2019 (15.x) e versões posteriores, o tuple-mover é ajudado por uma tarefa de mesclagem em segundo plano que compacta automaticamente grupos de linhas delta abertos menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla grupos de linhas compactados dos quais um grande número de linhas foi excluído.

Cada coluna tem alguns de seus valores em cada grupo de linhas. Esses valores são chamados de segmentos de coluna. Cada grupo de linhas contém um segmento de coluna para cada coluna da tabela. Cada coluna tem um segmento de coluna em cada grupo de linhas.

Quando o índice columnstore compacta um grupo de linhas, ele compacta cada segmento de coluna separadamente. Para descompactar uma coluna inteira, o índice columnstore só precisa descompactar um segmento de coluna de cada grupo de linhas.

Pequenas cargas e inserções vão para a deltastore

Um índice columnstore melhora a compactação e o desempenho ao comprimir pelo menos 102.400 linhas de cada vez para o índice columnstore. Para compactar linhas em massa, o índice columnstore acumula pequenas cargas e inserções no deltastore. As operações deltastore são tratadas em segundo plano. Para retornar os resultados da consulta, o índice columnstore clusterizado combina os resultados da consulta do columnstore e do deltastore.

As linhas são transferidas para o deltastore quando são:

• Inserido com a declaração INSERT INTO ... VALUES.

• No final de uma carga em massa, e o número é inferior a 102.400.

• Updated. Cada atualização é implementada como uma exclusão e uma inserção.

O deltastore também armazena uma lista de IDs para linhas que foram marcadas como apagadas, mas ainda não foram eliminadas fisicamente do columnstore.

Quando os grupos de linhas delta estão cheios, eles são compactados no columnstore

Os índices columnstore clusterizados coletam até 1.048.576 linhas em cada grupo de linhas delta antes de compactar o grupo de linhas no columnstore. Isso melhora a compactação do índice columnstore. Quando um grupo de linhas delta atinge o número máximo de linhas, ele transita de um OPEN para estado CLOSED . Um processo em segundo plano chamado movedor de tuplas verifica grupos de linhas fechados. Se o processo encontrar um grupo de linhas fechado, ele compactará o grupo de linhas e o armazenará no columnstore.

Quando um grupo de linhas delta é compactado, o grupo de linhas delta existente transita para o estado TOMBSTONE, sendo removido posteriormente pelo tuple-mover quando não há referência a ele, e o novo grupo de linhas compactado é marcado como COMPRESSED.

Para obter mais informações sobre os status do grupo de linhas, consulte sys.dm_db_column_store_row_group_physical_stats.

Você pode forçar a inclusão dos grupos de linhas delta no columnstore usando ALTER INDEX para reconstruir ou reorganizar o índice. Se houver pressão de memória durante a compactação, o índice de armazenamento em colunas (columnstore) poderá reduzir o número de linhas no grupo de linhas compactado.

Cada partição de tabela tem seus próprios grupos de linhas e grupos de linhas delta

O conceito de particionamento é o mesmo em um índice clusterizado, um heap e um índice columnstore. O particionamento de uma tabela divide a tabela em grupos menores de linhas de acordo com um intervalo de valores de coluna. É frequentemente utilizado para gerir os dados. Por exemplo, você pode criar uma partição para cada ano de dados e, em seguida, usar a comutação de partição para arquivar dados antigos em um armazenamento mais barato.

Os grupos de linhas são sempre definidos dentro de uma partição de tabela. Quando um índice columnstore é particionado, cada partição tem seus próprios grupos de linhas compactadas e grupos de linhas delta. Uma tabela não particionada contém uma partição.

Tip

Considere usar o particionamento de tabelas se precisar remover dados do columnstore. Trocar e truncar partições que não são mais necessárias é uma estratégia eficiente para excluir dados sem introduzir fragmentação no columnstore.

Cada partição pode ter vários grupos de linhas delta

Cada partição pode ter mais de um grupo de linhas delta. Quando o índice columnstore precisa adicionar dados a um grupo de linhas delta e o grupo de linhas delta está bloqueado por outra transação, o índice columnstore tenta obter um bloqueio em um grupo de linhas delta diferente. Se não houver grupos de linhas delta disponíveis, o índice columnstore criará um novo grupo de linhas delta. Por exemplo, uma tabela com 10 partições pode facilmente ter 20 ou mais grupos de linhas delta.

Combinar índices columnstore e rowstore na mesma tabela

Um índice não clusterizado contém uma cópia de parte ou de todas as linhas e colunas na tabela subjacente. O índice é definido como uma ou mais colunas da tabela e tem uma condição opcional que filtra as linhas.

Você pode criar um índice columnstore não clusterizado atualizável em uma tabela rowstore. O índice columnstore armazena uma cópia dos dados, por isso necessita de armazenamento extra. No entanto, os dados no índice columnstore são compactados para um tamanho muito menor do que a tabela rowstore exige. Ao fazer isso, é possível executar análises no índice columnstore e cargas de trabalho OLTP no índice rowstore simultaneamente. O armazenamento em coluna é atualizado quando os dados são alterados na tabela de armazenamento em linha, portanto, ambos os índices estão a operar sobre os mesmos dados.

Uma tabela rowstore pode ter um único índice columnstore não clusterizado. Para obter mais informações, consulte Índices de Columnstore - orientação de design.

Você pode ter um ou mais índices de armazenamento de linha não clusterizados em uma tabela columnstore clusterizada. Ao fazer isso, você pode executar pesquisas de tabela eficientes no columnstore subjacente. Outras opções também ficam disponíveis. Por exemplo, você pode impor exclusividade usando uma UNIQUE restrição na tabela rowstore. Quando um valor não exclusivo não é inserido na tabela rowstore, o Mecanismo de Banco de Dados também não insere o valor no columnstore.

Considerações de desempenho de columnstore não clusterizado

A definição do índice de armazenamento em coluna não clusterizado oferece suporte ao uso de uma condição filtrada. Para minimizar o efeito de desempenho da adição de um índice columnstore, use uma expressão de filtro para criar um índice columnstore não clusterizado somente no subconjunto de dados necessários para análise.

Uma tabela com otimização de memória pode ter um índice columnstore. Você pode criá-la quando a tabela for criada ou adicioná-la posteriormente com ALTER TABLE.

Para obter mais informações, consulte Índices columnstore - desempenho de consultas.

Diretrizes de design de índices de hash otimizados para memória

Ao usar In-Memory OLTP, todas as tabelas com otimização de memória devem ter pelo menos um índice. Para uma tabela com otimização de memória, cada índice também é otimizado para memória. Os índices de hash são um dos tipos de índice possíveis em uma tabela com otimização de memória. Para obter mais informações, consulte Índices nas Tabelas Memory-Optimized.

Arquitetura de índice de hash otimizada para uso de memória

Um índice de hash consiste em uma matriz de ponteiros, e cada elemento da matriz é chamado de hash bucket.

• Cada bucket tem 8 bytes, que são usados para armazenar o endereço de memória de uma lista de links de entradas de chave.
• Cada entrada é um valor para uma chave de índice, mais o endereço de sua linha correspondente na tabela subjacente otimizada para memória.
• Cada entrada aponta para a próxima entrada em uma lista de links de entradas, todas encadeadas ao bucket atual.

O número de buckets deve ser especificado no momento da criação do índice:

• Quanto menor a proporção de buckets para linhas de tabela ou para valores distintos, maior é a lista média de links de bucket.
• As listas de links curtos têm um desempenho mais rápido do que as listas de links longos.
• O número máximo de buckets em índices de hash é 1.073.741.824.

Tip

Para determinar a opção adequada BUCKET_COUNT para os seus dados, consulte Configurar a contagem de buckets do índice de hash.

A função hash é aplicada às colunas da chave de índice e o resultado da função determina em qual bucket essa chave se encaixa. Cada bucket tem um ponteiro para linhas cujos valores de chave hashed são mapeados para esse bucket.

A função de dispersão usada para índices de hash tem as seguintes características:

• O Mecanismo de Banco de Dados tem uma função de hash que é usada para todos os índices de hash.
• A função hash é determinística. O mesmo valor de chave de entrada é sempre mapeado para o mesmo bucket no índice de hash.
• Várias chaves de índice podem ser mapeadas para o mesmo hash bucket.
• A função hash é equilibrada, o que significa que a distribuição dos valores das chaves de índice sobre os buckets de hash normalmente segue uma distribuição de Poisson ou uma distribuição em forma de sino, e não uma distribuição linear uniforme.
• A distribuição de Poisson não é uma distribuição uniforme. Os valores de chave de índice não são distribuídos uniformemente nos hash buckets.
• Se duas chaves de índice forem mapeadas para o mesmo hash bucket, haverá uma colisão de hash. Um grande número de colisões de hash pode ter um impacto no desempenho nas operações de leitura. Uma meta realista é que 30% dos buckets contenham dois valores-chave diferentes.

A interação entre o índice de hash e os buckets é resumida na imagem a seguir.

Configurar a contagem de buckets de índice de hash

A contagem de bucket de índice de hash é especificada no momento de criação do índice e pode ser alterada utilizando a sintaxe ALTER TABLE...ALTER INDEX REBUILD.

Na maioria das situações, a contagem de buckets deveria ser de 1 a 2 vezes o número de valores distintos na chave de índice. Talvez nem sempre seja possível prever quantos valores uma determinada chave de índice tem. O desempenho geralmente ainda é bom se o BUCKET_COUNT valor estiver dentro de 10 vezes do número real de valores-chave, e superestimar geralmente é melhor do que subestimar.

Poucos contentores podem ter as seguintes desvantagens:

• Mais colisões de hash de valores de chave distintos.
• Cada valor distinto é obrigado a partilhar o mesmo agrupamento com outro valor distinto.
• O comprimento médio da cadeia por balde cresce.
• Quanto maior a cadeia de buckets, mais lenta é a realização de pesquisas de igualdade no índice.

Muitas caçambas podem ter as seguintes desvantagens:

• Uma contagem de baldes muito alta pode resultar em mais baldes vazios.
• Os buckets vazios afetam o desempenho de verificações de índice completas. Se as verificações forem realizadas regularmente, considere escolher uma contagem de bucket próxima ao número de valores de chave de índice distintos.
• Os buckets vazios usam memória, embora cada bucket use apenas 8 bytes.

Note

Adicionar mais buckets não faz nada para reduzir o encadeamento de entradas que compartilham um valor duplicado. A taxa de duplicação de valor é usada para decidir se um índice de hash ou um índice não clusterizado é o tipo de índice apropriado, não para calcular a contagem de buckets.

Considerações de desempenho para índices de hash

O desempenho de um índice de hash é:

• Excelente quando o WHERE predicado na cláusula especifica um valor exato para cada coluna na chave de índice de hash. Um índice de hash reverte para uma varredura dada um predicado de desigualdade.
• Pobre quando o WHERE predicado na cláusula procura um intervalo de valores na chave de índice.
• Ineficaz quando o predicado na WHERE cláusula estipula um valor específico para a primeira coluna de uma chave de índice de hash de duas colunas, mas não especifica um valor para outra coluna da chave.

Tip

O predicado deve incluir todas as colunas na chave de índice de hash. O índice de hash requer que toda a chave seja buscada no índice.

Se um índice de hash for usado e o número de chaves de índice exclusivas for menos de 1/100 da contagem de linhas, considere aumentar para um número de buckets maior para evitar grandes encadeamentos de linhas ou usar um índice não clusterizado.

Criar um índice de hash

Ao criar um índice de hash, considere:

• Um índice de hash só pode existir em uma tabela com otimização de memória. Ele não pode existir em uma tabela baseada em disco.
• Um índice de hash não é exclusivo por padrão, mas pode ser declarado como exclusivo.

O exemplo a seguir cria um índice de hash exclusivo:

ALTER TABLE MyTable_memop ADD INDEX ix_hash_Column2
    UNIQUE HASH (Column2) WITH (BUCKET_COUNT = 64);

Versões de linha e coleta de lixo em tabelas com otimização de memória

Em uma tabela com otimização de memória, quando uma linha é afetada por uma UPDATE instrução, a tabela cria uma versão atualizada da linha. Durante a transação de atualização, outras sessões poderão ler a versão mais antiga da linha e, assim, evitar a lentidão de desempenho associada a um bloqueio de linha.

O índice de hash também pode ter diferentes versões de suas entradas para acomodar a atualização.

Mais tarde, quando as versões mais antigas não forem mais necessárias, um thread de coleta de lixo (GC) atravessa os buckets e suas listas de links para limpar entradas antigas. O thread GC tem melhor desempenho se os comprimentos da cadeia da lista de links forem curtos. Para obter mais informações, consulte In-Memory Coleta de lixo OLTP.

Diretrizes de design de índice não clusterizado com otimização de memória

Além dos índices de hash, os índices não agrupados são os outros tipos de índice possíveis em uma tabela com otimização de memória. Para obter mais informações, consulte Índices nas Tabelas Memory-Optimized.

Arquitetura de índice não clusterizado otimizada para memória

Índices não clusterizados em tabelas com otimização de memória são implementados usando uma estrutura de dados chamada árvore Bw, originalmente prevista e descrita pela Microsoft Research em 2011. Uma árvore Bw é uma variação sem fechadura e trava de uma árvore B. Para obter mais informações, consulte The Bw-tree: A B-tree for New Hardware Platforms.

Em um alto nível, o Bw-tree pode ser entendido como um mapa de páginas organizadas por ID de página (PidMap), uma facilidade para alocar e reutilizar IDs de página (PidAlloc) e um conjunto de páginas vinculadas no mapa de página e entre si. Estes três subcomponentes de alto nível compõem a estrutura interna básica de uma árvore Bw.

A estrutura é semelhante a uma árvore B normal no sentido de que cada página tem um conjunto de valores-chave que são ordenados e há níveis no índice, cada um apontando para um nível mais baixo e os níveis de folha apontam para uma linha de dados. No entanto, existem várias diferenças.

Assim como acontece com os índices de hash, várias linhas de dados podem ser vinculadas para dar suporte ao controle de versão. Os ponteiros de página entre os níveis são IDs de página lógicas, que são deslocados em uma tabela de mapeamento de página, que por sua vez tem o endereço físico para cada página.

Não há atualizações internas de páginas de índice. Novas páginas delta são introduzidas para este fim.

• Nenhuma trava ou bloqueio é necessário para atualizações de página.
• As páginas de índice não têm um tamanho fixo.

O valor-chave em cada página de nível não folha é o valor mais alto que o filho para o qual ela aponta contém, e cada linha também contém esse ID de página lógica. Nas páginas de nível de folha, juntamente com o valor da chave, ele contém o endereço físico da linha de dados.

As consultas pontuais são semelhantes às árvores B, exceto que, como as páginas são vinculadas em apenas uma direção, o Mecanismo de Banco de Dados segue os ponteiros de página à direita, em que cada página não foliar apresenta o maior valor de seu filho, ao invés do menor valor encontrado em uma árvore B.

Se uma página de nível folha precisar ser alterada, o Mecanismo de Banco de Dados não modificará a página em si. Em vez disso, o Mecanismo de Banco de Dados cria um registro delta que descreve a alteração e a acrescenta à página anterior. Em seguida, ele também atualiza o endereço da tabela do mapa de página para essa página anterior, para o endereço do registro delta que agora se torna o endereço físico dessa página.

Há três operações diferentes que podem ser necessárias para gerenciar a estrutura de uma árvore Bw: consolidação, divisão e fusão.

Consolidação Delta

Uma longa cadeia de registos delta pode, eventualmente, degradar o desempenho da pesquisa, pois pode exigir percorrer uma cadeia longa na pesquisa num índice. Se um novo registro delta for adicionado a uma cadeia que já tenha 16 elementos, as alterações nos registros delta serão consolidadas na página de índice referenciada e a página será reconstruída, incluindo as alterações indicadas pelo novo registro delta que disparou a consolidação. A página recém-reconstruída tem o mesmo ID de página, mas um novo endereço de memória.

Página dividida

Uma página de índice em Bw-tree cresce conforme necessário, desde o armazenamento de uma única linha até o armazenamento de um máximo de 8 KB. Quando a página de índice cresce para 8 KB, uma nova inserção de uma única linha faz com que a página de índice seja dividida. Para uma página interna, isso significa quando não há mais espaço para adicionar outro valor de chave e ponteiro, e para uma página folha, significa que a linha seria muito grande para caber na página depois que todos os registros delta forem incorporados. As informações estatísticas no cabeçalho de uma página folha controlam quanto espaço é necessário para consolidar os registros delta. Essas informações são ajustadas à medida que cada novo registro delta é adicionado.

Uma operação de divisão é feita em duas etapas atômicas. No diagrama a seguir, suponha que uma página folha força uma divisão porque uma chave com valor 5 está sendo inserida e uma página não folha existe apontando para o final da página de nível folha atual (valor de chave 4).

1º Passo: Aloque duas novas páginas P1 e P2, e divida as linhas da página antiga P1 para essas novas páginas, incluindo a linha recém-inserida. Um novo slot na tabela de mapeamento de página é usado para armazenar o endereço físico da página .P2 Páginas P1 e P2 ainda não estão acessíveis a nenhuma operação simultânea. Além disso, o ponteiro lógico de P1 para P2 é definido. Em seguida, num só passo atómico, atualize a tabela de mapeamento de página para alterar o ponteiro do antigo P1 para o novo P1.

2º Passo: A página não folha aponta para, P1 mas não há um ponteiro direto de uma página não folha para P2. P2 só é acessível através do P1. Para criar um ponteiro de uma página não folha para P2, aloque uma nova página não folha (página de índice interna), copie todas as linhas da página não folha antiga e adicione uma nova linha para apontar para P2. Feito isso, em uma etapa atômica, atualize a tabela de mapeamento de página para alterar o ponteiro de página não folha antiga para nova página não folha.

Página de mesclagem

Quando uma DELETE operação resulta em uma página com menos de 10% do tamanho máximo da página (8 KB), ou com uma única linha, essa página é mesclada com uma página contígua.

Quando uma linha é excluída de uma página, um registro delta para a exclusão é adicionado. Além disso, uma verificação é feita para determinar se a página de índice (página não folha) se qualifica para mesclagem. Essa verificação verifica se o espaço restante após a exclusão da linha é inferior a 10% do tamanho máximo da página. Se for elegível, a fusão é realizada em três etapas atômicas.

Na imagem a seguir, suponha que uma DELETE operação exclua o valor da chave 10.

Passo 1: Uma página delta representando a chave de valor 10 (triângulo azul) é criada, e o seu ponteiro na página não-folha Pp1 é definido para a nova página delta. Além disso, uma página delta de mesclagem especial (triângulo verde) é criada e vinculada para apontar para a página delta. Neste estágio, ambas as páginas (página delta e página mesclagem-delta) não são visíveis para nenhuma transação simultânea. Em uma etapa atômica, o ponteiro para a página P1 de nível de folha da tabela de mapeamento de páginas é atualizado para apontar para a página delta de fusão. Após esta etapa, a entrada para o valor da chave 10 em Pp1 agora aponta para a página merge-delta.

Passo 2: A linha que representa o valor de chave 7 na página não folha Pp1 precisa ser removida, e a entrada para o valor de chave 10 deve ser atualizada para apontar para P1. Para fazer isso, uma nova página Pp2 não folha é alocada e todas as linhas de Pp1 são copiadas, exceto a linha que representa o valor da chave 7; em seguida, o valor da chave 10 é atualizado para apontar para a página P1. Uma vez feito isto, num único passo atómico, a entrada da tabela de mapeamento de página que aponta para Pp1 é atualizada para apontar para Pp2. Pp1 já não está acessível.

3º Passo: As páginas P2 de nível de folha e P1 são mescladas e as páginas delta removidas. Para fazer isso, uma nova página P3 é alocada e as linhas de P2 e P1 são mescladas, e as alterações da página delta são incluídas no novo P3. Em seguida, em um passo atómico, a entrada da tabela de mapeamento de página apontando para a página P1 é atualizada para apontar para a página P3.

Considerações de desempenho para índices não clusterizados otimizados para memória

O desempenho de um índice não clusterizado é melhor do que com índices de hash ao consultar uma tabela otimizada para memória com predicados de desigualdade.

Uma coluna em uma tabela com otimização de memória pode fazer parte de um índice de hash e de um índice não clusterizado.

Quando uma coluna de chave em um índice não clusterizado tem muitos valores duplicados, o desempenho pode diminuir para atualizações, inserções e exclusões. Uma maneira de melhorar o desempenho nessa situação é adicionar uma coluna que tenha melhor seletividade na chave de índice.

Metadados do índice

Para examinar metadados de índice, como definições de índice, propriedades e estatísticas de dados, use as seguintes exibições do sistema:

• sys.objects
• sys.indexes
• sys.index_columns
• sys.columns
• sys.types
• sys.partitions
• sys.internal_partitions
• sys.dm_db_index_usage_stats
• sys.dm_db_partition_stats
• sys.dm_db_index_operational_stats

As vistas anteriores aplicam-se a todos os tipos de índice. Para índices columnstore, use adicionalmente os seguintes modos de exibição:

• sys.column_store_row_groups
• sys.column_store_segments
• sys.column_store_dictionaries
• sys.dm_column_store_object_pool
• sys.dm_db_column_store_row_group_operational_stats
• sys.dm_db_column_store_row_group_physical_stats

Para índices columnstore, todas as colunas são armazenadas nos metadados como colunas incluídas. O índice columnstore não tem colunas-chave.

Para índices em tabelas com otimização de memória, use adicionalmente as seguintes exibições:

• sys.hash_indexes
• sys.dm_db_xtp_hash_index_stats
• sys.dm_db_xtp_index_stats
• sys.dm_db_xtp_nonclustered_index_stats
• sys.dm_db_xtp_object_stats
• sys.dm_db_xtp_table_memory_stats
• sys.memory_optimized_tables_internal_attributes

Conteúdo relacionado

• CRIAR ÍNDICE (Transact-SQL)
• Otimize a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos
• Tabelas e índices particionados
• Índices em tabelas Memory-Optimized
• Índices de columnstore: visão geral
• Índices em colunas computadas
• Ajuste índices não agrupados com sugestões de índice ausentes