Guia de arquitetura e design de índice

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

A criação de índices eficientes é fundamental para obter um bom desempenho de banco de dados e aplicativo. A falta de índices, super indexação ou índices mal projetados são as principais fontes de problemas de desempenho do banco de dados.

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

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

Este guia aborda os seguintes tipos de índices:

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

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

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

Noções básicas de índice

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

Um índice rowstore é semelhante: é uma lista ordenada de valores e, para cada valor, há ponteiros para as páginas de dados em que esses valores estão localizados. O índice em si também é armazenado em páginas, conhecidas como páginas de índice. Em um livro regular, se o índice abrange várias páginas e você precisa encontrar ponteiros para todas as páginas que contêm a palavra SQL , por exemplo, você teria que folhear desde o início do índice até localizar a página de índice que contém a palavra-chave SQL. Nele, você seguirá os ponteiros para todas as páginas do livro. Isso poderá ser otimizado ainda mais se, no início do índice, você criar uma única página contendo uma lista alfabética do local em que cada letra pode ser encontrada. Por exemplo: "A a D - página 121", "E a G - página 122" e assim por diante. Essa página extra eliminaria a etapa de folhear no índice para encontrar o local inicial. Essa página não existe em livros comuns, mas existe em um índice de rowstore. Essa página única é chamada de 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 chamadas de "páginas de folha" da árvore.

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

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

O design dos índices corretos para um banco de dados e sua carga de trabalho é um ato de balanceamento complexo entre velocidade de consulta, custo de atualização de índice e custo de armazenamento. Índices de armazenamento em linha estreitos baseados em disco, ou índices com poucas colunas na chave de índice, exigem menos espaço de armazenamento e apresentam um menor overhead de atualização. Índices largos, por outro lado, podem melhorar mais consultas. Talvez seja necessário experimentar vários designs diferentes antes de encontrar o conjunto de índices mais eficiente. À medida que o aplicativo evolui, os índices podem precisar ser alterados para manter o desempenho ideal. Os índices podem ser adicionados, modificados e removidos sem afetar o esquema de banco de dados ou o design do aplicativo. Portanto, você não deve hesitar em fazer experiências com diferentes índices.

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

Não equipare sempre o uso de índice com bom desempenho e bom desempenho com uso de índice eficiente. Se o uso de um índice sempre ajudasse a produzir o melhor desempenho, a tarefa do otimizador de consulta seria simples. Na realidade, a escolha incorreta de um índice pode causar um desempenho insatisfatório. Portanto, a tarefa do otimizador de consulta é selecionar um índice, ou uma combinação de índices, apenas quando isso melhorar o desempenho e evitar a recuperação indexada quando isso prejudicar o desempenho.

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

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

Tarefas de criação de índice

As seguintes tarefas compõem a estratégia recomendada para criação de índices:

1. Entenda as características do banco de dados e do aplicativo.

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

   Por outro lado, para um banco de dados de OLAP (Processamento Analítico Online) ou de data warehousing que deve processar rapidamente conjuntos de dados muito grandes, o uso de índices columnstore clusterizados seria especialmente apropriado. Para obter mais informações, consulte índices Columnstore: visão geral ou arquitetura de índice Columnstore neste guia.

2. Entenda as características das consultas mais usadas.

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

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

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

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

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

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

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

Diretrizes gerais de design de índices

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

Considerações sobre banco de dados

Quando você projeta um índice, considere as seguintes diretrizes para banco de dados:

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

  • Evite tabelas fortemente atualizadas em cima desindexações e mantenha os índices estreitos, ou seja, com o mínimo de colunas possível.

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

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

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

• Os bancos de dados em réplicas primárias no Banco de Dados SQL do Azure geram automaticamente as recomendações de desempenho do assistente de banco de dados para índices. Opcionalmente, você pode habilitar o ajuste automático de í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 consulta que permitem ver os índices selecionados pelo otimizador. Você pode usar esses dados para tornar as alterações de ajuste de índice mais impactantes, concentrando-se nas consultas mais frequentes e que consomem recursos.

