Índices de las tablas con optimización para memoriaIndexes on Memory-Optimized Tables

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Todas las tablas optimizadas para memoria deben tener como mínimo un índice porque son los índices los que conectan las filas.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. En una tabla optimizada para memoria, todos los índices también son optimizados para memoria.On a memory-optimized table, every index is also memory-optimized. Hay varias formas de diferenciar un índice en una tabla optimizada para memoria de un índice tradicional en una tabla basada en disco:There are several ways in which an index on a memory-optimized table differs from a traditional index on a disk-base table:

  • Las filas de datos no se almacenan en páginas, por lo que no existe ninguna colección de páginas o extensiones, ni unidades de asignación o particiones a las que se pueda hacer referencia para obtener todas las páginas de una tabla.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. Existe el concepto de páginas de índice para uno de los tipos de índices disponibles, pero se almacenan de una manera distinta a los índices para las tablas basadas en 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. No acumulan el tipo tradicional de fragmentación dentro de una página, por lo que no tienen ningún factor de relleno.They do not accrue the traditional type of fragmentation within a page, so they have no fillfactor.
  • Los cambios que se realizan en los índices de las tablas optimizadas para memoria durante la manipulación de los datos nunca se escriben en el disco.Changes made to indexes on memory-optimized tables during data manipulation are never written to disk. Solo las filas de datos, y los cambios en los datos, se escriben en el registro de transacciones.Only the data rows, and changes to the data, are written to the transaction log.
  • Los índices con optimización para memoria se vuelven a generar cuando la base de datos vuelve a estar en línea.Memory-optimized indexes are rebuilt when the database is brought back online.

Todos los índices en las tablas optimizadas para memoria se crean en función de las definiciones de índice durante la recuperación de la base de datos.All indexes on memory-optimized tables are created based on the index definitions during database recovery.

El índice debe ser uno de los siguientes:The index must be one of the following:

  • Índice de hashHash index
  • Índice no agrupado optimizado para memoria (es decir, la estructura interna predeterminada de un árbol B)Memory-optimized Nonclustered index (meaning the default internal structure of a B-tree)

Los índices de hash se analizan con más detalle en Índices de hash de tablas optimizadas para memoria.Hash indexes are discussed in more detail in Hash Indexes for Memory-Optimized Tables.
Los índices no agrupados se analizan con más detalle en Índice no agrupado de tablas optimizadas para memoria.Nonclustered indexes are discussed in more detail in Nonclustered Index for Memory-Optimized Tables.
Los índices dealmacén de columnas se tratan en otro artículo.Columnstore indexes are discussed in another article.

Sintaxis de índices optimizados para memoriaSyntax for memory-optimized indexes

Cada instrucción CREATE TABLE para una tabla optimizada para memoria debe incluir e indexar, ya sea explícitamente a través de un elemento INDEX, o implícitamente a través de un elemento PRIMARY KEY o una restricción 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 declararse con el valor predeterminado de DURABILITY = SCHEMA_AND_DATA, la tabla optimizada para memoria debe tener una clave principal.To be declared with the default DURABILITY = SCHEMA_AND_DATA, the memory-optimized table must have a primary key. La cláusula PRIMARY KEY NONCLUSTERED de la siguiente instrucción CREATE TABLE cumple dos requisitos:The PRIMARY KEY NONCLUSTERED clause in the following CREATE TABLE statement satisfies two requirements:

  • Proporciona un índice para satisfacer el requisito mínimo de tener un índice en la instrucción CREATE TABLE.Provides an index to meet the minimum requirement of one index in the CREATE TABLE statement.

  • Proporciona la clave principal necesaria para la 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);  
    

Nota

SQL Server 2014 (12.x)SQL Server 2014 (12.x) y SQL Server 2016 (13.x)SQL Server 2016 (13.x) tienen un límite de 8 índices por tabla optimizada para memoria o tipo de tabla.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 de SQL Server 2017 (14.x)SQL Server 2017 (14.x) y en Base de datos SQL de AzureAzure SQL Database, ya no hay un límite para el número de índices específicos para tablas optimizadas para memoria y tipos de tabla.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and in Base de datos SQL de AzureAzure SQL Database, there is no longer a limit on the number of indexes specific to memory-optimized tables and table types.

Ejemplo de código para sintaxisCode sample for syntax

