수안이의 컴퓨터 연구실
Kalen Delaney
최적화기가 가장 좋은 계획을 만들어낼 수 있게 돕는다.
필자가 SQL 서버에 대해 가르치기 좋아하는 주제 중 하나는 쿼리 최적화기에 대한 것이다. 쿼리 최적화기는 응용 프로그램이 제출한 쿼리를 처리하기 위해 어떤 쿼리 계획을 SQL 서버가 수행할지 결정하는 것이다. 최적화기의 주요한 임무 중 하나는 쿼리와 관련된 각 테이블에서 해당하는 데이터를 찾기 위해 SQL 서버가 어떤 인덱스를 사용할지 결정하는 것이다. 2001년 8월 기사 "튜닝 중 이세요?" 에서 필자는 검색 제한자(SARG)의 개념에 대해 다루었다. 검색 제한자를 WHERE 절에 사용되는 특별한 형태의 검색 매개변수 조건이라고 설명한다. 대부분의 경우, 컬럼이 SARG로서 나타나야만 쿼리 최적화기가 그 컬럼에 있는 인덱스를 사용할지 말지를 고려할 수 있다.
SARG 는 정확히 일치하는 값으로 검색을 한정시키거나 일정 부분 또는 둘 이상의 조건을 AND 나 OR로 연결하기 때문에 검색을 제한한다. 또 SARG 는 상수 표현식(constant expression) (또는 상수를 해결하는 변수)으로 SQL 서버 하나의 값을 연산자로 사용하여 비교 할 수 있음을 의미한다. SARG 는 컬럼명 포괄적연산자 <값> 또는 <값> 포괄적연산자 컬럼명 의 형식을 가진다. 컬럼명은 연산자의 한쪽 편에 나타나며 값은 다른 쪽에 나타난다. 다음 연산자들이 포괄적 연산자(inclusive operators)란 다음 과 같은 것이 해당된다( =, >, <, =>, <=, BETWEEN 그리고 경우에 따라서는 LIKE (문자 앞에 와일드 카드가 오지 않을 때)). 이 때 가장 중요한 조건은 연산자의 한쪽 편에 오직 컬럼만이 와야 한다는 것이다. 컬럼이 저 혼자만 오지 않는다면 그것은 SARG 가 아니고 SQL 서버는 컬럼에 인덱스가 있다고해도 인덱스를 사용할 것에 대해 고려치 않는다. 이 기사에 있는 샘플 코드는 SARG 가 언제, 어떤 방식으로 유용하게 사용되는 지를 보여준다. 기사의 예제를 수행하려면 [리스트 1] 에 있는 스크립트를 수행하여 Northwind 데이터베이스의 Orders 테이블에 대한 복사본을 만든다. 이 복사본은 NewOrders이며 정수 IDENTITY 컬럼이 추가되었고 OrderID 컬럼은 문자를 포함하고 있다. 같은 데이터를 가지는 행이 다섯번 나타나지만 OrderID 값은 서로 다른 숫자를 덧붙여서 고유하도록 설계되었다.
많은 경우에, 비 SARG 를 SARG 로 바꾸어서 SQL 서버가 쿼리를 처리할 때에 인덱스를 사용하도록 할 수 있다. 다음 쿼리를 새로 만든 NewOrders 테이블에 대해 수행하는 것에 대해 생각해 보자.
이 쿼리에 대한 실행계획은 SQL 서버가 테이블 스캔을 하고 있음을 보여준다. SET STATISTICS IO ON 을 쿼리 수행 전에 실행하면 106 페이지의 논리읽기를 보게 될 것이며, 이 숫자는 테이블의 페이지에 대해 한번씩 읽는 것을 의미한다. 논리적으로 동일하면서도 SARG 가 되려면 다음과 같이 쿼리를 바꾸면 된다.
수정된 쿼리를 위한 실행계획은 인덱스 찾기를 하고 있음을 보여준다. STATISTICS IO 는 같은 데이터를 찾기 위해서 단지 일곱 페이지의 논리 읽기를 했음을 보여준다. 보다 나은 실행계획과 성능을 얻게 되는데 이는 SARG 가 최적화기에게 OrderID에 있는 인덱스를 검토해볼 것을 지시하고 최적화기는 그 인덱스가 유용하다고 결정하기 때문이다. 이 예제와 같은 경우에는 쉽게 SARG 를 갖도록 쿼리를 재작성할 수 있다. 하지만 어떤 쿼리들은 SARG 가 없음이 명확치 않다. 이런 경우는 찾아내기도 힘들고 고치기도 힘들다. 이 기사의 뒷부분에서 이런 경우를 다루도록 하겠다.
변화는 때때로 좋은 것이다…
때로는 SQL 서버가 쿼리를 처리하는 방식의 변화가 그전에는 SARG 이었던 것을 더 이상 아닌 것으로 바꾸기도 한다. 2003년 12월호 기사 "향상된 옵티마이저 살펴보기” 와 2004년 1월호 기사 "최상의 것 찾아내기" 에서 필자는 SQL 서버 2000에서의 쿼리 최적화기의 변화에 대해 다루었으며, 대부분은 향상된 것에 대한 내용이었다. 하지만 독자인 Tim Kehoe는 이로 인해 오히려 성능이 더 나빠졌다고 말해왔다. SQL 서버 2000 이전에는 다른 자료형의 값을 가진 컬럼에 대한 비교에서 SQL 서버는 항상 값(상수 혹은 표현식)을 암시적 변환을 사용하여 그 컬럼의 자료형으로 변환하였다. 하지만 이 암시적 변환 규칙은 SQL 서버 2000에서 바뀌었다. “데이터 형식 선행 규칙” 항목을 온라인 설명서에서 찾아보면 다음과 같이 설명한다.
“데이터 형식이 다른 두 개의 식이 연산자에 의해 결합된 경우 데이터 형식 선행 규칙은 어떤 데이터 형식을 다른 데이터 형식으로 변환할 것인지를 지정합니다.- 선행 규칙이 낮은 데이터 형식이 선행 규칙이 높은 데이터 형식으로 변환됩니다. 이 때 변환이 암시적으로 지원되지 않으면 오류가 반환됩니다.- 피연산자 식이 같은 데이터 형식일 경우에는 연산 결과도 해당 데이터 형식이 됩니다.”
[리스트 1] Orders Table의 매우 큰 복사본을 만드는 스크립트
이어서 온라인 설명서는 모든 자료형의 선행 규칙 순서를 나열하고 있다. 목록은 정수형이 문자형보다 더 높은 우선 순위를 가지고 있음을 보여준다. Tim 의 쿼리는 다음과 같았다:
비록 OrderID 는 문자열 컬럼으로 정의되어 있지만, 쿼리에서는 이를 상수와 비교하고 있는데 상수는 SQL 서버에서 더 높은 우선순위를 가진 정수형의 값이다. SQL 서버는 두 자료형이 모두 같은 값을 가져야만 하기 때문에 자동적으로 OrderID 컬럼을 정수형 자료로 변환시킨다. 그래서 쿼리는 다음과 같이 수행된다:
사실, 첫 번째 실행계획에서도 변환이 있었으므로 이 쿼리는 이제 더 이상 SARG 를 가진 쿼리가 아니다. 이런 경우 해결책은 SQL 서버가 상수의 자료형이 정수값이라고 가정하게 두지 말고 일부러 SQL 서버로 하여금 상수값이 문자임을 강제로 알게 하는 것이다. 이 경우는 값에 따옴표만 붙이면 된다. 다음 두 쿼리를 실행하면 완전히 다른 성능과 실행계획을 얻게 될 것이다.
첫 번째 쿼리는 인덱스 스캔을 하며 15페이지의 논리 읽기를 하는 반면, 두 번째 쿼리는 인덱스 찾기를 하여 인덱스를 정확하게 직접 찾아오며 단 3페이지의 논리읽기를 한다.
최적화기가 낮은 선행 규칙의 자료형을 높은 선행규칙의 자료형으로 바꾸는 변화로 인해서 Tim 은 성능향상을 얻지 못했다. 이와 비슷한 문제를 해결하기 위해서 어떤 자료형을 가지고 일을 하고 있는지 정확히 알고 있어야 한다. SQL 서버 7.0은 이런 쿼리에 대해서 102533 값을 문자형으로 바꾸어서 좋은 성능을 가져다 주었다. 이런 경우는 쿼리가 항상 실수로 잘못 작성되었다는 것을 전제하고 있다. 따라서 모든 코드에서 사용하는 값에 대해서 문자라는 것을 확실히 나타내기 위해서는 반드시 따옴표를 값에 붙여야만 한다. 만약 자료형이 잘못 해석 되도록 놓아둔다면 엉뚱한 결과를 얻게 될 것이다.
예를 들어 등호(=) 연산자 대신 부등호를 사용하면 다음 두 쿼리는 완전히 다른 결과를 가져온다.
첫 번째 쿼리에서 SQL 서버는 10249를 정수로 처리하게 되고 따라서 (정수형으로 변환된) OrderID 값이 산술적으로 10249 보다 적은 행들을 돌려준다. 이런 행은 오직 10248 밖에 없다. 하지만, 10249에 따옴표를 붙여 값을 명시적으로 문자로 주게 되면 SQL 서버는 OrderID 값이 문자 10249 (즉, ASCII 순서로) 보다 작은 것들을 찾는다. 결과로서 SQL 서버는 10248, 102481, 102482, 102483, 102484 의 다섯 행을 돌려준다.
필자는 어떤 것이 응용 프로그램을 위해 올바른 것인지 말 할 수는 없지만 SQL 서버가 디자인 된 대로 변환을 하도록 내버려 둔다면 독자가 타당하다고 생각했던 SARG 가 더 이상 타당하지 않게 되므로 원하던 것과 다른 결과를 얻을 수 있음을 알아야만 한다. 나는 상수나 표현식을 컬럼이 가진 자료형과 동일하게 가져가도록 권장한다. 경우에 따라서 SQL 서버는 상수가 독자가 생각하는 것과 같지 않다고 가정할 수도 있다. 예를 들어 WHERE bitcolumn = 1이라고 하면 SQL 서버는 1이 int 라고 가정하지 bit라고 가정하지 않는다. WHERE bigintcolumn = 30000000 에서는 SQL 서버가 상수의 자료형이 numeric이라고 가정하지 bigint라고 가정하지 않는다. 어떤 불명확함이 존재한다면 CAST() 함수를 사용하여 자료형을 명확하게 바꾸어 주는 것이 SQL 서버가 전혀 다른 형태로 자료형을 바꾸는 것보다 바람직하다.
모든 SARG가 등호를 가지는 것은 아니다
최적화기가 쿼리에 SARG 가 있다고 판단한 후에 최적화기는 관련된 컬럼에 어떤 유용한 인덱스가 있는 지 검사한다. 만약 있다면 최적화기는 SQL 서버가 얼마나 많은 행을 액세스 해야 하는 지 알아내기 위해 그 인덱스의 통계 정보를 사용한다. 이런 방식으로 그 인덱스가 유용한지 아닌지를 검토한다. 통계는 두 부분으로 구성된다. 인덱스의 첫 번째 컬럼에 대해서 통계는 히스토그램을 가지고 있어서 주어진 값에 대해 최적화기는 주어진 행에 대해 얼마나 많은 값이 나타날지 매우 근접한 값을 예측 하게 된다. 예를 들어, 다음 쿼리의 실행계획을 살펴보자.
그래픽 실행계획을 사용한다면 커서를 index seek 아이콘 위에 가져다 대서 예상 행 수를 알아볼 수 있으며 6개라고 보여주고 있다. 실제 쿼리가 돌려주는 행은 한 행이지만 예측 값은 매우 근접했다. 인덱스가 최고 16개의 컬럼을 가질 수 있음에도 불구하고 인덱스의 첫 번째 컬럼만이 상응하는 히스토그램을 가지고 있음을 기억하자. 예를 들어 Employees 테이블의 lastname, firstname 두 컬럼에 대해 인덱스를 만들고 다음과 같은 쿼리를 실행할 수 있다.
비록 SQL 서버는 두 조건을 포함한 전체 WHERE 절 전체가 SARG라고 간주하지만 히스토그램은 last name 값이 Clark 인 것에 대해서만 예측 행 수를 가지고 있고 first names과 last name 이 동시에 조건을 만족시키는 행이 몇 개인지는 알려주지 못한다.
히스토그램에 추가하여 통계는 밀도 정보를 가지고 있다. 밀도는 왼쪽에 주어진 행의 집합에 대해 중복된 행의 평균 개수이다. 예를 들어 lastname, firstname, country에 대해 인덱스를 만들었다고 하면 통계는 각 lastname이 나타나는 평균 숫자를 알려주며 각 lastname/firstname 조합이 나타내는 평균 숫자, 그리고 lastname/firstname/country 조합이 나타내는 평균 숫자를 알려준다. (SQL 서버 2000의 최적화기 통계에 대한 보다 자세한 내용은, 기술 백서 "Statistics Used by the Query Optimizer in Microsoft SQL Server 2000", http://msdn.microsoft.com/library/defau ··· ery.asp.)를 살펴보라.) 최적화기에게 찾을 수 있는 알려진 값을 주지 않으면 히스토그램을 사용할 수 없게 되는 대신에 밀도 정보를 사용하게 된다. 예를 들어 다음 두 쿼리를 NewOrders 테이블에 대해 실행한다고 가정해보자.
첫 번째 쿼리에서, OrderID 는 문자 컬럼이며 쿼리는 문자 상수와 비교한다. 그래서 최적화기는 히스토그램을 사용하여 좋은 예측 값을 가져 오게 된다. 두 번째 쿼리에서 컬럼은 그 자체로만 나타나므로 SARG를 가진 쿼리가 된다. 하지만 최적화기는 substring 연산을 실제로 하지는 않는다. 실행 시에 “어떤” 주어진 값보다 작은 값을 찾도록 지시하기만 할 뿐이다. 따라서, 실제 실행되기 전에는 어떤 값이 올지 모르고, 그래서 히스토 그램을 사용할 수 없다. 최적화기는 단지 얼마나 많은 행이 WHERE 절을 만족할지 추측하기만 한다. 실행계획 의 상세 내용을 살펴보면 추정값은 대략 전체 테이블의 30퍼센트의 행을 가져오고 있음을 보여준다. 이 것은 인덱스가 유용하기에는 너무 많은 값이고 그래서 최적화기는 비록 결과적으로 첫 번째 쿼리와 동일한 수의 행이 돌려지는 두 번째 쿼리에 대해 테이블 스캔의 계획을 선택하게 된다.
이제 다음 두 유사한 쿼리에 대해 생각해 보자.
두 쿼리는 모두 SARG 를 가지고 있고 동일한 결과를 돌려준다. 하지만 최적화기는 두 번째 쿼리를 위해서 OrderID 컬럼에 있는 통계 정보를 사용할 수 없게 된다. 대신 최적화기는 등호(=) 연산에 대해 밀도 정보를 사용할 수 있다. 밀도 정보는 각 주어진 값에 대해서 단지 하나의 이상의 행이 나타나고 있다고 알려주며 그래서 최적화기가 어떤 값을 찾아야 하는지 알지 못함에도 불구하고 쿼리는 1행만 돌려줄 것을 알게 되고 따라서 OrderID 컬럼의 인덱스를 사용하는 것이 좋은 선택이라고 받아들인다.
최적화기 도와주기
이상적인 상황이라면 최적화기는 쿼리 수행에 필요한 모든 정보를 액세스 할 수 있어야 한다. 하지만 실제는 아니다. 그러므로 최적화기에게 더 많은 정보를 제공하여 최적화기를 돕는 것은 최적화기가 추정을 하지 않도록 하여 보다 나은 실행계획을 돌려주게 하는 방법이 된다. 자료형을 명확하게 하여 SQL 서버가 엉뚱한 자료형으로 가정하게 하지 말고 내부적 변환을 하게 하지 말자. 가능하다면 가장 명시적인 형태로 제공하도록 하자. 쿼리 처리 동안에 함수의 결과를 최적화기가 결정하게 하지 말자. 다음 달에는 쿼리의 성능을 높이기 위해 SARG 를 가지고 있지만 최적화기가 추정을 해야 하고 통계의 잇점을 제대로 얻을 수 없는 몇 가지 다른 사례를 소개 할 것이다.
최적화기가 가장 좋은 계획을 만들어낼 수 있게 돕는다.
필자가 SQL 서버에 대해 가르치기 좋아하는 주제 중 하나는 쿼리 최적화기에 대한 것이다. 쿼리 최적화기는 응용 프로그램이 제출한 쿼리를 처리하기 위해 어떤 쿼리 계획을 SQL 서버가 수행할지 결정하는 것이다. 최적화기의 주요한 임무 중 하나는 쿼리와 관련된 각 테이블에서 해당하는 데이터를 찾기 위해 SQL 서버가 어떤 인덱스를 사용할지 결정하는 것이다. 2001년 8월 기사 "튜닝 중 이세요?" 에서 필자는 검색 제한자(SARG)의 개념에 대해 다루었다. 검색 제한자를 WHERE 절에 사용되는 특별한 형태의 검색 매개변수 조건이라고 설명한다. 대부분의 경우, 컬럼이 SARG로서 나타나야만 쿼리 최적화기가 그 컬럼에 있는 인덱스를 사용할지 말지를 고려할 수 있다.
SARG 는 정확히 일치하는 값으로 검색을 한정시키거나 일정 부분 또는 둘 이상의 조건을 AND 나 OR로 연결하기 때문에 검색을 제한한다. 또 SARG 는 상수 표현식(constant expression) (또는 상수를 해결하는 변수)으로 SQL 서버 하나의 값을 연산자로 사용하여 비교 할 수 있음을 의미한다. SARG 는 컬럼명 포괄적연산자 <값> 또는 <값> 포괄적연산자 컬럼명 의 형식을 가진다. 컬럼명은 연산자의 한쪽 편에 나타나며 값은 다른 쪽에 나타난다. 다음 연산자들이 포괄적 연산자(inclusive operators)란 다음 과 같은 것이 해당된다( =, >, <, =>, <=, BETWEEN 그리고 경우에 따라서는 LIKE (문자 앞에 와일드 카드가 오지 않을 때)). 이 때 가장 중요한 조건은 연산자의 한쪽 편에 오직 컬럼만이 와야 한다는 것이다. 컬럼이 저 혼자만 오지 않는다면 그것은 SARG 가 아니고 SQL 서버는 컬럼에 인덱스가 있다고해도 인덱스를 사용할 것에 대해 고려치 않는다. 이 기사에 있는 샘플 코드는 SARG 가 언제, 어떤 방식으로 유용하게 사용되는 지를 보여준다. 기사의 예제를 수행하려면 [리스트 1] 에 있는 스크립트를 수행하여 Northwind 데이터베이스의 Orders 테이블에 대한 복사본을 만든다. 이 복사본은 NewOrders이며 정수 IDENTITY 컬럼이 추가되었고 OrderID 컬럼은 문자를 포함하고 있다. 같은 데이터를 가지는 행이 다섯번 나타나지만 OrderID 값은 서로 다른 숫자를 덧붙여서 고유하도록 설계되었다.
많은 경우에, 비 SARG 를 SARG 로 바꾸어서 SQL 서버가 쿼리를 처리할 때에 인덱스를 사용하도록 할 수 있다. 다음 쿼리를 새로 만든 NewOrders 테이블에 대해 수행하는 것에 대해 생각해 보자.
이 쿼리에 대한 실행계획은 SQL 서버가 테이블 스캔을 하고 있음을 보여준다. SET STATISTICS IO ON 을 쿼리 수행 전에 실행하면 106 페이지의 논리읽기를 보게 될 것이며, 이 숫자는 테이블의 페이지에 대해 한번씩 읽는 것을 의미한다. 논리적으로 동일하면서도 SARG 가 되려면 다음과 같이 쿼리를 바꾸면 된다.
수정된 쿼리를 위한 실행계획은 인덱스 찾기를 하고 있음을 보여준다. STATISTICS IO 는 같은 데이터를 찾기 위해서 단지 일곱 페이지의 논리 읽기를 했음을 보여준다. 보다 나은 실행계획과 성능을 얻게 되는데 이는 SARG 가 최적화기에게 OrderID에 있는 인덱스를 검토해볼 것을 지시하고 최적화기는 그 인덱스가 유용하다고 결정하기 때문이다. 이 예제와 같은 경우에는 쉽게 SARG 를 갖도록 쿼리를 재작성할 수 있다. 하지만 어떤 쿼리들은 SARG 가 없음이 명확치 않다. 이런 경우는 찾아내기도 힘들고 고치기도 힘들다. 이 기사의 뒷부분에서 이런 경우를 다루도록 하겠다.
변화는 때때로 좋은 것이다…
때로는 SQL 서버가 쿼리를 처리하는 방식의 변화가 그전에는 SARG 이었던 것을 더 이상 아닌 것으로 바꾸기도 한다. 2003년 12월호 기사 "향상된 옵티마이저 살펴보기” 와 2004년 1월호 기사 "최상의 것 찾아내기" 에서 필자는 SQL 서버 2000에서의 쿼리 최적화기의 변화에 대해 다루었으며, 대부분은 향상된 것에 대한 내용이었다. 하지만 독자인 Tim Kehoe는 이로 인해 오히려 성능이 더 나빠졌다고 말해왔다. SQL 서버 2000 이전에는 다른 자료형의 값을 가진 컬럼에 대한 비교에서 SQL 서버는 항상 값(상수 혹은 표현식)을 암시적 변환을 사용하여 그 컬럼의 자료형으로 변환하였다. 하지만 이 암시적 변환 규칙은 SQL 서버 2000에서 바뀌었다. “데이터 형식 선행 규칙” 항목을 온라인 설명서에서 찾아보면 다음과 같이 설명한다.
“데이터 형식이 다른 두 개의 식이 연산자에 의해 결합된 경우 데이터 형식 선행 규칙은 어떤 데이터 형식을 다른 데이터 형식으로 변환할 것인지를 지정합니다.- 선행 규칙이 낮은 데이터 형식이 선행 규칙이 높은 데이터 형식으로 변환됩니다. 이 때 변환이 암시적으로 지원되지 않으면 오류가 반환됩니다.- 피연산자 식이 같은 데이터 형식일 경우에는 연산 결과도 해당 데이터 형식이 됩니다.”
[리스트 1] Orders Table의 매우 큰 복사본을 만드는 스크립트
이어서 온라인 설명서는 모든 자료형의 선행 규칙 순서를 나열하고 있다. 목록은 정수형이 문자형보다 더 높은 우선 순위를 가지고 있음을 보여준다. Tim 의 쿼리는 다음과 같았다:
비록 OrderID 는 문자열 컬럼으로 정의되어 있지만, 쿼리에서는 이를 상수와 비교하고 있는데 상수는 SQL 서버에서 더 높은 우선순위를 가진 정수형의 값이다. SQL 서버는 두 자료형이 모두 같은 값을 가져야만 하기 때문에 자동적으로 OrderID 컬럼을 정수형 자료로 변환시킨다. 그래서 쿼리는 다음과 같이 수행된다:
사실, 첫 번째 실행계획에서도 변환이 있었으므로 이 쿼리는 이제 더 이상 SARG 를 가진 쿼리가 아니다. 이런 경우 해결책은 SQL 서버가 상수의 자료형이 정수값이라고 가정하게 두지 말고 일부러 SQL 서버로 하여금 상수값이 문자임을 강제로 알게 하는 것이다. 이 경우는 값에 따옴표만 붙이면 된다. 다음 두 쿼리를 실행하면 완전히 다른 성능과 실행계획을 얻게 될 것이다.
첫 번째 쿼리는 인덱스 스캔을 하며 15페이지의 논리 읽기를 하는 반면, 두 번째 쿼리는 인덱스 찾기를 하여 인덱스를 정확하게 직접 찾아오며 단 3페이지의 논리읽기를 한다.
최적화기가 낮은 선행 규칙의 자료형을 높은 선행규칙의 자료형으로 바꾸는 변화로 인해서 Tim 은 성능향상을 얻지 못했다. 이와 비슷한 문제를 해결하기 위해서 어떤 자료형을 가지고 일을 하고 있는지 정확히 알고 있어야 한다. SQL 서버 7.0은 이런 쿼리에 대해서 102533 값을 문자형으로 바꾸어서 좋은 성능을 가져다 주었다. 이런 경우는 쿼리가 항상 실수로 잘못 작성되었다는 것을 전제하고 있다. 따라서 모든 코드에서 사용하는 값에 대해서 문자라는 것을 확실히 나타내기 위해서는 반드시 따옴표를 값에 붙여야만 한다. 만약 자료형이 잘못 해석 되도록 놓아둔다면 엉뚱한 결과를 얻게 될 것이다.
예를 들어 등호(=) 연산자 대신 부등호를 사용하면 다음 두 쿼리는 완전히 다른 결과를 가져온다.
첫 번째 쿼리에서 SQL 서버는 10249를 정수로 처리하게 되고 따라서 (정수형으로 변환된) OrderID 값이 산술적으로 10249 보다 적은 행들을 돌려준다. 이런 행은 오직 10248 밖에 없다. 하지만, 10249에 따옴표를 붙여 값을 명시적으로 문자로 주게 되면 SQL 서버는 OrderID 값이 문자 10249 (즉, ASCII 순서로) 보다 작은 것들을 찾는다. 결과로서 SQL 서버는 10248, 102481, 102482, 102483, 102484 의 다섯 행을 돌려준다.
필자는 어떤 것이 응용 프로그램을 위해 올바른 것인지 말 할 수는 없지만 SQL 서버가 디자인 된 대로 변환을 하도록 내버려 둔다면 독자가 타당하다고 생각했던 SARG 가 더 이상 타당하지 않게 되므로 원하던 것과 다른 결과를 얻을 수 있음을 알아야만 한다. 나는 상수나 표현식을 컬럼이 가진 자료형과 동일하게 가져가도록 권장한다. 경우에 따라서 SQL 서버는 상수가 독자가 생각하는 것과 같지 않다고 가정할 수도 있다. 예를 들어 WHERE bitcolumn = 1이라고 하면 SQL 서버는 1이 int 라고 가정하지 bit라고 가정하지 않는다. WHERE bigintcolumn = 30000000 에서는 SQL 서버가 상수의 자료형이 numeric이라고 가정하지 bigint라고 가정하지 않는다. 어떤 불명확함이 존재한다면 CAST() 함수를 사용하여 자료형을 명확하게 바꾸어 주는 것이 SQL 서버가 전혀 다른 형태로 자료형을 바꾸는 것보다 바람직하다.
모든 SARG가 등호를 가지는 것은 아니다
최적화기가 쿼리에 SARG 가 있다고 판단한 후에 최적화기는 관련된 컬럼에 어떤 유용한 인덱스가 있는 지 검사한다. 만약 있다면 최적화기는 SQL 서버가 얼마나 많은 행을 액세스 해야 하는 지 알아내기 위해 그 인덱스의 통계 정보를 사용한다. 이런 방식으로 그 인덱스가 유용한지 아닌지를 검토한다. 통계는 두 부분으로 구성된다. 인덱스의 첫 번째 컬럼에 대해서 통계는 히스토그램을 가지고 있어서 주어진 값에 대해 최적화기는 주어진 행에 대해 얼마나 많은 값이 나타날지 매우 근접한 값을 예측 하게 된다. 예를 들어, 다음 쿼리의 실행계획을 살펴보자.
그래픽 실행계획을 사용한다면 커서를 index seek 아이콘 위에 가져다 대서 예상 행 수를 알아볼 수 있으며 6개라고 보여주고 있다. 실제 쿼리가 돌려주는 행은 한 행이지만 예측 값은 매우 근접했다. 인덱스가 최고 16개의 컬럼을 가질 수 있음에도 불구하고 인덱스의 첫 번째 컬럼만이 상응하는 히스토그램을 가지고 있음을 기억하자. 예를 들어 Employees 테이블의 lastname, firstname 두 컬럼에 대해 인덱스를 만들고 다음과 같은 쿼리를 실행할 수 있다.
비록 SQL 서버는 두 조건을 포함한 전체 WHERE 절 전체가 SARG라고 간주하지만 히스토그램은 last name 값이 Clark 인 것에 대해서만 예측 행 수를 가지고 있고 first names과 last name 이 동시에 조건을 만족시키는 행이 몇 개인지는 알려주지 못한다.
히스토그램에 추가하여 통계는 밀도 정보를 가지고 있다. 밀도는 왼쪽에 주어진 행의 집합에 대해 중복된 행의 평균 개수이다. 예를 들어 lastname, firstname, country에 대해 인덱스를 만들었다고 하면 통계는 각 lastname이 나타나는 평균 숫자를 알려주며 각 lastname/firstname 조합이 나타내는 평균 숫자, 그리고 lastname/firstname/country 조합이 나타내는 평균 숫자를 알려준다. (SQL 서버 2000의 최적화기 통계에 대한 보다 자세한 내용은, 기술 백서 "Statistics Used by the Query Optimizer in Microsoft SQL Server 2000", http://msdn.microsoft.com/library/defau ··· ery.asp.)를 살펴보라.) 최적화기에게 찾을 수 있는 알려진 값을 주지 않으면 히스토그램을 사용할 수 없게 되는 대신에 밀도 정보를 사용하게 된다. 예를 들어 다음 두 쿼리를 NewOrders 테이블에 대해 실행한다고 가정해보자.
첫 번째 쿼리에서, OrderID 는 문자 컬럼이며 쿼리는 문자 상수와 비교한다. 그래서 최적화기는 히스토그램을 사용하여 좋은 예측 값을 가져 오게 된다. 두 번째 쿼리에서 컬럼은 그 자체로만 나타나므로 SARG를 가진 쿼리가 된다. 하지만 최적화기는 substring 연산을 실제로 하지는 않는다. 실행 시에 “어떤” 주어진 값보다 작은 값을 찾도록 지시하기만 할 뿐이다. 따라서, 실제 실행되기 전에는 어떤 값이 올지 모르고, 그래서 히스토 그램을 사용할 수 없다. 최적화기는 단지 얼마나 많은 행이 WHERE 절을 만족할지 추측하기만 한다. 실행계획 의 상세 내용을 살펴보면 추정값은 대략 전체 테이블의 30퍼센트의 행을 가져오고 있음을 보여준다. 이 것은 인덱스가 유용하기에는 너무 많은 값이고 그래서 최적화기는 비록 결과적으로 첫 번째 쿼리와 동일한 수의 행이 돌려지는 두 번째 쿼리에 대해 테이블 스캔의 계획을 선택하게 된다.
이제 다음 두 유사한 쿼리에 대해 생각해 보자.
두 쿼리는 모두 SARG 를 가지고 있고 동일한 결과를 돌려준다. 하지만 최적화기는 두 번째 쿼리를 위해서 OrderID 컬럼에 있는 통계 정보를 사용할 수 없게 된다. 대신 최적화기는 등호(=) 연산에 대해 밀도 정보를 사용할 수 있다. 밀도 정보는 각 주어진 값에 대해서 단지 하나의 이상의 행이 나타나고 있다고 알려주며 그래서 최적화기가 어떤 값을 찾아야 하는지 알지 못함에도 불구하고 쿼리는 1행만 돌려줄 것을 알게 되고 따라서 OrderID 컬럼의 인덱스를 사용하는 것이 좋은 선택이라고 받아들인다.
최적화기 도와주기
이상적인 상황이라면 최적화기는 쿼리 수행에 필요한 모든 정보를 액세스 할 수 있어야 한다. 하지만 실제는 아니다. 그러므로 최적화기에게 더 많은 정보를 제공하여 최적화기를 돕는 것은 최적화기가 추정을 하지 않도록 하여 보다 나은 실행계획을 돌려주게 하는 방법이 된다. 자료형을 명확하게 하여 SQL 서버가 엉뚱한 자료형으로 가정하게 하지 말고 내부적 변환을 하게 하지 말자. 가능하다면 가장 명시적인 형태로 제공하도록 하자. 쿼리 처리 동안에 함수의 결과를 최적화기가 결정하게 하지 말자. 다음 달에는 쿼리의 성능을 높이기 위해 SARG 를 가지고 있지만 최적화기가 추정을 해야 하고 통계의 잇점을 제대로 얻을 수 없는 몇 가지 다른 사례를 소개 할 것이다.
"MSSQL" 카테고리의 다른 글
- XML을 사용하여 SQL Server 데이터 표시 (0)2007/05/21
- 과도한 동기화 (0)2007/05/21
- 검색 제한자 깊이보기 (0)2007/05/21
- 뷰를 실체화하기 (0)2007/05/21
- 지금 SQL 서버에서는 어떤 문제들이 벌어지고 있을까? (1)2007/05/21
Leave your greetings.