Guia de arquitetura e design de índice do SQL Server e Azure SQL

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsSistema de Plataforma Analítica (PDW)banco de dados SQL no Microsoft Fabric

Índices mal projetados e falta de índices são as principais fontes de gargalos de aplicativos de banco de dados. Projetar índices eficientes é fundamental para alcançar um bom desempenho de bancos de dados e aplicativos. Este guia de design de índice contém informações sobre arquitetura de índice e práticas recomendadas para ajudá-lo a projetar índices eficazes para atender às necessidades de seu aplicativo.

Este guia pressupõe que o leitor tenha uma compreensão geral dos tipos de índice disponíveis. Para obter uma descrição geral dos tipos de índice, consulte Índices.

Este guia abrange os seguintes tipos de índices:

Formato de armazenamento principal	Tipo de índice
Rowstore baseado em disco
	Agrupados
	Não agrupados
	Único
	Filtrado
Columnstore
	Columnstore agrupado
	Columnstore não clusterizado
Memória otimizada
	Hash
	Memory-Optimized não clusterizado

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 design 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 rowstore não é diferente: é 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 é 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 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 de armazenamento de linhas contém chaves criadas a partir de 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 a linha ou linhas associadas aos valores de chave de forma rápida e eficiente.

Um índice rowstore armazena dados organizados logicamente como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados em linha chamado rowstore¹ ou armazenados em um formato de dados em coluna chamado columnstore.

A seleção dos índices certos para uma base de dados e a sua carga de trabalho é um ato de equilíbrio complexo entre a velocidade da consulta e o custo da atualização. Os índices rowstore estreitos baseados em disco, ou índices com poucas colunas na chave de índice, requerem menos espaço em disco e despesas gerais de manutenção. Já os índices largos, abrangem mais consultas. Você pode ter que experimentar vários designs diferentes antes de encontrar o índice mais eficiente. Os índices podem ser adicionados, modificados e descartados 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 escolhe de forma confiável o índice mais eficaz na maioria dos casos. Sua estratégia geral de design de índice deve fornecer vários índices para o otimizador de consulta escolher e confiar nele para tomar a decisão certa. Isso reduz o tempo de análise e produz um bom desempenho em uma variedade de situações. Para ver quais índices o otimizador de consulta usa para uma consulta específica, no SQL Server Management Studio, no menu Consulta , selecione 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 combinação de índices, somente quando ele melhora o desempenho e evitar a recuperação indexada quando ele prejudica o desempenho.

¹ Rowstore tem sido a maneira tradicional de armazenar dados de tabelas relacionais. Rowstore refere-se à tabela em que o formato de armazenamento de dados subjacente é uma pilha, uma árvore B+ (índice clusterizado) ou uma tabela otimizada para 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 própria base de dados.

   • Por exemplo, é um banco de dados de processamento de transações on-line (OLTP) com modificações de dados frequentes que devem sustentar uma alta taxa de transferência? Tabelas e índices otimizados para memória são especialmente apropriados para esse cenário, fornecendo um design sem travamento. Para obter mais informações, consulte Índices nas tabelas Memory-Optimized, nas diretrizes de design de índice não clusterizadoMemory-Optimized e diretrizes sobre design de índice de hash neste guia.
   • Ou é um exemplo de um sistema de apoio à decisão (DSS) ou banco de dados de armazenamento de dados (OLAP) que deve processar conjuntos de dados muito grandes rapidamente? Os índices Columnstore são especialmente apropriados para conjuntos de dados típicos de armazenamento de dados. Os índices Columnstore podem transformar a experiência de armazenamento de dados para os usuários, permitindo um desempenho mais rápido para consultas comuns de armazenamento de dados, como filtragem, agregação, agrupamento e consultas de junção de estrelas. Para obter mais informações, consulte Columnstore indexes: overview, ou Columnstore index design guidelines 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 melhor tipo de índices a serem usados.

3. Compreender as características das colunas usadas nas consultas. Por exemplo, um índice é ideal para colunas que têm um tipo de dados inteiro e também são colunas exclusivas ou não nulas. Para colunas com subconjuntos de dados bem definidos, você pode usar um índice filtrado no SQL Server 2008 (10.0.x) e versões superiores. 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 quando o índice é criado ou mantido. Por exemplo, a criação de um índice clusterizado em uma tabela grande existente se beneficiaria 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. Para obter mais informações, consulte Definir opções de índice.

5. Determine o local de armazenamento ideal para o índice.

   Um índice não clusterizado pode ser armazenado no mesmo grupo de arquivos que a tabela subjacente ou em um grupo de arquivos diferente. O local de armazenamento dos índices pode melhorar o desempenho da consulta aumentando o desempenho de E/S do disco. Por exemplo, armazenar um índice não clusterizado em um grupo de arquivos que está em um disco diferente do grupo de arquivos de tabela pode melhorar o desempenho porque vários discos podem ser lidos ao mesmo tempo. Como alternativa, índices clusterizados e não clusterizados podem usar um esquema de partição em vários grupos de arquivos. Ao considerar o particionamento, determine se o índice deve ser alinhado, ou seja, particionado essencialmente da mesma maneira que a tabela, ou particionado independentemente. Saiba mais na seção posicionamento de índice em grupos de arquivos ou esquemas de partições deste artigo.

6. Quando você identifica índices ausentes com DMVs (Exibições de Gerenciamento Dinâmico), como sys.dm_db_missing_index_details e sys.dm_db_missing_index_columns, podem ser oferecidas variações semelhantes de índices na mesma tabela e colunas. Examine os índices existentes na tabela juntamente com as sugestões de índice ausentes para evitar a criação de índices duplicados. Saiba mais em sintonizar índices não agrupados com sugestões de índice ausentes.

