Compartir a través de

Determinar el recuento correcto de cubos para los índices hash

Debe especificar un valor para el BUCKET_COUNT parámetro al crear la tabla optimizada para memoria. En este tema se proporcionan recomendaciones para determinar el valor adecuado para el BUCKET_COUNT parámetro . Si no puede determinar el recuento correcto de cubos, utilice un índice no agrupado en su lugar. Un valor incorrecto BUCKET_COUNT , especialmente uno demasiado bajo, puede afectar significativamente al rendimiento de la carga de trabajo, así como al tiempo de recuperación de la base de datos. Es mejor sobrestimar el recuento de cubos.

Las claves de índice duplicadas pueden reducir el rendimiento con un índice hash porque las claves se aplican hash al mismo cubo, lo que hace que la cadena del cubo aumente.

Para obtener más información sobre los índices hash no agrupados, vea Índices hash e instrucciones para usar índices en tablas Memory-Optimized.

Se asigna una tabla hash para cada índice hash en una tabla optimizada para memoria. El tamaño de la tabla hash asignada para un índice se especifica mediante el BUCKET_COUNT parámetro en CREATE TABLE (Transact-SQL) o CREATE TYPE (Transact-SQL). El recuento de cubos se redondea internamente hasta la siguiente potencia de dos. Por ejemplo, especificar un número de cubos de 300 000 dará como resultado un recuento real de depósitos de 524 288.

Para obtener enlaces a un artículo y un vídeo sobre el recuento de cubos, consulte Cómo determinar el número correcto de cubos para los índices hash (In-Memory OLTP).

Recomendaciones

En la mayoría de los casos, el número de cubos debe estar comprendido entre 1 y 2 veces el número de valores distintos en la clave de índice. Si la clave de índice contiene muchos valores duplicados, en promedio hay más de 10 filas para cada valor de clave de índice, use un índice no agrupado en su lugar.

Es posible que no siempre pueda predecir cuántos valores puede tener o tendrá una clave de índice determinada. El rendimiento debe ser aceptable si el BUCKET_COUNT valor está dentro de 5 veces del número real de valores de clave.

Para determinar el número de claves de índice únicas en los datos existentes, use consultas similares a los ejemplos siguientes:

Clave principal e índices únicos

Dado que el índice de clave principal es único, el número de valores distintos de la clave corresponde al número de filas de la tabla. Para obtener una clave principal de ejemplo en (SalesOrderID, SalesOrderDetailID) de la tabla Sales.SalesOrderDetail en la base de datos AdventureWorks, emita la consulta siguiente para calcular el número de valores de clave principal distintos, que corresponde al número de filas de la tabla:

SELECT COUNT(*) AS [row count]   
FROM Sales.SalesOrderDetail

Esta consulta muestra un recuento de filas de 121 317. Use un recuento de cubos de 240 000 si el recuento de filas no cambiará significativamente. Utiliza un conteo de cubos de 480 000 si se espera que el número de pedidos de ventas de la tabla se cuadruplique.

Índices no únicos

Para otros índices, por ejemplo, un índice de varias columnas en (SpecialOfferID, ProductID), emita la consulta siguiente para determinar el número de valores de clave de índice únicos:

SELECT COUNT(*) AS [SpecialOfferID_ProductID index key count]  
FROM   
   (SELECT DISTINCT SpecialOfferID, ProductID   
    FROM Sales.SalesOrderDetail) t

Esta consulta devuelve un recuento de claves de índice para (SpecialOfferID, ProductID) de 484, lo que indica que se debe usar un índice no clúster en lugar de un índice hash no clúster.

Determinar el número de duplicados

Para determinar el número medio de valores duplicados de un valor de clave de índice, divida el número total de filas por el número de claves de índice únicas.

Para el índice de ejemplo en (SpecialOfferID, ProductID), esto conduce a 121317 / 484 = 251. Esto significa que los valores de clave de índice tienen un promedio de 251 y, por tanto, debe ser un índice no clúster.

Solución de problemas del número de cubos

Para solucionar problemas de recuento de cubos en tablas optimizadas para memoria, use sys.dm_db_xtp_hash_index_stats (Transact-SQL) para obtener estadísticas sobre los cubos vacíos y la longitud de las cadenas de filas. La consulta siguiente se puede usar para obtener estadísticas sobre todos los índices hash de la base de datos actual. La consulta puede tardar varios minutos en ejecutarse si hay tablas grandes en la base de datos.

SELECT   
   object_name(hs.object_id) AS 'object name',   
   i.name as 'index name',   
   hs.total_bucket_count,  
   hs.empty_bucket_count,  
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',  
   hs.avg_chain_length,   
   hs.max_chain_length  
