Procedimientos recomendados para grupos de SQL dedicados en Azure Synapse Analytics

Sugerencia

Microsoft Fabric Data Warehouse es un almacenamiento relacional de escala empresarial en una base de lago de datos, con una arquitectura lista para el futuro, inteligencia artificial integrada y nuevas características. Si no está familiarizado con el almacenamiento de datos, comience con Fabric Data Warehouse. Las cargas de trabajo del grupo dedicado de SQL pueden actualizarse a Fabric para acceder a funcionalidades avanzadas en ciencia de datos, análisis en tiempo real e informes.

En este artículo se proporciona una colección de procedimientos recomendados que le ayudarán a conseguir un rendimiento óptimo con los grupos de SQL dedicados en Azure Synapse Analytics. Si trabaja con un grupo de SQL sin servidor, consulte Procedimientos recomendados para grupos de SQL sin servidor para obtener instrucciones específicas. A continuación, encontrará instrucciones básicas y áreas importantes en las que centrar su atención durante la creación de la solución. En cada sección se presenta un concepto y se le indican artículos que lo desarrollan más en detalle.

Carga de grupos de SQL dedicados

Puede encontrar una guía sobre la carga de grupos de SQL dedicados en Guía para cargar datos.

Menos costos gracias a las características de pausa y escalado

Para obtener más información acerca de cómo reducir los costos mediante la pausa y el escalado, consulte Administración de procesos.

Mantenimiento de estadísticas

El grupo de SQL dedicado se puede configurar para detectar y crear automáticamente estadísticas en columnas. Los planes de consulta que crea el optimizador son igual de buenos que las estadísticas disponibles.

Se recomienda habilitar AUTO_CREATE_STATISTICS para las bases de datos y mantener actualizadas las estadísticas a diario o después de cada carga. Así, se asegurará de que las estadísticas de las columnas utilizadas en las consultas siempre estén actualizadas.

Para acortar el tiempo de mantenimiento de las estadísticas, sea selectivo sobre qué columnas tienen estadísticas o cuáles necesitan actualizaciones más frecuentes. Por ejemplo, debería actualizar las columnas de fecha en las que se añaden valores todos los días. Céntrese en tener estadísticas sobre las columnas que intervienen en combinaciones, las columnas que se usan en la cláusula WHERE y las columnas que se encuentran en GROUP BY.

Puede encontrar información adicional sobre las estadísticas en los artículos Administración de estadísticas de tabla, CREATE STATISTICS y UPDATE STATISTICS.

Ajustar rendimiento de consulta

Agrupe las instrucciones INSERT en lotes

Una carga única en una tabla pequeña con una instrucción INSERT como INSERT INTO MyLookup VALUES (1, 'Type 1') puede ser el mejor enfoque en función de sus necesidades. Sin embargo, si necesita cargar miles o millones de filas a lo largo del día, es posible que las instrucciones INSERT individuales no sean óptimas.

Una manera de solucionar este problema consiste en desarrollar un proceso que escriba en un archivo y, a continuación, otro proceso para cargar periódicamente este archivo. Consulte el artículo sobre INSERT para más información.

Uso de PolyBase para cargar y exportar los datos rápidamente

El grupo de SQL dedicado admite la carga y exportación de datos con varias herramientas, como Azure Data Factory, PolyBase y BCP. Para pequeñas cantidades de datos donde el rendimiento no es clave, cualquier herramienta le sirve.

Nota

PolyBase es la mejor opción para cargar o exportar grandes volúmenes de datos o si se necesita un rendimiento rápido.

Las cargas de PolyBase se pueden ejecutar utilizando CTAS o INSERT INTO. CTAS reducirá el registro de transacciones y es la manera más rápida de cargar datos. Azure Data Factory también admite cargas de PolyBase y puede lograr un rendimiento parecido a CTAS. PolyBase admite varios formatos de archivo, como Gzip.

Con el fin de conseguir un mayor rendimiento al usar archivos de texto Gzip, divídalos en 60 o más archivos para aumentar el paralelismo de la carga. Para conseguir un rendimiento total más rápido, cargue los datos simultáneamente. En los siguientes artículos se incluye información adicional pertinente para esta sección:

