Índices en pools de SQL dedicados en Azure Synapse Analytics

• Iniciar una prueba gratuita de Fabric.
• Asistente de migración para almacén de datos en tejido.

Recomendaciones y ejemplos para indexar tablas en un grupo de SQL dedicado en Azure Synapse Analytics.

Tipos de índice

El grupo de SQL dedicado ofrece varias opciones de indexación, entre las que se incluyen índices de almacén de columnas agrupados, índices agrupados y no agrupados y una opción que no es de índice también conocida como montón.

Para crear una tabla con un índice, consulte la documentación de CREATE TABLE (grupo de SQL dedicado).

Índices de almacén de columnas en clúster

De forma predeterminada, el grupo de SQL dedicado crea un índice de almacén de columnas agrupado cuando no se especifican opciones de índice en una tabla. Las tablas del almacén de columnas en clúster ofrecen el máximo nivel de compresión de datos, así como el mejor rendimiento general de las consultas. Por lo general, las tablas de almacén de columnas agrupadas funcionan mejor que las tablas de montón o de índice agrupado, y suelen ser la mejor opción para tablas grandes. Por estos motivos, el almacén de columnas agrupado es el mejor lugar por el que empezar cuando no se sabe cómo indexar una tabla.

Para crear una tabla columnstore agrupada, especifique CLUSTERED COLUMNSTORE INDEX en la cláusula WITH, o bien deje fuera la cláusula WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );

Hay algunos escenarios en los que un almacén de columnas agrupado puede que no sea una buena opción:

• Las tablas de almacén de columnas no admiten varchar (max), nvarchar (max) y varbinary (max). Considere la posibilidad de usar un índice agrupado o de montón en su lugar.
• Las tablas columnstore podrían ser menos eficientes para datos transitorios. Considere la posibilidad de usar tablas de montón y tablas temporales.
• Tablas pequeñas con menos de 60 millones de filas. Considere la posibilidad de usar tablas de montón.

Tablas de montón

Cuando almacena datos temporalmente en el pool de SQL dedicado, puede descubrir que el uso de tablas heap agiliza el proceso global. Esto se debe a que las cargas en montones son más rápidas que la indexación de tablas y, en algunos casos, la posterior lectura se puede realizar desde la memoria caché. Si solo estás cargando datos para prepararlos antes de realizar más transformaciones, cargar los datos en una tabla heap es mucho más rápido que cargarlos en una tabla de almacén de columnas con índice agrupado. Además, la carga de datos en una tabla temporal es una operación mucho más rápida que la carga de una tabla en un almacenamiento permanente. Después de cargar los datos, puede crear índices en la tabla para obtener un mejor rendimiento de las consultas.

Las tablas de almacén de columnas agrupadas empiezan a lograr una compresión óptima cuando hay más de 60 millones de filas. En el caso de tablas de búsqueda pequeñas, menos de 60 millones filas, considere la posibilidad de usar HEAP o el índice agrupado para mejorar el rendimiento de las consultas.

Para crear una tabla de montón, solo es preciso especificar HEAP en la cláusula WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( HEAP );

Índices agrupados y no agrupados

Los índices agrupados pueden superar a las tablas columnstore agrupadas cuando se necesita recuperar rápidamente una sola fila. En el caso de las consultas en las que se deba efectuar una búsqueda en una sola o en muy pocas filas a gran velocidad, considere la posibilidad de incorporar un índice agrupado o un índice secundario sin agrupar. La desventaja de utilizar un índice clúster es que solo se benefician las consultas que utilicen un filtro muy selectivo en la columna del índice agrupado. Para mejorar el filtro en las restantes columnas se puede agregar un índice no clúster a las mismas. Sin embargo, cada índice que se agrega a una tabla agrega espacio y tiempo de procesamiento a las cargas.

Para crear una tabla de índice en clúster, especifique CLUSTERED INDEX en la cláusula WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED INDEX (id) );

Para agregar un índice no agrupado en una tabla, use la sintaxis siguiente:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Optimización de índices columnstore agrupados

