SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables

SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4)

– SQL 문 최적화 및 Worktables

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

[SELECT 문 최적화]

단일 SQL문 처리는 SQL Server가 SQL문을 실행하는 기본적인 방법이다. SELECT 문은 프로시저를 통하지 않는다. 즉 데이터베이스 서버가 요청한 데이터를 검색하는데 사용해야 하는 정확한 단계를 지정하고 있지 않는다. 이는 데이터베이스 서버가 요청 구문을 분석하여 데이터를 출하는 가장 효율적인 방법을 판단해야 함을 의미 한다. 이것을 SELECT 문 최적화라고 하며 이를 위한 구성 요소를 쿼리 최적화 프로그램이라고 한다. 최적화 프로그램에 대한 입력은 쿼리, 데이터베이스, 스키마(테이블 및 인덱스 정의), 데이터베이스 통계로 이루어 진다.

 

[SELECT 문 정의]

• 결과 집합의 서식: 대부분 SELECT 목록에 지정된다. 하지만 ORDER BY 및 GROUP BY와 같은 다른 절도 결과 집합에 영향을 준다.
• 원본 데이터를 포함하는 테이블 : FROM 절에 지정 된다.
• 테이블이 SELECT 문의 목적과 논리적으로 관련되는 방식 : 조인 사양에 정의되며 FROM 뒤에 따라오는 WHERE 절이나 ON에 포함된다.
• 원본 테이블의 행이 SELECT 문의 결과에 포함되기 위해 만족시켜야 할 조건 : WHERE 및 HAVING 절에 지정 된다.

 

 

[원본 테이블 액세스 순서]

일반적으로 데이터베이스 서버는 다양한 방법으로 기본 테이블을 액세스하여 결과 집합을 작성할 수 있다. 예를들어 SELECT 문이 세 개의 테이블을 참조하는 TableA, TableB, TableC가 있을 때 TableA를 액세스하고 TableA의 데이터를 사용하여 TableB에서 일치하는 행을 추출한 후 TableB의 데이터를 사용하여 TableC에서 데이터를 추출한다. 액세스 하는 순서는 A->B->C, C->A->B, B->A->C 등 다양하다.

 

[데이터 추출에 사용되는 방법]

일반적으로 각 테이블의 데이터에 액세스 하는 방법에는 여러 가지가 있다. 특정 키 값을 가진 행만 필요한 경우 데이터베이스 서버는 인덱스를 사용할 수 있다. 테이블의 모든 행이 필요한 경우 데이터베이스 서버는 인덱스를 무시하고 테이블을 검색할 수 있다. 테이블의 모든 행이 필요하지만 키 열이 ORDER BY에 있는 인덱스가 있으면 테이블 검색 대신 인덱스 검색을 수행하여 다른 종류의 결과 집합을 저장할 수 있다. 테이블이 매우 작은 경우 테이블 검색은 모든 테이블 액세스를 위해 가장 효율적인 방법일 수 있다.

 

[쿼리 최적화 프로그램]

여러 가능한 실행 계획 중에서 하나의 실행 계획을 선택하는 프로세스를 최적화라 한다. 쿼리 최적화 프로그램에서 쿼리를 분석하고 계획을 선택할 때 오버헤드가 발생하지만 효율적인 실행 계획을 선택하면 오버헤드가 상당히 감소한다.

SQL Server 쿼리 최적화 프로그램은 비용을 기반으로 하는 최적화 프로그램이다. 가능한 각 실행 계획은 사용되는 컴퓨터 리소스의 양과 관련하여 비용을 추산한다. 처리 가능한 실행계획을 분석하여 비용이 가장 낮은 계획을 선택한다.

복잡한 SELECT 문은 가능한 수 많은 실행 계획을 포함한다. 이 경우 최적화 프로그램은 가능한 모든 경우를 조합하지 않는다. 대신 복잡한 알고리즘을 사용하여 가장 최소 비용에 근접하는 실행 계획을 찾는다.

 

• 쿼리 길이에 따른 CPU 소요량 : http://sqlmvp.kr/140183021243

 