En este apartado se incluye un bloque de código de Transact-SQL que muestra la sintaxis para crear varios índices en una tabla optimizada para memoria.This subsection contains a Transact-SQL code block that demonstrates the syntax to create various indexes on a memory-optimized table. El código muestra lo siguiente:The code demonstrates the following:

  1. Crear una tabla optimizada para memoria.Create a memory-optimized table.

  2. Usar instrucciones ALTER TABLE para agregar dos índices.Use ALTER TABLE statements to add two indexes.

  3. Usar INSERT para insertar algunas filas de datos.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 clave de índice duplicadosDuplicate index key values

Los valores duplicados de una clave de índice pueden reducir el rendimiento de las tablas optimizadas para memoria.Duplicate values for an index key might reduce the performance of memory-optimized tables. Valores duplicados del sistema que atraviesan cadenas de entrada en la mayoría operaciones de lectura y escritura de índices.Duplicates for the system to traverse entry chains for most index read and write operations. Cuando una cadena de entradas duplicadas supera las 100 entradas, la degradación del rendimiento puede ser palpable.When a chain of duplicate entries exceeds 100 entries, the performance degradation can become measurable.

Valores hash duplicadosDuplicate hash values

Este problema es más patente en el caso de los índices hash.This problem is more visible in the case of hash indexes. Los índices hash sufren más debido a las siguientes consideraciones:Hash indexes suffer more due to the following considerations:

  • El menor coste por operación de los índices hash.The lower cost per operation for hash indexes.
  • La interferencia de grandes cadenas de duplicados con la cadena de colisión de hash.The interference of large duplicate chains with the hash collision chain.

Para reducir la duplicación en un índice, pruebe a realizar los siguientes ajustes:To reduce duplication in an index, try the following adjustments:

  • Utilice un índice no agrupado.Use a nonclustered index.
  • Agregue más columnas al final de la clave de índice para reducir el número de duplicados.Add additional columns to the end of the index key, to reduce the number of duplicates.
    • Por ejemplo, podría agregar columnas que estén también en la clave principal.For example, you could add columns that are also in the primary key.

Para más información sobre las colisiones de hash, consulte el artículo sobre los índices de hash de tablas optimizadas para memoria.For more information about hash collisions, see Hash Indexes for Memory-Optimized Tables.

Ejemplo de mejoraExample improvement

Este es un ejemplo en el que se indica cómo evitar que se produzca un rendimiento ineficaz en el índice.Here is an example of how to avoid any performance inefficiency in your index.

Considere una tabla Customers que tiene una clave principal en CustomerId y un índice en la columna CustomerCategoryID.Consider a Customers table that has a primary key on CustomerId, and has an index on column CustomerCategoryID. En una categoría determinada suele haber muchos clientes.Typically there will be many customers in a given category. Por tanto, habrá muchos valores duplicados de CustomerCategoryID dentro de una determinada clave del índice.Thus there will be many duplicate values for CustomerCategoryID inside a given key of the index.

En este escenario, el procedimiento recomendado es usar un índice no agrupado en (CustomerCategoryID, CustomerId).In this scenario, the best practice is to use a nonclustered index on (CustomerCategoryID, CustomerId). Este índice se puede usar en consultas que usan un predicado donde CustomerCategoryID existe, pero la clave de índice no contiene duplicados.This index can be used for queries that use a predicate involving CustomerCategoryID, yet the index key does not contain duplication. Por tanto, no surgen ineficiencias en el mantenimiento de índices provocadas por valores duplicados de CustomerCategoryID o por la columna extra en el índice.Therefore, no inefficiencies in index maintenance are cause by either the duplicate CustomerCategoryID values, or by the extra column in the index.

La consulta siguiente muestra el promedio de valores clave de índice duplicados para el índice en CustomerCategoryID en la tabla Sales.Customers, en la base de datos de ejemplo 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 evaluar el promedio de duplicados de clave de índice para su propia tabla e índice, reemplace Sales.Customers por su nombre de la tabla y reemplace CustomerCategoryID por la lista de columnas de clave 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.

Comparación del uso de cada tipo de índiceComparing when to use each index type

La naturaleza de las consultas concretas determina qué tipo de índice es la mejor opción.The nature of your particular queries determines which type of index is the best choice.

Al implementar tablas optimizadas para memoria en una aplicación existente, la recomendación general es comenzar por los índices no agrupados, ya que sus capacidades se parecen más a las de los índices agrupados y no agrupados tradicionales de las tablas basadas en 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.

Recomendaciones para el uso de índices no agrupadosRecommendations for nonclustered index use