Considerações de consulta

Quando você projeta um índice, considere as seguintes diretrizes para consultas:

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

  O termo SARGable em bancos de dados relacionais refere-se a umpredicado S earch ARGumentable que pode usar um índice para acelerar a execução da consulta. Para obter mais informações, consulte o guia de arquitetura e design do índice SQL do Azure e do SQL do Azure.

  Tip

  Sempre verifique se os índices criados são realmente usados pela carga de trabalho de consulta. Descartar índices não utilizados.

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

• Cobrindo índices pode melhorar desempenho de consulta porque todos os dados precisaram satisfazer os requisitos da consulta existe dentro do próprio índice. Ou seja, apenas as páginas de índice, e não as páginas de dados da tabela ou do índice clusterizado, são necessárias para recuperar os dados solicitados, portanto reduzindo as operações de E/S gerais do disco. Por exemplo, uma consulta de colunas A e B em uma tabela que tem um índice de composição criado em colunas A, B e C pode recuperar os dados especificados somente do índice.

  Note

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

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

  Há potencialmente muito menos operações de E/S para executar a consulta, se o índice for estreito o suficiente em comparação com as linhas e colunas da própria tabela, o que implica que é um pequeno subconjunto de todas as colunas.

  Considere utilizar índices ao recuperar uma pequena porção de uma tabela extensa, onde essa porção é definida por um predicado fixo.

  Evite criar um índice de cobertura com muitas colunas porque isso diminui seu benefício ao inflar o armazenamento de banco de dados, a E/S e o volume de memória.

• Escreva consultas que insiram ou modifiquem o máximo de filas possível em uma única instrução, em vez de usar consultas múltiplas para atualizar essas mesmas filas. Isso reduz a sobrecarga de atualização de índice.

Considerações de coluna

Quando você projeta um índice, considere as seguintes diretrizes para as colunas:

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

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

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

• Examine a distribuição de dados na coluna. Criar um índice em uma coluna com muitas linhas, mas poucos valores distintos pode não melhorar o desempenho da consulta mesmo se o índice for usado pelo otimizador de consulta. Como analogia, um diretório telefônico físico classificado em ordem alfabética no nome da família não agiliza a localização de uma pessoa se todas as pessoas na cidade se chamarem Smith ou Jones. Para obter mais informações sobre distribuição de dados, consulte Statistics.

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

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

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

• Considere indexar colunas computadas se elas forem incluídas em predicados de consulta. Para obter mais informações, consulte Índices de colunas computadas.

Características de í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 de índice incluem:

• Clusterizado ou não clusterizado
• Exclusivo ou não exclusivo
• Coluna única ou multicolumn
• Ordem crescente ou decrescente para as colunas de chave no índice
• Todas as linhas ou filtradas para índices não clusterizados
• Armazenamento em coluna ou armazenamento em linha
• Hash ou não clusterizado para tabelas com otimização de memória

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

À medida que desenvolve sua estratégia de design de índices, considere a colocação dos índices nos grupos de arquivos associados ao banco de dados.

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

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

• Particione índices cluster e não cluster para que ocupem vários grupos de arquivos.

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

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

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

Índices particionados

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

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

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

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

Para saber mais, confira Tabelas particionadas e índices.

Diretrizes de design da ordem de classificação do índice

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

A especificação da ordem de armazenamento dos valores de chave em um índice é útil quando as consultas que fazem referência à tabela contêm cláusulas ORDER BY que especificam direcionamentos diferentes para a coluna de chave ou as colunas daquele índice. Nesses casos, o índice pode remover a necessidade de um Sortoperator no plano de consulta.

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

