Inside SQL Server / Kalen Delaney 
최적화 깊이 알기

쿼리 최적화기는 SQL 서버 데이터베이스 엔진에서 가장 정교하고 복잡한 코드로 구성되어 있다. 최적화기의 역할은 실행 계획(쿼리 플랜)을 만들어 내는 것인데, 이것은 SQL 서버가 사용자의 쿼리를 실행할 단계들의 집합이다. 최적화기는 각 테이블에서 어떤 인덱스를 사용할 지, 조인되는 테이블을 어떤 순서로 어떤 방법을 써서 할 것인지, 중간 결과 값을 저장하기 위해 임시 작업 테이블을 만들 것인지, 내부적인 정렬작업을 할 것인지, 그리고 여러 개의 CPU를 사용하여 처리할 것인지 등을 결정한다.
이제 앞으로 몇 달 동안 나는 지난 두 번의 출시 동안 마이크로소프트가 SQL 서버를 내 놓으면서 최적화기에 대해 변경시킨 많은 것들에 대해 이야기 하고자 한다. 이번 달에는 최적화기의 배경과 최적화기가 변경된 방법에 대해 설명하고자 한다. 이후 다룰 내용에서는 그 전 버전에 비해서 SQL 서버 2000과 7.0에서 달라진, 그래서 더 낳은 성능을 보이는 예들을 보여주고자 한다.

과거에는…

SQL 서버 7.0 이전에는 최적화기가 별로 선택할 수 있는 것이 없었다. 최적화기는 유용하게 쓰일 가능성이 있는 인덱스를 평가해 보고, 그것들 중 하나를 선택하거나 아니면 테이블 스캔을 하는 정도를 했었다. SQL 서버는 JOIN 연산을 오직 하나의 방법으로만 처리 할 수 있었기 때문에 최적화기는 조인되는 테이블의 순서만 정하면 되었다. 또한 SQL 서버는 GROUP BY와 DISTINCT 쿼리를 오직 하나의 방법으로만 처리할 수 있었으며, 쿼리는 다중 프로세서에서 처리될 수 없었다. 

SQL 서버 6.5의 최적화기와 그 이전 버전들은 매우 분명하고 직관적이어서, 한번 그것이 동작하는 방식과 SQL 서버가 인덱스를 사용하는 방식을 이해하기만 하면, 심지어 쿼리를 수행하기도 전에 어떤 실행 계획이 나올지 거의 정확하게 예측해 낼 수 있다. 만약 최적화기가 생각해낸 쿼리 플랜과 다른 플랜을 만들어 냈다면 몇 개의 추적 플래그를 설정하여 최적화기가 계획을 만들어 내기 위해 밟는 단계와 최적화기가 고려하는 모든 계획들을 살펴 볼 수 있다. 뿐만 아니라, 최적화기는 아주 작은 선택권밖에 없으므로 그것이 최적의 결정을 내리지 못했다면 마이크로소프는 이를 버그로 간주했다. 그런 결과로 최적화기의 결정은 항상 최상의 결정이라는 것을 내재적으로 보장받게 된다. 
SQL 서버 7.0을 위해서 마이크로소프트는 최적화기를 완전히 새로 작성했고, 많은 쿼리 처리 기술을 적용했다. 새로운 최적화기는 완전히 모듈화 되어 있으며, 그래서 마이크로소프트 개발자들은 제품에 새로운 처리 기술을 추가했고, 어떤 실행 계획에서도 새로운 기술이 적용될 수 있도록 쉽게 수정할 수 있었다. 

SQL 서버 7.0과 그 이후의 쿼리 최적화기는 그 앞의 것들에 비해 매우 거대하게 복잡해졌다. SQL 서버는 많은 쿼리를 위해서 매우 다른 처리 전략을 가지고 있으며 그래서 최적화기가 어떤 계획을 만들어 낼지 예상해 내는 것이 거의 불가능하다. 이들은 앞선 버전이 제공하던 최적화기가 고려하는 계획을 보여주는 추적 플래그도 없다. 이것은 아마도 각각의 플랜을 만들어 내기 위해 수백에서 수천의 단계를 밟기 때문에 추적 플래그를 사용해서 보여줄 결과가 지나치게 복잡하기 때문일 것이다.

