Consideraciones de rendimiento en PolyBase para SQL Server

  • Artículo

Se aplica a: SQL Server 2016 (13.x): Windows y versiones posteriores SQL Server 2017 (14.x): Linux y versiones posteriores Azure Synapse Analytics

En PolyBase para SQL Server, no hay límites estrictos para el número de archivos o la cantidad de datos que se pueden consultar. El rendimiento de las consultas depende de la cantidad de datos, el formato de los datos, la forma en que se organizan los datos y la complejidad de las consultas y las combinaciones.

En este artículo se tratan temas e instrucciones importantes sobre el rendimiento.

Estadísticas

La recopilación de estadísticas de los datos externos es una de las cosas más importantes que puede hacer para la optimización de las consultas. Cuanto más sepa la instancia sobre los datos, más rápido puede ejecutar consultas. El optimizador de consultas del motor de SQL está basado en el costo. Compara el costo de varios planes de consulta y elige el menor de ellos. En la mayoría de los casos, elige el plan que se ejecuta más rápidamente.

Creación automática de estadísticas

A partir de SQL Server 2022, el Motor de base de datos analiza las consultas de usuario entrantes para las estadísticas que faltan. Si faltan las estadísticas, el optimizador de consultas crea automáticamente las estadísticas en columnas individuales en el predicado de consulta o en la condición de combinación para mejorar las estimaciones de cardinalidad del plan de consulta. La creación automática de estadísticas se realiza de forma sincrónica, por lo que puede sufrir una ligera degradación del rendimiento de consulta si a las columnas les faltan estadísticas. El tiempo necesario para crear estadísticas de una sola columna depende del tamaño de los archivos de destino.

Crear estadísticas manuales de OPENROWSET

Las estadísticas de columna única para la ruta de acceso OPENROWSET se pueden crear con el procedimiento almacenado sys.sp_create_openrowset_statistics pasando la consulta de selección con una columna única como parámetro:

EXEC sys.sp_create_openrowset_statistics N' 
SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows';

De manera predeterminada, la instancia utiliza el 100 % de los datos proporcionados en el conjunto de datos para crear estadísticas. También puede especificar opcionalmente el tamaño de la muestra como un porcentaje con las opciones TABLESAMPLE. A fin de crear estadísticas de columna única para varias columnas, ejecute sys.sp_create_openrowset_statistics para cada una de las columnas. No se pueden crear estadísticas de varias columnas para la ruta de acceso OPENROWSET.

Si desea actualizar las estadísticas existentes, anúlelas primero con el procedimiento almacenado sys.sp_drop_openrowset_statistics y, luego, vuelva a crearlas con sys.sp_create_openrowset_statistics:

EXEC sys.sp_drop_openrowset_statistics 
N'SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows 
';

Crear estadísticas manuales de tablas externas

La sintaxis para crear estadísticas en tablas externas es similar a la que se utiliza para las tablas de usuario normales. Para crear estadísticas en una columna, indique un nombre para el objeto de estadísticas y el nombre de la columna:

CREATE STATISTICS sVendor 
ON tbl_TaxiRides (vendorID) 
WITH FULLSCAN, NORECOMPUTE;

Las opciones WITH son obligatorias y, para el tamaño de la muestra, las opciones permitidas son FULLSCAN y SAMPLE n PERCENT.

  • A fin de crear estadísticas de columna única para varias columnas, ejecute CREATE STATISTICS para cada una de las columnas.
  • No se admiten estadísticas de varias columnas.

Consulta de datos con particiones

Los datos a menudo se organizan en subcarpetas también denominadas particiones. Puede indicar a la instancia de SQL Server que consulte solo carpetas y archivos concretos. De este modo, se reduce el número de archivos y la cantidad de datos que la consulta tiene que leer y procesar, lo que proporciona un mejor rendimiento. Este tipo de optimización de consultas se conoce como eliminación de particiones. Puede eliminar las particiones de la ejecución de consultas mediante el uso de la función de metadatos filepath() en la cláusula WHERE de la consulta.

En primer lugar, cree un origen de datos externo:

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net'
);
GO

En el ejemplo de consulta siguiente se leen archivos de datos de NYC Yellow Taxi solo para los últimos tres meses de 2017:

SELECT 
    r.filepath() AS filepath 
    ,r.filepath(1) AS [year] 
    ,r.filepath(2) AS [month] 
    ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
        BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
        DATA_SOURCE = 'NYCTaxiExternalDataSource', 
        FORMAT = 'parquet' 
    ) 
WITH ( 
    vendorID INT 
) AS [r] 
WHERE 
    r.filepath(1) IN ('2017') 
    AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
    r.filepath() 
    ,r.filepath(1) 
    ,r.filepath(2) 
ORDER BY filepath;

Si los datos almacenados no tienen particiones, considere la posibilidad de crear particiones para mejorar el rendimiento de las consultas.

Si usa tablas externas, se admiten las funciones filepath() y filename() pero no en la cláusula WHERE. Todavía puede filtrar por filename o filepath si se usan en columnas calculadas. En el siguiente ejemplo se muestra esto:

CREATE EXTERNAL TABLE tbl_TaxiRides ( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT, 
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning 
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
); 
GO 
 
SELECT * 
      FROM tbl_TaxiRides 
WHERE 
      [year]=2017             
      AND [month] in (10,11,12);

Si los datos almacenados no tienen particiones, considere la posibilidad de crear particiones para mejorar el rendimiento de las consultas.

Inserción de cálculo en Hadoop

Solo se aplica a SQL Server 2016 (13.x), SQL Server 2017 (14.x) y SQL Server 2019 (15.x).

PolyBase inserta algunos cálculos en el origen externo para optimizar la consulta global. El optimizador de consultas toma una decisión basada en costos para insertar cálculo en Hadoop si, al hacerlo, se va a mejorar el rendimiento de las consultas. El optimizador de consultas usa estadísticas relativas a las tablas externas para tomar la decisión basada en costos. La inserción de cálculo crea trabajos MapReduce y aprovecha los recursos de cálculo distribuidos de Hadoop. Para obtener más información, consulte Cálculos de la aplicación en PolyBase.

Escalado de los recursos de proceso

Solo se aplica a SQL Server 2016 (13.x), SQL Server 2017 (14.x) y SQL Server 2019 (15.x).

Para mejorar el rendimiento de las consultas, puede usar grupos de escalado horizontal de PolyBasede SQL Server. Gracias a esto, la transferencia de datos paralelos entre instancias de SQL Server y nodos de Hadoop es factible y, además, se agregan recursos de cálculo para operar en los datos externos.

Importante

Los grupos de escalado horizontal de PolyBase de Microsoft SQL Server se retirarán. La funcionalidad de los grupos de escalabilidad horizontal se quitará del producto en SQL Server 2022 (16.x). La virtualización de datos de PolyBase seguirá siendo totalmente compatible como una característica de escalado vertical en SQL Server. Para obtener más información, consulte Opciones de macrodatos en la plataforma Microsoft SQL Server.

Contenido relacionado