Como demonstrado pela consulta a seguir no banco de dados de exemplo AdventureWorks, recuperar os dados para atender esses critérios requer que a coluna RejectedQty na tabela Purchasing.PurchaseOrderDetail seja classificada em ordem decrescente (do maior para o menor) e que a coluna ProductID seja classificada em ordem crescente (do menor para o maior).

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

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

Se um índice rowstore baseado em disco for criado com colunas de chave que correspondam àquelas na cláusula ORDER BY da consulta, o operador Sort será eliminado no plano de consulta, tornando-o mais eficiente.

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

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

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

A ordem de classificação só pode ser especificada para colunas de chave no índice. A exibição de catálogo sys.index_columns informa se uma coluna de índice é armazenada em ordem crescente ou decrescente.

orientações de design de índice clusterizado

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

A tabela base pode se referir a um índice clusterizado ou a um heap. Um heap é uma estrutura de dados não classificada no disco que contém todas as linhas e todas as colunas de uma tabela.

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

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

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

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

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

Arquitetura de índice clusterizado

Os índices rowstore são organizados como árvores B+. Cada página em uma árvore B+ de índice é chamada de nó do índice. O nó superior da árvore B+ é chamado de nó raiz. Os nós inferiores no índice são chamados de nós folha. Quaisquer níveis de índice entre os nós raiz e folha 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 intermediário e raiz contêm páginas de índice com linhas de índice. Cada linha de índice contém um valor de chave e um ponteiro para uma página de nível de intermediário na árvore B+ ou uma linha de dados no nível folha do índice. As páginas em cada nível do índice são vinculadas em uma lista duplamente vinculada.

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

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

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

Esta ilustração mostra a estrutura de um índice clusterizado em um único particionamento.

Diretrizes de design de índices não clusterizados

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

Um índice não clusterizado de rowstore baseado em disco contém os localizadores de linha que apontam para o local de armazenamento da linha na tabela base. Você pode criar vários índices não clusterizados em uma tabela ou exibição indexada. Em geral, índices não clusterizados devem ser projetados para melhorar o desempenho de consultas usadas com frequência que precisariam verificar a tabela base caso contrário.

Semelhante à maneira como o índice de um livro é usado, 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, depois, recupera os dados diretamente daquele local. Isso faz com que os índices não clusterizados sejam a opção ideal para consultas de correspondência exata, uma vez que o índice contém entradas que descrevem o local preciso na tabela dos valores de dados pesquisados pelas consultas.

Por exemplo, para consultar a HumanResources.Employee tabela de 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; isso tem ManagerID como sua primeira coluna de chave. Como os ManagerID valores são ordenados no índice não clusterizado, o otimizador de consulta pode localizar rapidamente todas as entradas no índice que correspondem ao valor especificado ManagerID . Cada entrada de índice aponta para a página exata e a linha na tabela base em que os dados correspondentes de todas as outras colunas podem ser recuperados. Depois que o otimizador de consulta encontrar todas as entradas no índice, ele poderá ir diretamente para a página e a linha exatas para recuperar os dados em vez de verificar toda a tabela base.

Arquitetura de índice não clusterizado

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

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

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

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

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

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

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

O Mecanismo de Banco de Dados nunca armazena uma determinada coluna mais de uma vez em um índice não clusterizado. A ordem de 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 de índice. Os localizadores de linha de chave de índice clusterizado em um índice não clusterizado podem ser usados no processamento de consulta, independentemente de serem explicitamente especificados na definição de índice ou adicionados implicitamente.

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

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

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

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

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

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

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

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

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

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

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

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

Diretrizes para índices com colunas incluídas

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

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

• São permitidos todos os tipos de dados, exceto text, ntexte image.

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

• Assim como acontece com as colunas de chave, colunas computadas derivadas dos tipos de dados image, ntext e text podem ser incluídas como colunas, desde que o tipo de dado da coluna computada seja permitido em uma coluna incluída.

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

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

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

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

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

