Índices em tabelas com otimização de memóriaIndexes on Memory-Optimized Tables

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure SQL nãoAzure Synapse Analytics (SQL DW) nãoData Warehouse Paralelo APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Todas as tabelas com otimização de memória devem ter, pelo menos, um índice, porque são os índices que conectam as linhas.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. Em uma tabela com otimização de memória, cada índice também tem otimização de memória.On a memory-optimized table, every index is also memory-optimized. Existem várias diferenças entre um índice em uma tabela com otimização de memória e um índice tradicional em uma tabela baseada em disco:There are several ways in which an index on a memory-optimized table differs from a traditional index on a disk-base table:

  • As linhas de dados não são armazenadas em páginas e, portanto, não há nenhuma coleção de páginas ou extensões, nenhuma partição ou unidade de alocação que pode ser referenciada para obter todas as páginas de uma tabela.Data rows are not stored on pages, so there is no collection of pages or extents, no partitions or allocation units that can be referenced to get all the pages for a table. Há o conceito de páginas de índice para um dos tipos de índices disponíveis, mas elas são armazenadas de modo diferente dos índices para tabelas baseadas em disco.There is the concept of index pages for one of the available types of indexes, but they are stored differently than indexes for disk-based tables. Eles não acumulam o tipo tradicional de fragmentação em uma página e, portanto, não têm nenhum fator de preenchimento.They do not accrue the traditional type of fragmentation within a page, so they have no fillfactor.
  • As alterações feitas nos índices em tabelas com otimização de memória durante a manipulação de dados nunca são gravadas em disco.Changes made to indexes on memory-optimized tables during data manipulation are never written to disk. Apenas as linhas de dados e as alterações nos dados são gravadas no log de transações.Only the data rows, and changes to the data, are written to the transaction log.
  • Os índices com otimização de memória são recriados quando o banco de dados fica online novamente.Memory-optimized indexes are rebuilt when the database is brought back online.

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.All indexes on memory-optimized tables are created based on the index definitions during database recovery.

O índice deve ser um dos seguintes:The index must be one of the following:

  • Índice de hashHash index
  • Índice não clusterizado com otimização de memória (ou seja, a estrutura interna padrão de uma árvore B)Memory-optimized Nonclustered index (meaning the default internal structure of a B-tree)

Os índices de hash são abordados mais detalhadamente em Índices de hash para tabelas com otimização de memória.Hash indexes are discussed in more detail in Hash Indexes for Memory-Optimized Tables.
Os índices não clusterizados são abordados mais detalhadamente em Índice não clusterizado para tabelas com otimização de memória.Nonclustered indexes are discussed in more detail in Nonclustered Index for Memory-Optimized Tables.
Índicescolumnstore são abordados em outro artigo.Columnstore indexes are discussed in another article.

Sintaxe para índices com otimização de memóriaSyntax for memory-optimized indexes

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.Each CREATE TABLE statement for a memory-optimized table must include an index, either explicitly through an INDEX or implicitly through a PRIMAY KEY or UNIQUE constraint.

Para ser declarada com a DURABILITY = SCHEMA_AND_DATA padrão, a tabela com otimização de memória deve ter uma chave primária.To be declared with the default DURABILITY = SCHEMA_AND_DATA, the memory-optimized table must have a primary key. A cláusula PRIMARY KEY NONCLUSTERED na seguinte instrução CREATE TABLE atende a dois requisitos:The PRIMARY KEY NONCLUSTERED clause in the following CREATE TABLE statement satisfies two requirements:

  • Fornece um índice para atender ao requisito mínimo de um índice na instrução CREATE TABLE.Provides an index to meet the minimum requirement of one index in the CREATE TABLE statement.

  • Fornece a chave primária necessária para a cláusula SCHEMA_AND_DATA.Provides the primary key that is required for the SCHEMA_AND_DATA clause.

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

Observação

SQL Server 2014 (12.x)SQL Server 2014 (12.x) e SQL Server 2016 (13.x)SQL Server 2016 (13.x) têm um limite de 8 índices por tabela com otimização de memória ou tipo de tabela.and SQL Server 2016 (13.x)SQL Server 2016 (13.x) have a limit of 8 indexes per memory-optimized table or table type. A partir do SQL Server 2017 (14.x)SQL Server 2017 (14.x) e no Banco de Dados SQL do AzureAzure SQL Database, não há mais um limite no número de índices específicos para as tabelas com otimização de memória e os tipos de tabela.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and in Banco de Dados SQL do AzureAzure SQL Database, there is no longer a limit on the number of indexes specific to memory-optimized tables and table types.

Exemplo de código de sintaxeCode sample for syntax