보증 못함

이미 언급한 것처럼 SQL 서버 7.0 이전에는 만약 최적화기가 필요로 하는 모든 정보가 제공되었다면 최적화기는 제출된 쿼리에 대한 최적의 결정을 내린다고 보장할 수 있었다. 프로그래머로서 만약 쿼리 성능을 보다 낫게 할 수 있는 쿼리 힌트를 사용할 수 있었다면, 그것은 최적화기가 가장 최상의 플랜을 만들어 내지 못했음을 의미하며, 마이크로소프트는 종종 이를 버그로 간주하곤 했다. SQL 서버 2000과 7.0은 이제 명시적으로든 묵시적으로든 이런 보장을 더 이상 하지 않는다. 이들 버전에서는 최적화기의 목표는 “그 정도면 충분한” 플랜을 찾아내는 것이다. 대부분의 경우 이 플랜은 최적이지만 항상 그런 것은 아니다.

현재의 최적화기가 처리하는 방식은 순환 방식으로서 한번 사이클을 돌 때마다 보다 복잡한 플랜을 만들어 낸다. 예를 들어 첫 번째 반복에서 각 테이블의 클러스터 인덱스를 사용한 단순 루프 조인의 비용을 평가해 본다. 만약 이 방법이 좋은 플랜을 만들어 내지 못한다면 최적화기는 넌클러스터 인덱스를 평가하고, 다시 다른 조인의 방법을 평가하고, 그래도 안되면 다시 여러 개의 인덱스를 평가해 본다.
최적화기가 점진적으로 복잡한 플랜을 평가하기 때문에 지금까지 찾아낸 것의 비용에 대해 새로운 것을 찾는 비용이 얼마나 이득이 있는지를 계속적으로 평가해 본다. 예를 들어, 최적화기가 실행하는 데 30ms 걸리는 플랜을 하나 찾았고, 이때 1ms가 걸린다고 가정해보자. 그리고 최적화기가 25ms 걸리는 플랜을 하나 더 찾아냈다고 해보자. 하지만 이를 찾는 데 2초가 추가로 걸린다고 해보자. SQL 서버는 더 이상의 플랜을 찾아내는 것이 이득이 없다고 판단하게 된다. 그래서 최적화기는 30ms 걸리는 플랜을 사용하게 되는데, 이것이 “그 정도면 충분한” 것이다.
만약 플랜이 “그 정도면 충분”하지 않다고 생각한다면 쿼리를 힌트를 사용하여 수정하는 것도 생각해 볼 수 있다. 힌트를 사용해서 SQL 서버가 강제적으로 특정한 인덱스를 사용하도록 혹은 특정한 조인 방법을 사용하도록 할 수 있으며, 이를 통해서 25ms로 실행시간을 단축시킬 수도 있을 것이다. 중요한 것은 이제 최적화기가 더 빠른 플랜을 찾아내지 못하는 것이 버그가 아니라 하나의 선택일 뿐이라는 사실이다. 더 나은 플랜을 찾기 위해서 최적화기가 계속적으로 일하는 것을 중단시킨 SQL 서버의 선택의 결과일 뿐이다. 그리고 또한 독자도 선택권이 있다. 힌트를 사용해서 5ms 의 성능 이득을 얻을 것인지 말 것인지를 선택할 수 있다.

힌트를 사용할 것인가?

나는 SQL 서버 쿼리를 작성할 때 사용할 수 있는 모든 힌트에 대해 의도적으로 완전하게 설명하지는 않을 것이다. 모든 쿼리 힌트가 최적화기가 플랜을 선택할 때에 영향을 미치는 것은 아니다. 일부 쿼리 힌트는 SQL 서버 쿼리를 실행할 때의 잠금에 영향을 끼치며, 이들에 대한 논의는 여기서 다루기에 부적절하다. 그러나 나는 독자들이 힌트에 대한 몇 가지 중요한 상세 내용을 알고 있기를 원한다. 
첫째로, 힌트는 실제로 우리가 아는 것과 같은 힌트가 아니라는 것이다. 영어로 힌트는 “부드러운 제안”의 의미를 가지고 있지만, SQL 서버의 용어로 힌트는 “명령”에 가깝다. 이 명령이 물리적 혹은 논리적으로 불가능하지 않다면 SQL 서버는 독자들이 제시하는 어떤 힌트에도 “순종”한다.

