Partilhar via

Índices em tabelas Memory-Optimized

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

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. Há várias maneiras pelas quais um índice em uma tabela com otimização de memória difere de um índice tradicional em uma tabela de base de disco:

  • As linhas de dados não são armazenadas em páginas, portanto, não há nenhuma coleção de páginas ou extensões, nem partições ou unidades de alocação que possam ser referenciadas para obter todas as páginas de uma tabela. Existe o conceito de páginas de índice para um dos tipos de índices disponíveis, mas elas são armazenadas de forma diferente dos índices para tabelas baseadas em disco. Eles não acumulam o tipo tradicional de fragmentação dentro de uma página, portanto, não têm fator de preenchimento.
  • As alterações feitas em índices em tabelas com otimização de memória durante a manipulação de dados nunca são gravadas no disco. Somente as linhas de dados e as alterações nos dados são gravadas no log de transações.
  • Os índices otimizados para memória são reconstruídos quando o banco de dados é colocado online novamente.

Todos os índices em tabelas com otimização de memória são criados com base nas definições de índice durante a recuperação do banco de dados.

O índice deve ser um dos seguintes:

  • Índice de hash
  • Índice não clusterizado otimizado para memória (ou seja, a estrutura interna padrão de uma árvore B)

Índices de Hash são discutidos com mais detalhes em Índices de Hash para tabelas Memory-Optimized.
Índices de não agrupados são discutidos com mais detalhes em Índice não clusterizado para tabelas Memory-Optimized.
Columnstore índices são discutidos em outro artigo.

Sintaxe para índices otimizados para memória

Cada instrução CREATE TABLE para uma tabela com otimização de memória deve incluir um índice, seja explicitamente por meio de um INDEX ou implicitamente por meio de uma restrição PRIMARY KEY ou UNIQUE.

Para ser declarada com o padrão DURABILITY = SCHEMA_AND_DATA, a tabela com otimização de memória deve ter uma chave primária. Na seguinte instrução CREATE TABLE, a cláusula PRIMARY KEY NONCLUSTERED satisfaz dois requisitos.

  • Fornece um índice para cumprir o requisito mínimo de ter um índice na instrução CREATE TABLE.

  • Fornece a chave primária necessária para a cláusula SCHEMA_AND_DATA.

    CREATE TABLE SupportEvent  
(  
    SupportEventId   int NOT NULL  
        PRIMARY KEY NONCLUSTERED,  
    ...  
)  
    WITH (  
        MEMORY_OPTIMIZED = ON,  
        DURABILITY = SCHEMA_AND_DATA);

Observação

O SQL Server 2014 (12.x) e o SQL Server 2016 (13.x) têm um limite de 8 índices por tabela ou tipo de tabela com otimização de memória. A partir do SQL Server 2017 (14.x) e do Banco de Dados SQL do Azure, não há mais um limite no número de índices específicos para tabelas e tipos de tabela com otimização de memória.

Exemplo de código para sintaxe

Esta subseção contém um bloco de código Transact-SQL que demonstra a sintaxe para criar vários índices em uma tabela com otimização de memória. O código demonstra o seguinte:

  1. Crie uma tabela com otimização de memória.

  2. Utilize instruções ALTER TABLE para adicionar dois índices.

  3. INSIRA algumas linhas de dados.

    DROP TABLE IF EXISTS SupportEvent;  
go  

CREATE TABLE SupportEvent  
(  
    SupportEventId   int               not null   identity(1,1)  
    PRIMARY KEY NONCLUSTERED,  

    StartDateTime        datetime2     not null,  
    CustomerName         nvarchar(16)  not null,  
    SupportEngineerName  nvarchar(16)      null,  
    Priority             int               null,  
    Description          nvarchar(64)      null  
)  
    WITH (  
    MEMORY_OPTIMIZED = ON,  
    DURABILITY = SCHEMA_AND_DATA);  
go  

    --------------------  

ALTER TABLE SupportEvent  
    ADD CONSTRAINT constraintUnique_SDT_CN  
    UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);  
go  

ALTER TABLE SupportEvent  
    ADD INDEX idx_hash_SupportEngineerName  
    HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.  
go  

    --------------------  

INSERT INTO SupportEvent  
    (StartDateTime, CustomerName, SupportEngineerName, Priority, Description)  
    VALUES  
    ('2016-02-23 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'     ),  
    ('2016-02-24 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'    ),  
    ('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'      ),  
    ('2016-02-26 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');  
go

Valores de chave de índice duplicados

Valores duplicados para uma chave de índice podem reduzir o desempenho de tabelas com otimização de memória. Duplicados para o sistema percorrer cadeias de entradas para a maioria das operações de leitura e escrita em índices. Quando uma cadeia de entradas duplicadas excede 100 entradas, a degradação do desempenho pode tornar-se mensurável.

Valores de hash duplicados

Esse problema é mais visível no caso de índices hash. Os índices de hash sofrem mais devido às seguintes considerações:

  • O menor custo por operação para índices de hash.
  • A interferência causada por grandes cadeias duplicadas na cadeia de colisão de hash.

Para reduzir a duplicação em um índice, tente os seguintes ajustes:

  • Use um índice não clusterizado.
  • Adicione colunas adicionais ao final da chave de índice, para reduzir o número de duplicatas.
    • Por exemplo, você pode adicionar colunas que também estão na chave primária.

Para mais informações sobre colisões de hash, consulte Índices de Hash para Tabelas Memory-Optimized.