Esta subseção contém um bloco de códigos Transact-SQL que demonstra a sintaxe para criar vários índices em uma tabela com otimização de memória.This subsection contains a Transact-SQL code block that demonstrates the syntax to create various indexes on a memory-optimized table. O código demonstra o seguinte:The code demonstrates the following:

  1. Crie uma tabela com otimização de memória.Create a memory-optimized table.

  2. Use as instruções ALTER TABLE para adicionar dois índices.Use ALTER TABLE statements to add two indexes.

  3. Insira algumas linhas de dados.INSERT a few rows of data.

    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 duplicadosDuplicate index key values

Valores duplicados para uma chave de índice podem reduzir o desempenho de tabelas com otimização de memória.Duplicate values for an index key might reduce the performance of memory-optimized tables. Duplicatas para o sistema percorrer as cadeias de entrada para a maioria das operações de leitura e gravação do índice.Duplicates for the system to traverse entry chains for most index read and write operations. Quando uma cadeia de entradas duplicadas excede 100 entradas, a degradação do desempenho pode se tornar mensurável.When a chain of duplicate entries exceeds 100 entries, the performance degradation can become measurable.

Valores de hash duplicadosDuplicate hash values

Esse problema é mais visível no caso de índices de hash.This problem is more visible in the case of hash indexes. Índices de hash sofrem mais devido às considerações a seguir:Hash indexes suffer more due to the following considerations:

  • Menor custo por operação de índices de hash.The lower cost per operation for hash indexes.
  • A interferência de grandes cadeias duplicadas na cadeia de colisão de hash.The interference of large duplicate chains with the hash collision chain.

Para reduzir a duplicação em um índice, tente realizar os seguintes ajustes:To reduce duplication in an index, try the following adjustments:

  • Usar um índice não clusterizado.Use a nonclustered index.
  • Adicionar colunas adicionais ao final da chave de índice para reduzir o número de duplicatas.Add additional columns to the end of the index key, to reduce the number of duplicates.
    • Por exemplo, você pode adicionar colunas que também estão na chave primária.For example, you could add columns that are also in the primary key.

Para obter mais informações sobre colisões de hash, consulte Índices de hash para tabelas com otimização de memória.For more information about hash collisions, see Hash Indexes for Memory-Optimized Tables.

Exemplo de aperfeiçoamentoExample improvement

Aqui está um exemplo de como evitar qualquer ineficiência de desempenho em seu índice.Here is an example of how to avoid any performance inefficiency in your index.

Considere uma tabela Customers que tenha uma chave primária em CustomerId e um índice na coluna CustomerCategoryID.Consider a Customers table that has a primary key on CustomerId, and has an index on column CustomerCategoryID. Normalmente, haverá muitos clientes em uma determinada categoria.Typically there will be many customers in a given category. Portanto, haverá muitos valores duplicados para CustomerCategoryID dentro de uma determinada chave do índice.Thus there will be many duplicate values for CustomerCategoryID inside a given key of the index.

Nesse cenário, a melhor prática é usar um índice não clusterizado em (CustomerCategoryID, CustomerId).In this scenario, the best practice is to use a nonclustered index on (CustomerCategoryID, CustomerId). Esse índice pode ser usado para consultas que usam um predicado que envolve CustomerCategoryID, mas a chave de índice não contém duplicação.This index can be used for queries that use a predicate involving CustomerCategoryID, yet the index key does not contain duplication. Portanto, nenhuma ineficiência na manutenção do índice é causada pelos valores CustomerCategoryID duplicados nem pela coluna extra no índice.Therefore, no inefficiencies in index maintenance are cause by either the duplicate CustomerCategoryID values, or by the extra column in the index.

A consulta a seguir mostra o número médio de valores de chave de índice duplicado para o índice em CustomerCategoryID na tabela Sales.Customers, no banco de dados de exemplo WideWorldImporters.The following query shows the average number of duplicate index key values for the index on CustomerCategoryID in table Sales.Customers, in the sample database 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 duplicatas de chave de índice para sua própria tabela e índice, substitua Sales.Customers pelo nome da tabela e substitua CustomerCategoryID pela lista de colunas de chave de índice.To evaluate the average number of index key duplicates for your own table and index, replace Sales.Customers with your table name, and replace CustomerCategoryID with the list of index key columns.

Comparando quando usar cada tipo de índiceComparing when to use each index type

A natureza de cada consulta específica determina que tipo de índice é a melhor opção.The nature of your particular queries determines which type of index is the best choice.

Ao implementar tabelas com otimização de memória em um aplicativo existente, a recomendação geral é iniciar com índices não clusterizados, já que as funcionalidades se assemelham mais às funcionalidades de índices clusterizados e não clusterizados tradicionais em tabelas baseadas em disco.When implementing memory-optimized tables in an existing application, the general recommendation is to start with nonclustered indexes, as their capabilities more closely resemble the capabilities of traditional clustered and nonclustered indexes on disk-based tables.

Recomendações para o uso do índice não clusterizadoRecommendations for nonclustered index use