Un índice no agrupado es preferible a un índice de hash cuando:A nonclustered index is preferable over a hash index when:

  • Las consultas tienen una cláusula ORDER BY en la columna indexada.Queries have an ORDER BY clause on the indexed column.
  • Las consultas en las que solo se comprueban las primeras columnas de un índice con varias columnas.Queries where only the leading column(s) of a multi-column index is tested.
  • Las consultas prueban la columna indexada mediante el uso de una cláusula WHERE con:Queries test the indexed column by use of a WHERE clause with:
    • Una desigualdad: WHERE StatusCode != 'Done'An inequality: WHERE StatusCode != 'Done'
    • Un examen de intervalo de valores: WHERE Quantity >= 100A value range scan: WHERE Quantity >= 100

En todas las instrucciones SELECT siguientes, es preferible un índice no agrupado a un í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';  

Recomendaciones para el uso de índices de hashRecommendations for hash index use

Los índices de hash se usan principalmente para búsquedas de puntos y no para exámenes de intervalos.Hash indexes are primarily used for point lookups and not for range scans.

Es preferible un índice de hash sobre un índice no agrupado cuando las consultas usan predicados de igualdad y la cláusula WHERE se asigna a todas las columnas de clave de índice, como se muestra en el ejemplo siguiente: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 varias columnasMulti-column index

Un índice de varias columnas puede ser un índice no agrupado o un índice de hash.A multi-column index could be a nonclustered index or a hash index. Supongamos que las columnas de índice son col1 y col2.Suppose the index columns are col1 and col2. Si tenemos la siguiente instrucción SELECT, solo el índice no agrupado sería útil para el optimizador de consultas: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';  

El índice de hash requiere que la cláusula WHERE contenga una prueba de igualdad para cada una de las columnas en la clave.The hash index needs the WHERE clause to specify an equality test for each of the columns in its key. De lo contrario, dicho índice no tendrá ninguna utilidad para el optimizador de consultas.Else the hash index is not useful to the query optimizer.

Ningún tipo de índice resultará útil si solo se especifica la segunda columna de la clave de índice en la cláusula WHERE.Neither index type is useful if the WHERE clause specifies only the second column in the index key.

Tabla de resumen para comparar los escenarios de uso de los índicesSummary table to compare index use scenarios

En la tabla siguiente se enumeran todas las operaciones que son compatibles con distintos tipos de índices.The following table lists all operations that are supported by the different index types. se refiere a que el índice puede atender la solicitud con eficiencia y No se refiere a que el índice no puede satisfacer la solicitud con eficiencia.Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.

OperaciónOperation Con optimización para memoria,Memory-optimized,
hashhash
Con optimización para memoria,Memory-optimized,
no agrupadosnonclustered
Basada en disco,Disk-based,
(no)agrupados(non)clustered
Index scan, recupera todas las filas de la tabla.Index Scan, retrieve all table rows. Yes Yes Yes
Index seek en predicados de igualdad (=).Index seek on equality predicates (=). Yes
(Se requiere la clave completa).(Full key is required.)
Yes Yes
Index seek en predicados de desigualdad y de intervaloIndex seek on inequality and range predicates
(>, <, <=, >=, BETWEEN).(>, <, <=, >=, BETWEEN).
NoNo
(Resultados de un examen de índice).(Results in an index scan.)
1Yes 1 Yes
Recuperar filas según un criterio de ordenación que coincida con la definición de índice.Retrieve rows in a sort order that matches the index definition. NoNo Yes Yes
Recuperar filas según un criterio de ordenación que coincida con el opuesto de la definición de índice.Retrieve rows in a sort-order that matches the reverse of the index definition. NoNo NoNo Yes
       

1 Para un índice optimizado para memoria no agrupado, no es necesaria la clave completa para realizar la búsqueda de índice.1 For a memory-optimized Nonclustered index, the full key is not required to perform an index seek.

Administración automática de índice y estadísticasAutomatic index and statistics management

Aproveche soluciones como la desfragmentación de índice adaptable para administrar automáticamente las actualizaciones de estadísticas y la desfragmentación de índices para una o varias bases de datos.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Este procedimiento elige automáticamente si se debe volver a generar o reorganizar un índice según su nivel de fragmentación, entre otros parámetros y actualiza las estadísticas con un umbral lineal.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 tambiénSee Also

Guía de diseño de índices de SQL Server SQL Server Index Design Guide
Índices de hash para tablas optimizadas para memoria Hash Indexes for Memory-Optimized Tables
Índices no agrupados para tablas optimizadas para memoria Nonclustered Indexes for Memory-Optimized Tables
Desfragmentación de índice adaptableAdaptive Index Defrag