Carga y consulta de tablas externas

PolyBase no es óptimo para las consultas. Las tablas de PolyBase para grupos de SQL dedicados solo admiten actualmente archivos de blobs de Azure y almacenamiento de Azure Data Lake. Estos archivos no tienen ningún recurso de proceso que los respalde. Como resultado, los grupos de SQL dedicados no pueden delegar este trabajo y deben cargar el archivo completo en tempdb para poder leer los datos.

Si tiene varias consultas para acceder a estos datos, es mejor cargarlos una vez y dejar que las consultas usen la tabla local. Se incluyen más instrucciones sobre PolyBase en el artículo Guía de uso de PolyBase.

Distribuir tablas grandes usando Hash

De forma predeterminada, las tablas se distribuyen según el patrón Round Robin. Esta opción predeterminada facilita a los usuarios la tarea de comenzar a crear tablas sin tener que decidir sobre la distribución. Las tablas Round Robin pueden proporcionar un rendimiento suficiente para algunas cargas de trabajo. Sin embargo, en la mayoría de los casos, una columna de distribución ofrece un mejor rendimiento.

El ejemplo más común de una tabla distribuida por una columna que supera en rendimiento a una tabla round robin es cuando se unen dos grandes tablas de hechos.

Por ejemplo, si tiene una tabla de pedidos que se distribuye por order_id, y una tabla de transacciones que también se distribuye por order_id, cuando se una la tabla de pedidos a la de transacciones en order_id, esta consulta se convertirá en una consulta de paso a través. A continuación, se eliminan las operaciones de movimiento de datos. Menos pasos suponen consultas más rápidas. Menos movimiento de datos también se traduce en consultas más rápidas.

Sugerencia

Al cargar una tabla distribuida, los datos entrantes no se deben ordenar con la clave de distribución. Hacerlo ralentizará tus cargas.

Los vínculos a los artículos que se proporcionan a continuación le ofrecerán más detalles sobre cómo mejorar el rendimiento al seleccionar una columna de distribución. Además, encontrará información sobre cómo definir una tabla distribuida en la cláusula WITH de la instrucción CREATE TABLE:

Sin particiones excesivas

Crear particiones de datos puede resultar eficaz para el mantenimiento de los datos mediante la eliminación de particiones o la optimización de escaneos, pero el exceso de particiones puede ralentizar las consultas. A menudo, una estrategia de creación de particiones con granularidad alta que puede funcionar bien en SQL Server no funciona correctamente en el grupo de SQL dedicado.

El exceso de particiones puede reducir la eficacia de los índices de almacén de columnas agrupadas si cada partición tiene menos de 1 millón de filas. Los grupos de SQL dedicados particionan automáticamente los datos en 60 bases de datos. Por lo tanto, si crea una tabla con 100 particiones, el resultado serán 6000 particiones. Cada carga de trabajo es diferente, así que lo mejor es probar las particiones para ver qué funciona mejor para su carga.

Una opción que debe tener en cuenta consiste en usar una granularidad inferior a la que estaba implementada al usar SQL Server. Por ejemplo, puede usar particiones semanales o mensuales, en lugar de particiones diarias.

Encontrará más información sobre las particiones en el artículo Creación de particiones de tablas.

Reducción del tamaño de las transacciones

Las instrucciones SELECT, UPDATE y DELETE se ejecutan en una transacción. Cuando fallan, deben revertirse. Para que la reversión no se tarde tanto, minimice el tamaño de las transacciones siempre que pueda. Minimizar el tamaño de las transacciones puede lograrse dividiendo las sentencias INSERT, UPDATE y DELETE en partes. Por ejemplo, si tiene una instrucción INSERT que espera que tarde 1 hora, puede dividirla en cuatro partes. Cada ejecución durará 15 minutos.

Sugerencia

Utilice casos especiales de registro mínimo, como CTAS, TRUNCATE, DROP TABLE, o INSERT para vaciar las tablas, reduciendo así el riesgo de reversión.

