쿼리 성능 저하 문제 해결: 카디널리티 예측 중 상수 폴딩 및 식 평가
SQL Server에서는 일부 상수 식을 초기에 평가하여 쿼리 성능을 향상시킵니다. 이를 상수 폴딩이라고 합니다. 상수는 3, 'ABC', '2005-12-31', 1.0e3 또는 0x12345678과 같은 Transact-SQL 리터럴입니다.
폴딩 가능 식
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
이 쿼리에 대해 PARAMETERIZATION 데이터베이스 옵션이 FORCED로 설정되어 있지 않으면 117.00 + 1000.00 식이 평가된 다음 쿼리를 컴파일하기 전에 평가 결과인 1117.00으로 바뀝니다. 이러한 상수 폴딩의 이점은 다음과 같습니다.
- 런타임에 식을 반복해서 평가할 필요가 없습니다.
- 식 평가 후 쿼리 최적화 프로그램에서 식 값을 사용하여 쿼리의
TotalDue > 117.00 + 1000.00부분에 대한 결과 집합의 크기를 추정합니다.
반면 if dbo.f가 스칼라 사용자 정의 함수인 경우에는 SQL Server에서 사용자 정의 함수가 포함된 식을 계산하지 않으므로 dbo.f(100) 식이 폴딩되지 않습니다. 사용자 정의 함수가 결정적 함수인 경우에도 마찬가지입니다.
카디널리티 예측에 대한 컴파일 시간 식 평가
뿐만 아니라 상수 폴딩 가능 식이 아니지만 해당 인수를 컴파일 시간에 알 수 없는 일부 식은 최적화 중 최적화 프로그램의 구성 요소인 결과 집합 크기(카디널리티) 평가자에 의해 평가됩니다. 이때 인수가 매개 변수인지 또는 상수인지 여부는 고려하지 않습니다.
특히 기본 제공 함수 및 특수 연산자는 해당 입력을 모두 알 수 있는 경우 컴파일 시간에 평가됩니다. UPPER, LOWER, RTRIM, DATEPART(YY만 해당), GetDate, CAST 및 CONVERT가 여기에 해당합니다.
다음 연산자도 해당 입력을 모두 알 수 있는 경우 컴파일 시간에 평가됩니다.
- 산술 연산자: +, -, *, /, 단항 -
- 논리 연산자: AND, OR, NOT
- 비교 연산자: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL
이외에 다른 함수나 연산자는 카디널리티 예측 중 최적화 프로그램에서 평가하지 않습니다.
컴파일 시간 식 평가의 예
다음 Transact-SQL 저장 프로시저를 살펴보십시오.
USE AdventureWorks
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1
프로시저의 SELECT 문을 최적화하는 동안 최적화 프로그램은 OrderDate > @d+1 조건에 대한 결과 집합의 예상 카디널리티를 평가하려고 합니다. @d가 매개 변수이므로 @d+1 식은 상수 폴딩 가능 식이 아닙니다. 그러나 최적화할 때 매개 변수 값이 알려집니다. 따라서 최적화 프로그램은 결과 집합의 크기를 정확하게 예측하여 적절한 쿼리 계획을 선택할 수 있습니다.
이제 앞의 예와 유사한 다음 예를 살펴보십시오. 로컬 변수로 @d+1 대신 @d2가 사용되고, 식이 쿼리 내에서가 아니라 SET 문에서 실행된다는 점만 다릅니다.
USE AdventureWorks
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END
SQL Server 2005에서 MyProc2의 SELECT 문을 최적화할 때 @d2 값이 알려지지 않습니다. 따라서 최적화 프로그램에서는 OrderDate > @d2의 선택도에 대해 기본 추정값(이 예의 경우 30%)을 사용합니다.
최선의 구현 방법: 최적 쿼리 계획 생성을 위해 상수 폴딩 및 컴파일 시간 식 평가 사용
최적의 쿼리 계획을 생성하려면 쿼리 최적화 프로그램에서 데이터 분포 통계를 바탕으로 쿼리 내의 조건에 대한 선택도를 정확하게 평가할 수 있도록 쿼리, 저장 프로시저 및 일괄 처리를 디자인하는 것이 가장 좋습니다. 이렇게 하지 않으면 최적화 프로그램에서 선택도 추정 시 기본 추정값을 사용해야 합니다(예: 앞의 예에서는 30% 사용).
최적화 프로그램의 카디널리티 평가자가 적절한 추정값을 제공하도록 하려면 먼저 AUTO_CREATE_STATISTICS 및 AUTO_UPDATE_STATISTICS 데이터베이스 SET 옵션을 ON(기본 설정)으로 설정하거나, 쿼리 조건에서 참조되는 모든 열에 대해 직접 통계를 생성해야 합니다. 그런 다음 쿼리에서 조건을 디자인할 때 다음을 수행합니다.
- 쿼리에서 로컬 변수를 사용하지 않도록 합니다. 대신 쿼리에서 매개 변수, 리터럴 또는 식을 사용합니다.
- 매개 변수가 포함된 쿼리에는 카디널리티 예측에 대한 컴파일 시간 식 평가에 나오는 연산자 및 함수만 사용합니다.
- 쿼리 조건에 포함된 상수 전용 식이 상수 폴딩 가능 식이거나 컴파일 시간에 평가할 수 있는 식인지 확인합니다.
- 쿼리에서 사용할 식을 로컬 변수를 사용하여 평가해야 할 경우에는 쿼리와는 다른 범위에서 평가하십시오. 예를 들어 다음 중 하나를 수행할 수 있습니다.
- 평가할 쿼리가 포함된 저장 프로시저에 변수 값을 전달하고 쿼리가 로컬 변수 대신 이 프로시저의 매개 변수를 사용하도록 합니다.
- 부분적으로 로컬 변수 값에 기반을 둔 쿼리가 포함된 문자열을 만든 다음 동적 SQL(EXEC 또는 sp_executesql)을 사용하여 문자열을 실행합니다.
- 쿼리를 매개 변수화하고 sp_executesql을 사용하여 실행한 다음 변수 값을 매개 변수로 쿼리에 전달합니다.