쿼리 성능 저하 문제 해결: 카디널리티 예측 중 상수 폴딩 및 식 평가

폴딩 가능 식

SQL Server에서는 다음 유형의 식에 상수 폴딩을 사용합니다.

• 상수만 포함된 1+1, 5/3*2와 같은 산술 식

• 상수만 포함된 1=1 및 1>2 AND 3>4와 같은 논리 식

• CAST 및 CONVERT를 비롯하여 SQL Server에서 폴딩 가능한 것으로 간주하는 기본 제공 함수. 일반적으로 입력으로만 사용되고, SET 옵션, 언어 설정, 데이터베이스 옵션 및 암호화 키와 같은 다른 컨텍스트 정보를 제공하지 않는 내장 함수가 폴딩 가능한 함수입니다. 비결정적 함수는 폴딩 가능하지 않습니다. 몇 가지 예외를 제외하고 결정적 기본 제공 함수는 폴딩 가능 함수입니다.

큰 개체 유형의 경우에는 예외입니다. 폴딩 프로세스의 출력 유형이 큰 개체 유형(text, image, nvarchar(max), varchar(max) 또는 varbinary(max))이면 SQL Server에서 식을 계산하지 않습니다.

폴딩 가능하지 않은 식

다른 모든 식 유형은 폴딩할 수 없습니다. 특히 다음 식 유형은 폴딩할 수 없습니다.

• 비상수 식(예: 열 값에 따라 결과가 달라지는 식)

• 지역 변수 또는 매개 변수에 따라 결과가 달라지는 식(예: @x)

• 비결정적 함수

• 사용자 정의 함수(Transact-SQL 및 CLR)

• 언어 설정에 따라 결과가 달라지는 식

• SET 옵션에 따라 결과가 달라지는 식

• 서버 구성 옵션에 따라 결과가 달라지는 식

폴딩 가능 식 및 폴딩 가능하지 않은 식의 예

SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00

카디널리티 예측에 대한 컴파일 시간 식 평가

뿐만 아니라 상수 폴딩 가능 식이 아니지만 해당 인수를 컴파일 시간에 알 수 없는 일부 식은 최적화 중 최적화 프로그램의 구성 요소인 결과 집합 크기(카디널리티) 평가자에 의해 평가됩니다. 이때 인수가 매개 변수인지 또는 상수인지 여부는 고려하지 않습니다.

특히 기본 제공 함수 및 특수 연산자는 해당 입력을 모두 알 수 있는 경우 컴파일 시간에 평가됩니다. UPPER, LOWER, RTRIM, DATEPART(YY만 해당), GetDate, CAST 및 CONVERT가 여기에 해당합니다.

다음 연산자도 해당 입력을 모두 알 수 있는 경우 컴파일 시간에 평가됩니다.

• 산술 연산자: +, -, *, /, 단항 -

• 논리 연산자: AND, OR, NOT

• 비교 연산자: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

이외에 다른 함수나 연산자는 카디널리티 예측 중 최적화 프로그램에서 평가하지 않습니다.

컴파일 시간 식 평가의 예

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1;

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END;

최선의 구현 방법: 최적 쿼리 계획 생성을 위해 상수 폴딩 및 컴파일 시간 식 평가 사용

최적의 쿼리 계획을 생성하려면 쿼리 최적화 프로그램에서 데이터 분포 통계를 바탕으로 쿼리 내의 조건에 대한 선택도를 정확하게 평가할 수 있도록 쿼리, 저장 프로시저 및 일괄 처리를 디자인하는 것이 가장 좋습니다. 이렇게 하지 않으면 최적화 프로그램에서 선택도 추정 시 기본 추정값을 사용해야 합니다(예: 앞의 예에서는 30% 사용).

최적화 프로그램의 카디널리티 평가자가 적절한 추정값을 제공하도록 하려면 먼저 AUTO_CREATE_STATISTICS 및 AUTO_UPDATE_STATISTICS 데이터베이스 SET 옵션을 ON(기본 설정)으로 설정하거나, 쿼리 조건에서 참조되는 모든 열에 대해 직접 통계를 생성해야 합니다. 그런 다음 쿼리에서 조건을 디자인할 때 다음을 수행합니다.

• 쿼리에서 지역 변수를 사용하지 않도록 합니다. 대신 쿼리에서 매개 변수, 리터럴 또는 식을 사용합니다.

• 매개 변수가 포함된 쿼리에는 Compile-Time Expression Evaluation for Cardinality Estimation에 나오는 연산자 및 함수만 사용합니다.

• 쿼리 조건에 포함된 상수 전용 식이 상수 폴딩 가능 식이거나 컴파일 시간에 평가할 수 있는 식인지 확인합니다.

• 쿼리에서 사용할 식을 지역 변수를 사용하여 평가해야 할 경우에는 쿼리와는 다른 범위에서 평가하십시오. 예를 들어 다음 중 하나를 수행할 수 있습니다.

  • 평가할 쿼리가 포함된 저장 프로시저에 변수 값을 전달하고 쿼리가 지역 변수 대신 이 프로시저의 매개 변수를 사용하도록 합니다.

  • 부분적으로 지역 변수 값에 기반을 둔 쿼리가 포함된 문자열을 만든 다음 동적 SQL(EXEC 또는 sp_executesql)을 사용하여 문자열을 실행합니다.

  • 쿼리를 매개 변수화하고 sp_executesql을 사용하여 실행한 다음 변수 값을 매개 변수로 쿼리에 전달합니다.

참고 항목

개념

관련 자료