Otra manera de eliminar reversiones es usar operaciones de solo metadatos, como el cambio de particiones para la administración de datos. Por ejemplo, en lugar de ejecutar una instrucción DELETE para eliminar todas las filas de una tabla en la que order_date fue en octubre de 2001, podría particionar los datos mensualmente. A continuación, puede cambiar la partición con datos por una partición vacía de otra tabla (consulte ejemplos de ALTER TABLE).

En el caso de tablas que no están particionadas, puede usar CTAS en lugar de DELETE para escribir los datos que quiera mantener en una tabla. Si un CTAS tarda lo mismo en ejecutarse, es mucho más seguro ya que su registro de transacciones es mínimo y se puede cancelar rápidamente si es necesario.

En los artículos siguientes se incluye más información sobre el contenido relacionado con esta sección:

Reducción del tamaño de los resultados de consultas

Reducir los tamaños de los resultados de consulta le ayuda a evitar problemas en el entorno del cliente debido a resultados de gran tamaño. Puede editar la consulta para reducir el número de filas devueltas. Algunas herramientas de generación de consultas le permiten agregar la sintaxis de "primeros N" a cada consulta. También puede aplicar una instrucción CETAS al resultado de la consulta en una tabla temporal y, a continuación, usar la exportación de PolyBase para el procesamiento de nivel inferior.

Utilice el tamaño de columna más pequeño posible

Al definir su DDL, utilice el tipo de datos más pequeño que sea compatible con sus datos, ya que esto mejorará el rendimiento de las consultas. Esta recomendación tiene especial importancia para las columnas CHAR y VARCHAR. Si el valor mayor máximo de una columna es 25 caracteres, defina la columna como VARCHAR(25). Evite definir todas las columnas de caracteres con una longitud predeterminada de gran tamaño. Además, defina las columnas como VARCHAR en lugar de NVARCHAR cuando no se necesite nada más.

Consulte los artículos Información general sobre tablas, Tipos de datos de tabla y CREATE TABLE para una revisión más detallada de los conceptos esenciales relacionados con la información anterior.

Uso de tablas de apilamiento temporal para datos transitorios

Cuando almacene temporalmente datos en grupos de SQL dedicados, las tablas heap normalmente harán que el proceso en general sea más rápido. Si va a cargar datos solo para almacenarlos temporalmente antes de ejecutar más transformaciones, será más rápido cargar la tabla en una tabla de montón que cargar los datos en una tabla agrupada de almacén de columnas.

Cargar datos en una tabla temporal se realizará mucho más rápido que cargar datos en una tabla de almacenamiento permanente. Las tablas temporales empiezan por "#" y solo se puede acceder a ellas desde la sesión en la que se crean. Por lo tanto, solo pueden funcionar en escenarios limitados. Las tablas heap se definen en la cláusula WITH de CREATE TABLE. Si usa una tabla temporal, no olvide crear estadísticas en ella también.

Para obtener más información, consulte los artículos Tablas temporales, CREATE TABLE y CREATE TABLE AS SELECT.

Optimización de tablas de almacén de columnas agrupadas

Los índices de almacén de columnas agrupados son una de las maneras más eficaces de almacenar los datos en el grupo de SQL dedicado. De forma predeterminada, las tablas del grupo dedicado de SQL se crean como almacenamiento de columnas agrupadas. Para obtener un rendimiento óptimo en las consultas sobre tablas de almacén de columnas, es importante contar con una buena calidad de segmentos. 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.

La calidad de los segmentos se puede medir por el número de filas en un grupo de filas comprimido. Para obtener instrucciones detalladas acerca de la detección y mejora de la calidad de los segmentos en las tablas de almacén de columnas agrupadas, consulte la sección Causas de una calidad deficiente del índice de almacén de columnas del artículo sobre Indexación de tablas.

Como es importante que los segmentos de almacén de columnas sean de una buena calidad, es conveniente usar identificadores de usuario que se encuentren en la clase de recursos grande o mediana para cargar los datos. El uso de unidades de almacenamiento de datos inferiores significa que desea asignar una clase de recurso mayor para el usuario que realiza la carga.