Exemplo de melhoria

Aqui está um exemplo de como evitar qualquer ineficiência de desempenho no seu índice.

Considere uma tabela Customers que tenha uma chave primária no CustomerIde tenha um índice na coluna CustomerCategoryID. Normalmente, haverá muitos clientes em uma determinada categoria. Assim, haverá muitos valores duplicados para CustomerCategoryID dentro de uma determinada chave do índice.

Nesse cenário, a prática recomendada é usar um índice não clusterizado em (CustomerCategoryID, CustomerId). Esse índice pode ser usado para consultas que usam um predicado envolvendo CustomerCategoryID, mas a chave de índice não contém duplicação. Portanto, nenhuma ineficiência na manutenção do índice é causada pelos valores duplicados de CustomerCategoryID ou pela coluna extra no índice.

A consulta a seguir mostra o número médio de valores de chave de índice duplicados para o índice em CustomerCategoryID na tabela Sales.Customers, no banco de dados de exemplo WideWorldImporters.

SELECT AVG(row_count) FROM
    (SELECT COUNT(*) AS row_count 
	    FROM Sales.Customers
	    GROUP BY CustomerCategoryID) a

Para avaliar o número médio de duplicados de chave de índice para sua própria tabela e índice, substituaSales.Customers pelo nome da tabela e substituaCustomerCategoryID pela lista de colunas de chave de índice.

Comparando quando usar cada tipo de índice

A natureza das suas consultas específicas determina qual tipo de índice é a melhor escolha.

Ao implementar tabelas com otimização de memória em um aplicativo existente, a recomendação geral é começar com índices não clusterizados, pois seus recursos se assemelham mais aos recursos de índices clusterizados e não clusterizados tradicionais em tabelas baseadas em disco.

Recomendações para o uso de índices não agrupados

Um índice não clusterizado é preferível a um índice de hash quando:

  • As consultas têm uma cláusula ORDER BY na coluna indexada.
  • Consultas em que apenas a(s) primeira(s) coluna(s) de um índice multi-colunas é testada.
  • As consultas testam a coluna indexada usando uma cláusula WHERE com:
    • Uma desigualdade: WHERE StatusCode != 'Done'
    • Uma verificação de intervalo de valores: WHERE Quantity >= 100

Em todos os SELECTs a seguir, um índice não clusterizado é preferível a um índice de hash:

SELECT CustomerName, Priority, Description 
FROM SupportEvent  
WHERE StartDateTime > DateAdd(day, -7, GetUtcDate());  

SELECT StartDateTime, CustomerName  
FROM SupportEvent  
ORDER BY StartDateTime DESC; -- ASC would cause a scan.

SELECT CustomerName  
FROM SupportEvent  
WHERE StartDateTime = '2016-02-26';

Recomendações para o uso do índice de hash

Os índices de hash são usados principalmente para pesquisas pontuais e não para verificações de intervalo.

Um índice de hash é preferível a um índice não clusterizado quando as consultas usam predicados de igualdade, e a cláusula WHERE mapeia para todas as colunas de chave de índice, como no exemplo a seguir:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

Índice de várias colunas

Um índice de várias colunas pode ser um índice não clusterizado ou um índice de hash. Suponha que as colunas de índice sejam col1 e col2. Dada a seguinte instrução SELECT, somente o índice não clusterizado seria útil para o otimizador de consulta:

SELECT col1, col3  
FROM MyTable_memop  
WHERE col1 = 'dn';

O índice de hash precisa da cláusula WHERE para especificar um teste de igualdade para cada uma das colunas em sua chave. Caso contrário, o índice de hash não é útil para o otimizador de consulta.

Nenhum tipo de índice será útil se a cláusula WHERE especificar apenas a segunda coluna na chave de índice.

Tabela de resumo para comparar cenários de uso de índice

A tabela a seguir lista todas as operações suportadas pelos diferentes tipos de índice. Sim significa que o índice pode atender eficientemente a solicitação, e Não significa que o índice não pode satisfazer eficientemente a solicitação.

Funcionamento Memória otimizada,
hash		 Memória otimizada,
não agrupados		 Baseado em disco
(não)agrupados
Verificação de índice, recupere todas as linhas da tabela. Sim Sim Sim
Busca de índice para predicados de igualdade (=). Sim
(A chave completa é necessária.)		 Sim Sim
Busca de índice em predicados de desigualdade e intervalo
(>, <, <=, >=, BETWEEN).		 Não
(Resultados em uma verificação de índice.)		 Sim 1 Sim
Recupere linhas em uma ordem de classificação que corresponda à definição de índice. Não Sim Sim
Recupere linhas em uma ordem de classificação que corresponda ao inverso da definição de índice. Não Não Sim

1 Para um índice não clusterizado otimizado para memória, a chave completa não é necessária para executar uma busca de índice.

Gestão automática de índices e estatísticas

Aproveite soluções como Adaptive Index Defrag para gerenciar automaticamente a desfragmentação de índice e atualizações de estatísticas para um ou mais bancos de dados. Este procedimento escolhe automaticamente se pretende reconstruir ou reorganizar um índice de acordo com o seu nível de fragmentação, entre outros parâmetros, e atualizar as estatísticas com um limiar linear.

Ver também

Guia de Design de Índice do SQL Server
índices de hash para tabelas Memory-Optimized
índices não agrupados para tabelas Memory-Optimized
Adaptive Index Defrag