Diretrizes gerais de design do índice

Administradores de banco de dados experientes podem projetar um bom conjunto de índices, mas essa tarefa é complexa, demorada e propensa a erros, mesmo para bancos de dados e cargas de trabalho moderadamente complexos. Compreender as características do seu banco de dados, consultas e colunas de dados pode ajudá-lo a projetar índices ideais.

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 em uma tabela afeta o desempenho de INSERT, UPDATE, DELETE e MERGE instruções porque todos os índices devem ser ajustados adequadamente conforme 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 deverá ser atualizado, bem como a coluna na tabela base subjacente (heap ou índice clusterizado).

  • 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.

  • Use muitos índices para melhorar o desempenho da consulta em tabelas com baixos requisitos de atualização, mas grandes volumes de dados. Um grande número de índices pode ajudar o desempenho de consultas que não modificam dados, como SELECT instruções, porque o otimizador de consulta tem mais índices para escolher para determinar o método de acesso mais rápido.

• A indexação de tabelas pequenas pode não ser ideal porque o otimizador de consulta pode levar mais tempo para percorrer o índice em busca de dados do que para executar uma verificação básica de tabela. Portanto, os índices em tabelas pequenas podem nunca ser usados, mas ainda devem ser mantidos à medida que os dados na tabela mudam.

• Os índices em vistas podem fornecer ganhos de desempenho significativos quando a vista contém agregações, junções de tabelas ou uma combinação de agregações e junções. A vista não precisa ser explicitamente referenciada na consulta para o otimizador de consultas utilizá-la.

• 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 documentam índices selecionados pelo otimizador.

Considerações sobre consultas

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 condições em consultas. Estas são as colunas SARGable¹. No entanto, você deve evitar adicionar colunas desnecessárias. Adicionar muitas colunas de índice pode afetar negativamente o espaço em disco e o desempenho de manutenção do índice.

• 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.

  Índices de cobertura são a designação para um índice não clusterizado que resolve um ou vários resultados de consulta semelhantes diretamente, sem acesso à sua tabela base e sem incorrer em pesquisas.

  Tais índices têm todas as colunas não SARGable necessárias em seu nível foliar. Isso significa que as colunas retornadas pela cláusula SELECT e todos os argumentos WHERE e JOIN são cobertas pelo í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 na própria tabela, o que significa que é um subconjunto real do total de colunas.

  Considere cobrir índices ao selecionar uma pequena parte de uma tabela grande e onde essa pequena parte é definida por um predicado fixo, como colunas esparsas que contêm apenas alguns valores não NULL, por exemplo.

• 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. Usando apenas uma instrução, a manutenção otimizada do índice pode ser explorada.

• Avalie o tipo de consulta e como as colunas são usadas na consulta. Por exemplo, uma coluna usada em um tipo de consulta de correspondência exata seria um bom candidato para um índice não clusterizado ou clusterizado.

¹ O termo SARGable em bases de dados relacionais refere-se a um predicado Search ARGument capaz que pode usar um índice para acelerar a execução da consulta.

Considerações sobre a coluna

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

• Mantenha o comprimento da chave de índice curto para índices clusterizados. Além disso, os índices clusterizados se beneficiam da criação em colunas exclusivas ou não nulas.

• As colunas dos tipos de dados ntext, text, image, varchar(max), nvarchar(max) e varbinary(max) não podem ser especificadas como colunas de chave de índice. No entanto, os tipos de dados varchar(max), nvarchar(max), varbinary(max) e xml podem participar de um índice não clusterizado como colunas de índice não-chave. Para obter mais informações, consulte a seção Índice com colunas incluídas neste guia.

• Um tipo de dados xml só pode ser uma coluna de chave somente em um índice XML. Para obter mais informações, consulte Índices XML (SQL Server). O SQL Server 2012 SP1 introduziu um novo tipo de índice XML conhecido como Índice XML Seletivo. Esse novo índice pode melhorar o desempenho de consultas sobre dados armazenados como XML, permitir indexação mais rápida de grandes cargas de trabalho de dados XML e melhorar a escalabilidade reduzindo os custos de armazenamento do próprio índice. Para obter mais informações, consulte Índices XML seletivos (SXI).

• Examine a exclusividade da coluna. Um índice exclusivo em vez de um índice não exclusivo na mesma combinação de colunas 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. Freqüentemente, uma consulta de longa execução é causada pela indexação de uma coluna com poucos valores exclusivos ou pela execução de uma junção em tal coluna. Este é um problema fundamental com os dados e a consulta, e geralmente não pode ser resolvido sem identificar essa situação. Por exemplo, 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 esparsas, colunas com valores principalmente NULL , 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 manutenção do índice e os custos de armazenamento.

• Considere a ordem das colunas se o índice contiver várias colunas. A coluna usada na cláusula WHERE igual a (=), maior que (>), menor que (<), ou na condição de pesquisa BETWEEN, ou participa numa 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 critério de pesquisa 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 FirstName (WHERE FirstName = 'Jane').

• Considere a indexação de colunas computadas. 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 a seguinte lista:

• Agrupados versus não agrupados
• Único versus não único
• Coluna única versus coluna múltipla
• Ordem crescente ou decrescente nas colunas do índice
• Tabela completa versus filtrada para índices não agrupados
• Armazenamento em colunas versus armazenamento em linhas
• Hash versus não clusterizado para tabelas com otimização de memória