FROM sys.dm_db_xtp_hash_index_stats AS hs   
   JOIN sys.indexes AS i   
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id

Los dos indicadores clave del estado del índice hash son:

empty_bucket_percent
empty_bucket_percent indica el número de cubos vacíos en el índice hash.

Si empty_bucket_percent es inferior al 10 por ciento, es probable que el número de cubos sea demasiado bajo. Idealmente, el empty_bucket_percent debe ser del 33 por ciento o superior. Si el recuento de buckets coincide con el número de valores de clave de índice, aproximadamente 1/3 de los buckets están vacíos, debido a la distribución del hash.

avg_chain_length
avg_chain_length indica la longitud media de las cadenas de filas en los cubos hash.

Si avg_chain_length es mayor que 10 y empty_bucket_percent es mayor que el 10 por ciento, es probable que haya muchos valores de clave de índice duplicados y un índice no clúster sería más adecuado. Una longitud media de cadena de 1 es ideal.

Hay dos factores que afectan a la longitud de la cadena:

  1. Duplicados; todas las filas duplicadas forman parte de la misma cadena en el índice hash.

  2. Varios valores de clave se asignan al mismo cubo. Cuanto menor sea la cantidad de cubos, más cubos habrá con varios valores asignados.

Por ejemplo, considere la siguiente tabla y script para insertar filas de ejemplo en la tabla:

CREATE TABLE [Sales].[SalesOrderHeader_test]  
(  
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),  
   [OrderSequence] int NOT NULL,  
   [OrderDate] [datetime2](7) NOT NULL,  
   [Status] [tinyint] NOT NULL,  
  
PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),  
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),  
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),  
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),  
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
DECLARE @i int = 0  
BEGIN TRAN  
WHILE @i < 262144  
BEGIN  
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)  
   SET @i += 1  
END  
COMMIT  
GO

El script inserta 262 144 filas en la tabla. Inserta valores únicos en el índice de clave principal y en IX_OrderSequence. Inserta muchos valores duplicados en el índice IX_Status: el script solo genera 8 valores distintos.

La salida de la consulta de solución de problemas de BUCKET_COUNT es la siguiente:

nombre de índice recuento_total_de_cubetas conteo_de_cubeta_vacía empty_bucket_percent longitud_promedio_de_cadena longitud_máxima_de_cadena
IX_Estado 8 4 50 65536 65536
IX_OrderSequence 32768 13 0 8 26
PK_SalesOrd_B14003C3F8FB3364 262144 96319 36 1 8

Tenga en cuenta los tres índices hash de esta tabla:

  • IX_Status: el 50 % de los cubos están vacíos, lo que es bueno. Sin embargo, la longitud media de la cadena es muy alta (65.536). Esto indica un gran número de valores duplicados. Por lo tanto, el uso de un índice hash no clúster no es adecuado en este caso. En su lugar, se debe usar un índice no clúster.

  • IX_OrderSequence: el 0 por ciento de los cubos están vacíos, lo que es demasiado bajo. Además, la longitud media de la cadena es de 8. Como los valores de este índice son únicos, esto significa que, en promedio, se asignan 8 valores a cada cubo. Se debe aumentar el número de cubos. Dado que la clave de índice tiene 262 144 valores únicos, el recuento de cubos debe ser al menos 262 144. Si se espera un crecimiento futuro, el número debe ser mayor.

  • Índice de clave principal (PK__SalesOrder...): el 36 % de los contenedores están vacíos, lo cual es bueno. Además, la longitud media de la cadena es 1, que también es buena. No se necesita ningún cambio.

Para obtener más información sobre cómo solucionar problemas con los índices hash optimizados para memoria, consulte Solución de problemas comunes de rendimiento con índices hash de Memory-Optimized.

Consideraciones detalladas para la optimización adicional

En esta sección se describen consideraciones adicionales para optimizar la cantidad de cubetas.

Para lograr el mejor rendimiento para los índices hash, equilibre la cantidad de memoria asignada a la tabla hash y el número de valores distintos de la clave de índice. También hay un equilibrio entre el rendimiento de las consultas puntuales y los escaneos de tabla.

  • Cuanto mayor sea el valor de recuento de cubos, más cubos vacíos habrá en el índice. Esto afecta al uso de memoria (8 bytes por cubo) y al rendimiento de los exámenes de tabla, ya que cada cubo se examina como parte de un examen de tabla.

  • Cuanto menor sea el número de cubos, más valores se asignan a un único cubo. Esto reduce el rendimiento de las búsquedas e inserciones de puntos, ya que SQL Server puede necesitar atravesar varios valores en un único cubo para encontrar el valor especificado por el predicado de búsqueda.