Considere redesenhar índices não clusterizados com um tamanho grande para a chave de índice, para que apenas as colunas usadas em predicados de consulta, agregações e classificações sejam colunas-chave. Faça todas as outras colunas que abrangem a consulta colunas não chave incluídas. Dessa forma, você tem todas as colunas necessárias para cobrir a consulta, mas a chave do índice propriamente dita é pequena e eficiente.

Por exemplo, suponha que você quer projetar um índice para abranger a consulta seguinte.

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

Para abranger a consulta, cada coluna deve ser definida no índice. Embora você possa definir todas as colunas como colunas de chave, o tamanho chave seria de 334 bytes. Em razão da única coluna usada como critério de pesquisa ser a coluna PostalCode , que tem um comprimento de 30 bytes, um melhor design de índice definiria PostalCode como sendo a coluna de chave e incluiria todas as outras colunas como colunas que não são colunas de chave.

A seguinte declaração cria um índice com colunas incluídas para abranger a consulta.

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

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

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

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

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

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

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

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

Diretrizes de design de índice exclusivo

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

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

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

Os benefícios dos índices exclusivos incluem:

• As regras de negócios que exigem exclusividade de dados são impostas.
• São fornecidas informações úteis adicionais ao otimizador de consultas.

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

Considerações de índice exclusivas

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

• Se os dados forem exclusivos e você quiser impor exclusividade, criar um índice exclusivo em vez de um índice não exclusivo, na mesma combinação de colunas, fornecerá informações adicionais para otimizador de consultas que poderá produzir planos de execução mais eficientes. Nesse caso, é recomendável criar uma UNIQUE restrição ou um índice exclusivo.

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

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

Diretrizes de design de índice filtrado

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

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

• Melhor desempenho de consultas e qualidade de plano

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

• Redução dos custos de atualização de índice

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

• Redução dos custos de armazenamento do í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.

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

• Colunas que contêm muitos NULLs.

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

• Colunas que contêm intervalos de valores, como quantias, tempo e datas.

Os custos de atualização reduzidos para índices filtrados são mais perceptí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 da tabela, sua manutenção poderá ser mais cara do que a do índice de tabela completa. Nesse caso, você deve usar um índice de tabela completa em vez do índice filtrado.

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

Considerações de design de índice filtrado

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

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

Índices filtrados de subconjuntos de dados

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

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

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

O índice filtrado FIBillOfMaterialsWithEndDate é válido para a consulta a seguir. Exiba o plano de execução estimado para determinar se o otimizador de consulta usou o índice filtrado.

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

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

Índices filtrados para dados heterogêneos

Quando a tabela contém linhas de dados heterogêneos, é possível criar um índice filtrado para uma ou mais categorias de dados.

Por exemplo, a cada produto listado na tabela Production.Product é atribuído um ProductSubcategoryIDque, por sua vez, está associado às categorias de produtos Bikes, Components, Clothing ou Accessories. Essas categorias são heterogêneas porque os valores de suas colunas na tabela Production.Product não estão intimamente correlacionados. Por exemplo, as colunas Color, ReorderPoint, ListPrice, Weight, Classe Style têm características exclusivas para cada categoria de produto. Suponha que haja consultas frequentes para acessórios, que têm subcategorias entre 27 e 36, inclusive. É possível aprimorar o desempenho das consultas sobre acessórios criando um índice filtrado nas subcategorias de acessórios, conforme ilustrado no exemplo a seguir.

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

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

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

Chave principal e colunas incluídas em índices filtrados

É uma prática recomendada adicionar um pequeno número de colunas em uma definição de índice filtrada, somente conforme necessário para o otimizador de consulta escolher 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 ele cobrir ou não a consulta. No entanto, é mais provável que o otimizador de consulta escolha um índice filtrado se ele abranger a consulta.

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

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

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

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

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