Você também pode personalizar as características iniciais de armazenamento do índice para otimizar seu desempenho ou manutenção definindo uma opção como FILLFACTOR. Além disso, você pode determinar o local de armazenamento do índice usando grupos de arquivos ou esquemas de partição para otimizar o desempenho.

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. A seleção cuidadosa do grupo de arquivos ou esquema de partição pode melhorar o desempenho da consulta.

Por padrão, os índices são armazenados no mesmo grupo de arquivos que a tabela base na qual o índice é criado. Um índice clusterizado não particionado e a tabela base sempre residem no mesmo grupo de arquivos. No entanto, você pode fazer as seguintes etapas:

• Crie índices não clusterizados em um grupo de arquivos diferente do grupo de arquivos da tabela base ou do índice clusterizado.
• Particione índices agrupados e não clusterizados para abranger vários grupos de arquivos.
• Mova uma tabela 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.

Ao criar o índice não clusterizado em um grupo de arquivos diferente, você pode obter ganhos de desempenho se os grupos de arquivos estiverem usando unidades físicas diferentes com seus próprios controladores. Os dados e as informações de índice podem então ser lidos em paralelo pelas várias cabeças de disco. Por exemplo, se Table_A no grupo f1 de arquivos e Index_A no grupo f2 de arquivos estiverem sendo usados pela mesma consulta, os ganhos de desempenho podem ser obtidos porque ambos os grupos de arquivos estão sendo totalmente usados sem contenção. No entanto, se Table_A for verificado pela consulta, mas Index_A não for referenciado, apenas o grupo f1 de arquivos será usado. Isso não gera ganho de desempenho.

Como você não pode prever que tipo de acesso ocorre e quando ocorre, pode ser uma decisão melhor distribuir suas tabelas e índices em todos os grupos de arquivos. Isso garantiria que todos os discos estão sendo acessados porque todos os dados e índices são distribuídos uniformemente em todos os discos, independentemente da maneira como os dados são acessados. Esta também é uma abordagem mais simples para administradores de sistema.

Partições em vários grupos de arquivos

Você também pode considerar o particionamento de índices clusterizados e não clusterizados baseados em disco em vários grupos de arquivos. Os índices particionados são particionados horizontalmente ou por linha, com base em uma função de partição. A função de partição define como cada linha é mapeada para um conjunto de partições com base nos valores de determinadas colunas, chamadas colunas de particionamento. Um esquema de partição especifica o mapeamento das partições para um conjunto de grupos de arquivos.

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

• Forneça sistemas escaláveis que tornam os grandes índices mais gerenciáveis. Os sistemas OLTP, por exemplo, podem implementar aplicativos com reconhecimento de partição que lidam com grandes índices.

• Faça com que as consultas sejam executadas de forma mais rápida e eficiente. Quando as consultas acessam várias partições de um índice, o otimizador de consulta pode processar partições individuais ao mesmo tempo e excluir partições que não são afetadas pela consulta.

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 os dados da coluna de chave de índice devem ser armazenados em ordem crescente ou decrescente. Ascendente é o padrão e mantém a compatibilidade com versões anteriores do Mecanismo de Banco de Dados. 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 eliminar a necessidade de um SORT operador no plano de consulta, portanto, isso torna a consulta mais eficiente. 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 esses 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;
GO

O seguinte plano de execução para esta consulta mostra que o otimizador de consulta usou um SORT operador 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 coincidam com as da cláusula ORDER BY na consulta, o operador SORT pode ser eliminado no plano de consulta, tornando o plano mais eficiente.

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

Depois que a consulta é executada novamente, o plano de execução a seguir mostra que o SORT operador foi eliminado e o índice não clusterizado recém-criado é usado.

O Mecanismo de Banco de Dados pode se mover de forma igualmente eficiente em qualquer direção. Um índice definido como (RejectedQty DESC, ProductID ASC) ainda pode ser usado para uma consulta na qual a direção de classificação das colunas na ORDER BY cláusula é invertida. Por exemplo, uma consulta com a ORDER BY cláusula ORDER BY RejectedQty ASC, ProductID DESC pode usar o índice.

A ordem de classificação pode ser especificada apenas para as colunas de chave no índice. A exibição de catálogo sys.index_columns e a INDEXKEY_PROPERTY função relatam se uma coluna de índice é armazenada em ordem crescente ou decrescente.

Caso esteja a seguir os exemplos de código no banco de dados de exemplo AdventureWorks, pode eliminar o IX_PurchaseOrderDetail_RejectedQty com o seguinte Transact-SQL:

DROP INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail;
GO

Metadados

Use essas exibições de metadados para ver atributos de índices. Mais informações arquitetónicas estão incorporadas em algumas destas perspetivas.

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

• sys.column_store_dictionaries
• sys.column_store_row_groups
• sys.column_store_segments
• sys.dm_column_store_object_pool
• sys.dm_db_column_store_row_group_operational_stats
• sys.dm_db_column_store_row_group_physical_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• 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.hash_indexes
• sys.index_columns
• sys.indexes
• sys.internal_partitions
• sys.memory_optimized_tables_internal_attributes
• sys.partições

Diretrizes de design de índice agrupado

Os índices agrupados classificam e armazenam as linhas de dados na tabela com base em seus valores de chave. Só pode haver um índice clusterizado por tabela, porque as próprias linhas de dados só podem ser classificadas em uma ordem. Com poucas exceções, cada tabela deve ter um índice clusterizado definido na coluna, ou colunas, que oferece o seguinte:

• Pode ser usado para consultas usadas com freqüência.