Normalmente, las tablas de Columnstore no envían datos a un segmento comprimido de almacén de columnas hasta que haya más de 1 millón de registros por tabla. Cada tabla de grupo de SQL dedicada se distribuye en 60 distribuciones diferentes. Por lo tanto, las tablas de almacén de columnas no beneficiarán a una consulta, a menos que la tabla tenga más de 60 millones de filas.

Sugerencia

En el caso de las tablas con menos de 60 millones de filas, un índice de almacén de columnas puede no ser la solución óptima.

Si particiona sus datos, cada partición deberá tener 1 millón de filas para beneficiarse de un índice columnstore agrupado. En el caso de una tabla con 100 particiones, necesita tener al menos 6 mil millones de filas para beneficiarse de un almacenamiento de columnas agrupadas (60 distribuciones 100 particiones 1 millón de filas).

Si la tabla no tiene 6 mil millones filas, tiene dos opciones principales. Reduzca el número de particiones o considere la posibilidad de usar una tabla de montón en su lugar. También podría valer la pena experimentar para ver si se obtiene un mejor rendimiento mediante una tabla de montón con índices secundarios, en lugar de una tabla de almacén de columnas.

Al consultar una tabla de almacén de columnas, las consultas se ejecutarán más rápido si selecciona solo las que necesita. Más información sobre los índices de tabla y de almacén de columnas puede encontrarse en los siguientes artículos:

Uso de clases de recursos más grandes para mejorar el rendimiento de las consultas

Los grupos de SQL usan grupos de recursos para asignar memoria a las consultas. Inicialmente, todos los usuarios se asignan a los recursos de la clase pequeña, que concede 100 MB de memoria por distribución. Siempre hay 60 distribuciones. Cada distribución recibe un mínimo de 100 MB. La asignación de memoria total en todo el sistema es de 6000 MB o poco menos de 6 GB.

Algunas consultas, como las combinaciones grandes o las cargas a las tablas de columnstore agrupadas, se beneficiarán de asignaciones de memoria más grandes. Algunas consultas, como los escaneos puros, no verán ningún beneficio. El uso de clases de recursos más grandes afecta a la simultaneidad. Por lo tanto, deberá tener en cuenta estos hechos antes de mover a todos sus usuarios a una clase de recursos grande.

Para obtener más información acerca de las clases de recursos, consulte el artículo sobre clases de recursos y administración de cargas de trabajo.

Uso de clases de recursos más pequeñas para aumentar la simultaneidad

Si observa retrasos prolongados en las consultas de usuario, es posible que los usuarios estén ejecutándose en clases de recursos más grandes. Esta situación promueve el consumo de espacios de simultaneidad, que puede ocasionar la puesta en cola de otras consultas. Para determinar si hay consultas de usuarios en cola, ejecute SELECT * FROM sys.dm_pdw_waits para ver si se devuelve alguna fila.

Los artículos Clases de recursos para la administración de cargas de trabajo y sys.dm_pdw_waits le proporcionarán más información.

Utiliza las vistas de administración dinámica (DMVs) para supervisar y optimizar tus consultas

Los grupos de SQL dedicados tienen varias DMVs que sirven para supervisar la ejecución de las consultas. El siguiente artículo de supervisión le guía con instrucciones paso a paso acerca de cómo revisar los detalles de una consulta en curso. Usar la opción LABEL con las consultas puede ayudar a encontrar rápidamente las consultas en estas DMV. Para obtener información detallada adicional, consulte los artículos incluidos en la siguiente lista:

Contenido relacionado

Consulte también el artículo sobre Solución de problemas para conocer los problemas comunes y sus soluciones.

Si necesita información que no se proporciona en este artículo, busque en la página de preguntas y respuestas de Microsoft para Azure Synapse, donde puede plantear preguntas a otros usuarios y al grupo de productos de Azure Synapse Analytics.

Supervisamos continuamente este foro para garantizar que sus preguntas las responde otro usuario o alguno de nosotros. Si prefiere formular sus preguntas en Stack Overflow, también tenemos un foro de Stack Overflow acerca de Azure Synapse Analytics.

  • Last updated on