Optimización del rendimiento con el índice de almacén de columnas agrupado ordenadoPerformance tuning with ordered clustered columnstore index

Cuando los usuarios consultan una tabla del almacén de columnas de Azure SQL Data Warehouse, el optimizador comprueba los valores mínimo y máximo almacenados en cada segmento.When users query a columnstore table in Azure SQL Data Warehouse, the optimizer checks the minimum and maximum values stored in each segment. Los segmentos que están fuera de los límites del predicado de la consulta no se leen del disco a la memoria.Segments that are outside the bounds of the query predicate aren't read from disk to memory. Una consulta puede tener un rendimiento más rápido si el número de segmentos que se van a leer y su tamaño total son pequeños.A query can get faster performance if the number of segments to read and their total size are small.

Índice de almacén de columnas agrupado ordenado frente al no ordenadoOrdered vs. non-ordered clustered columnstore index

De forma predeterminada, para cada tabla de Azure Data Warehouse que se crea sin una opción de índice, un componente interno (generador de índices) crea un índice de almacén de columnas agrupado (CCI) no ordenado.By default, for each Azure Data Warehouse table created without an index option, an internal component (index builder) creates a non-ordered clustered columnstore index (CCI) on it. Los datos de cada columna se comprimen en un segmento de grupo de filas de CCI independiente.Data in each column is compressed into a separate CCI rowgroup segment. Hay metadatos en el intervalo de valores de cada segmento, por lo que los segmentos que están fuera de los límites del predicado de la consulta no se leen desde el disco durante la ejecución de la consulta.There's metadata on each segment’s value range, so segments that are outside the bounds of the query predicate aren't read from disk during query execution. CCI ofrece el máximo nivel de compresión de datos y reduce el tamaño de los segmentos que se van a leer para que las consultas se ejecuten más rápido.CCI offers the highest level of data compression and reduces the size of segments to read so queries can run faster. Sin embargo, dado que el generador de índices no ordena los datos antes de comprimirlos en segmentos, pueden darse segmentos con intervalos de valores superpuestos, lo que hace que las consultas lean más segmentos del disco y tarden más en finalizar.However, because the index builder doesn't sort data before compressing them into segments, segments with overlapping value ranges could occur, causing queries to read more segments from disk and take longer to finish.

Al crear un CCI ordenado, el motor de Azure SQL Data Warehouse ordena los datos existentes en memoria por las claves de orden antes de que el generador de índices los comprima en segmentos de índice.When creating an ordered CCI, the Azure SQL Data Warehouse engine sorts the existing data in memory by the order key(s) before the index builder compresses them into index segments. Con los datos ordenados, se reduce la superposición de segmentos, lo que permite que las consultas tengan una eliminación de segmentos más eficaz y, por tanto, un rendimiento más rápido, ya que el número de segmentos que se leerán desde el disco es menor.With sorted data, segment overlapping is reduced allowing queries to have a more efficient segment elimination and thus faster performance because the number of segments to read from disk is smaller. Si todos los datos se pueden ordenar en memoria de una vez, se puede evitar la superposición de segmentos.If all data can be sorted in memory at once, then segment overlapping can be avoided. Dado el gran tamaño de los datos de las tablas del almacenamiento de datos, este escenario no se produce con frecuencia.Given the large size of data in data warehouse tables, this scenario doesn't happen often.

Para comprobar los intervalos de segmentos de una columna, ejecute este comando con el nombre de la tabla y el nombre de la columna:To check the segment ranges for a column, run this command with your table name and column name:

SELECT o.name, pnp.index_id, cls.row_count, pnp.data_compression_desc, pnp.pdw_node_id, 
pnp.distribution_id, cls.segment_id, cls.column_id, cls.min_data_id, cls.max_data_id, cls.max_data_id-cls.min_data_id as difference
FROM sys.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
   JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table_Name>' and cols.name = '<Column_Name>' 
ORDER BY o.name, pnp.distribution_id, cls.min_data_id

Nota

En una tabla de CCI ordenada, los nuevos datos resultantes del mismo lote de DML o de operaciones de carga de datos se organizan dentro de ese lote, no hay ninguna organización global de todos los datos de la tabla.In an ordered CCI table, the new data resulting from the same batch of DML or data loading operations are sorted within that batch, there is no global sorting across all data in the table. Los usuarios pueden RECOMPILAR el CCI ordenado para ordenar todos los datos de la tabla.Users can REBUILD the ordered CCI to sort all data in the table. En Azure SQL Data Warehouse, la RECOMPILACIÓN del índice de almacén de columnas es una operación sin conexión.In Azure SQL Data Warehouse, the columnstore index REBUILD is an offline operation. En el caso de una tabla con particiones, la RECOMPILACIÓN se realiza en una partición cada vez.For a partitioned table, the REBUILD is done one partition at a time. Los datos de la partición que se está recompilando estarán "sin conexión" y no estarán disponibles hasta que la RECOMPILACIÓN se complete para esa partición.Data in the partition that is being rebuilt is "offline" and unavailable until the REBUILD is complete for that partition.

