次の方法で共有

リンク サーバー間でビューのクエリを実行するときのパフォーマンスの問題

  • [アーティクル]

適用対象: SQL Server

現象

リンクされたサーバー上のビューに対してクエリを実行するとリンク サーバー上のベース テーブルに対して同じクエリを直接実行するよりも、リモートで時間がかかります。

原因

ビューとベース テーブルに対してクエリを実行すると、返される予想される行数の計算に使用されるデータ カーディナリティ推定が原因で、異なる動作が発生します。 ビューのクエリの数は 10,000 の定数値に設定され、基本テーブルに対するクエリの数は 統計情報から派生します。

リンク サーバーによって照会されたベース テーブルのカーディナリティ推定

Microsoft OLE DB Provider (SQLOLEDB) (管理されなくなったもの) または Microsoft® OLE DB Driver 18 for SQL Server® (MSOLEDBSQL) は、どちらもベース テーブルのディストリビューション統計をサポートします。 SQL Server では、通常のクエリと同じ方法で、リンク サーバーの統計とヒストグラムを利用できます。 たとえば、Sales.SalesOrderDetail テーブルを持つAdventureWorks2019 データベースを持つ LS1 というリンク サーバーを作成すると、次のクエリで統計ヒストグラムを利用できます。

SELECT * FROM LS1.AdventureWorks2019.Sales.SalesOrderDetail;
SELECT * FROM LS1.AdventureWorks2019.Sales.SalesOrderDetail WHERE SalesOrderID=43659;

次のグラフィカル クエリ プランは、各リモート クエリで返される推定行数を示しています。

リンク サーバーのテーブルのクエリのスクリーンショット。

リンク サーバーによってクエリされるビューのカーディナリティ推定

ビューに対するリンク サーバー クエリでは、統計ベースのカーディナリティ推定は利用されません。 ビューに対するカーディナリティ推定は、定数値 10,000 です。 次のテストは、これを示しています。

  1. リンク サーバー LS1 のAdventureWorks2019 データベースからSales.SalesOrderDetail テーブルを参照するdbo.View1という名前のビューを作成します。

    USE AdventureWorks2019;
GO
CREATE VIEW [dbo].[view1]
AS
SELECT * FROM Sales.SalesOrderDetail;

  2. ビューのクエリをリモートで実行します。

    SELECT * FROM LS1.AdventureWorks2019.dbo.view1;

  3. クエリのカーディナリティ推定では、正確に 10,000 行 表示されます。

    リンク サーバーのビューのクエリのスクリーンショット。

WHERE 句を使用してリンク サーバーによってクエリされるビューのカーディナリティ推定

リンク サーバー上のビューに対して WHERE 句を使用してクエリを実行する場合、ビューに対するカーディナリティ推定も定数値になります。 ただし、値はデータベースの互換性レベルによって異なります。

Note

カーディナリティ推定値は、ビューが定義されているデータベースの互換性レベルの影響を受けることはありません。

  • データベース互換性レベルが 120 以上の場合 (新しいバージョンのカーディナリティ推定)、カーディナリティ推定は 100 です。
  • データベース互換性レベルが 110 以下 (レガシ カーディナリティ推定機能) の場合、カーディナリティ推定は 1,000 です。

例については、次のクエリを参照してください。

SELECT * FROM LS1.AdventureWorks2019.dbo.view1
WHERE SalesOrderID=43659 
    OPTION (USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));
GO
SELECT * FROM LS1.AdventureWorks2019.dbo.view1 
WHERE SalesOrderID=43659
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

例については、次のクエリのカーディナリティ推定を参照してください。

リンク サーバーのビュー CE のクエリのスクリーンショット。

解決方法

ほとんどの場合、ほとんどのアプリケーション ワークロードは、リンク サーバーからクエリを実行したときにビューのカーディナリティ推定の影響を受けないため、アクションは必要ありません。 ワークロードが影響を受けた場合は、次のいずれかの方法を使用します。

  • ビューを更新し、 インデックス付きビューとして変更します。 インデックス付きビューには、対応する統計を含むインデックスが少なくとも 1 つ含まれます。 インデックス付きビューを作成してクエリを実行するには、次のクエリを参照してください。

    Note

    このメソッドは、基になるデータを表す実際の統計を公開します。

    --Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
   QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
IF OBJECT_ID ('Sales.vOrderDetails1', 'view') IS NOT NULL
   DROP VIEW Sales.vOrderDetails1;
GO
--Create view with schemabinding
CREATE VIEW Sales.vOrderDetails1
   WITH SCHEMABINDING
   AS  
      SELECT SalesOrderID, 
       SalesOrderDetailID, 
       CarrierTrackingNumber, 
       OrderQty, 
       ProductID,
       UnitPrice,
       UnitPriceDiscount,
       LineTotal,
       ModifiedDate
      FROM Sales.SalesOrderDetail
GO
--Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
   ON Sales.vOrderDetails1 (SalesOrderID, ProductID);
--Select from the materialized view
SELECT * FROM LS1.adventureworks2019.Sales.vOrderDetails1;

    例については、次のクエリのカーディナリティ推定を参照してください。

    リンク サーバーのインデックス付きビューのクエリのスクリーンショット。

  • WHERE句を使用してクエリを実行する場合は、Query Hint OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) を使用してカーディナリティ推定を増やすことができます。 この方法は、特に大きなテーブルと結合する場合に役立つ場合があります。

    Note

    このメソッドは、ビューが多数の行を公開する場合に適しています。

  • 必要に応じて、リンク サーバー上のビューではなく、ベース テーブルに対して直接クエリを実行します。

関連項目