Partage via

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 étant effectuée de façon synchrone, les performances des requêtes risquent observer une légère détérioration si des statistiques manquent dans vos colonnes. 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 à une seule colonne pour le chemin OPENROWSET peuvent être créées à l’aide de la procédure stockée sys.sp_create_openrowset_statistics, 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 également spécifier la taille de l’échantillon en pourcentage à l’aide des options TABLESAMPLE. Pour créer des statistiques de colonne unique pour plusieurs colonnes, exécutez sys.sp_create_openrowset_statistics pour chacune des colonnes. Vous ne pouvez pas créer de statistiques sur 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 des tables externes

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 options WITH sont obligatoires et, pour la taille de l’échantillon, les options autorisées sont FULLSCAN et SAMPLE n PERCENT.

  • Pour créer des statistiques de colonne unique pour plusieurs colonnes, exécutez CREATE STATISTICS pour 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 connu sous le nom de nettoyage de partition ou d’élimination de partition. Vous pouvez éliminer les partitions de l’exécution de la requête à l’aide de la fonction de métadonnées filepath() dans la clause WHERE de la requête.

Dans un premier temps, 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, les fonctions filepath() et filename() sont prises en charge, mais pas dans la clause WHERE. 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.

Déléguer les calculs à 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