• Proporciona um elevado grau de singularidade.

  Observação

  Quando se cria uma PRIMARY KEY restrição, cria-se automaticamente um índice exclusivo na coluna ou colunas. Por padrão, esse índice é agrupado; no entanto, você pode especificar um índice não clusterizado ao criar a restrição.

• Pode ser usado em consultas de intervalo.

Se o índice clusterizado não for criado com a UNIQUE propriedade, o Mecanismo de Banco de Dados adicionará automaticamente uma coluna uniqueifier de 4 bytes à tabela. Quando necessário, o Mecanismo de Banco de Dados adiciona automaticamente um valor exclusivo a uma linha para tornar cada chave exclusiva. Esta coluna e seus valores são usados internamente e não podem ser vistos ou acessados pelos usuários.

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, com index_id = 1 para cada partição usada pelo índice. 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+ que contém os dados para essa partição específica. Por exemplo, se um índice clusterizado tiver quatro partições, haverá quatro estruturas de árvore B+; um 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 unidade de alocação de LOB_DATA por partição se contiver colunas de objetos grandes (LOB). Ele também tem uma unidade de alocação ROW_OVERFLOW_DATA 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 cadeia de dados e as linhas nelas são ordenadas no valor da chave de índice clusterizada. Todas as inserções são feitas no ponto em que o valor da chave na linha inserida se encaixa na sequência de ordenação entre as linhas existentes.

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

Considerações sobre consultas

Antes de criar índices clusterizados, entenda como seus dados são acessados. Considere o uso de um índice clusterizado para consultas que façam o seguinte:

• Retornar um intervalo de valores usando operadores como BETWEEN, >, >=, <e <=.

  Depois que a linha com o primeiro valor é encontrada usando o índice clusterizado, as linhas com valores indexados subsequentes têm a garantia de estar fisicamente adjacentes. Por exemplo, se uma consulta recuperar registros entre um intervalo de números de ordem de venda, um índice agrupado na coluna SalesOrderNumber poderá localizar rapidamente a linha que contém o número da ordem de venda inicial e, em seguida, recuperar todas as linhas sucessivas na tabela até que o último número da ordem de venda seja atingido.

• Devolver grandes conjuntos de resultados.

• Utilize JOIN cláusulas; estas normalmente são colunas de chave estrangeira.

• Use cláusulas ORDER BY ou GROUP BY.

  Um índice nas colunas especificadas na ORDER BY cláusula ou GROUP BY pode remover a necessidade de o Mecanismo de Banco de Dados classificar os dados, porque as linhas já estão classificadas. Isso melhora o desempenho da consulta.

Considerações sobre a coluna

Geralmente, você deve definir a chave de índice clusterizada com o menor número possível de colunas. Considere colunas que tenham um ou mais dos seguintes atributos:

• São únicos ou contêm muitos valores distintos

  Por exemplo, um ID de funcionário identifica exclusivamente os funcionários. Um índice agrupado ou uma restrição de CHAVE PRIMÁRIA na EmployeeID coluna melhoraria o desempenho das consultas que pesquisam informações de funcionários com base no número de ID do funcionário. Alternativamente, um índice agrupado poderia ser criado em LastName, FirstName, MiddleName porque os registos de funcionários são frequentemente agrupados e consultados desta maneira, e a combinação destas colunas ainda proporcionaria um alto nível de distinção.

  Sugestão

  Se não for especificado de forma diferente, ao criar uma restrição de CHAVE PRIMÁRIA , o Mecanismo de Banco de Dados criará um índice clusterizado para dar suporte a essa restrição.

  Embora um identificador exclusivo possa ser usado para impor exclusividade como um PRIMARY KEY, ele não é uma chave de cluster eficiente.

  Se estiver usando um identificador exclusivo como PRIMARY KEY, a recomendação é criá-lo como um índice não clusterizado e usar outra coluna, como an IDENTITY , para criar o índice clusterizado.

• São acessados sequencialmente

  Por exemplo, um ID de produto identifica exclusivamente os Production.Product produtos na tabela do AdventureWorks2022 banco de dados. As consultas nas quais uma pesquisa sequencial é especificada, como WHERE ProductID BETWEEN 980 and 999, se beneficiariam de um índice clusterizado no ProductID. Isso ocorre porque as linhas seriam armazenadas em ordem ordenada nessa coluna de chave.

• Definido como IDENTITY.

• Usado com freqüência para classificar os dados recuperados de uma tabela.

  Pode ser uma boa ideia agrupar (classificar fisicamente) a tabela nessa coluna, para economizar o custo de uma operação de classificação sempre que a coluna for consultada.

Os índices clusterizados não são uma boa opção para os seguintes atributos:

• Colunas que sofrem alterações frequentes

  Isso faz com que toda a linha seja movida, porque o Mecanismo de Banco de Dados deve manter os valores de dados de uma linha em ordem física. Esta é uma consideração importante em sistemas de processamento de transações de alto volume, nos quais os dados são tipicamente voláteis.

• Teclas largas

  As teclas largas são compostas por várias colunas ou várias colunas de grandes dimensões. Os valores de chave do índice clusterizado são usados por todos os índices não clusterizados como chaves de pesquisa. Todos os índices não clusterizados definidos na mesma tabela são significativamente maiores, porque as entradas de índice não clusterizadas contêm a chave de clustering e também as colunas de chave definidas para esse índice não clusterizado.

Diretrizes de design de índice não agrupado

