Solución de problemas de índices de hash para tablas optimizadas para memoria

Artículo
11/15/2023

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance

Requisito previo

Encontrará información de contexto importante para comprender este artículo en:

Números prácticos

Cuando se crea un índice de hash para una tabla optimizada para memoria, se debe especificar el número de depósitos en el momento de la creación. En la mayoría de los casos, lo ideal es que el número de cubos esté entre 1 y 2 veces el número de valores distintos de la clave de índice.

Sin embargo, aunque el BUCKET_COUNT esté moderadamente por debajo o por encima del rango preferido, es probable que el rendimiento del índice de hash sea tolerable o aceptable. Como mínimo, considere asignar al índice de hash un BUCKET_COUNT aproximadamente igual al número de filas que prevé que terminará teniendo la tabla optimizada para memoria.
Suponga que la tabla en aumento tiene 2 000 000 filas, pero la predicción indica que dicha cantidad crecerá 10 veces hasta 20 000 000 filas. Comience por un número de cubos que sea 10 veces el número de filas de la tabla. Esto deja espacio para una mayor cantidad de filas.

Lo ideal es aumentar el número de cubos cuando la cantidad de filas alcanza el número de cubos inicial.
Aunque la cantidad de filas aumente hasta cinco veces más que el número de cubos, el rendimiento seguirá siendo bueno casi siempre.

Suponga que un índice de hash tiene 10 000 000 valores de claves distintos.

Un número de cubos de 2 000 000 sería la menor cantidad que podría aceptar. El grado de degradación del rendimiento puede ser tolerable.

¿Demasiados valores duplicados en el índice?

Si los valores de hash indizados tienen una alta tasa de duplicados, los depósitos de hash toleran cadenas más largas.

Suponga que tiene la misma tabla SupportEvent del bloque de código anterior de la sintaxis de T-SQL. El código de T-SQL siguiente muestra cómo buscar y mostrar la proporción de todos los valores con respecto a los valores únicos :

-- Calculate ratio of:  Rows / Unique_Values.  
DECLARE @allValues float(8) = 0.0, @uniqueVals float(8) = 0.0;  
  
SELECT @allValues = Count(*) FROM SupportEvent;  
  
SELECT @uniqueVals = Count(*) FROM  
  (SELECT DISTINCT SupportEngineerName  
      FROM SupportEvent) as d;  
  
    -- If (All / Unique) >= 10.0, use a nonclustered index, not a hash.   
SELECT Cast((@allValues / @uniqueVals) as float) as [All_divby_Unique];  
go

Una proporción de 10,0 o superior significa que un tipo de índice de hash sería deficiente. Sopese la posibilidad de usar un índice no agrupado en su lugar.

Solución de problemas de número de cubos de índice de hash

En esta sección se explica cómo resolver problemas relativos al número de cubos del índice de hash.

Supervisión de estadísticas de cadenas y depósitos vacíos

Puede supervisar el mantenimiento estadístico de los índices de hash ejecutando la siguiente instrucción SELECT de T-SQL. SELECT usa la vista de administración de datos (DMV) denominada sys.dm_db_xtp_hash_index_stats.

SELECT  
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
  i.name                   as [index],   
  h.total_bucket_count,  
  h.empty_bucket_count,  
    
  FLOOR((  
    CAST(h.empty_bucket_count as float) /  
      h.total_bucket_count) * 100)  
                            as [empty_bucket_percent],  
  h.avg_chain_length,   
  h.max_chain_length  
FROM  
        sys.dm_db_xtp_hash_index_stats  as h   
  JOIN sys.indexes                     as i  
          ON h.object_id = i.object_id  
          AND h.index_id  = i.index_id  
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];

Compare los resultados de SELECT con las siguientes directrices estadísticas:

Cubos vacíos:
- 33 % es un buen valor de destino, pero un porcentaje mayor (incluso el 90 %) suele funcionar correctamente.
- Cuando el número de cubos es igual al número de valores de clave distintos, aproximadamente un 33 % de los cubos están vacíos.
- Un valor inferior al 10 % es demasiado bajo.
Cadenas dentro de cubos:
- Una longitud media de cadena de 1 es ideal en caso de que no haya valores de clave de índice duplicados. Las longitudes de cadena de hasta 10 suelen ser aceptables.
- Si la longitud promedio de la cadena es superior a 10 y el porcentaje de depósitos vacíos es superior al 10 %, los datos tienen tantos duplicados que es posible que un índice de hash no sea el tipo más adecuado.

Demostración de cadenas y depósitos vacíos

El siguiente bloque de código de T-SQL constituye un sencillo método de comprobar una instrucción SELECT * FROM sys.dm_db_xtp_hash_index_stats;. El bloque de código se completa en 1 minuto. Estas son las fases del código de bloque en cuestión:

Crea una tabla optimizada para memoria que tiene algunos índices hash.
Rellena la tabla con miles de filas.
a. Se usa un operador de módulo para configurar la tasa de valores duplicados en la columna StatusCode.
b. El bucle inserta 262,144 filas aproximadamente en un minuto.
PRINT imprime un mensaje pidiéndole que ejecute la instrucción SELECT anterior en sys.dm_db_xtp_hash_index_stats.