A coluna na expressão do índice filtrado deverá ser uma coluna de chave ou incluída na definição do índice filtrado se fizer parte do conjunto de resultados da consulta. Por exemplo, FIBillOfMaterialsWithEndDate não abrange a consulta a seguir porque ela retorna a coluna EndDate nos resultados da consulta. Portanto, EndDate deve ser uma coluna de chave ou incluída na definição do índice filtrado.

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

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

Operadores de conversão de dados no predicado do filtro

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

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

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

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

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

A solução é converter a constante à direita para o mesmo tipo da coluna b, como mostra o seguinte exemplo:

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

A movimentação da conversão de dados da esquerda para a direita de um operador de comparação pode alterar o significado da conversão. No exemplo anterior, quando o operador CONVERT foi adicionado à direita, a comparação foi alterada de uma comparação int para uma comparação varbinary.

Arquitetura de índices columnstore

Um índice columnstore é uma tecnologia para armazenar, recuperar e gerenciar dados usando um formato de dados columnar, chamado columnstore. Para obter mais informações, consulte índices Columnstore: visão geral.

Para obter informações sobre a versão e descobrir o que há de novo, visite Novidades nos Índices columnstore.

Conhecer essas noções básicas torna mais fácil entender outros artigos sobre columnstore que explicam como usar essa tecnologia de forma eficaz.

O armazenamento de dados utiliza columnstore e rowstore

Em discussões sobre índices columnstore, usamos os termos rowstore e columnstore para enfatizar o formato do armazenamento de dados. Os índices columnstore usam os dois tipos de armazenamento.

• Um columnstore são dados logicamente organizados como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados com reconhecimento de 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 sejam solicitados pela consulta. Isso acelera a verificação de uma coluna inteira de uma tabela grande.

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

  Um índice columnstore também armazena fisicamente algumas linhas em um formato rowstore, chamado deltastore. O deltastore, também chamado de delta rowgroups, é um local de retenção para linhas que são muito poucas em número para se qualificarem para a compactação em columnstore. Cada rowgroup delta é implementado como um índice de árvore B+ clusterizado, que é um rowstore.

Operações são executadas em rowgroups e em segmentos de colunas

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

Por exemplo, o índice columnstore executa estas operações em rowgroups:

• Compacta rowgroups no columnstore. A compactação é executada em cada segmento de coluna dentro de um rowgroup.

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

• Recria todos os rowgroups durante a operação ALTER INDEX ... REBUILD.

• Relata a integridade e a fragmentação do rowgroup nas DMVs (exibições de gerenciamento dinâmico).

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

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

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 os rowgroups delta abertos menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla rowgroups compactados dos quais um grande número de linhas foi excluído.

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

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

Cargas e inserções pequenas vão para o deltastore

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

As linhas vão para o deltastore quando:

• São inseridas com a instrução INSERT INTO ... VALUES.

• Estão no final de uma carga em massa e em número menor de 102.400.

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

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

Quando os rowgroups delta estão cheios, eles são compactados no columnstore

Os índices columnstore clusterizados coletam até 1.048.576 linhas em cada rowgroup delta antes de compactar o rowgroup no columnstore. Isso melhora a compactação do índice columnstore. Quando um rowgroup delta alcança o número máximo de linhas, ele faz a transição do estado OPEN para CLOSED. Um processo em segundo plano chamado de motor de tupla verifica os rowgroups fechados. Se o processo encontrar um rowgroup fechado, ele o compactará e o armazenará no columnstore.

Quando um rowgroup delta é compactado, o grupo de rowgroup delta existente faz a transição para o estado TOMBSTONE para ser removido posteriormente pelo motor de tupla quando não há nenhuma referência a ele, e o novo rowgroup compactado é marcado como COMPRESSED.

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

Você pode forçar rowgroups delta para o columnstore usando ALTER INDEX para reccompilar ou reorganizar o índice. Se houver pressão na memória durante a compactação, o índice columnstore poderá reduzir o número de linhas no grupo de linhas compactado.

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