Um índice não clusterizado de armazenamento de linha baseado em disco contém os valores de chave de índice e localizadores de linha que apontam para o local de armazenamento dos dados da tabela. 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 não são cobertas pelo índice clusterizado.

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 coluna chave. O otimizador de consulta pode localizar rapidamente todas as entradas no índice que correspondem ao especificado ManagerID. Cada entrada de índice aponta para a página e linha exatas na tabela, ou índice agrupado, no qual os dados correspondentes podem ser encontrados. Depois que o otimizador de consulta encontrar todas as entradas no índice, ele poderá ir diretamente para a página e a linha exatas para recuperar os dados.

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 significativas:

• As linhas de dados da tabela subjacente não são classificadas e armazenadas em ordem com base em suas chaves não clusterizadas.

• 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 e colunas incluídas.

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, conforme descrito a seguir:

• 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).

• 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.

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	Tipo de índice não agrupado	Localizador de linhas
Pilha
	Não único	RID adicionado às colunas principais
	Único	RID adicionado às colunas incluídas
Índice clusterizado exclusivo
	Não único	Chaves de índice clusterizadas adicionadas às colunas de chave
	Único	Chaves de índice clusterizadas adicionadas às colunas incluídas
Índice clusterizado não exclusivo
	Não único	Chaves de índices clusterizados e uniqueifier (quando presente) adicionados às colunas de chave
	Único	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 duas vezes 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 respeitada: 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. As colunas do localizador de linhas baseadas em chave de índice clusterizado em um índice não clusterizado podem ser usadas pelo otimizador de consulta, independentemente de terem sido especificadas explicitamente na definição de índice.

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	Explicação
Í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 com index_id > 1 para cada partição usada pelo índice. 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, haverá quatro estruturas de árvore B+, com 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 unidade de alocação IN_ROW_DATA por partição que armazena as páginas da árvore de índice B+. O índice não clusterizado também tem uma unidade de alocação LOB_DATA por partição se contiver colunas de grandes objetos (LOB). Além disso, ele tem uma unidade de alocação ROW_OVERFLOW_DATA 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.

Considerações sobre o banco de dados

Considere as características do banco de dados ao projetar índices não clusterizados.

• Bancos de dados ou tabelas com baixos requisitos de atualização, mas grandes volumes de dados podem se beneficiar de muitos índices não clusterizados para melhorar o desempenho da consulta. Considere a criação de índices filtrados para subconjuntos de dados bem definidos para melhorar o desempenho da consulta, reduzir os custos de armazenamento de índice e reduzir os custos de manutenção do índice em comparação com índices não clusterizados de tabela completa.

  As aplicações e bases de dados dos Sistemas de Apoio à Decisão que contêm principalmente dados de leitura podem beneficiar-se de muitos índices não agrupados. O otimizador de consulta tem mais índices para escolher para determinar o método de acesso mais rápido, e as características de baixa atualização do banco de dados significam que a manutenção do índice não impede o desempenho.

• Aplicativos OLTP (Online Transaction Processing) e bancos de dados que contêm tabelas altamente atualizadas devem evitar a indexação excessiva. Além disso, os índices devem ser estreitos, ou seja, com o menor número possível de colunas.

  Um grande número de índices em uma tabela afeta o desempenho de INSERT, UPDATE, DELETE e MERGE instruções porque todos os índices devem ser ajustados adequadamente conforme os dados na tabela mudam.

Considerações sobre consultas

Antes de criar índices não clusterizados, você deve entender como seus dados são acessados. Considere o uso de um índice não clusterizado para consultas que tenham os seguintes atributos:

• Use cláusulas JOIN ou GROUP BY.

  Crie vários índices não clusterizados em colunas envolvidas em operações de junção e agrupamento e um índice clusterizado em qualquer coluna de chave estrangeira.

• Consultas que não retornam grandes conjuntos de resultados.

  Crie índices filtrados para cobrir consultas que retornam um subconjunto bem definido de linhas de uma tabela grande.

  Sugestão

  Normalmente, a cláusula da instrução WHERECREATE INDEX corresponde à cláusula WHERE de uma consulta coberta.

• Contêm colunas frequentemente envolvidas nas condições de pesquisa de uma consulta, como uma cláusula WHERE, que retorna correspondências exatas.

  Sugestão

  Considere o custo versus benefício ao adicionar novos índices. Pode ser preferível consolidar necessidades de consulta adicionais em um índice existente. Por exemplo, considere adicionar uma ou duas colunas extras de nível de folha a um índice existente, se ele permitir a cobertura de várias consultas críticas, em vez de ter um índice de cobertura exata por cada consulta crítica.

Considerações sobre a coluna

Considere colunas que tenham um ou mais destes atributos:

• Responda à pergunta

  Os ganhos de desempenho são obtidos quando o índice contém todas as colunas na consulta. O otimizador de consulta pode localizar todos os valores de coluna dentro do índice; os dados de tabela ou índice clusterizado não são acessados, resultando em menos operações de E/S de disco. Use índice com colunas incluídas para adicionar colunas de cobertura em vez de criar uma chave de índice ampla.

  Se a tabela tiver um índice clusterizado, a coluna ou colunas definidas no índice clusterizado serão adicionadas automaticamente a cada índice não clusterizado na tabela. Isso pode produzir uma consulta coberta sem especificar as colunas de índice clusterizadas na definição do índice não clusterizado. Por exemplo, se uma tabela tiver um índice clusterizado na coluna C, um índice não exclusivo não clusterizado nas colunas B e A tiver como valores chave as colunas B, Ae C. Para obter mais informações, visite arquitetura de índice não clusterizado.