Rendimiento de consultasQuery performance

La mejora del rendimiento de una consulta desde un CCI ordenado depende de los patrones de consulta, el tamaño de los datos, el grado de orden de los datos, la estructura física de los segmentos y la unidad de almacenamiento de datos y la clase de recurso elegidos para la ejecución de la consulta.A query's performance gain from an ordered CCI depends on the query patterns, the size of data, how well the data is sorted, the physical structure of segments, and the DWU and resource class chosen for the query execution. Los usuarios deben revisar todos estos factores antes de elegir las columnas de ordenación al diseñar una tabla de CCI ordenado.Users should review all these factors before choosing the ordering columns when designing an ordered CCI table.

Las consultas con todos estos patrones suelen ejecutarse más rápido con CCI ordenado.Queries with all these patterns typically run faster with ordered CCI.

  1. Las consultas tienen predicados de igualdad, desigualdad o intervalo.The queries have equality, inequality, or range predicates
  2. Las columnas de predicado y las columnas de CCI ordenado son las mismas.The predicate columns and the ordered CCI columns are the same.
  3. Las columnas de predicado se usan en el mismo orden que el índice de columna de las columnas de CCI ordenado.The predicate columns are used in the same order as the column ordinal of ordered CCI columns.

En este ejemplo, la tabla T1 tiene un índice de almacén de columnas en clúster ordenado en la secuencia Col_C, Col_B y Col_A.In this example, table T1 has a clustered columnstore index ordered in the sequence of Col_C, Col_B, and Col_A.


CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON  T1
ORDER (Col_C, Col_B, Col_A)

El rendimiento de la consulta 1 puede beneficiarse más del CCI ordenado que las otras tres consultas.The performance of query 1 can benefit more from ordered CCI than the other three queries.

-- Query #1: 

SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- Query #2

SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';

-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';

Rendimiento de la carga de datosData loading performance

El rendimiento de la carga de datos en una tabla de CCI ordenado es similar a una tabla con particiones.The performance of data loading into an ordered CCI table is similar to a partitioned table. La carga de datos en una tabla de CCI ordenado puede tardar más que en una tabla de CCI no ordenado debido a la operación de ordenación de datos; sin embargo, posteriormente las consultas podrán ejecutarse más rápidamente con el CCI ordenado.Loading data into an ordered CCI table can take longer than a non-ordered CCI table because of the data sorting operation, however queries can run faster afterwards with ordered CCI.

A continuación se muestra un ejemplo de comparación de rendimiento de la carga de datos en tablas con esquemas diferentes.Here is an example performance comparison of loading data into tables with different schemas.

Performance_comparison_data_loading

A continuación se ofrece una comparación de rendimiento de consultas de ejemplo entre CCI y CCI ordenado.Here is an example query performance comparison between CCI and ordered CCI.

Performance_comparison_data_loading

Reducción de la superposición de segmentosReduce segment overlapping

El número de segmentos superpuestos depende del tamaño de los datos que se van a ordenar, la memoria disponible y el grado máximo de paralelismo (MAXDOP) durante la creación del CCI ordenado.The number of overlapping segments depends on the size of data to sort, the available memory, and the maximum degree of parallelism (MAXDOP) setting during ordered CCI creation. A continuación, se muestran opciones para reducir la superposición de segmentos al crear un CCI ordenado.Below are options to reduce segment overlapping when creating ordered CCI.

  • Use la clase de recurso xlargerc en una unidad de almacenamiento de datos superior para permitir más memoria para la ordenación de datos antes de que el generador de índices comprima los datos en segmentos.Use xlargerc resource class on a higher DWU to allow more memory for data sorting before the index builder compresses the data into segments. Una vez que está en un segmento de índice, no se puede cambiar la ubicación física de los datos.Once in an index segment, the physical location of the data cannot be changed. No hay ningún tipo de organización de datos dentro de un segmento o entre segmentos.There's no data sorting within a segment or across segments.

  • Cree un CCI ordenado con MAXDOP = 1.Create ordered CCI with MAXDOP = 1. Cada subproceso que se usa para la creación del CCI ordenado trabaja en un subconjunto de datos y lo ordena localmente.Each thread used for ordered CCI creation works on a subset of data and sorts it locally. No hay ninguna ordenación global entre los datos ordenados por subprocesos diferentes.There's no global sorting across data sorted by different threads. El uso de subprocesos paralelos puede reducir el tiempo de creación de un CCI ordenado, pero se generarán más segmentos superpuestos que con el uso de un único subproceso.Using parallel threads can reduce the time to create an ordered CCI but will generate more overlapping segments than using a single thread. Actualmente, la opción MAXDOP solo se admite en la creación de una tabla de CCI ordenado con el comando CREATE TABLE AS SELECT.Currently, the MAXDOP option is only supported in creating an ordered CCI table using CREATE TABLE AS SELECT command. La creación de un CCI ordenado mediante los comandos CREATE INDEX o CREATE TABLE no admite la opción MAXDOP.Creating an ordered CCI via CREATE INDEX or CREATE TABLE commands does not support the MAXDOP option. Por ejemplo,For example,

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);
  • Ordene previamente los datos por las claves de ordenación antes de cargarlos en tablas de Azure SQL Data Warehouse.Pre-sort the data by the sort key(s) before loading them into Azure SQL Data Warehouse tables.