O conceito de particionamento é o mesmo em um índice clusterizado, um heap e um índice columnstore. Particionar uma tabela divide a tabela em grupos menores de linhas de acordo com um intervalo de valores de coluna. Geralmente, ele é usado para gerenciar os dados. Por exemplo, você pode criar uma partição para cada ano de dados e, em seguida, usar a alternância de partição para arquivar dados antigos para armazenamento mais barato.

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

Cada partição pode ter vários rowgroups delta

Cada partição pode ter mais de um rowgroup delta. Quando o índice columnstore precisa adicionar dados a um rowgroup delta e o rowgroup delta é bloqueado por outra transação, o índice columnstore tenta obter um bloqueio em um rowgroup delta diferente. Se não houver nenhum rowgroup delta disponível, o índice columnstore criará um novo rowgroup delta. Por exemplo, uma tabela com 10 partições poderia facilmente ter 20 ou mais rowgroups 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, portanto, você precisa de armazenamento extra. No entanto, os dados no índice columnstore são compactados para tamanhos muito menores do que a tabela rowstore requer. Ao fazer isso, você pode executar análises nas cargas de trabalho de índice columnstore e OLTP no índice rowstore ao mesmo tempo. O repositório de colunas é atualizado quando dados são alterados na tabela rowstore, assim, ambos os índices trabalham com os mesmos dados.

Uma tabela rowstore pode ter um índice columnstore não clusterizado. Para obter mais informações, consulte índices Columnstore – diretrizes de design.

Você pode ter um ou mais índices não clusterizados de rowstore em uma tabela columnstore clusterizada. Fazendo isso, você pode executar buscas de tabela eficientes no columnstore subjacente. Outras opções também são disponibilizadas. Por exemplo, você pode impor a exclusividade usando uma UNIQUE restrição na tabela rowstore. Quando um valor não exclusivo falha ao inserir na tabela rowstore, o Mecanismo de Banco de Dados também não insere o valor no columnstore.

Considerações de desempenho de índice de colunas não-clusterizado

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

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

Para obter mais informações, consulte índices Columnstore – desempenho da consulta.

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

Ao usar In-Memory OLTP, todas as tabelas com otimização de memória devem ter pelo menos um índice. Para uma tabela com otimização de memória, cada índice também é otimizado para memória. Í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 em tabelas com otimização de memória.

Arquitetura de índice de hash com otimização de memória

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

• 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 de uma chave de índice, mais o endereço de sua linha correspondente na tabela com otimização de memória subjacente.
• Cada um dos pontos de entrada para a próxima entrada em uma lista de links de entradas, todos encadeados no bucket atual.

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

• Quanto menor a proporção de buckets para linhas ou valores distintos, maior será a lista média de links de bucket.
• Listas de links curtas executam com mais rapidez do que listas de links longas.
• O número máximo de buckets em índices de hash é de 1.073.741.824.

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

A função de hash 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 de 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 bucket de hash.
• A função de hash é equilibrada, o que significa que a distribuição de valores de chave do índice em buckets de hash geralmente segue uma distribuição de Poisson ou curva de sino, não uma distribuição linear simples.
• A distribuição de Poisson não é uma distribuição uniforme. Os valores de chave do índice não são distribuídos uniformemente nos buckets de hash.
• Se duas chaves de índice forem mapeadas para o mesmo bucket de hash, haverá uma colisão de hash. Um grande número de colisões de hash pode afetar o desempenho em operações de leitura. Uma meta realista é que 30% dos buckets contenham dois valores de chave diferentes.

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

Configurar a contagem de buckets do índice de hash

O número de buckets do índice de hash é especificado no momento da criação do índice e pode ser alterado com a sintaxe ALTER TABLE...ALTER INDEX REBUILD.