• Muitos valores distintos, como uma combinação de apelido e primeiro nome, se um índice clusterizado for usado para outras colunas.

  Se houver muito poucos valores distintos, como apenas 1 e 0, a maioria das consultas não usará o índice porque uma verificação de tabela geralmente é mais eficiente. Para esse tipo de dados, considere a criação de um índice filtrado em um valor distinto que ocorre apenas em algumas linhas. Por exemplo, se a maioria dos valores for 0, o otimizador de consulta poderá usar um índice filtrado para as linhas de dados que contêm 1.

Usar colunas incluídas para estender índices não clusterizados

Você pode estender a funcionalidade de índices não clusterizados adicionando colunas não-chave ao nível de folha do índice não clusterizado. Ao incluir colunas não-chave, você pode criar índices não clusterizados que cobrem mais consultas. Isso ocorre porque as colunas não-chave têm os seguintes benefícios:

• Eles podem ser tipos de dados não permitidos como colunas de chave de índice.

• Eles não são considerados pelo Mecanismo de Banco de Dados ao calcular o número de colunas de chave de índice ou o tamanho da chave de índice.

Um índice com colunas não-chave incluídas pode melhorar significativamente o desempenho da consulta quando todas as colunas na consulta são incluídas no índice como colunas chave ou não-chave. Os ganhos de desempenho são alcançados porque o otimizador de consulta pode localizar todos os valores de coluna dentro do índice; os dados de tabela ou índice clusterizado não são acessados, resultando em menos operações de E/S de disco.

Observação

Quando um índice contém todas as colunas referenciadas pela consulta, normalmente é referido como abrangendo a consulta.

Enquanto as colunas de chave são armazenadas em todos os níveis do índice, as colunas não-chave são armazenadas apenas no nível de folha.

Use colunas incluídas para evitar limites de tamanho

Você pode incluir colunas não-chave em um índice não clusterizado para evitar exceder as limitações atuais de tamanho de índice de um máximo de 16 colunas de chave e um tamanho máximo de chave de índice de 900 bytes. O Mecanismo de Banco de Dados não considera colunas não-chave ao calcular o número de colunas de chave de índice ou o tamanho da chave de índice.

Por exemplo, suponha que você deseja indexar as seguintes colunas na Document tabela:

Title NVARCHAR(50)
Revision NCHAR(5)
FileName NVARCHAR(400)

Como os tipos de dados nchar e nvarchar exigem 2 bytes para cada caractere, um índice que contenha essas três colunas excederia a limitação de tamanho de 900 bytes em 10 bytes (455 * 2). Usando a cláusula INCLUDE da instrução CREATE INDEX, a chave de índice pode ser definida como (Title, Revision) e FileName como uma coluna não-chave. Desta forma, o tamanho da chave de índice seria de 110 bytes (55 * 2), e o índice ainda conteria todas as colunas necessárias. A instrução a seguir cria esse índice.

CREATE INDEX IX_Document_Title
ON Production.Document(Title, Revision)
    INCLUDE(FileName);
GO

Se você estiver acompanhando os exemplos de código, poderá descartar esse índice usando esta instrução Transact-SQL:

DROP INDEX IX_Document_Title
ON Production.Document;
GO

Índice com diretrizes de colunas incluídas

Ao criar índices não clusterizados com colunas incluídas, considere as seguintes diretrizes:

• As colunas não-chave são definidas na cláusula INCLUDE da declaração CREATE INDEX.

• As colunas não-chave só podem ser definidas em índices não clusterizados 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 computadas derivadas dos tipos de dados image, ntext e text podem ser colunas não-chave (incluídas), desde que o tipo de dados de coluna computada seja permitido como uma coluna de índice não-chave.

• 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.

Diretrizes de tamanho de coluna

• Pelo menos uma coluna chave deve ser definida. O número máximo de colunas não-chave é de 1.023 colunas. Este é o número máximo de colunas da tabela menos 1.

• As colunas de chave de índice, excluindo não-chaves, devem seguir as restrições de tamanho de índice existentes de 16 colunas de chave no máximo e um tamanho total de chave de índice de 900 bytes.

• O tamanho total de todas as colunas não-chave é limitado apenas pelo tamanho das colunas especificadas na cláusula, por INCLUDE exemplo, as colunas varchar(max) são limitadas a 2 GB.

Diretrizes de modificação de coluna

Quando você modifica uma coluna de tabela que foi definida como uma coluna incluída, as seguintes restrições se aplicam:

• As colunas não-chave não podem ser descartadas da tabela, a menos que o índice seja descartado primeiro.

• As colunas não-chave não podem ser alteradas, exceto para fazer o seguinte:

  • Altere a anulabilidade da coluna de NOT NULL para NULL.

  • Aumente o comprimento das colunas varchar, nvarcharou varbinary.

    Observação

    Essas restrições de modificação de coluna também se aplicam a colunas de chave de índice.

Recomendações de design

Redesenhe índices não clusterizados com um tamanho de chave de índice grande, de forma que apenas as colunas usadas para buscas e consultas sejam colunas chave. 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';
GO

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 apenas um SELECT operador e um operador Index Seek para o IX_Address_PostalCode índice, a consulta será coberta pelo índice.

Você pode eliminar o índice com a seguinte instrução:

DROP INDEX IX_Address_PostalCode
ON Person.Address;
GO

Considerações sobre desempenho

Evite adicionar colunas desnecessárias. Adicionar muitas colunas de índice, chave ou não-chave, pode ter as seguintes implicações de desempenho:

• Menos linhas de índice cabem em uma página. Isso poderia criar aumentos de E/S e reduzir 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 como colunas de índice não-chave 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.

• A manutenção do índice pode aumentar o tempo necessário para executar modificações, inserções, atualizações ou exclusões na tabela subjacente ou na exibição indexada.

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