Las tablas de columna agrupadas organizan los datos en segmentos. El hecho de que los segmentos sean de gran calidad es fundamental para conseguir un rendimiento óptimo de las consultas en una tabla de almacén de columnas. La calidad de los segmentos se puede medir por el número de filas de un grupo de filas comprimido. La calidad de los segmentos es mayor donde hay al menos 100 000 filas por grupo de filas comprimido y su rendimiento aumenta a medida que el número de filas por grupo se aproxima a 1 048 576, que es el número máximo de filas que puede contener un grupo.

La siguiente vista se puede crear y utilizar en su sistema para calcular el promedio de filas por grupo de filas e identificar cualquier índice de almacén de columnas agrupados que no sea óptimo. La última columna de esta vista genera una instrucción SQL que se puede usar para volver a crear los índices.

CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
        GETDATE()                                                               AS [execution_date]
,       DB_Name()                                                               AS [database_name]
,       s.name                                                                  AS [schema_name]
,       t.name                                                                  AS [table_name]
,       MAX(p.partition_number)                                                 AS [table_partition_count]
,       SUM(rg.[total_rows])                                                    AS [row_count_total]
,       SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id])               AS [row_count_per_distribution_MAX]
,       CEILING((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
,       SUM(CASE WHEN rg.[State] = 0 THEN 1                   ELSE 0    END)    AS [INVISIBLE_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE 0    END)    AS [INVISIBLE_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 1 THEN 1                   ELSE 0    END)    AS [OPEN_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE 0    END)    AS [OPEN_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 2 THEN 1                   ELSE 0    END)    AS [CLOSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE 0    END)    AS [CLOSED_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 3 THEN 1                   ELSE 0    END)    AS [COMPRESSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE 0    END)    AS [COMPRESSED_rowgroup_rows]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows]   ELSE 0    END)    AS [COMPRESSED_rowgroup_rows_DELETED]
,       MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_AVG]
,       'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;'             AS [Rebuild_Index_SQL]
FROM    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg
JOIN    sys.[pdw_nodes_tables] nt                   ON  rg.[object_id]          = nt.[object_id]
                                                    AND rg.[pdw_node_id]        = nt.[pdw_node_id]
                                                    AND rg.[distribution_id]    = nt.[distribution_id]
JOIN    sys.[pdw_permanent_table_mappings] mp                 ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[tables] t                              ON  mp.[object_id]  = t.[object_id]
JOIN    sys.[schemas] s                             ON t.[schema_id]    = s.[schema_id]
JOIN    sys.[partitions] p                          ON P.object_id      = t.object_id
GROUP BY
        s.[name]
,       t.[name];

Ahora que ha creado la vista, ejecute esta consulta para identificar las tablas con grupos de filas con menos de 100 000 filas. Puede aumentar el umbral de 100 K si desea obtener una mayor calidad de los segmentos.

SELECT    *
FROM    [dbo].[vColumnstoreDensity]
WHERE    COMPRESSED_rowgroup_rows_AVG < 100000
        OR INVISIBLE_rowgroup_rows_AVG < 100000;

Una vez ejecutada la consulta, puede empezar a examinar los datos y analizar los resultados. En esta tabla se explica lo que hay que buscar en el análisis de los grupos de filas.

Impacto del mantenimiento de índices

La columna Rebuild_Index_SQL de la vista vColumnstoreDensity contiene una instrucción ALTER INDEX REBUILD que se puede usar para volver a generar los índices. Al volver a crear los índices, asegúrese de asignar suficiente memoria a la sesión que volverá a generar el índice. Para ello, aumente la clase de recurso de un usuario que tenga permisos para volver a generar el índice en esta tabla al mínimo recomendado. Para obtener un ejemplo, consulte Regeneración de índices para mejorar la calidad de los segmentos más adelante en este artículo.

Para una tabla con un índice de almacén de columnas agrupado ordenado, ALTER INDEX REBUILD reordenará los datos mediante el uso de tempdb. Supervise tempdb durante las operaciones de recompilación. Si necesita más espacio en tempdb, amplíe el grupo de bases de datos. Reduzca la escala una vez completada la reconstrucción del índice.