Na maioria dos casos, a contagem de buckets deve estar 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 chave de índice específica tem. Normalmente, o desempenho ainda é bom se o valor BUCKET_COUNT estiver dentro de 10 vezes o número real de valores de chave, e superestimar é geralmente melhor do que subestimar.

Um número muito pequeno de buckets tem as seguintes desvantagens:

• Mais colisões de hash de valores de chave distintos.
• Cada valor distinto é forçado a compartilhar o mesmo bucket com um valor distinto diferente.
• O comprimento médio de cadeia por bucket aumenta.
• Quanto maior é a cadeia de bucket, mais lentas são as pesquisas de igualdade no índice.

Um número muito grande de buckets tem as seguintes desvantagens:

• Um número de buckets muito alto pode resultar em mais buckets vazios.
• Buckets vazios afetam o desempenho de verificações de índice completas. Se elas forem executadas regularmente, considere escolher uma contagem de buckets próximo ao número de valores de chave de índice distintos.
• Buckets vazios usam a memória, embora cada bucket use apenas 8 bytes.

Considerações de desempenho para índices de hash

O desempenho de um índice de hash é:

• Excelente quando o predicado na cláusula WHERE especifica um valor exato para cada coluna na chave de índice de hash. Um índice de hash reverterá para um exame de acordo com um predicado de desigualdade.
• Fraco quando o predicado na cláusula WHERE procura um intervalo de valores na chave de índice.
• Ruim quando o predicado na cláusula WHERE 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 as outras colunas da chave.

Se um índice de hash for usado e o número de chaves de índice exclusivas for mais de 100 vezes menor que a contagem de linhas, considere aumentar para uma contagem maior de buckets para evitar cadeias de linhas grandes ou usar um índice não clusterizado .

Criar um índice de hash

Ao criar um índice de hash, considere:

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

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

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

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

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

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

Posteriormente, quando as versões mais antigas não forem mais necessárias, um thread de GC (coleta de lixo) percorrerá os buckets e suas listas de links para limpar as entradas antigas. A execução do thread da GC é melhor se os tamanhos de cadeia de lista de link são curtos. Para obter mais informações, confira Coleta de lixo de OLTP in-memory.

Diretrizes de design de índices não clusterizados com otimização de memória

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

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

Índices não clusterizados em tabelas com otimização de memória são implementados usando uma estrutura de dados chamada árvore Bw, originalmente prevista e descrita pela Microsoft Research em 2011. Uma árvore Bw é uma variação livre de bloqueios e travas de uma árvore B. Para obter mais informações, consulte A árvore Bw: uma árvore B para novas plataformas de hardware.

Em um alto nível, a árvore Bw pode ser compreendida como um mapa de páginas organizadas por ID de página (PidMap), um mecanismo para alocar e reutilizar IDs de página (PidAlloc) e um conjunto de páginas interligadas no mapa de páginas e entre si. Esses três subcomponentes de alto nível formam a estrutura básica interna de uma árvore Bw.

A estrutura é semelhante a uma árvore B normal, pois cada página tem um conjunto de valores de chave ordenados e existem níveis no índice que apontam para um nível inferior e os níveis folha apontam para uma linha de dados. Entretanto, há várias diferenças.

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

Não existem atualizações in-loco de páginas de índice. Novas páginas delta são introduzidas para essa finalidade.

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

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

As pesquisas de ponto são semelhantes às árvores B, exceto que, como as páginas estão vinculadas em apenas uma direção, o Mecanismo de Banco de Dados segue os ponteiros de página à direita, em que cada página não 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 precisar ser alterada, o Mecanismo de Banco de Dados não modificará a própria página. Em vez disso, o Mecanismo de Banco de Dados cria um registro delta que descreve a alteração e a acrescenta à página anterior. Em seguida, ele também atualizará o endereço da tabela de mapa da 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 mesclagem.

Consolidação delta