Si el número de cubos es significativamente menor que el número de claves de índice únicas, muchos valores se asignarán a cada cubo. Esto reduce el rendimiento de la mayoría de las operaciones DML, especialmente las búsquedas puntuales (búsquedas de claves de índice individuales) y las operaciones de inserción. Por ejemplo, puede observar un rendimiento deficiente en las consultas SELECT y en las operaciones UPDATE y DELETE con criterios de igualdad que coinciden con las columnas clave de índice en la cláusula WHERE. Un número de cubos bajo también afectará al tiempo de recuperación de la base de datos, ya que los índices se vuelven a crear en el inicio de la base de datos.

Valores de clave de índice duplicados

Los valores duplicados pueden aumentar el impacto en el rendimiento de las colisiones de hash. Normalmente no es un problema si cada clave de índice tiene un número bajo de duplicados. Pero esto puede ser un problema si la discrepancia entre el número de claves de índice únicas y el número de filas de las tablas se vuelve muy grande.

Todas las filas con la misma clave de índice entrarán en la misma cadena duplicada. Si hay varias claves de índice en el mismo cubo debido a una colisión de hash, los escáneres de índice siempre deben examinar la cadena duplicada completa para el primer valor antes de poder localizar la primera fila correspondiente al segundo valor. Las claves duplicadas también dificultan que la recolección de basura realice su labor para localizar la fila. Por ejemplo, si hay 1000 duplicados para cualquier llave y se elimina una de las filas, el recolector de basura debe examinar la cadena de 1000 duplicados para desvincular la fila del índice. Esto es cierto incluso si la consulta que encontró la operación de eliminación usó un índice más eficaz (un índice de clave principal) para localizar la fila, ya que el recolector de basura debe desvincularse de todos los índices.

En el caso de los índices hash, hay dos maneras de reducir el trabajo causado por valores de clave de índice duplicados:

  • En su lugar, use un índice no clúster. Puede reducir los duplicados agregando columnas a la clave de índice sin necesidad de realizar ningún cambio en la aplicación.

  • Especifique un recuento de cubos muy alto para el índice. Por ejemplo, de 20 a 100 veces el número de claves de índice únicas. Esto reducirá las colisiones de hash.

Tablas pequeñas

En el caso de las tablas más pequeñas, el uso de memoria no suele ser un problema, ya que el tamaño del índice será pequeño en comparación con el tamaño general de la base de datos.

Ahora debe elegir en función del tipo de rendimiento que desee:

  • Si las operaciones críticas para el rendimiento en el índice son principalmente búsquedas de puntos o operaciones de inserción, un recuento de cubos mayor sería adecuado para reducir la probabilidad de colisiones de hash. Tres veces el número de filas o incluso más sería la mejor opción.

  • Si los escaneos completos de índice son las operaciones más críticas para el rendimiento, use un conteo de cubetas que esté cerca del número real de valores de clave de índice.

Tablas grandes

En el caso de las tablas grandes, el uso de memoria podría convertirse en un problema. Por ejemplo, con una tabla de 250 millones de filas que tiene 4 índices hash, cada uno con un recuento de cubos de 100 millones, la sobrecarga de las tablas hash es de 4 índices * 1 mil millones de cubos * 8 bytes = 32 gigabytes de uso de memoria. Al elegir un recuento de cubos de 250 millones para cada uno de los índices, la sobrecarga total de las tablas hash será de 8 gigabytes. Tenga en cuenta que esto se suma a los 8 bytes de uso de memoria que cada índice agrega a cada fila individual, que es de 8 gigabytes en este escenario (4 índices * 8 bytes * 250 millones de filas).

Los escaneos de tabla completa no suelen estar en la ruta crítica de rendimiento para las cargas de trabajo OLTP. Por lo tanto, la elección es entre el uso de memoria frente al rendimiento de las operaciones de búsqueda e inserción de puntos:

  • Si el uso de memoria es un problema, elija un recuento de cubos cerca del número de valores de clave de índice. El recuento de cubos no debe ser significativamente menor que el número de valores de clave de índice, ya que esto afecta a la mayoría de las operaciones DML, así como el tiempo necesario para recuperar la base de datos después de reiniciar el servidor.

  • Al optimizar el rendimiento de las búsquedas de puntos, un número de cubos mayor de dos o incluso tres veces el número de valores de índice únicos sería adecuado. Un mayor número de cubos significaría un aumento del uso de memoria y un aumento en el tiempo necesario para un examen de índice completo.

Véase también

Índices en tablas de Memory-Optimized

  • Last updated on