연결된 서버에서 뷰를 쿼리할 때의 성능 문제

적용 대상: SQL Server

증상

연결된 서버의 뷰에 대해 쿼리를 원격으로 실행하는 것은 연결된 서버 의 기본 테이블에 대해 직접 동일한 쿼리를 실행하는 것보다 더 많은 시간이 걸립니다.

원인

뷰 및 기본 테이블에 대해 쿼리를 실행하면 반환되는 예상 행 수를 계산하는 데 사용되는 데이터 카디널리티 추정으로 인해 다른 동작이 발생합니다. 뷰 쿼리 수는 상수 값 10,000으로 설정되고 기본 테이블 쿼리 수는 통계 정보에서 파생됩니다.

연결된 서버에서 쿼리한 기본 테이블의 카디널리티 추정

Microsoft OLE DB 공급자(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 뷰에 대해 절을 사용하여 쿼리를 실행하는 경우 뷰에 대한 카디널리티 예측도 상수 값입니다. 그러나 값은 데이터베이스의 호환성 수준에 따라 달라집니다.

참고 항목

카디널리티 예측 값은 뷰가 정의된 데이터베이스의 호환성 수준에 의해 영향을 받지 않습니다.

  • 데이터베이스 호환성 수준이 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 보기 쿼리 스크린샷.

해결

대부분의 경우 대부분의 애플리케이션 워크로드는 연결된 서버에서 쿼리할 때 뷰에 대한 카디널리티 예측의 영향을 받지 않으므로 작업이 필요하지 않습니다. 워크로드에 영향을 미치는 경우 다음 방법 중 하나를 사용합니다.

  • 뷰를 인덱싱된 뷰로 업데이트하고 변경합니다. 인덱싱된 뷰에는 해당 통계가 있는 인덱스가 하나 이상 있습니다. 인덱싱된 뷰를 만들고 쿼리하려면 다음 쿼리를 참조하세요.

    참고 항목

    이 메서드는 기본 데이터를 나타내는 실제 통계를 노출합니다.

    --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 를 실행하는 경우 (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))를 사용하여 카디널리티 추정을 Query Hint OPTION 늘릴 수 있습니다. 이 방법은 경우에 따라 특히 큰 테이블과 조인하는 경우에 도움이 될 수 있습니다.

    참고 항목

    이 메서드는 뷰가 많은 행을 노출하는 경우에 적합합니다.

  • 적절한 경우 연결된 서버의 뷰 대신 기본 테이블에 대해 쿼리를 직접 실행합니다.

참고 항목

  • Last updated on