• 아티클

상관 관계에 있는 datetime 열에 액세스하는 쿼리 최적화

데이터베이스 SET 옵션인 DATE_CORRELATION_OPTIMIZATION을 사용하면 date 또는 datetime 열이 상호 관련된 두 테이블 간에 동등 조인을 수행하여 조건자에 날짜 제한을 지정하는 쿼리의 성능을 향상시킬 수 있습니다.

date 또는 datetime 열이 상관 관계에 있고 DATE_CORRELATION_OPTIMIZATION을 설정하면 성능상 이점이 있는 테이블은 대개 일 대 다 관계에 속하며 의사 결정 지원, 보고 또는 데이터 웨어하우징에 사용되는 테이블입니다.

예를 들어 AdventureWorks2008R2 예제 데이터베이스에서는 Purchasing.PurchaseOrderHeader 테이블의 OrderDate 열과 Purchasing.PurchaseOrderDetail 테이블의 DueDate 열이 상관 관계에 있습니다. PurchaseOrderDetail.DueDate의 날짜 값은 대개 PurchaseOrderHeader.OrderDate의 날짜 값 바로 다음에 옵니다.

DATE_CORRELATION_OPTIMIZATION 데이터베이스 옵션을 ON으로 설정하면 SQL Server가 데이터베이스에서 date 또는 datetime 열을 포함하며 이러한 열이 단일 열 FOREIGN KEY 제약 조건으로 상호 연결되어 있는 모든 두 테이블 간의 상관 관계 통계를 유지 관리합니다. 기본적으로 이 옵션은 OFF로 설정되어 있습니다.

SQL Server는 이러한 상관 관계 통계와 함께 쿼리 조건자에 지정된 날짜 제한을 사용하여 결과 집합을 변경하지 않고 쿼리에 제한을 추가할 수 있다고 유추합니다. 쿼리 최적화 프로그램에서는 이렇게 유추된 조건을 사용하여 쿼리 계획을 선택합니다. 제한을 추가하면 SQL Server에서 쿼리를 처리할 때 보다 적은 수의 데이터를 읽으므로 쿼리 계획이 더 빨라질 수 있습니다. 두 테이블에 모두 클러스터형 인덱스가 정의되어 있고 상관 관계 통계가 유지 관리되는 date 또는 datetime 열이 클러스터형 인덱스의 첫 번째 키거나 단독 키인 경우에도 성능이 향상됩니다.

예를 들어 다음 Transact-SQL 스크립트를 실행하여 Purchasing.PurchaseOrderDetail과 Purchasing.PurchaseOrderHeader에 대한 상관 관계 정보를 유지 관리하기 위해 AdventureWorks2008R2 데이터베이스를 준비한다고 가정합니다.

USE AdventureWorks2008R2;
GO
-- Create a unique index to take the place of the existing 
-- primary key constraint
CREATE UNIQUE NONCLUSTERED INDEX
IX_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID 
ON Purchasing.PurchaseOrderDetail(PurchaseOrderID,PurchaseOrderDetailID);
GO
-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID;
GO
-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate);
GO
--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks2008R2
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

이번에는 다음 쿼리를 실행한다고 가정합니다.

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
INNER JOIN Purchasing.PurchaseOrderDetail AS d
    ON h.PurchaseOrderID = d.PurchaseOrderID
WHERE h.OrderDate BETWEEN '20060101' AND '20060201';

이 쿼리에서 반환되는 PurchaseOrderDetail.DueDate 값은 대개 PurchaseOrderHeader.OrderDate 값의 특정 날짜 기간(예: 14일) 내에 있습니다. 이 때문에 SQL Server에서는 다음과 같은 쿼리를 사용하여 위의 쿼리를 보다 잘 표현할 수 있다고 유추할 수 있습니다.

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
    Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '1/1/06' AND '2/1/06'
AND d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14;

두 번째 AND 절에서 지정하는 추가 조건의 정확한 형식은 원래 쿼리와 데이터베이스의 데이터 값에 따라 다릅니다. 유추 조건을 추가하면 최적화 프로그램에서 이를 사용하여 실행 계획을 구성합니다. 이 예에서는 PurchaseOrderDetail.DueDate에 클러스터형 인덱스가 있으므로 d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14를 충족하는 행을 검색하는 데 인덱스를 사용할 수 있습니다. 이 쿼리를 실행할 때 Purchasing.PurchaseOrderDetail에 몇 년치의 데이터가 있으면 원래 쿼리보다 실행 시간이 몇 배는 더 걸릴 수 있습니다.

DATE_CORRELATION_OPTIMIZATION 설정에 따른 유추 조건으로 쿼리 계획을 실행하기 전에 SQL Server에서 현재 데이터베이스 내용을 기반으로 쿼리가 정확한 답을 산출하는지 확인합니다.

DATE_CORRELATION_OPTIMIZATION 데이터베이스 옵션 사용을 위한 요구 사항