DROP TABLE IF EXISTS SalesOrder_Mem;  
go  
  
  
CREATE TABLE SalesOrder_Mem  
(  
  SalesOrderId   uniqueidentifier  NOT NULL  DEFAULT newid(),  
  OrderSequence  int               NOT NULL,  
  OrderDate      datetime2(3)      NOT NULL,  
  StatusCode     tinyint           NOT NULL,  
  
  PRIMARY KEY NONCLUSTERED  
      HASH (SalesOrderId)  WITH (BUCKET_COUNT = 262144),  
  
  INDEX ix_OrderSequence  
      HASH (OrderSequence) WITH (BUCKET_COUNT = 20000),  
  
  INDEX ix_StatusCode  
      HASH (StatusCode)    WITH (BUCKET_COUNT = 8),  
  
  INDEX ix_OrderDate       NONCLUSTERED (OrderDate DESC)  
)  
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
go  
  
--------------------  
  
SET NOCOUNT ON;  
  
-- Same as PK bucket_count.  68 seconds to complete.  
DECLARE @i int = 262144;  
  
BEGIN TRANSACTION;  
  
WHILE @i > 0  
BEGIN  
  
  INSERT SalesOrder_Mem  
      (OrderSequence, OrderDate, StatusCode)  
    Values  
      (@i, GetUtcDate(), @i % 8);  -- Modulo technique.  
  
  SET @i -= 1;  
END  
COMMIT TRANSACTION;  
  
PRINT 'Next, you should query:  sys.dm_db_xtp_hash_index_stats .';  
go

El bucle INSERT anterior hace lo siguiente:

Inserta valores únicos en el índice de clave principal y en ix_OrderSequence.
Inserta un par de cientos de miles de filas que solo representan ocho valores distintos para StatusCode. Por lo tanto, hay una alta tasa de duplicación de valores en el índice ix_StatusCode.

Para solucionar problemas cuando el número de cubos no es óptimo, examine la salida siguiente de la instrucción SELECT en sys.dm_db_xtp_hash_index_stats. Para estos resultados, hemos agregado WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' a la instrucción SELECT que copiamos de la sección D.1.

Los resultados de nuestra instrucción SELECT se muestran después del código, divididos artificialmente en dos tablas de resultados más estrechas para poder verlos mejor.

Estos son los resultados del número de cubos.

IndexName	total_bucket_count	empty_bucket_count	EmptyBucketPercent
ix_OrderSequence	32768	13	0
ix_StatusCode	8	4	50
PK_SalesOrd_B14003...	262144	96525	36

Luego tenemos los resultados de longitud de la cadena.

IndexName	avg_chain_length	max_chain_length
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

Vamos a interpretar la tabla de resultados anterior para los tres índices de hash:

ix_StatusCode:

El 50 % de los depósitos están vacíos, lo que es adecuado.
Sin embargo, el promedio de longitud de cadena es muy elevado (65 536).
- Esto indica una alta tasa de valores duplicados.
- Por consiguiente, el uso de un índice de hash no es adecuado en este caso. Se debe usar un índice no clúster en su lugar.

ix_OrderSequence:

El 0 % de los depósitos están vacíos, lo que es demasiado bajo.
La longitud promedio de la cadena es 8, aunque todos los valores de este índice sean únicos.
- Por lo tanto, el número de cubos debe aumentarse para reducir el promedio de longitud de cadena a un valor más cercano a 2 o 3.
Como la clave de índice tiene 262 144 valores únicos, el número de cubos debe ser al menos 262 144.
- Si se espera un aumento futuro, el número de cubos debe ser superior.

Índice de clave principal (PK_SalesOrd_...):

El 36 % de los depósitos están vacíos, lo que es adecuado.
El promedio de longitud de cadena es 1, lo que también es adecuado. No se necesita ningún cambio.

Equilibrio de ventajas y desventajas

Las cargas de trabajo de OLTP se centran en filas individuales. Los recorridos de tabla completos no suelen estar en la ruta crítica de rendimiento para cargas de trabajo de OLTP. Por lo tanto, las ventajas y desventajas que debe mantener en equilibrio están entre la cantidad de uso de memoria y el rendimiento de las operaciones de inserción y pruebas de igualdad.

Si el uso de memoria es la mayor preocupación:

Elija un número de cubos cercano al número de valores de clave de índice único.
El número de cubos no debería ser significativamente menor que el número de valores de clave de índice único, puesto que esto afecta a la mayoría de las operaciones de DML, así como al tiempo que lleva recuperar la base de datos después de reiniciar el servidor.

Si las pruebas de rendimiento de igualdad es la mayor preocupación:

Un número mayor de cubos, de dos o tres veces el número de valores de índice únicos, es adecuado. Un número mayor significa:
- Recuperaciones más rápidas cuando se busca un valor específico.
- Mayor uso de memoria.
- Aumento del tiempo necesario para un examen completo del índice de hash.

Otras lecturas

Índices de hash para tablas optimizadas para memoria
Índices no agrupados para tablas optimizadas para memoria