En una tabla con un índice de almacén de columnas agrupado ordenado, ALTER INDEX REORGANIZE no reordenará los datos. Para reordenar los datos, use ALTER INDEX REBUILD.

Para más información sobre los índices de almacén de columnas agrupados ordenados, consulte Optimización del rendimiento con el índice de almacén de columnas agrupado ordenado.

Causas de la baja calidad del índice columnstore

Si ha identificado las tablas con una calidad deficiente de sus segmentos, querrá identificar la causa principal. A continuación se muestran otras causas comunes de baja calidad de los segmentos:

1. Presión de la memoria cuando se generó el índice
2. Gran volumen de operaciones de DML
3. Operaciones de carga pequeña o mínima
4. Demasiadas particiones

Estos factores pueden ser la causa de que un índice de almacén de columnas tenga mucho menos de un millón de filas por grupo de filas, que es el número óptimo. También pueden provocar que las filas vayan al grupo de filas delta, en lugar de a un grupo de filas comprimido.

Presión de la memoria cuando se generó el índice

El número de filas por grupo de filas comprimido está directamente relacionado con el ancho de la fila y la cantidad de memoria disponible para procesar el grupo de filas. Cuando las filas se escriben en las tablas de almacén de columnas bajo presión de memoria, la calidad del segmento de almacén de columnas puede verse afectada. Por lo tanto, la mejor práctica es dar a la sesión que está escribiendo en las tablas de índices de almacenamiento de columnas acceso a tanta memoria como sea posible. Dado que hay un equilibrio entre memoria y simultaneidad, la guía sobre la asignación de almacenamiento adecuada depende de los datos de cada fila de la tabla, de las unidades de almacenamiento de datos que ha asignado al sistema y de la cantidad de ranuras de simultaneidad que puede dar a la sesión que escribe datos en la tabla.

Gran volumen de operaciones de DML

Un alto volumen de operaciones DML que actualizan y eliminan filas puede introducir ineficiencia en el almacén de columnas. Esto sucede especialmente cuando se modifica la mayor parte de las filas de un grupo de filas.

• La eliminación de una fila de un grupo de filas comprimido solo marca lógicamente la fila como eliminada. La fila permanece en el grupo de filas comprimido hasta que se reconstruyen la partición o la tabla.
• La inserción de una fila agrega la fila a una tabla de almacén de filas interna denominada grupo de filas delta. La fila insertada no se convierte en almacén de columnas hasta que el grupo de filas delta está lleno y marcado como cerrado. Los grupos de filas se cierran cuando alcanzan la capacidad máxima de 1 048 576 filas.
• La actualización de una fila en formato de almacén de columnas se procesa como eliminación lógica y luego como inserción. La fila insertada se puede guardar en el almacén delta.

Las operaciones de actualización e inserción por lotes que superan el límite de volumen de 102 400 filas por distribución alineada a la partición pasan directamente al formato de almacén de columnas. Pero para ello, suponiendo una distribución uniforme, tendría que estar modificando más de 6.144 millones de filas en una sola operación. Si el número de filas para una distribución alineada con particiones determinada es inferior a 102 400, las filas van al almacén delta y permanecen allí hasta que se hayan insertado o modificado un número suficiente de filas para completar el grupo de filas o hasta que se haya recompilado el índice.

Operaciones de carga pequeña o de goteo

Las cargas pequeñas que fluyen al grupo SQL dedicado también se conocen como cargas goteo. Normalmente, representan una transmisión prácticamente constante de los datos que ingiere el sistema. Pero, dado que esta transmisión es casi continua, el volumen de filas de datos no es especialmente grande. Con frecuencia los datos están significativamente por debajo del umbral necesario para una carga directa en formato de almacén de columnas.

En estas situaciones, a menudo es preferible que los datos vayan primero a almacenamiento de blobs de Azure y dejar que se acumulen antes de cargarlos. A menudo, esta técnica se conoce como procesamiento por micro-lotes.

Demasiadas particiones

