[SQL Server] Basic Select Query의 작동원리

Professional SQL Server 2012 Internals and Troubleshooting 내용 정리

1. 사용된 네트워크 프로토콜의 종류에 관계없이 서버와 연결이 성립되면 SNI는 서버의 TDS endpoint로 보안된 연결을 생성한다. 이것은 요청을 보내고 응답을 받는데 사용된다. 

* TDS(Tabular Data Stream) Endpoints 

-  SQL서버와 Client 사이를 연결해주는 역할. 예를 들면 Client에서 SELECT 문을 SQL Server로 보내면 TDS message가 TCP/IP Connection을 맺어준다.

* Protocol Layer

 - SQL Server가 TDS 패킷을 수신했을 때 클라이언트가 요구하는 사항을 확인하는 역할을 한다. 이것은 TDS Message로 다시 클라이언트에게 결과와 상태를 전송하는 역할을 한다. TDS 패킷이 풀어져서 SQL Command 라는 것으로 확인이되면 Command Parser로 넘겨주는 역할을 한다. 

* Command Parser

 - Command Parser은 T-SQL 작업을 관리한다. 처음에 SNI를 통해 전달받은 T-SQL의 문법을 체크하여 에러가 있을 경우 다시 클라이언트에게 에러를 내보낸다. 문법이 옳을 경우 해당 명령이 Query Plan을 생성하거나 이전에 존재한 것이 있는지를 찾는다. 쿼리 플랜은 코드를 어떻게 나누어 실행 할지 자세하게 담고있는데 이것을 execution plan이라고 부른다. 쿼리 플랜을 체크한 Command Parser는 이를 Plan Cache에 적절한 플랜이 존재하고 있는지 체크를 한다. Plan Cache는 Buffer Pool에 존재하고 있으며, query plan을 캐쉬하는데 사용된다. 적절한 플랜이 이미 존재하고 있다면, 해당 쿼리문은 실행을 위해 Query Executor로 보내진다. (다음에 설명하는 내용은 해당 실행계획이 없을 경우에 스텝이다.)

* Plan Cache 

 - 실행계획들을 만드는 것은 시간과 자원을 소비하게 되는 것이다. 그래서 SQL은 괜찮은 실행 계획이 있다면 후속 요청들을 생각해서 실행계획 재사용을 시도한다. SQL 서버의 buffer pool에 속하는 Plan cache는 나중에 필요할 것을 생각하여 실행계획을 저장해둔다. Plan Cache에서도 해당 계획을 찾을 수 없을 때 Command Parser는 쿼리 트리를 만들어 낸다. 이것은 Query Optimizer 로 전달 되어진다. 

* Query Optimizer

 - Query Optimizer는 실행 계획을 세울 때, Cost Base로 실행계획을 결정한다. 실행 계획의 방법이 여러 개가 있을 때, 가장 효율적인 계획을 선택한다는 것이다. 이것은 무조건 Best를 선택하는 것이 아니라 Good을 선택할 수도 있다는 것이다. 그 이유는 Best를 찾기 위해 시간을 소비하는 것이 Good을 실행하는 것이 더 효율적일 수도 있다는 것이다. Optimizer는 Good 플랜을 찾으면 해당 작업을 종료하고 다음 단계로 실행을 한다. 

* Query Executor 

- 단순하게 말하면 쿼리를 수행하는 것이다. 쿼리플랜에 의해서 쿼리를 수행한다. SELECT 쿼리는 데이터 검색을 필요로 한다. 그래서 이 요청은 Storage Engine의 Access Methods 에 전달이 된다. 

* Access Methods

- Access Methods는 데이터와 인덱스에 대한 스토리지 구조는 물론 데이터 검색 및 수정을 위한 인터페이스를 제공하는 코드 모음이다. 데이터를 검색하는 모든 코드가 포함되어 있지만 실제로는 작동 자체를 수행하지는 않고, 요청을 Buffer Manager로 전달한다.

* Buffer Manager 

- Buffer Pool을 관리하는 역할을 한다. 페이지에서 일부 행을 읽어야 할 경우 Buffer Manager는 Buffer Pool의 데이터 캐시를 검사하여 이미 페이지가 메모리에 캐시되어 있는지 확인한다. 페이지가 이미 캐시 된 경우 결과는 Access Methods로 다시 전달되지만 페이지가 캐시에 없는 경우 Buffer Manager는 디스크의 데이터베이스에서 페이지를 가져 와서 데이터 캐시에 저장하고 결과를 Access Methods에 전달한다. 여기서 핵심은 Buffer Manager는 오직 메모리에있는 데이터로만 작업한다는 것이다. 새로운 데이터를 요청받으면 디스크에서 처음 읽은 다음 결과 세트로 반환되기 전에 메모리 (데이터 캐시)에 기록된다. 이것은 SQL Server가 메모리의 사용 가능한 페이지를 최소 수준이 유지 되어야 하는 이유이다. 캐시에 처음 읽은 데이터를 넣을 공간이 없으면 새로운 데이터를 읽을 수 없다. Access Methods 코드는 SELECT 쿼리에 단일 페이지가 필요하다다고 확인 되었으면 Buffer Manager에 요청한다. Buffer Manager는 데이터 캐시에 데이터가 있는지 확인 후 데이터를 찾지 못했을 경우 디스크에서 캐시로 로드한다.

* Data Cache

- buffer pool에서 가장 많이 차지하는 부분이다. 메모리 최대 사용자이다. 사용자가 읽기 전에 디스크에서 데이터를 읽어와 기록해둔다. sys.dm_os_buffer_descriptors DMV는 각 데이터베이스가 데이터 캐시에서 사용하는 공간의 양을 확인할 수 있다.

캐시의 각 페이지 Header에는 액세스 되었을 때 세부 정보가 저장되며 캐시를 통한 주기적 스캔은 SQL Server가 일부 캐시를 비워야하는 경우 가장 낮은 카운터가있는 페이지가 먼저 플러시된다. 

Perfmon에서 MSSQL$<instance>:Buffer Manager\Page Life Expectancy 항목은 페이지가 얼마나 오래 캐시에 유지되고 있는지를 확인 할 수 있다. 메모리가 부족하다면 데이터 페이지가 캐시에서 훨씬 더 자주 플러시된다. Page Life Expectancy(PLE)는 최소 300 초 권장하지만 요즘에는 일반적으로 1000 초로 간주한다. 허용되는 임계 값은 데이터 사용량에 따라 달라질 수 있지만 대부분 PLE가 1000 초 미만이거나 300 미만인 경우 메모리 부족 문제를 쉽게 찾을 수 있다.

SELECT 쿼리에 대한 결과 집합이 버퍼 풀의 데이터 캐시에 있고 sys.dm_os_buffer_descriptors DMV에 항목이 있다. Buffer Manager에 결과 집합이 생기면 Access Methods로 전달되어 클라이언트에 전달된다.

위 그림은 SIMPLE SELECT 쿼리가 입력되었을 때 결과를 클라이언트에 도출하기 위한 과정을 보여주는 것이다. 

출처: Professional SQL Server 2012 Internals and Troubleshooting 7-15p.