Um índice não clusterizado é preferível em vez de um índice de hash quando:A nonclustered index is preferable over a hash index when:

  • As consultas têm uma cláusula ORDER BY na coluna indexada.Queries have an ORDER BY clause on the indexed column.
  • Consultas nas quais apenas as colunas à esquerda de um índice de várias colunas são testadas.Queries where only the leading column(s) of a multi-column index is tested.
  • As consultas testam a coluna indexada usando uma cláusula WHERE com:Queries test the indexed column by use of a WHERE clause with:
    • Uma desigualdade: WHERE StatusCode != 'Done'An inequality: WHERE StatusCode != 'Done'
    • Uma verificação do intervalo de valores: WHERE Quantity >= 100A value range scan: WHERE Quantity >= 100

Em todos os SELECTs a seguir, um índice não clusterizado é preferível em vez de um índice de hash:In all the following SELECTs, a nonclustered index is preferable over a hash index:

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 hashRecommendations for hash index use

Índices de hash são usados principalmente para pesquisas de ponto e não para verificações de intervalo.Hash indexes are primarily used for point lookups and not for range scans.

Um índice de hash é preferível a um índice não clusterizado quando as consultas usam predicados de igualdade e a cláusula WHERE é mapeada para todas as colunas de chave de índice, como no seguinte exemplo:A hash index is preferable over a nonclustered index when queries use equality predicates, and the WHERE clause maps to all index key columns, as in the following example:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

Índice de várias colunasMulti-column index

O índice de várias colunas pode ser um índice não clusterizado ou um índice de hash.A multi-column index could be a nonclustered index or a hash index. Suponha que as colunas de índice sejam col1 e col2.Suppose the index columns are col1 and col2. Considerando a seguinte instrução SELECT, apenas o índice não clusterizado será útil para o otimizador de consulta:Given the following SELECT statement, only the nonclustered index would be useful to the query optimizer:

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.The hash index needs the WHERE clause to specify an equality test for each of the columns in its key. Caso contrário, o índice de hash não será útil para o otimizador de consulta.Else the hash index is not useful to the query optimizer.

Nenhum tipo de índice é útil se a cláusula WHERE especifica somente a segunda coluna na chave de índice.Neither index type is useful if the WHERE clause specifies only the second column in the index key.

Tabela de resumo de comparação dos cenários de uso de índicesSummary table to compare index use scenarios

A tabela a seguir lista todas as operações com suporte dos diferentes tipos de índice.The following table lists all operations that are supported by the different index types. Sim significa que o índice pode atender à solicitação com eficiência e Não significa que o índice não pode atender à solicitação com eficiência.Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.

OperaçãoOperation Com otimização de memória,Memory-optimized,
hashhash
Com otimização de memória,Memory-optimized,
não clusterizadononclustered
Com base em disco,Disk-based,
(não) clusterizado(non)clustered
Verificação de índice, recuperar todas as linhas da tabela.Index Scan, retrieve all table rows. SimYes SimYes SimYes
Busca de índice em predicados de igualdade (=).Index seek on equality predicates (=). SimYes
(A chave completa é necessária.)(Full key is required.)
SimYes SimYes
Busca de índice em predicados de desigualdade e intervaloIndex seek on inequality and range predicates
(>, <, <=, >=, BETWEEN).(>, <, <=, >=, BETWEEN).
NãoNo
(Resulta em uma verificação de índice.)(Results in an index scan.)
Sim 1Yes 1 SimYes
Recuperar linhas em uma ordem de classificação que corresponda à definição do índice.Retrieve rows in a sort order that matches the index definition. NãoNo SimYes SimYes
Recuperar linhas em uma ordem de classificação que corresponda ao inverso da definição do índice.Retrieve rows in a sort-order that matches the reverse of the index definition. NãoNo NãoNo SimYes
       

1 Para um índice não clusterizado com otimização de memória, a chave completa não é necessária para a execução de uma busca de índice.1 For a memory-optimized Nonclustered index, the full key is not required to perform an index seek.

Índice automático e gerenciamento de estatísticasAutomatic index and statistics management

Aproveite soluções como a Desfragmentação de índice adaptável para gerenciar automaticamente a desfragmentação de índice e as atualizações de estatísticas em um ou mais bancos de dados.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Este procedimento escolhe automaticamente se deve recompilar ou reorganizar um índice de acordo com seu nível de fragmentação, entre outros parâmetros, e atualizar as estatísticas com um limite linear.This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

Consulte tambémSee Also

Guia de criação de índice do SQL Server SQL Server Index Design Guide
Índices de hash para tabelas com otimização de memória Hash Indexes for Memory-Optimized Tables
Índices não clusterizados para tabelas com otimização de memória Nonclustered Indexes for Memory-Optimized Tables
Desfragmentação de índice adaptávelAdaptive Index Defrag