DATE_CORRELATION_OPTIMIZATION 데이터베이스 옵션을 설정하여 두 테이블 관련 작업의 성능을 향상시키려면 다음 조건이 모두 충족되어야 합니다.

  • 데이터베이스 SET 옵션을 다음과 같이 설정해야 합니다. ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL 및 QUOTED IDENTIFIER를 ON으로 설정해야 합니다. NUMERIC_ROUNDABORT를 OFF로 설정해야 합니다.

  • 테이블 간에 단일 열 외래 키 관계가 있어야 합니다.

  • 두 테이블 모두에 NOT NULL로 정의된 datetime 열이 있어야 합니다.

  • 하나 이상의 datetime 열이 클러스터형 인덱스의 키 열이어야 합니다. 이때 인덱스 키가 복합 키면 첫 번째 키여야 합니다. 또는 분할된 테이블일 경우 분할 열이어야 합니다.

  • 두 테이블의 소유자가 같아야 합니다.

DATE_CORRELATION_OPTIMIZATION 데이터베이스 옵션을 ON으로 설정할 때 다음 사항을 고려합니다.

  • SQL Server는 통계 형태로 상관 관계 정보를 유지 관리합니다. 이러한 통계는 INSERT, UPDATE 및 DELETE 작업 동안 SQL Server에서 업데이트되므로 작업 성능에 영향을 줄 수 있습니다. 업데이트 작업이 많은 데이터베이스 환경에서는 DATE_CORRELATION_OPTIMIZATION을 설정하지 마십시오.

  • 상관 관계 통계가 유지 관리되는 datetime 열이 클러스터형 인덱스의 첫 번째 키나 단독 키가 아니면 해당 열에 클러스터형 인덱스를 만드는 것이 좋습니다. 이렇게 하면 상관 관계 통계 범위에 해당하는 쿼리 유형의 쿼리 성능이 향상됩니다. 기본 키 열에 클러스터형 인덱스가 이미 있으면 클러스터형 인덱스와 기본 키에서 다른 열 집합을 사용하도록 테이블을 수정할 수 있습니다.

  • 다음과 같은 경우에는 DATE_CORRELATION_OPTIMIZATION을 설정해도 효과가 없습니다.

    • 위에서 언급한 상관 관계 통계 유지 관리를 위한 조건에 맞는 테이블 쌍이 없습니다.

    • 상관 관계 통계 유지 관리를 위한 조건에 맞는 테이블 쌍은 있지만 이러한 테이블을 조인하는 쿼리의 조건자에 날짜 제한이 지정되어 있지 않습니다.

DATE_CORRELATION_OPTIMIZATION 데이터베이스 옵션을 설정하려면

상관 관계 통계 작업

DATE_CORRELATION_OPTIMIZATION 데이터베이스 옵션을 ON으로 설정하면 적합한 모든 테이블 쌍에 대해 인덱싱된 뷰 형식으로 상관 관계 통계가 자동으로 생성됩니다. SQL Server 쿼리 최적화 프로그램에서 datetime 열 쌍 간의 상관 관계를 이용할 수 있으면 해당 쿼리 계획에 이러한 상관 관계 통계가 사용됩니다. 상관 관계 통계는 통계에 영향을 주는 INSERT, UPDATE 및 DELETE 문의 논리에도 포함됩니다. 상관 관계 통계의 이름은 다음 형식을 따릅니다. 

MPStats_Sys<constraint_object_id><GUID><FK_constraint_name>

<FK_constraint_name> 은 datetime 일치의 기반이 되는 sys.objects 카탈로그 뷰에 있는 FOREIGN KEY 제약 조건의 이름입니다. <constraint_object_id>는 FOREIGN KEY 제약 조건의 objectid를 8자리의 16진수 값으로 표현한 것입니다.

[!참고]

SQL Server는 상관 관계 통계 이름이 식별자 길이 제한을 초과하면 이름의 FK_constraint_ 부분을 짧게 줄입니다.

SET SHOWPLAN XML을 사용하여 쿼리를 실행할 때 상관 관계 통계에서 파생되는 모든 필터 노드에는 다음 특성이 포함됩니다.

DateCorrelationOptimization="true"

예를 들어 상관 관계 통계의 영향을 받는 <Predicate> 노드는 다음과 같습니다.

<Predicate DateCorrelationOptimization="true">

이 특성은 상관 관계 통계에서 생성되는 모든 필터 노드에 포함되거나 상관 관계 특성의 영향을 받는 조건자와 다른 조건자를 조합하여 생성되는 모든 필터 노드에 포함됩니다.

일반적으로 DATE_CORRELATION_OPTIMIZATION 데이터베이스 옵션을 ON으로 설정하면 SQL Server에서 적합한 모든 datetime 열 쌍에 대해 상관 관계 통계를 생성합니다. 다음 작업을 수행할 때 SQL Server에서 추가 상관 관계 통계를 생성합니다.

  • CREATE TABLE 또는 ALTER TABLE을 통해 datetime 상관 관계 최적화 요구 사항에 맞는 FOREIGN KEY 제약 조건을 만듭니다.

  • 다른 테이블의 datetime 열과 상관 관계를 이루기에 적합한 datetime 열에 클러스터형 인덱스를 만듭니다.

    [!참고]

    ONLINE = ON 옵션을 사용하여 클러스터형 인덱스를 만들면 상관 관계 통계가 생성되지 않습니다. 그러나 인덱스 작성을 커밋한 후 FOREIGN KEY 제약 조건 생성과 같은 다른 트랜잭션의 이벤트 결과로 해당 인덱스에 종속된 상관 관계 통계가 작성될 수 있습니다.

  • 다른 테이블의 datetime 열과 상관 관계를 이루기에 적합하도록 열의 Null 허용 여부나 데이터 형식을 변경합니다.