Diretrizes para o projeto de índice único

Um índice exclusivo garante que a chave de índice não contém valores duplicados e, portanto, cada linha na tabela é, de alguma forma, exclusiva. Especificar um índice exclusivo só faz sentido 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. Se o usuário tentar inserir o mesmo valor nessa coluna para mais de um funcionário, uma mensagem de erro será exibida e o valor duplicado não será inserido.

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 a mesma combinação de valores para essas colunas.

Os índices agrupados e não agrupados podem ser exclusivos. Se os dados na coluna forem exclusivos, você poderá criar um índice clusterizado exclusivo e vários índices não clusterizados exclusivos na mesma tabela.

Os benefícios dos índices exclusivos incluem o seguinte:

• A integridade dos dados das colunas definidas é assegurada.
• 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, deve criar uma restrição UNIQUE ou PRIMARY KEY na coluna para garantir a integridade dos dados. Ao fazer isso, o objetivo do índice é claro.

Considerações

• 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 um índice exclusivo (de preferência criando uma UNIQUE restrição).

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

Diretrizes de design de índice filtrado

Um índice filtrado é um índice não clusterizado otimizado, especialmente adequado para cobrir consultas que selecionam a partir de um subconjunto de dados bem definido. Ele usa um predicado de filtro para indexar uma parte das linhas na tabela. Um índice filtrado bem projetado pode melhorar o desempenho da consulta, reduzir os custos de manutenção do índice e reduzir os custos de armazenamento do índice em comparação com os índices 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 e tem estatísticas filtradas. As estatísticas filtradas são mais precisas do que as estatísticas de tabela completa porque cobrem apenas as linhas do índice filtrado.

• Custos reduzidos de manutenção do índice

  Um índice é mantido somente quando as instruções DML (linguagem de manipulação de dados) afetam os dados no índice. Um índice filtrado reduz os custos de manutenção do índice em comparação com um índice não clusterizado de tabela completa porque é menor e só é mantido 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 reduzirá 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. Você pode 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 bem definidos de dados que as consultas utilizam como referência em SELECT declarações. São exemplos:

• Colunas esparsas que contêm apenas alguns valores diferentes de NULL.
• Colunas heterogêneas que contêm categorias de dados.
• Colunas que contêm intervalos de valores, como valores em dólares, hora e datas.
• Partições de tabela definidas por uma lógica de comparação simples para valores de coluna.

Os custos de manutençã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 você precisar de uma expressão de filtro que faça referência a várias tabelas ou tenha lógica complexa, crie uma exibição.

Considerações de design

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 valores maioritariamente NULL , colunas com categorias heterogéneas de valores e colunas com intervalos distintos de valores. As considerações de design a seguir fornecem uma variedade de cenários para quando um índice filtrado pode fornecer vantagens em relação aos índices de tabela completa.

Sugestão

A definição do índice columnstore não clusterizado suporta o uso de uma condição filtrada. Para minimizar o impacto no desempenho da adição de um índice columnstore em uma tabela OLTP, use uma condição filtrada para criar um índice columnstore não clusterizado somente nos dados frios de sua carga de trabalho operacional.

Í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 os valores em uma coluna são principalmente NULL e a consulta seleciona apenas entre os valores não-NULL, pode criar um índice filtrado para as linhas de dados não-NULL. O índice resultante é menor e custa menos para manter do que um índice não clusterizado de tabela completa definido nas mesmas colunas de chave.

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

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

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';
GO

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;
GO

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 inclui uma pesquisa de 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;
GO

Colunas-chave

É uma prática recomendada incluir algumas colunas de chave ou incluídas em uma definição de índice filtrada e incorporar apenas as colunas necessárias 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. FIBillOfMaterialsWithEndDate não precisa de EndDate como chave ou como coluna incluída na definição de um í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 esta consulta sem procurar 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.

Para eliminar os índices FIBillOfMaterialsWithEndDate e FIProductAccessories, execute as seguintes instruções:

DROP INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials;
GO

DROP INDEX FIProductAccessories
ON Production.Product;
GO

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 vários tipos de dados.

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

Na seguinte definição de índice filtrado, a coluna b é implicitamente convertida em um tipo de dados inteiro para fins de comparação 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;
GO

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);
GO

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 CONVERT operador foi adicionado ao lado direito, a comparação mudou de uma comparação inteira para uma comparação varbinária .

Solte os objetos criados neste exemplo executando a seguinte instrução:

DROP TABLE TestTable;
GO

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 armazenamentos em coluna que explicam como utilizá-los eficazmente.

O armazenamento de dados utiliza a compactação de columnstore e de 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 heap ou um índice de árvore B+ clusterizado.

  Um índice columnstore também armazena fisicamente algumas linhas num 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+ agrupado.

• O deltastore é um local para armazenar linhas que são demasiado poucas para serem compactadas no columnstore. O deltastore armazena as linhas no formato rowstore.

Para obter mais informações sobre termos e conceitos de armazenamento de colunas, consulte Índices de armazenamento de colunas: visão geral.

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 deve ser grande o suficiente para melhorar as taxas de compressão e pequeno o suficiente para tirar proveito de operações na 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 ALTER INDEX ... REORGANIZE operação, incluindo a remoção de dados excluídos.
• Cria novos 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 conjunto de linhas delta é um índice de árvore B+ agrupado que armazena pequenos carregamentos em massa e insere até que o conjunto de linhas contenha 1.048.576 linhas, momento em que um processo chamado tuple-mover compacta automaticamente o conjunto 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.

Sugestão

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 fusão, a qualidade do índice deve ser melhorada.