Otra cosa que se debe tener en cuenta es el impacto de la creación de particiones en las tablas de almacén de columnas en clúster. Antes de crear particiones, el grupo de SQL dedicado ya ha dividido los datos en 60 bases de datos. La creación de particiones divide aún más los datos. Si crea particiones de los datos, debe considerar que cada partición debe tener un mínimo de un millón de filas para beneficiarse de un índice de almacén de columnas en clúster. Si particiona su tabla en 100 particiones, entonces esta deberá tener al menos 6000 millones de filas para beneficiarse de un índice de almacén de columnas en clúster (60 distribuciones x 100 particiones x 1 millón de filas). Si la tabla de 100 particiones no tiene 6000 millones de filas, reduzca el número de particiones o considere la posibilidad de usar una tabla de montón en su lugar.

Una vez que las tablas se hayan cargado con datos, siga los pasos a continuación para identificar y reconstruir las tablas con índices de almacén de columnas agrupadas subóptimos.

Volver a generar los índices para mejorar la calidad del segmento

Paso 1: Identificación o creación de un usuario que use la clase de recurso adecuada

Una manera rápida de mejorar rápidamente la calidad de los segmentos es volver a generar el índice. La instrucción SQL que devuelve la vista anterior contiene una instrucción ALTER INDEX REBUILD que puede utilizarse para volver a crear los índices. Al volver a crear los índices, asegúrese de asignar suficiente memoria a la sesión que volverá a generar el índice. Para ello, aumente la clase de recurso de un usuario que tenga permisos para volver a generar el índice en esta tabla al mínimo recomendado.

A continuación se muestra un ejemplo de cómo asignar más memoria a un usuario mediante el aumento de su clase de recurso. Para trabajar con clases de recursos, consulte Clases de recursos para la administración de cargas de trabajo.

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

Paso 2: Reconstruir índices de almacén de columnas en clúster con un usuario de clase de recursos más alta

Inicie sesión como el usuario del paso 1 (LoadUser), que ahora usa una clase de recurso superior, y ejecute las instrucciones ALTER INDEX. Asegúrese de que este usuario tiene el permiso ALTER en las tablas en las que se vuelve a generar el índice. En estos ejemplos se muestra cómo volver a generar todo el índice columnstore o cómo volver a generar una sola partición. En tablas mayores, es más práctico volver a generar los índices partición por partición.

Como alternativa, en lugar de volver a crear el índice, se puede copiar la tabla en otra tabla nueva con CTAS. ¿De qué manera es mejor? En el caso de grandes volúmenes de datos, CTAS suele ser más rápido que ALTER INDEX. Sin embargo, en el caso de volúmenes menores de datos, ALTER INDEX es más fácil de usar y no requerirá el intercambio de la tabla.

-- Rebuild the entire clustered index
ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;

-- Rebuild a single partition
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5;

-- Rebuild a single partition with archival compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Rebuild a single partition with columnstore compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE);

La regeneración de un índice en el grupo de SQL dedicado es una operación que se realiza fuera de línea. Para más información sobre cómo volver a crear los índices, consulte la sección ALTER INDEX REBUILD de Desfragmentación de índices de almacén de columnas y ALTER INDEX.

Paso 3: Comprobación de que ha mejorado la calidad de los segmentos de almacén de columnas en clúster

Vuelva a ejecutar la consulta que identificó la tabla con una calidad deficiente de los segmentos y compruebe que dicha calidad ha mejorado. Si la calidad del segmento no ha mejorado, puede deberse a que las filas de su tabla son demasiado anchas. Considere utilizar una clase de recurso más alta o DWU al reconstruir sus índices.

Recompilación de índices con CTAS y conmutación de particiones

Este ejemplo utiliza la instrucción CREATE TABLE AS SELECT (CTAS) y el cambio de partición para volver a crear una partición de tabla.

-- Step 1: Select the partition of data and write it out to a new table using CTAS
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

-- Step 2: Switch IN the rebuilt data with TRUNCATE_TARGET option
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO  [dbo].[FactInternetSales] PARTITION 2 WITH (TRUNCATE_TARGET = ON);

Para más información sobre cómo volver a crear particiones mediante CTAS, consulte Uso de particiones en el grupo de SQL dedicado.

Contenido relacionado

Para más información sobre el desarrollo de tablas, consulte el artículo sobre desarrollo de tablas.