SQL Server에서 언제든지 상관 관계 통계를 삭제할 수 있으므로 응용 프로그램에서 상관 관계 통계를 직접 참조하면 안 됩니다. 상관 관계 통계를 유지 관리하는 비용이 성능에 영향을 준다고 판단되면 개별 상관 관계 통계를 삭제할 수 있습니다. 상관 관계 통계에 대한 DROP 권한은 기본적으로 sysadmin 고정 서버 역할, db_owner 및 db_ddladmin 고정 데이터베이스 역할의 멤버 및 상관 관계 통계가 정의된 테이블 쌍의 소유자에게 부여됩니다. 이 권한은 양도할 수 없습니다.

다음과 같은 경우 상관 관계 통계가 삭제됩니다.

  • DATE_CORRELATION_OPTIMIZATION 데이터베이스 옵션을 OFF로 설정하면 SQL Server에서 생성된 상관 관계 통계가 모두 삭제됩니다.

  • 유지 관리에 너무 많은 저장 공간이 필요한 상관 관계 통계나 효용이 없을 것으로 예상되는 상관 관계 통계는 삭제됩니다.

  • DROP TABLE 또는 ALTER TABLE을 사용하여 FOREIGN KEY 제약 조건을 삭제하면 이 제약 조건과 연결된 상관 관계 통계가 모두 삭제됩니다.

  • 특정 작업으로 인해 상관 관계에 있는 두 테이블의 소유자가 서로 달라지게 되면 해당 상관 관계 통계가 삭제됩니다.

  • ALTER TABLE...SWITCH 문을 실행할 경우 원본 테이블이나 대상 테이블에 정의된 상관 관계 통계가 있으면 해당 상관 관계 통계가 삭제됩니다.

  • datetime 열에 클러스터형 인덱스를 만들 때 같은 테이블의 다른 datetime 열을 기반으로 하는 상관 관계 통계가 있으면 이 상관 관계 통계는 삭제됩니다. 조건이 맞을 경우 새로 만든 클러스터형 인덱스를 기반으로 SQL Server에서 새 상관 관계 통계를 만들 수 있습니다.

  • 맨 앞의 인덱스 키가 datetime 열인 클러스터형 인덱스를 삭제할 경우 같은 테이블에 새 상관 관계 통계를 만들 수 있는 또 다른 datetime 열이 있으면 관련 상관 관계 통계가 모두 삭제됩니다.

  • ALTER TABLE을 실행하여 상관 관계 통계에 사용되는 열의 데이터 형식이나 Null 허용 여부를 변경하면 해당 통계가 삭제됩니다.

상관 관계 통계 생성이나 삭제를 유발한 같은 트랜잭션의 일부로 상관 관계 통계가 생성되거나 삭제됩니다. 이 트랜잭션은 온라인 또는 비동기가 아닙니다.

단일 서버 기반의 간단한 튜닝 시나리오에서 데이터베이스 엔진 튜닝 관리자를 사용하여 프로덕션 서버를 튜닝할 때 상관 관계 통계의 비용과 이점이 고려됩니다. 그러나 테스트 프로덕션 서버 시나리오에서 데이터베이스 엔진 튜닝 관리자를 사용할 때는 상관 관계 통계가 내부 시스템 개체로 고려되지 않습니다. 따라서 상관 관계 통계는 인덱스 튜닝 분석 시 데이터베이스 엔진 튜닝 관리자의 쿼리 최적화에 사용되지 않습니다. 테스트 프로덕션 시나리오에서는 데이터베이스 엔진 튜닝 관리자가 상관 관계 통계의 비용은 알지만 이점은 모르기 때문에 상관 관계 통계를 포함하는 인덱싱된 뷰에 대한 데이터베이스 엔진 튜닝 관리자의 제안 사항을 무시할 수 있습니다. 두 시나리오 모두에서 데이터베이스 엔진 튜닝 관리자는 datetime 열의 클러스터형 인덱스와 같은 특정 인덱스의 선택을 권장하지 않을 수 있습니다. 이는 DATE_CORRELATION_OPTIMIZATION을 설정한 경우 유용합니다.

상관 관계 통계에 대한 메타데이터 쿼리

DATE_CORRELATION_OPTIMIZATION 데이터베이스 옵션의 설정을 보려면 sys.databases카탈로그 뷰의 is_date_correlation_on 열을 선택합니다.

뷰가 상관 관계 통계 기반인지 여부를 확인하려면 sys.views 카탈로그 뷰의 is_date_correlation_view 열을 선택합니다.