Uma longa cadeia de registros delta pode eventualmente degradar o desempenho da pesquisa, pois pode exigir um longo percorrimento da cadeia ao pesquisar em um índice. Se um novo registro delta for adicionado a uma cadeia que já tem 16 elementos, as alterações nos registros delta serão consolidadas na página de índice referenciada e, em seguida, a página será recriada, incluindo as alterações indicadas pelo novo registro delta que dispararam a consolidação. A página recém-recriada tem a mesma ID de página, mas um novo endereço de memória.

Página de divisão

Uma página de índice na árvore Bw cresce conforme a necessidade, desde o armazenamento de uma única linha até o armazenamento de um máximo de 8 KB. Depois que a página de índice aumentar para 8 KB, uma nova inserção de uma única linha fará com que a página de índice seja dividida. Para uma página interna, isso significa que 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 fossem incorporados. As informações estatísticas no cabeçalho da página para uma página de folha controlam a quantidade de espaço necessária para consolidar os registros delta. Essas informações são ajustadas à medida que cada novo registro delta é adicionado.

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

Etapa 1: alocar duas novas páginas P1 e P2 e dividir as linhas da página P1 antiga nessas 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. As páginas P1 e P2 ainda não estão acessíveis a nenhuma operação concorrente. Além disso, o ponteiro lógico de P1 para P2 é definido. Em seguida, em uma etapa atômica, atualize a tabela de mapeamento de página para alterar o ponteiro do antigo P1 para o novo P1.

Etapa 2: a página não folha aponta para P1, mas não há ponteiro direto de uma página não folha para P2. P2 só é acessível por meio de 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 interno), copie todas as linhas da página não-folha antiga e adicione uma nova linha para apontar para P2. Uma vez feito isso, em uma etapa atômica, atualize a tabela de mapeamento de página para alterar o ponteiro da página não-folha antiga para a nova página não-folha.

Página mesclar

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

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

Na figura abaixo, suponha que uma operação DELETE exclua o valor da chave 10.

Etapa 1: uma página delta que representa o valor de chave 10 (triângulo azul) é criada e seu ponteiro na página não folha Pp1 é definido para a nova página delta. Além disso, uma página especial delta de mesclagem (triângulo verde) é criada e é vinculada para apontar para a página delta. Nesse estágio, as duas páginas (página delta e página delta de mesclagem) não são visíveis para nenhuma transação simultânea. Em uma etapa atômica, o ponteiro para a página de nível de folha P1 na tabela de mapeamento de página é atualizado para apontar para a página de mesclagem delta. Após essa etapa, a entrada do valor de chave 10 em Pp1 agora apontará para a página delta de mesclagem.

Etapa 2: a linha que representa o valor de chave 7 na página não folha Pp1 precisa ser removida e a entrada do valor-chave 10 atualizada para apontar para P1. Para isso, uma nova página não folha Pp2 é alocada e todas as linhas de Pp1 são copiadas, exceto a linha que representa o valor de chave 7; em seguida, a linha do valor de chave 10 é atualizada para apontar para a página P1. Feito isso, em uma etapa atômica, a entrada da tabela de mapeamento de página que aponta para Pp1 é atualizada para apontar para Pp2. Pp1 não é mais acessível.

Etapa 3: as páginas de nível de folha P2 e P1 são mescladas e as páginas delta são 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 uma etapa atômica, a entrada da tabela de mapeamento de página que aponta para a página P1 é atualizada para apontar para a página P3.

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

O desempenho de um índice não clusterizado é melhor do que com índices de hash 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 do índice.

Metadados de índice

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

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

As exibições anteriores se aplicam a todos os tipos de índice. Para os índices columnstore, use também as seguintes visões:

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

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

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

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

Conteúdo relacionado

• CRIAR ÍNDICE (Transact-SQL)
• Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos
• Tabelas e índices particionados
• Índices em tabelas com otimização de memória
• Visão geral dos índices columnstore
• Índices em colunas computadas
• Ajustar índices não clusterizados com sugestões de índice ausente