PolyBase 中適用於 SQL Server 的效能考量

  • 文章

適用於：SQL Server 2016 (13.x) - Windows 和更新版本SQL Server 2017 (14.x) - Linux 和更新版本 Azure Synapse Analytics

在適用於 SQL Server 的 PolyBase 中，檔案數目或可查詢的資料量沒有硬性限制。 查詢效能取決於資料量、資料格式、資料組織方式，以及查詢和聯結的複雜性。

本文涵蓋重要的效能主題和指導。

統計資料

收集外部資料的統計資料，是最佳化查詢的最重要工作之一。 執行個體愈了解您的資料，執行查詢的速度就愈快。 SQL 引擎查詢最佳化工具是以成本為基礎的最佳化工具。 它會比較各種查詢方案的成本，然後選擇成本最低的方案。 在大多數情況下，它會選擇執行最快的方案。

自動建立統計資料

從 SQL Server 2022 開始，資料庫引擎會分析傳入的使用者查詢是否有遺漏的統計資料。 如果缺少統計資料，查詢最佳化工具會自動在查詢述詞或聯結條件中的個別資料行上建立統計資料，以改善查詢計畫的基數估計值。 自動建立統計資料是同步進行的，因此，如果資料行缺少統計資料，可能會觀測到查詢效能略為降低。 為單一資料行建立統計資料的時間，取決於目標檔案的大小。

建立 OPENROWSET 手動統計資料

您可以使用 sys.sp_create_openrowset_statistics 預存程序來建立 OPENROWSET 路徑的單一資料行統計資料，方法是使用單一資料行作為參數，傳遞所選取查詢：

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

根據預設，執行個體會使用資料集中提供的 100% 資料來建立統計資料。 您可以選擇性地使用 TABLESAMPLE 選項，將取樣大小指定為百分比。 若要為多個資料行建立單一資料行統計資料，請針對每個資料行執行 sys.sp_create_openrowset_statistics。 您無法為 OPENROWSET 路徑建立多資料行統計資料。

若要更新現有的統計資料，請先使用 sys.sp_drop_openrowset_statistics 預存程序卸除統計資料，然後使用 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 
';

建立外部表格手動統計資料

在外部表格上建立統計資料的語法，類似於用於一般使用者資料表的語法。 若要建立資料行的統計資料，請提供統計資料物件名稱和資料行名稱：

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

WITH 選項是必要項目，而且針對取樣大小的允許選項為 FULLSCAN 和 SAMPLE n PERCENT。

  • 若要為多個資料行建立單一資料行統計資料，請針對每個資料行執行 CREATE STATISTICS。
  • 不支援多重資料行統計資料。

查詢分割區的資料

資料的組織方式通常會分成不同的子資料夾，也稱為分割區。 您可以指示 SQL Server 執行個體僅查詢特定資料夾和檔案。 這麼做會減少查詢需要讀取和處理的檔案數目和資料量，進而提升效能。 這種類型的查詢最佳化稱為資料分割剪除或資料分割刪除。 您可以在查詢的 filepath() 子句中使用中繼資料函式 WHERE，這樣就不需要對分割區執行查詢。

首先，建立外部資料來源：

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

下列範例查詢只會讀取 2017 年最後三個月的 NYC Yellow Taxi 資料檔案：

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;

如果您的儲存資料未分割，請考慮進行資料分割以提升查詢效能。

如果您使用外部表格，filepath() 和 filename() 功能不受支援，但不在 WHERE 子句中。 如果在計算資料列中使用，則仍然可按 filename 或 filepath 篩選。 下列範例為其示範：

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

如果您的儲存資料未分割，請考慮進行資料分割以提升查詢效能。

將計算推送到 Hadoop

適用於僅限 SQL Server 2016 (13.x)、SQL Server 2017 (14.x) 和 SQL Server 2019 (15.x)

PolyBase 會將某些計算推送到外部來源來將整體查詢最佳化。 查詢最佳化工具會做出成本型決策，以將計算推送到 Hadoop (在那麼做可以改善查詢效能的前提下)。 查詢最佳化工具會使用外部表格上的統計資料來做出成本型決策。 推送計算會建立 MapReduce 工作，並利用 Hadoop 的分散式運算資源。 如需詳細資訊，請參閱 PolyBase 中的下推計算。

調整計算資源

適用於僅限 SQL Server 2016 (13.x)、SQL Server 2017 (14.x) 和 SQL Server 2019 (15.x)

若要改善查詢效能，您可以使用 SQL Server PolyBase 向外延展群組。 這可啟用 SQL Server 執行個體與 Hadoop 節點之間的平行資料傳輸，而且會加入在外部資料上運作的計算資源。

重要

Microsoft SQL Server PolyBase 向外延展群組將會淘汰。 向外延展群組功能將從 SQL Server 2022 (16.x) 產品移除。 PolyBase 資料虛擬化將在 SQL Server 作為向上擴充的功能來繼續受到完整支援。 如需詳細資訊，請參閱 Microsoft SQL Server 平台的巨量資料選項。

相關內容