El siguiente es un ejemplo de una distribución de tabla de CCI ordenado que no tiene ningún segmento superpuesto tras aplicar las recomendaciones anteriores.Here is an example of an ordered CCI table distribution that has zero segment overlapping following above recommendations. La tabla de CCI ordenada se crea en una base de datos de DWU1000c a través de la instrucción CTAS a partir de una tabla de montón de 20 GB mediante MAXDOP 1 y xlargerc.The ordered CCI table is created in a DWU1000c database via CTAS from a 20-GB heap table using MAXDOP 1 and xlargerc. El CCI se ordena en una columna BIGINT sin duplicados.The CCI is ordered on a BIGINT column with no duplicates.

Segment_No_Overlapping

Creación de un CCI ordenado en tablas grandesCreate ordered CCI on large tables

La creación de un CCI ordenado es una operación sin conexión.Creating an ordered CCI is an offline operation. En el caso de tablas sin particiones, los datos no serán accesibles para los usuarios hasta que se complete el proceso de creación del CCI ordenado.For tables with no partitions, the data won't be accessible to users until the ordered CCI creation process completes. En el caso de tablas con particiones, como el motor crea el CCI ordenado partición a partición, los usuarios todavía pueden acceder a los datos de las particiones en las que la creación del CCI ordenado no está en curso.For partitioned tables, since the engine creates the ordered CCI partition by partition, users can still access the data in partitions where ordered CCI creation isn't in process. Puede usar esta opción para minimizar el tiempo de inactividad durante la creación del CCI ordenado en tablas grandes:You can use this option to minimize the downtime during ordered CCI creation on large tables:

  1. Cree particiones en la tabla grande de destino (llamada Table_A).Create partitions on the target large table (called Table_A).
  2. Cree una tabla de CCI ordenado vacía (llamada Tabla_B) con la misma tabla y esquema de particiones que la tabla A.Create an empty ordered CCI table (called Table_B) with the same table and partition schema as Table A.
  3. Cambie una partición de la tabla A a la tabla B.Switch one partition from Table A to Table B.
  4. Ejecute ALTER INDEX <Índice_CCI_ordenado> ON <Tabla_B> REBUILD PARTITION = <Identificador_de_partición> en la tabla B para volver a generar la partición cambiada.Run ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> on Table B to rebuild the switched-in partition.
  5. Repita los pasos 3 y 4 para cada partición de la tabla A.Repeat step 3 and 4 for each partition in Table_A.
  6. Una vez que todas las particiones se han cambiado de la tabla A a la tabla B y se han vuelto a generar, elimine la tabla A y cambie el nombre de la tabla B a tabla A.Once all partitions are switched from Table_A to Table_B and have been rebuilt, drop Table_A, and rename Table_B to Table_A.

EjemplosExamples

A. Para comprobar las columnas ordenadas y el ordinal del orden:A. To check for ordered columns and order ordinal:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal 
FROM sys.index_columns i 
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0

B. Para cambiar el ordinal de la columna, agregar o eliminar columnas de la lista de ordenación o cambiar de CCI a CCI ordenado:B. To change column ordinal, add or remove columns from the order list, or to change from CCI to ordered CCI:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON  InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON)

Pasos siguientesNext steps

Para obtener más sugerencias sobre desarrollo, consulte la información general sobre desarrollo de SQL Data Warehouse.For more development tips, see SQL Data Warehouse development overview.