또한 오늘 이 힌트를 써서 보다 나은 성능을 얻었다고 할 지라도 내일은 그것이 오히려 성능을 저하시킬 수도 있다는 것이다. 일단 한 번 쿼리를 어떻게 처리할 것인지 SQL 서버에게 힌트로 제시했다면 믿을 수 없이 복잡한 SQL 서버의 쿼리 최적화기의 여러 장점들을 잃어버리는 것이다. 업데이트나 배치 작업의 결과로 데이터 분포도에 변화가 생겼을 때, 주어진 힌트는 더 이상 최적의 방법이 아닐 수 있지만, 최적화기는 더 나은 플랜을 위해서 힌트를 버리지 못하게 된다. 

실제로 수행하기

쿼리 최적화기가 결정을 내릴 때 가장 중요한 것은 처리하고자 하는 데이터에 대한 정확하고 최신의 것으로 갱신된 통계 정보이다. 비록 SQL 서버가 통계를 추적하는 방법은 수년간 변경되긴 했지만 그것의 근본 사상은 동일하다. 통계는 최적화기에게 인덱스가 유용한지 아닌지에 대해 근거 있는 예측값을 만들어주게 된다. 

예를 들어 독자들은 아마도 SQL 서버의 넌클러스터 인덱스(테이블의 모든 행에 대해 북마크라고 불리는 포인터를 리프레벨에 가지고 있는)의 기본 구조에 대해 친숙할 것이다. Lastname 컬럼에 대해서 넌클러스터 인덱스를 가지고 있다고 가정해 보자. 테이블에 있는 모든 lastname 값은 중복된 값을 포함해서 모두 인덱스 리프 레벨에 나타나며, 실제 테이블에서 행이 존재하는 위치를 가리키게 된다. 리프레벨에서의 lastname은 정렬되어 있고, 그래서 Mc로 시작하는 것은 모두 비슷한 위치에 있게 된다. 그러나 실제 테이블의 데이터는 서로 다른 페이지에 존재하게 되며, 수십여 개 페이지에 걸쳐 존재할 수도 있다. 따라서 모든 Mc로 시작하는 이름을 찾는 쿼리는 다음과 같을 것이다.

SELECT lastname, firstname, address, phone
FROM PeopleTable
WHERE lastname LIKE 'Mc%'