SQL Server에서 최적화 프로그램은 비용이 가장 낮은 실행 계획만 선택하는 것은 아니다. 여러 가지 리소스 비용을 사용하여 사용자에게 결과를 반환하고 가장 빠른 결과를 반환하는 계획을 선택 한다. 예를 들어 병렬처리의 경우 일반적으로 직렬로 처리하는 것보다 많은 리소스를 사용하지만 쿼리를 좀더 빠르게 끝낸다.

 

쿼리 최적화 프로그램은 테이블 또는 인덱스에서 정보를 추출하는 다른 방법의 리소스 비용을 예상할 때 배포 통계를 이용한다. 열 및 인덱스에 대해 배포 통계가 보유 된다. 인덱스 통계가 최신 상태가 아닐 경우 최상의 선택을 하지 못할 수도 있다.

 

• 통계 업데이트 : http://msdn.microsoft.com/ko-kr/library/ms187348.aspx

 

[SELECT문 처리]

SQL Server가 단일 SELECT 문을 처리하는데 사용하는 기본 단계

1. 파서는 SELECT 문을 검색하고 그 결과를 키워드, 식, 연산자 및 식별자와 같은 논리 단위로 분류.
2. 시퀀스 트리라고 하는 쿼리 트리 작성, 결과 집합에서 필요로 하는 서식으로 원본 데이터를 변환하는데 필요한 논리 단계 정의.
3. 쿼리 최적화 프로그램은 소스 테이블에 액세스 할 수 있는 여러 방법을 분석. 리소스 사용을 줄이는 동시에 가장 빨리 반환하는 방법을 선택. 쿼리 트리는 이러한 일련의 단계가 기록되도록 업데이트. 최적화된 최종 쿼리 트리 버전은 실행계획이라고 한다.
4. 관계형 에엔진이 실행 계획을 실행. 기본 테이블의 데이터를 필요로 하는 단계가 처리 될 때 관게형 엔진은 저장소 에엔진이 관계형 엔진에서 요청된 행 집합의 데이터를 무시하도록 요청.
5. 관계형 엔진은 저장소 엔진에서 반환된 데이터를 결과 집합에 대해 정의된 서식으로 처리하고 클라이언트에 결과 집합을 반환.

 

 

 

[다른 문 처리]

SELECT 문 처리의 기본 단계는 INSERT, UPDATE, DELETE와 같은 다른 SQL문에도 적용 된다. UPDATE문과 DELETE문을 둘 다 수정되거나 삭제 될 행 집합을 대상으로 해야 한다. 이러한 행을 식별하는 프로세스는 SELECT 문의 결과 집합을 구하는데 사용되는 원본 행을 식별하는 방식과 동일 하다. UPDATE, INSERT 문은 모두 업데이트되거나 삽입될 데이터 값을 제공하는 SELECT문을 포함할 수 있다.

 

CREATE PROCEDURE 또는 ALTER TABLE과 같은 DDL문도 결과적으로 시스템 카탈로그 테이블에 대한 관계형 작업으로 해석되며 ALTER TABLE ADD COLUMN 문처럼 데이터 테이블에 대한 관계형 작업으로 해석되는 경우도 있다.

 

[worktables]

관계형 엔진은 SQL문에 지정된 논리 작업을 수행하기 위해 worktable을 생성 한다. Workterble은 중간 결과를 보관하는데 사용되는 내부 테이블 이다. Worktable은 특정 GROUP BY, ORDER BY, UNION 쿼리에 대해서 생성 된다. Worktable은 쿼리 계획 일부의 실행 결과를 임시로 보관하는 스풀(spool)로 사용되기도 한다. Worktable은 tempdb에 작성되며 필요 없을 때 자동으로 삭제 된다.

 

 

[참고자료]

• http://msdn.microsoft.com/ko-kr/library/ms190623(v=sql.105).aspx
• http://msdn.microsoft.com/ko-kr/library/ms181037(v=sql.105).aspx
• http://msdn.microsoft.com/ko-kr/library/ms187348.aspx
• http://sqlmvp.kr/140183021243

 

 

 

2013-05-02 / 강성욱 / http://sqlmvp.kr