Considérations relatives aux performances dans PolyBase pour SQL Server

  • Article

S’applique à : SQL Server 2016 (13.x) - Windows et versions ultérieures SQL Server 2017 (14.x) - Linux et versions ultérieures Azure Synapse Analytics

Dans PolyBase pour SQL Server, il n’existe aucune limite stricte au nombre de fichiers ou à la quantité de données pouvant être interrogées. Les performances des requêtes dépendent de la quantité de données, du format des données, de la façon dont les données sont organisées et de la complexité des requêtes et des jointures.

Cet article traite des rubriques et conseils importants sur les performances.

Statistiques

La collecte de statistiques sur vos données externes est l’une des actions les plus importantes pour optimiser vos requêtes. Plus l’instance a d’informations sur vos données, plus elle peut exécuter de requêtes rapidement. L’optimiseur de requête du moteur SQL est un optimiseur basé sur les coûts. Il compare le coût de différents plans de requête, puis choisit le plan avec le coût le plus bas. Dans la plupart des cas, il choisit le plan qui s'exécute le plus rapidement.

Création automatique de statistiques

À compter de SQL Server 2022, le Moteur de base de données analyse les requêtes utilisateur entrantes pour obtenir des statistiques manquantes. Si des statistiques manquent, l’optimiseur de requête crée automatiquement des statistiques sur des colonnes individuelles dans le prédicat de requête ou la condition de jointure afin d’améliorer les estimations de cardinalité pour le plan de requête. La création automatique de statistiques est effectuée de manière synchrone afin que vous puissiez observer des performances de requête légèrement dégradées si vos colonnes sont manquantes. La durée de création de statistiques pour une seule colonne dépend de la taille des fichiers ciblés.

Créer des statistiques manuelles OPENROWSET

Les statistiques à colonne unique pour le chemin OPENROWSET peuvent être créées à l’aide de la sys.sp_create_openrowset_statistics procédure stockée, en passant la requête select avec une seule colonne en tant que paramètre :

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';

Par défaut, l’instance utilise 100 % des données fournies dans le jeu de données pour créer des statistiques. Vous pouvez éventuellement spécifier la taille de l’échantillon sous forme de pourcentage à l’aide des options TABLESAMPLE. Pour créer des statistiques à colonne unique pour plusieurs colonnes, exécutez sys.sp_create_openrowset_statistics chacune des colonnes. Vous ne pouvez pas créer de statistiques à plusieurs colonnes pour le chemin OPENROWSET.

Pour mettre à jour les statistiques existantes, supprimez-les d’abord à l’aide de la procédure stockée sys.sp_drop_openrowset_statistics, puis recréez-les à l’aide de 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 
';

Créer des statistiques manuelles de table externe

La syntaxe pour créer des statistiques sur des tables externes est semblable à celle utilisée pour les tables utilisateur ordinaires. Pour créer des statistiques sur une colonne, indiquez le nom de l’objet de statistiques, ainsi que celui de la colonne :

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

Les WITH options sont obligatoires et, pour la taille de l’échantillon, les options autorisées sont FULLSCAN et SAMPLE n PERCENT.

  • Pour créer des statistiques à colonne unique pour plusieurs colonnes, exécutez CREATE STATISTICS chacune des colonnes.
  • Les statistiques sur plusieurs colonnes ne sont pas prises en charge.

Interroger des données partitionnées

Les données sont souvent organisées dans des sous-dossiers, aussi appelés partitions. Vous pouvez demander à l’instance SQL Server d’interroger uniquement des dossiers et des fichiers particuliers. En procédant ainsi, vous réduisez le nombre de fichiers et la quantité de données que la requête doit lire et traiter, et augmentez les performances. Ce type d’optimisation de requête est appelé taille de partition ou élimination de partition. Vous pouvez éliminer les partitions de l’exécution de requête à l’aide de la fonction filepath() de métadonnées dans la WHERE clause de la requête.

Tout d’abord, créez une source de données externe :

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

L’exemple de requête suivant lit les fichiers de données NYC Yellow Taxi uniquement pour les trois derniers mois 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 vos données stockées ne sont pas partitionnées, envisagez de les partitionner pour améliorer les performances des requêtes.

Si vous utilisez des tables externes et filename() que les fonctions sont prises en charge, filepath() mais pas dans la WHERE clause. Vous pouvez toujours filtrer par filename ou filepath si vous les utilisez dans des colonnes calculées. Cela est illustré par l'exemple suivant :

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 vos données stockées ne sont pas partitionnées, envisagez de les partitionner pour améliorer les performances des requêtes.

Envoyer (push) le calcul vers Hadoop

S’applique à SQL Server 2016 (13.x), SQL Server 2017 (14.x) et SQL Server 2019 (15.x) uniquement

Polybase transmet certains calculs à la source externe pour optimiser la requête globale. En fonction des coûts, l’optimiseur de requête prend la décision de déléguer les calculs à Hadoop, si cela permet d’améliorer les performances des requêtes. L’optimiseur de requête utilise des statistiques sur des tables externes pour prendre la décision basée sur les coûts. La délégation des calculs a pour effet de créer des tâches MapReduce et de tirer parti des ressources de calcul distribuées de Hadoop. Pour plus d’informations, consultez Calculs pushdown dans Polybase.

Mettre à l’échelle des ressources de calcul

S’applique à SQL Server 2016 (13.x), SQL Server 2017 (14.x) et SQL Server 2019 (15.x) uniquement

Pour améliorer les performances des requêtes, vous pouvez utiliser des groupes de scale-out PolyBaseSQL Server. Cela autorise un transfert de données en parallèle entre les instances SQL Server et les nœuds Hadoop, et cela ajoute des ressources de calcul qui permettent d’exploiter les données externes.

Important

Les groupes de montée en puissance parallèle Microsoft SQL Server PolyBase seront mis hors service. La fonctionnalité de groupe de scale-out sera supprimée du produit dans SQL Server 2022 (16.x). La virtualisation des données PolyBase continue d’être entièrement prise en charge en tant que fonctionnalité de scale-up dans SQL Server. Pour plus d’informations, consultez Options Big data sur la plateforme Microsoft SQL Server.

Contenu connexe