No SQL Server 2019 (15.x) e versões posteriores, o tuple-mover é auxiliado por uma tarefa de mesclagem em segundo plano que compacta automaticamente pequenos grupos de linhas delta OPEN que existem há algum tempo, conforme determinado por um limite interno, ou mescla grupos de linhas COMPRESSED de onde 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.

Para obter mais informações sobre termos e conceitos de armazenamento de colunas, consulte Índices de armazenamento de colunas: visão geral.

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 da deltastore são geridas nos bastidores. Para retornar os resultados corretos 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.
• Atualizado. 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.

Para obter mais informações sobre termos e conceitos de armazenamento de colunas, consulte Índices de armazenamento de colunas: visão geral.

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.

Para obter mais informações sobre termos e conceitos de armazenamento de colunas, consulte Índices de armazenamento de colunas: visão geral.

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 para um armazenamento mais barato. A comutação de partições funciona em índices columnstore e facilita a movimentação de uma partição de dados para outro local.

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.

Sugestão

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 gerar fragmentação introduzida por ter grupos de linhas menores.

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, 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 menor do que a tabela rowstore exige. Ao fazer isso, você pode executar análises no índice columnstore e transações no índice rowstore ao mesmo tempo. 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.

Você pode ter um ou mais índices rowstore não clusterizados num índice columnstore. 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 uma restrição de chave primária usando uma UNIQUE restrição na tabela rowstore. Como um valor não único falha ao ser inserido na tabela rowstore, o Mecanismo de Banco de Dados não consegue inserir o valor no columnstore.

Considerações sobre desempenho

• A definição do índice de armazenamento em coluna não clusterizado oferece suporte ao uso de uma condição filtrada. Para minimizar o impacto no desempenho da adição de um índice columnstore em uma tabela OLTP, use uma condição filtrada para criar um índice columnstore não clusterizado somente nos dados frios de sua carga de trabalho operacional.

• Uma tabela na memória pode ter um índice de columnstore. Você pode criá-la quando a tabela for criada ou adicioná-la mais tarde com ALTER TABLE (Transact-SQL). Antes do SQL Server 2016 (13.x), apenas uma tabela baseada em disco podia ter um índice columnstore.

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

Orientação de design

• Uma tabela rowstore pode ter um índice columnstore não clusterizado que seja atualizável. Antes do SQL Server 2014 (12.x), o índice columnstore não-clusterizado era de só leitura.

Para obter mais informações, consulte Índices de Columnstore - orientação de design.

Diretrizes de design de índice de hash

Todas as tabelas com otimização de memória devem ter pelo menos um índice, pois são os índices que conectam as linhas. Em 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.

Aplica-se a: SQL Server, Banco de Dados SQL do Azure e Instância Gerenciada SQL do Azure.

Arquitetura de índice de hash

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 definiçã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.

Sugestão

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. Muitas colisões de hash podem ter impacto no desempenho das 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 dos casos, o número de compartimentos seria, idealmente, entre 1 e 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 pode ter, ou terá. 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.

Observação

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 hash é o tipo de índice apropriado, e não para calcular o número de compartimentos.

Considerações sobre desempenho

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.

Sugestão

O predicado deve incluir todas as colunas na chave do índice de hash. O índice de hash requer uma chave (para hash) para buscar no índice.

Se uma chave de índice consistir em duas colunas e a WHERE cláusula fornecer apenas a primeira coluna, o Mecanismo de Banco de Dados não terá uma chave completa para hash. Isto resulta num plano de consulta de varredura de índice.

Se um índice de hash for usado e o número de chaves de índice exclusivas for 100 vezes (ou mais) superior à contagem de linhas, considere aumentar a contagem de baldes para evitar grandes cadeias de linhas ou utilizar um índice não agrupado.

Considerações sobre a declaração

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 pode ser declarado como:

• UNIQUE, ou pode ser definido como não exclusivo por padrão.
• NONCLUSTERED, que é o padrão.

O exemplo de sintaxe a seguir cria um índice hash fora da instrução CREATE TABLE.

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

Versões de linha e coleta de lixo

Em uma tabela com otimização de memória, quando uma linha é afetada por um UPDATE, 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

Os índices não clusterizados 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.

Aplica-se a: SQL Server, Banco de Dados SQL do Azure e Instância Gerenciada SQL do Azure.

Arquitetura de índice não clusterizado na memória

Os índices não clusterizados na 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 os índices de hash, várias linhas de dados podem ser vinculadas (versões). 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 representada é o valor mais alto que o filho para o qual ela aponta contém, e cada linha também contém esse ID lógico da página. 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 pesquisas de pontos 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 do SQL Server segue os ponteiros de página corretos, onde cada página não folha tem o valor mais alto de seu filho, em vez do menor valor como em uma árvore B.

Se uma página de nível folha tiver que ser alterada, o Mecanismo de Banco de Dados do SQL Server não modificará a página em si. Em vez disso, o Mecanismo de Banco de Dados do SQL Server 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 registros delta pode, eventualmente, degradar o desempenho da pesquisa, pois pode significar que estamos atravessando longas cadeias ao pesquisar um í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 Estas páginas P1 e P2 ainda não estão acessíveis a quaisquer operações simultâneas. 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 fusão

Quando uma DELETE operação resulta em uma página com menos de 10% do tamanho máximo da página (atualmente 8 KB), ou com uma única linha nela, 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. Caso haja qualificação, a fusão é realizada em três etapas fundamentais.

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 sobre desempenho

O desempenho de um índice não clusterizado é melhor do que os índices de hash não clusterizados ao consultar uma tabela com otimização de 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.

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