MSSQL 옵티마이저(optimizer)

MS SQL Server 2008 R2 버전 옵티마이저에 대한 포스트 입니다.

이전에 쓴글을 네이버 블로그에서 T스토리로 옮겼습니다.

MS SQL Server는 사용자가 쿼리를 입력하면 내부적으로 어떤 일이 일어날까? 이번 포스트의 주요 관점은 옵티마이저이므로 간단하게 전체적으로 어떠한 일이 일어나는지 알아 보도록 하자. 우선 파싱을 살펴보자 사용자가 쿼리를 실행하면 파싱을 한다. 파싱은 사용자가 정확한 쿼리를 입력했는지 구문을 검사한다.    예를 들어 사용자가 다음과 같은 쿼리를 입력했다고 생각해보자   SELECT * 테이블 WHERE ID = '주형권'   여기서 이 쿼리가 무엇이 잘못되었는지 알지 못한다면 이 포스트를 읽지 않기를 권장한다. (어차피 무슨말인지 모른다.) 위의 쿼리는 FROM이 없다. 물론 실행하면 오류가 난다. 무엇을 실행할지 확인했는데 없다니... 이런것을 파싱에서 처리한다.  또한 위쪽의 순서도에서 보면 DML일 경우 바로 Algebrizing 단계를 거쳐서 실행이 되는 것을 볼 수 있다.     각각의 언어의 뜻을 살펴보면 왜 그런지 알 수 있다. 1. DDL(Data Definition Language) – 객체의 생성,변경,삭제 명령어 (CREATE , ALTER , DROP,RENAME 등)    2. DML(Data Manipulation Language)- 레코드 제어 명령어 (SELECT, INSERT, UPDATE ,DELETE ) 3. DCL(Data Control Language) – 객체 권한 부여등 제어어 (GRANT 등)    조금만 생각해보면 답은 나온다.    DML이 아닌 경우 좋은 실행계회을 만들 필요가 없기 때문이다.         다음으로 Algebrizing의 단계로 넘어가는데 이 과정에서는 또 한번이 검증과 바인딩을 담당한다.  SUM과 GROUP BY등에 관여한다고 보면 될 것이다. 또한 테이블의 실제 존재여부 및 접근 권한등을 검사한다. 너무 길어지니 이것은 다른 포스트에서 다루도록 하겠다.   그 다음으로 이번 포스트에서 다루고자 하는 옵티마이저 단계로 넘어간다. 그리고 들어가기 전에 MS SQL의 옵티마이저는 기보적으로 비용기반 옵티마이저를 사용한다.  한눈에 볼 수 있도록 순서를 보면서 확인해 보자.      ① 캐시가 존재하는지 살펴본다. 실행계획을 재사용 하려고 하는 것이다.  옵티마이저가 실행계획을 만들기 위해서는 많은 비용이 들어든다. 그 비용을 조금이라도 줄이고자 기존에 사용하였던 실행계획이 있는지 확인하고 그 실행계획을 사용하고자 하는 것이다.  ② 단순화를 적용한다. 단순화 단계에서는 기본적으로 Query Tree를 사용자 관점 구문에서 추후 프로세스에서 원활하게 사용 할 수 있는 형태로 단순화 하는 작업이 이뤄진다. 서브쿼리들은 Join 형태로 변경하거나 Join 구문의 ON절이나 혹은 SELECT 구문 내에 있는 조건들을 모두 WHERE절로 내리는 형태의 작업 그리고 Join 순서에 대한 초기 설정 등이 진행된다. ( 이 과정은 실행계획을 생성하지 않음)   ③ 이것이 명백한 실행계획인지 확인한다. Trivial Plan은 너무도 명백하기 때문에 실행계획을 구지 만들 필요가 없는 쿼리를 말한다.  즉, 너무도 명백해서 이것을 최적화 시킬 필요가 없는 것이다. 예를 들어보자 select * from sys.dm_exec_query_optimizer_info where counter in (   'optimizations' , 'trivial plan' , 'search 0' , 'search 1' , 'search 2' ) order by [counter] 위의 쿼리는 옵티마이저가 어떠한 쿼리로 인식했는지 알수 있다. 실행 해보면 다음과 같이 나온다.   trivial plan이 보인다. (search0~2는 잠시 후에 설명하도록 하겠다.) 그렇다면 어떠한 쿼리를 trivial plan으로 인식할까? select * from [dbo].[Card_count] 위의 쿼리를 실행해보자   trivial plan의 숫자가 증가했다. 이 쿼리를 명백한 쿼리로 인식한 것이다.    ④ 마지막으로 비용기반 최적화를 시작하기에 앞서 한번 더 물어본다. 이거 정말 비싼가?  그만큼 최적화를 하면 비용이 많이 들기 때문에 재차 확인 하는 것을 볼수 있다. 그리고 보면 NO일 경우와 YES일 경우 둘다 화살표가 옵티마이저로 항하는 것을 볼 수 있는데 이것은 옵티마이저에서 어느단계 (Phase 0 ~2단계)에서 처리할지를 나타낸다. 만약에 비용이 충분히 쌀 경우 0단계에서 처리하고 그도 아니면 1단계로 넘어가기 때문이다.    그렇다면 이제 비용기반 옵티마이저를 좀 더 자세히 살펴보자      ① Phase 0로 들어가기 전에 묻는다. Phase 0으로 처리가 불가능 한가? 여기서 Phase 0은 최적화 비용 0.2 이전의 값을 뜻한다.     비용이 올라갈 수록 Phase1 ~2단계 마지막으로 병렬처리(Parallel)까지 간다. 만약에 이것을 Phase0단계로 처리가 불가능 하다고 판단할 경우 바로 Phase1단계로 넘어간다.  하지만 Phase0 즉 0.2의 비용이 초과 하지 않는다고 생각되면 위의 순서도의 순서대로 다시 이동한다. 여기서 다시 한번 검사하여 정말로 Phase0의 0.2 비용을 초과하는지 확인한다.   만약에 비용이 0.2를 넘을 경우 Phase0을 이용하고 넘는다면 Phase1의 단계로 넘어간다.      ② Phase1의 단계로 넘어오면 옵티마이저는 많은 생각을 한다. 조인의 순서도 바꿔보고 병렬처리도 고려해 본다. 그리고 1.0의 비용이 넘지 않을 경우 이 방법을 사용하여 처리한다. 만약에 병렬처리를 만들었는데 시리얼 처리(직렬처리)가 더욱 저렴한것 같다면 시리얼처리를 이용하여 쿼리를 실행한다. 그리고 여기서도 비용이 너무 높게 나온다면 Phase2의 단계로 넘어간다.   ③ Phase2의 단계는 Full Optimization Phase라고 불리며 옵티마이저가 할 수 있는 모든 방법을 통해서 다양한 계획을 세운다.  결국 모든 계획을 사용해봤지만 비용이 너무 높다고 판단되었기에 마지막 최후에 방법을 동원 하는 것이다.   옵티마이저는 결론적으로 위의 수많은 과정을 거쳐서 사용자에게 가장 좋은 계획을 보여주는 것이다. 여기서 결정적으로 알아야 할 것이 있는데 옵티마이저는 최고의 실행계획을 제공하지는 않는다.  여기서 최고란 "무엇보다 이 방법이 가장 최상이다" 라는 의미를 지닌다. 옵티마이저는 가장 빠르게 실행계획을 만들어서 쿼리를 실행해야 하므로 느긋하게 이리 저리 쿼리를 연구해볼 시간이 없다. 그러므로 사용자에게 가장 빠르게 가장 좋은 (여기서 좋은이란 쓸만한 자기(옵티마이저)가 볼때 비용대비 성능이 뛰어난) 계획을 이용한다.    이상 포스트를 마친다.