일반적으로 넌클러스터 인덱스는 인덱스를 통해서 아주 적은 양의 데이터를 가져올 때만 유용하다. 만약 많은 행을 액세스해야 한다면 전체 테이블 스캔을 하는 것이 SQL 서버가 모든 북마크 포인터를 따라다니는 것보다 효율적일 수 있다. 그럼 얼마나 많은 행이 “많은” 축에 끼는 것일까? 그것은 테이블의 크기, 가져오려는 행의 비율, 한 페이지에 들어가는 행의 수 등 많은 요인으로 결정된다. 나는 쿼리가 테이블의 행 중에서 1%보다 적은 양의 데이터를 가져올 때만 SQL 서버가 넌클러스터 인덱스를 사용하여 그 위치를 찾는 데 도움을 받는다는 것을 발견하였다. 물론 이것은 대략의 값이다. 모든 쿼리와 인덱스는 자신만의 임계값이 있다(인덱스의 사용에 대해 보다 자세히 알고 싶다면 2001년 8월호 “Are You in Tune?" InstantDoc ID 21038를 참조하자).

그렇다면 쿼리가 실행되기도 전에 어떻게 SQL 서버는 Mc로 시작되는 이름이 많은지 적은지 알 수 있는 걸까? 답은 인덱스 통계를 조사해서 추정 값을 얻어내기 때문이다. 이미 언급한 바와 같이, 통계 페이지의 형식은 SQL 서버 7.0에서 완전히 변했고 다시 또 SQL 서버 2000에서 변했다. SQL 서버 2000이 어떻게 통계를 저장하는지에 대해 알고 싶다면 마이크로소프트 기술 백서(white paper) "Statistics Used by the Query Optimizer in Microsoft SQL Server 2000" http://msdn.microsoft.com/library /default.asp?url=/library/techart/statquery .htm을 살펴보도록 하자. 2001년 11월호 필자의 글 "통계적으로 말하기(Statistically Speaking," InstantDoc ID 22075)에도 SQL 서버가 어떻게 인덱스 통계를 이용하는지를 다루고 있다. 

DBA들에게 SQL 서버 7.0에서 가장 환영 받았던 기능은 통계 자동 업데이트였다. SQL 서버 7.0 이전에 DBA들은 데이터량의 변화가 생기거나 통계가 변경된 때마다 통계 업데이트를 잊지 말아야 했다. 예를 들어 지난 주간 6명 정도의 아칸소주에 사는 고객이 있었고, 이 주에 사는 고객을 찾는 쿼리를 수행한다고 해보자. 이 경우는 state 컬럼의 넌 클러스터 인덱스가 도움이 될 것이다. 하지만 이번 주에 아칸소에 근거를 둔 두 개의 회사를 얻었고 거기에 수천 명의 고객이 있다고 가정해보자. 이제는 지난주에 사용하던 인덱스를 사용하는 방법은 최악의 성능을 가져오게 된다. SQL 서버 6.5의 최적화기는 수동으로 통계 페이지를 업데이트하지 않으면 아칸소주의 새로운 고객에 대해 알지 못하던. 더욱이 데이터베이스 내의 모든 통계 페이지를 업데이트할 수 있는 방법이 없었다. UPDATE STATISTICS 명령은 오직 한 테이블에 대해서만 수행될 수 있고, 따라서 주기적으로 모든 테이블의 통계를 업데이트하기를 원한다면 데이터베이스 안에 있는 모든 테이블을 액세스해서 통계를 갱신하는 프로시저를 스스로 작성해야 했다. 

SQL 서버 2000과 7.0은 auto update statistics라는 옵션을 설정해 놓기만 하면 이것이 자동으로 되며, 기본적으로 이것은 TRUE로 설정되어 있다. 내부적으로 정해진 비율의 변화가 인덱스에 생기면 최적화기는 최적화된 계획을 결정하기 전에 통계를 먼저 업데이트 한다. 뿐만 아니라 SQL 서버 2000과 7.0은 sp_updatestats라는 프로시저를 가지고 있으며, 이것은 현재 데이터베이스에 있는 모든 인덱스의 통계를 갱신한다. 
마이크로소프트는 SQL 서버 7.0에서 이와 연관된 사양으로서 개개의 인덱스를 자동 통계 업데이트에서 제외시킬 수 있게 했다. 각각의 인덱스에 대해서 WITH NORECOMPUTE 옵션을 UPDATE STATISTICS 명령과 함께 사용하면 데이터베이스에서 auto update statistics 옵션을 설정한 것보다 우선되게 된다. 이 옵션은 인덱스-통계 업데이트에 대한 모든 최종 제어를 할 수 있게 해준다.

최적화기가 일 할 수 있게 하자 

최적화기는 이제 너무나 모듈화되고 개선하기 쉽게 되어서, 마이크로소프트는 README 파일에 언급도 하지 않은 채 서비스 팩에서 자주 쿼리 처리 기술을 추가하곤 한다. 대부분의 사람들에게 이것은 문제가 되지 않는다. 만약 SQL 서버에 새로운 서비스 팩을 추가하고 나서 보다 나은 성능을 얻는다면 대부분의 사람들은 불평하지 않을 것이다. 그러나 최적화기와 다양한 쿼리 처리 기술에 대해 글을 쓰고 강연을 하는 나와 같은 일부분의 사람들에게 그것은 내가 짠 모든 데모 스크립트와 샘플 파일을 서비스 팩 적용 후에 모두 다시 테스트해 보아서 원래 의도했던 것과 같은지를 점검해 보아야 한다는 의미이다. 나는 매번 이 사실을 기억하지 못하며 한 번 이상 청중 앞에서 강연 도중 놀라는 일을 겪곤했다. 다음 달에 나는 나를 당혹하게 했던 최적화기의 변화에 대해서 언급하고자 한다. 또한 SQL 서버에서 이런 장점 혹은 다른 향상으로 인해 얻게 되는 쿼리의 예를 보여줄 것이다. 


*출처 : SQL 매거진 11월호