Consideraciones de rendimiento en PolyBase para SQL Server
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 STATISTICSpara cada una de las columnas. - No se admiten estadísticas de varias columnas.
Consulta de datos con particiones
Se aplica a: Azure SQL Managed Instance y Azure Synapse Analytics.
Cuando los datos se organizan en carpetas o archivos (también denominados particiones), use la eliminación de particiones para consultar solo carpetas y archivos específicos. La eliminación de particiones 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.
Para eliminar las particiones de la ejecución de consultas, use 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.