Hal Berenson, Kalen Delaney
2000년 1월
요약:이 문서는 클라이언트에서 Microsoft SQL Server 쿼리를 처리하는 방법, 여러 클라이언트와 SQL Server가 상호 작용하는 방법, 클라이언트 요청을 처리하기 위한 SQL Server 작업을 설명합니다(26페이지/인쇄 페이지 기준).
소개
Microsoft® SQL Server™의 내부 및 아키텍처는 광범위한 주제이므로 이 문서에서는 개발자와 관련된 분야로 한정하여 다른 글에서 자세히 설명되어 있지 않은 항목들을 중심으로 알아보겠습니다. SQL Server의 아키텍처를 논의하면서 클라이언트에서 작업이 처리되는 방법, 여러 클라이언트와 SQL Server가 상호 작용하는 방법, 클라이언트 요청을 처리하기 위해 SQL Server가 하는 작업 등에 대해 중점적으로 살펴봅니다. SQL Server의 다른 측면을 설명하는 글도 있습니다. 이 중 Microsoft Press에서 출판한 Ron Soukup과 Kalen Delaney의 "Inside SQL Server 7.0"은 저장소 엔진의 메커니즘 및 작동에 관한 내용을 아주 상세하게 다루고 있지만 쿼리 프로세서는 그다지 자세하게 다루지 않았습니다. 이 문서에서는 이와 같이 다른 글에서 자세하게 다루지 않는 부분을 상세히 설명합니다.
이 정보를 통해 더 나은 응용 프로그램을 작성할 수 있게 되기를 바랍니다. 이 문서를 읽으면 새로운 관점과 이해를 통해 성능 문제를 바라볼 수 있을 것입니다.
클라이언트/서버 시스템인 SQL Server
오랫동안 SQL Server는 클라이언트/서버 시스템으로 알려졌습니다. 사실 SQL Server의 토대가 된 Sybase DataServer는 초기에 클라이언트/서버 시스템으로 개발된 최초의 상업용 관계형 데이터베이스 시스템이었습니다. 그러나 그 의미는 SQL Server가 2계층 시스템이라는 것 이상을 나타냅니다. 일반적으로 2계층 시스템은 하나의 컴퓨터에서 클라이언트 응용 프로그램이 실행되고 다른 컴퓨터에 있는 서버에 요청을 보내는 것을 의미합니다. SQL Server에서 클라이언트/서버는 서버 구성 요소 자체와는 별개이며 프로세스 구조에서 원격에 위치하는 클라이언트 API의 일부분인 SQL Server부분입니다.
기존의 2계층 모델에서 클라이언트 부분은 대량의 클라이언트 응용 프로그램 논리 및 업무 논리와 함께 데스크톱 컴퓨터에 위치하며 데이터베이스 시스템에 대한 요청을 직접 생성합니다. 그런 다음 클라이언트는 요청에 대한 응답으로 서버에서 데이터를 받습니다.
3계층 구조에서도 동일한 모델이 적용됩니다. 오랫동안 SQL Server는 20, 30년대의 고전적인 3계층 모델을 대표하는 BEA의 Tuxedo나 Compaq의 ACMSxp와 같은 트랜잭션 처리 모니터와 함께 사용되었습니다. Microsoft의 MTS와 새로운 COM+ 1.0 등의 응용 프로그램 서버에서 볼 수 있듯이 오늘날 대부분의 웹 기반 응용 프로그램은 3계층 모델을 사용하고 있습니다. SQL Server 측면에서 보면 3계층 솔루션의 클라이언트는중간계층에 위치하는 논리의 일부입니다. 이 중간 계층은 데이터베이스 시스템과 직접 상호 작용합니다. 실제 데스크톱이나 씬 클라이언트는 다른 메커니즘을 사용하여 데이터베이스 시스템이 아닌 중간 계층과 직접 상호 작용합니다. 그림 1에서는 이러한 구조를 보여 줍니다.
그림 1. 3계층 시스템 모델
클라이언트 아키텍처
아키텍처 측면에서 보면 SQL Server 관계형 서버 구성 요소에서는 클라이언트가 실행되는 위치가 크게 중요하지는 않습니다. 실제로 SQL Server가 실행되는 동일한 시스템에서 응용 프로그램을 실행하더라도 SQL Server 측면에서 보면 클라이언트/서버 모델과 다른 점이 없습니다. 서버에서는 별도의 다중 스레드 프로세스를 실행하므로 클라이언트의 위치에 관계 없이 클라이언트로의 요청을 처리합니다. 클라이언트 코드 자체는 클라이언트 응용 프로그램 내에서 실행되는 별개의 DLL이며 SQL Server에 대한 실제 인터페이스는 클라이언트와 서버 사이에서 통신하는 TDS(Tabular Data Stream) 프로토콜입니다.
"SQL Server의 원시 인터페이스는 무엇입니까?"라는 질문을 종종 받습니다. 오랫동안 많은 개발자들은 Sybase에서 개발한 클라이언트 API인 DB-Library가 SQL Server에 대한 원시 인터페이스라고 생각했으므로 ODBC 등의 인터페이스를 사용하지 않으려고 했습니다. 사실 SQL Server 관계형 서버 자체에는 원시 API가 없습니다. SQL Server 관계형 서버의 인터페이스는 클라이언트와 서버 간의 통신 스트림용 프로토콜인 TDS입니다. TDS는 서버에서 클라이언트로 다시 보낸 결과 집합과 클라이언트에서 서버로 보낸 SQL 문을 캡슐화합니다. TDS를 직접 처리하는 모든 API는원시SQL Server 인터페이스입니다.
이제 그림 2에 나타난 대로 클라이언트 구성 요소에 대해 알아보도록 합니다. SQL Server 자체의 범위를 벗어나는 클라이언트 아키텍처 부분은 언급하지 않겠지만 응용 프로그램을 작성하려면 이러한 부분들에 대해서 알고 있어야 합니다. 가장 많이 알려진 부분은 다양한 개체 모델입니다. ASP나 Microsoft Visual Basic® 응용 프로그램을 작성할 경우 ADO를 통해 데이터베이스 시스템과 상호 작용합니다. 이때 ODBC나 OLE-DB 등 낮은 수준의 API를 직접 호출하지 않습니다. ADO는 OLE-DB로 매핑되고 RDO는 ODBC로 매핑됩니다. 따라서 프로그래밍 모델에서 가장 많이 사용되는 이러한 개체 모델은 엄격하게 따지면 SQL Server의 클라이언트 아키텍처가 아닙니다. 이 수준에서 SQL Server 인프라 위에 놓일 수 있는 추가 구성 요소도 있습니다. OLE-DB의 Session Pooling Service Provider도 이러한 구성 요소 중 하나입니다.
그림 2. 클라이언트 아키텍처
클라이언트 인터페이스
SQL Server7.0, OLE-DB 및 ODBC의 원시 인터페이스로 생각할 수 있는 SQL Server에 대한 인터페이스가 두 가지 있습니다. TDS와 관련된 DB-Library 인터페이스도 원시 인터페이스이지만 DB-Library에서는 서버측에 변환이 필요한 이전 버전의 TDS를 사용합니다. SQL Server 7.0에 기존 DB-Library 응용 프로그램을 계속 사용하고 있지만 ODBC와 OLE DB를 사용해야 여러 새로운 기능을 접할 수 있고 성능을 향상시킬 수 있습니다. SQL Server 7.0의 새로운 기능을 지원하기 위해 DB-Library를 업데이트하면 기존 응용 프로그램과 호환되지 않는 문제가 여러 곳에서 발생하게 되며 이에 따라 응용 프로그램을 변경해야 합니다. 5년 전부터 새로운 SQL Server 응용 프로그램에 대한 기본 API로 DB-Library 대신 ODBC를 사용하고 있으므로 호환되지 않는 새 DB-Library 버전을 도입하는 것은 바람직하지 않습니다.
위의 그림 2에서 볼 수 있듯이 모든 클라이언트 API에는 세 가지 구성 요소가 있습니다. 가장 위에는 행 집합이 표시되는 모양, 커서 모양 등 API 관련 요소가 구현됩니다. TDS 포맷터에서 SQL 문과 같은 실제 요청을 받아들인 다음 TDS 메시지로 묶어 SQL Server로 보내고 결과를 되돌려 받은 다음 그 결과를 인터페이스 구현에 공급합니다.
또한 모든 공급자가 사용하는 공통 라이브러리 코드가 몇 가지 있습니다. 예를 들어, BCP 기능은 ODBC와 OLE-DB에서 호출하는 라이브러리입니다. DTC는 두 번째 예이고 제3의 예는 ODBC 표준 SQL 문으로 모든 공급자 간의 공통된 매개 변수 표식이 있는 CALL 구문입니다.
이전에 언급한 대로 DB-Library가 SQL Server 6.5 버전을 계속 사용한다는 한계를 제외하고 TDS 프로토콜은 모든 API에 대해 동일하게 적용됩니다. SQL Server 7.0에서 작업할 때 ODBC와 OLE-DB는 SQL Server 7.0 버전을 사용하지만 7.0 이전의 6.5나 6.0 서버에서도 작업할 수 있는 기능이 있습니다. 또한 클라이언트와 서버 모두 네트워크 추상화 인터페이스와 통신할 수 있으므로 IPX나 TCP/IP를 고려하지 않아도 되는 추상화 계층인 Net-Library도 있습니다. Net-Library에서 하는 작업에 대해서는 자세히 다루지 않습니다. Net-Library는 기본적으로 낮은 수준의 네트워크 통신 정보를 나머지 소프트웨어에서 숨기는 작업을 수행합니다.
클라이언트에서
이미 언급한 대로 클라이언트에서는 주로 TDS 메시지를 사용하여 SQL Server와 통신합니다. TDS는 단순한 프로토콜로 SQL Server에서 메시지를 받으면 이벤트 발생으로 간주됩니다. 먼저 연결 상태에서 클라이언트가 로그인 메시지나 이벤트를 보낸 다음 이에 대한 성공이나 실패 응답을 받습니다. SQL 문으로 보내려면 SQL 언어 메시지를 사용하여 클라이언트가 패키지화하여 SQL Server로 보낼 수 있도록 합니다. 또한 저장 프로시저, 시스템 프로시저 또는 이후에 논의할 의사 시스템 저장 프로시저를 호출하려면 SQL Server의 RPC 이벤트에 해당하는 RPC 메시지를 클라이언트에서 보낼 수도 있습니다. 마지막 두 프로시저의 경우 서버에서는 토큰 데이터 스트림으로 결과를 되돌려 보냅니다. 이는 SQL Server 구성 요소 간의 사적 계약으로 간주되므로 Microsoft에서 실제 TDS 메시지를 문서화하지 않습니다.
카탈로그 저장 프로시저는 클라이언트/서버 상호 작용의 또 다른 핵심 요소입니다. 카탈로그 저장 프로시저는 SQL Server 6.0의 ODBC에서 처음 소개되었으며 sp_tables와 sp_columns 등의 프로시저를 포함합니다. ODBC와 OLE-DB API는 데이터베이스 개체에 대한 메타데이터를 기술하는 표준 방법을 정의합니다. 모든 종류의 RDBMS에 이러한 표준을 사용할 수 있어야 하므로 SQL Server의 각 시스템 테이블에 맞춰 표준이 조정되지는 않습니다. 클라이언트에서 시스템 테이블에 대한 여러 쿼리를 서버로 보내 클라이언트에 있는 메타데이터의 이러한 표준 뷰로 만드는 대신 시스템 저장 프로시저 집합이 서버에 만들어져 해당 API에 적절한 형식으로 정보를 반환합니다. 이렇게 함으로써 여러 중요한 메타데이터 요청이 단 한 번의 라운드트립으로 완료됩니다.
ODBC용으로 작성된 프로시저는 문서화되어 있으며 다른 메커니즘에서 제공되지 않는 시스템 테이블의 정보가 필요한 경우 이 프로시저를 유용하게 사용할 수 있습니다. 이를 통해 Transact-SQL 프로시저와 DB-Library 응용 프로그램은 SQL Server 시스템 테이블에 대한 복잡한 쿼리를 작성하지 않고도 메타데이터에 액세스하여 앞으로 Microsoft에서 시스템 테이블에 적용할 변경 내용이 응용 프로그램에 적용되지 않도록 할 수 있습니다.
OLE DB는 ODBC 메타데이터와 다르지만 유사한 점이 있는 일련의 스키마 행 집합을 정의합니다. 이러한 스키마 행 집합을 효율적으로 채우기 위해 새 카탈로그 저장 프로시저 집합이 만들어졌습니다. 그러나 저장 프로시저가 이전 기능을 복제하므로 이 새로운 저장 프로시저 집합은 문서화되지 않았습니다. 메타데이터를 구할 수 있는 기존의 여러 가지 방법을 사용할 수 있으므로 SQL Server 팀은 프로그래밍 모델에 도움이 되지 않는 기능을 문서화하지 않기로 결정했습니다.
클라이언트가 서버와 상호 작용하는 방법에 대해서는 제3의 측면이 있습니다. 이 개념은 SQL Server 6.0에서 처음 도입되었지만 크게 부각되지는 않았습니다. 이는 의사 시스템 저장 프로시저의 개념으로 SQL Server 7.0에서 매우 중요한 역할을 합니다. 처음 SQL Server 6.0에 서버 커서가 개발되었을 때 개발자들은 클라이언트/서버 상호 작용을 관리하는 방법을 선택할 수 있었습니다. 커서는 클라이언트에서 추가 SQL 문을 지정하지 않고 데이터 행이 반환되도록 하므로 사용 가능한 TDS 메시지 집합에 완벽하게 적합하지는 않았습니다. 개발자가 TDS 프로토콜에 메시지를 추가할 수는 있지만 그렇게 하려면 많은 다른 구성 요소를 변경해야 했습니다. SQL Server 6.0의 TDS 버전 또한 상호 운영성을 보장하기 위해 Sybase 버전에 가깝게 만들어졌으므로 개발자들은 대체 메커니즘을 사용하게 되었습니다. 이들은 저장 프로시저가 실제로는 단순히 SQL Server 코드 기준으로의 진입점임에도 새로운 서버 커서 기능이 시스템 저장 프로시저로 보이게 만들었습니다. 표준 RPC TDS 메시지를 사용하여 클라이언트 응용 프로그램에서 이를 호출합니다. 클라이언트에서 저장 프로시저처럼 호출되지만 다른 저장 프로시저처럼 단순한 SQL 문으로 이루어진 것이 아니므로 이 프로시저를 의사 시스템 저장 프로시저라고 합니다. 대부분의 이러한 의사 시스템 저장 프로시저는 개인 프로시저이고 문서화되지 않았습니다. 커서 프로시저의 경우 모든 API가자체의커서 API 모델 집합과 고유 커서 조작 함수를 보여 주므로 저장 프로시저 자체를 문서화할 필요가 없습니다. Transact-SQL 언어에도 DECLARE, OPEN, FETCH 등을 사용하여 커서를 보여 줄 수 있는 구문이 있으므로 내부적으로만 사용되는 sp_cursor와 같은 의사 시스템 저장 프로시저를 문서화할 필요가 전혀 없습니다.
ODBC와 OLE DB는 매개 변수화된 쿼리와 준비/실행 모델의 개념을 보여 줍니다. SQL Server 7.0 이전에는 클라이언트 API의 코드로 이러한 개념이 구현되었습니다. SQL Server 7.0에서 Microsoft는 관계형 서버에 이러한 개념에 대한 지원을 추가하여 새 의사 시스템 저장 프로시저를 통해 이러한 지원을 보여 줍니다. 이러한 기능과 서버에서 이 기능을 지원하는 방법에 대해서는 이 문서의 후반부에서 살펴봅니다. sp_executesql 프로시저를 통한 매개 변수화된 쿼리 지원은 직접 Transact-SQL 및 DB-Library를 사용할 때 유용하며 이 프로시저는 문서화되었습니다. 준비/실행 프로시저는 ODBC 드라이버 및 OLE DB 공급자에서만 사용됩니다.
SQL Server와 통신하는 모든 클라이언트는 TDS 프로토콜, 카탈로그 저장 프로시저, 의사 시스템 저장 프로시저의 세 가지 기능 집합을 기반으로 만들어졌습니다.
서버 아키텍처
SQL Server, 좀 더 구체적으로 SQL Server 관계형 서버는 일반적으로 관계형 엔진과 저장소 엔진의 두 부분으로 구성됩니다. 이미 언급한 대로 저장소 엔진에 대한 문서는 많이 있으므로 이 문서에서는 관계형 엔진의 기능을 중점적으로 설명합니다. 그림 3에서는 SQL Server 관계형 엔진 부분의 기본 구성 요소를 보여 줍니다. 그림의 구성 요소를 세 개의 하위 시스템 그룹으로 분류할 수 있습니다. 그림의 왼쪽은 쿼리 최적화 프로그램을 포함하여 쿼리 컴파일을 지시하는 구성 요소입니다. 최적화 프로그램은 모든 관계형 데이터베이스 엔진에서 가장 모호한 부분 중 하나이지만 성능 측면에서 볼 때 가장 중요한 부분이기도 합니다. 쿼리 최적화 프로그램에서는 SQL에 표현된 비프로시저 요청을 받아 들여 디스크 I/O, 필터링 및 이 요청을 효율적으로 만족시키는 다른 프로시저 논리로 변환합니다. 그림의 오른쪽은 실행 인프라이며 이 부분은 아주 작은 기능 집합입니다. 컴파일 구성 요소에서 해당 작업을 마치면 몇 가지 서비스를 제공함으로써 바로 실행할 수 있는 것을 만들어냅니다.
그림 3. 서버 아키텍처
그림의 중앙은 SQL 관리자라고 하는 부분으로 SQL Server 내의 모든 흐름을 제어합니다. SQL 관리자에서는 RPC 메시지를 처리하며 SQL Server 7.0에서는 클라이언트로부터의 기능을 대부분 호출합니다. 이전 단원에서 논의된 의사 시스템 저장 프로시저 또한 논리적으로는 SQL 관리자의 일부입니다. 일반적으로 TDS SQL 언어 메시지로 들어오는 SQL 문은 컴파일할 때 직접 처리합니다. SQL Server 7.0에서는 이전 버전에서만큼 빈번하지는 않지만 일반적으로 사용되는 방법입니다. ODS를 호출하여 TDS 결과 메시지의 형식을 지정하는 실행 엔진의 구성 요소에서 결과를 되돌립니다.
출력의 대부분은 그림의 실행쪽에서 제공하며 그 결과물은 실제로 표현 서비스에서 제공합니다. Expression Services 라이브러리는 데이터 변환, 평가 예측(필터링), 산술 계산 등의 작업을 하는 구성 요소입니다. 이 라이브러리는 또한 ODS 계층과 작업하여 그 출력 결과물을 TDS 메시지 형식으로 지정합니다.
또한 관계형 엔진에서 추가 서비스를 제공하는 두 가지 구성 요소에 대해 간단하게 설명합니다. 하나는 CREATE TABLE, CREATE VIEW 등과 같은 정의 문을 처리하는 카탈로그 서비스 구성 요소로 카탈로그 서비스는 의사 테이블을 유형화하여 시스템 테이블을 처리합니다. 카탈로그 서비스 구성 요소는 기본적으로 관계형 엔진 내에 위치하지만 실제로 구성 요소 작업의 1/3은 저장소 엔진 영역 내에서 수행되므로 이 구성 요소는 공유 구성 요소로 간주됩니다.
다른 하나는 파이버와 스레드를 처리하기 위한 SQL Server 자체의 내부 스케줄러인 UMS(User Mode Scheduler)입니다. 이 스케줄러에는 서버 구성에 따라 파이버나 스레드에 작업이 할당되는 방법을 예약하여 SQL Server에서 SMP 시스템의 여러 프로세서 간에 적절하게 로드 균형을 조정하는 매우 복잡한 내부 메커니즘이 있습니다. 또한 UMS는 SQL Server가 동시에 너무 많은 스레드를 실행하는 것을 방지합니다. 마지막으로 이 스케줄러에는 우리에게 친숙한 시스템 프로시저가 있습니다. 논리적으로 시스템 프로시저는 관계형 엔진의 일부입니다. 쉽게 sp_helptext를 사용하여 이러한 시스템 프로시저를 정의하는 Transact-SQL 코드를 조사할 수 있으므로 이 프로시저는 분명히 서버 코드가 아닙니다. 그러나 시스템 프로시저의 목적은 응용 프로그램 사용을 위한 더 높은 그리고 더 적절한 수준에서 시스템 테이블과 같은 기본 서버 기능을 보여 주는 데 있으므로 이 프로시저를 서버의 일부로 간주합니다. 응용 프로그램 개발자가 더 쉽게 사용할 수 있는 높은 수준의 시스템 프로시저를 인터페이스로 사용하면 릴리스마다 기본 수준 시스템 테이블이 변경되더라도 응용 프로그램이 계속 작동할 수 있습니다.
SQL 처리 시 클라이언트/서버 상호 작용
이제 클라이언트 응용 프로그램이 SQL Server와 상호 작용할 때 일어나는 일에 대해 알아봅니다. 다음은 ODBC 호출에 대한 예제입니다.
SQLExecDirect(hstmt, "SELECT * FROM parts where partid =7", SQL_NTS)
OLE-DB에도 이와 거의 동일한 호출이 있지만 처리 과정이 ODBC 호출과 동일하므로 OLE-DB의 경우는 설명하지 않습니다. ODBC 호출은 SQL 문을 가져온 다음 SQL Server로 보내 실행합니다.
이 쿼리에서는 특정 부품 ID를 갖는 행에 대해 parts 테이블의 모든 열을 선택합니다. 다음은 임시 SQL의 기본 예제입니다. SQL Server 7.0 이전의 릴리스에서는 저장 프로시저와 비교되는 임시 SQL의 두드러진 특징 중 하나로 쿼리 최적화 프로그램에서 생성된 계획이 캐시되지 않는다는 점을 들 수 있습니다. 쿼리를 입력하면 컴파일되고 실행된 다음 계획은 폐기됩니다. 이후에 논의할 내용이지만 SQL Server 7.0에는 실제로 임시 쿼리의 계획을 캐시할 수 있는 메커니즘이 있습니다.
이 문을 SQL Server로 보내기 전에 몇 가지 제기해야 할 질문이 있습니다. 클라이언트는 모두 일종의 커서 개념을 제공하므로 클라이언트 내부에서 프로그래머가 요청할 결과 집합이나 커서 종류에 대해 질문을 제기해야 합니다. 가장 빠른 유형은 이 문서에서기본 결과 집합이라고 하는 유형입니다. 이 커서를 firehose 커서라고 하는데 전혀 커서로 간주되지 않는 경우도 있습니다. SQL 요청이 서버로 보내지면 서버는 다시 클라이언트로 결과를 보내기 시작하며 클라이언트에서 전체 집합을 모두 소비할 때까지 계속 보냅니다. 이는 거대한 소방 호스에서 클라이언트로 데이터를 뿜는 것에 비유할 수 있습니다.
클라이언트에서 이를 기본 결과 집합으로 결정하면 다음 작업으로 매개 변수 표식이 있는지 확인합니다. ODBC 및 OLE-DB에서 이 SQLExecDirect 호출을 사용할 때의 옵션 중 하나로 WHERE 절의 7과 같이 특정 값을 제공하는 대신 다음 예제처럼 상수를 물음표로 대체하여 매개 변수 표식을 전달할 수 있습니다.
SQLExecDirect(hstmt, "SELECT * FROM parts where partid =?", SQL_NTS)
별도로 매개 변수의 실제 값을 제공해야 한다는 점에 유의하십시오.
클라이언트는 SQL 문에 매개 변수 표식이 있는지, 특정 값이 있는지, 아니면 임시 매개 변수화되지 않은 SQL인지 인식하고 있어야 합니다. 이러한 사항은 클라이언트가 이 문에 내부적으로 수행하는 작업에 영향을 주며 실제로 SQL Server에 메시지로 보내지는 내용을 결정합니다. 이 경우에는 물음표가 없으므로 클라이언트가 단순히 이 요청을 SQL 언어 TDS 메시지로 보낸 다음 firehose 의 끝에서 그 결과를 받으려 한다는 것을 분명히 알 수 있습니다. 그런 다음 클라이언트는 응용 프로그램의 매개 변수에 따라 그 결과를 응용 프로그램에 반환할 수 있습니다. ODBC나 OLE DB API를 통한 요청의 측면에서 클라이언트의 내부 처리 선택 사항은 약간 모호할 수도 있습니다. 예를 들어, 응용 프로그램에서는 기본 결과 집합을 직접 요청하지 않습니다. 대신 ODBC에서 읽기 전용 커서와 앞으로 가기 전용 커서를 요청하면 클라이언트 내부에 관한 한 기본 결과 집합인 firehose 커서로 정의되는 행이 한 번에 하나씩 제공됩니다.
firehose 커서에는 기본적으로 한 가지 문제점이 있습니다. 모든 행을 다 소모한 후에야 클라이언트가 다른 SQL 문을 서버로 보낼 수 있습니다. 결과 집합에는 많은 수의 행이 있으므로 일부 응용 프로그램은 firehose 커서와 잘 작동하지 않습니다. 이후에 논의할 빠른 앞으로 가기 전용 커서는 특히 이 문제를 해결하기 위해 만들어진 SQL Server 7.0의 새로운 기능입니다.
SQL Server 7.0 이전 버전에서는 상수 대신 매개 변수 표식을 사용했는지 여부와 상관 없이 거의 동일한 방법으로 SQLExecDirect 호출이 처리되었습니다. 매개 변수 표식을 지정하면 클라이언트는 실제로 다른 호출(이 단원 시작 부분 예제에서의 값 "7")을 통해 사용자가 제공한 값을 취한 다음 물음표가 있는 위치에 삽입합니다. 그런 다음 다른 값으로 대체된 새 문이 임시 SQL 문으로 보내집니다. 서버에서 매개 변수화된 SQL을 사용함으로써 얻을 수 있는 이점은 없었습니다.
그러나 SQL Server 7.0에서 SQLExecDirect에 매개 변수 표식을 사용하면 SQL Server로 보내진 TDS 메시지는 SQL 언어 메시지가 아닙니다. 대신 sp_executesql 프로시저를 사용하여 서버로 보내지므로 TDS 프로토콜과 관련된 한 이는 RPC입니다. 클라이언트에서는 결과가 기본적으로 같고 클라이언트는 firehose 데이터를 다시 받습니다.
firehose 데이터를 다시 받지 않으려면 블록 커서나 스크롤 가능한 커서를 사용합니다. 이 경우 흐름이 복잡해집니다. SQL 텍스트를 통과하는 sp_cursoropen 진입점(의사 저장 프로시저 중 하나)으로 호출이 이루어집니다. sp_cursoropen은 SQL을 조작하여 스크롤 가능하게 만드는 논리를 추가하여 잠재적으로 일부 결과를 임시 테이블로 리디렉션한 다음 커서가 이제 열려 있음을 나타내는 커서에 핸들로 응답합니다. 프로그래머가 제어할 수 없는 영역에서 클라이언트는 sp_cursorfetch를 호출하고 사용자 응용 프로그램으로 반환된 하나 이상의 행을 클라이언트로 보냅니다. 클라이언트는 또한 sp_cursor를 사용하여 커서의 위치를 다시 지정하거나 특정 통계값을 변경합니다. 사용자가 커서 처리 작업을 마치면 클라이언트는 sp_cursorclose를 호출합니다.
이제 클라이언트에 하나의 행을 반환하는 단순한 경우를 검토해 봅니다. 기본 결과 집합의 경우에는 클라이언트에서 서버로, 다시 서버에서 클라이언트로 한 번의 메시지 라운드트립이 있습니다. 서버로 향하는 SQL 메시지 또는 sp_executesql이 있으며 그런 다음 결과가 다시 돌아옵니다. 동일한 하나의 행에 대해 firehose 커서가 아닌 경우 SQL Server에서 기존과 같은 상황이 발생합니다. 열기 작업을 하기 위한 라운드트립, 페치(fetch) 작업을 하기 위한 라운드트립, 닫기 작업을 하기 위한 라운드트립이 있습니다. 이 프로세스는 기본 결과 집합에서 사용하는 메시지의 세 배를 사용합니다. SQL Server 7.0에는 동일한 커서 인프라를 사용하는 빠른 앞으로 가기 전용 커서가 있습니다. 이 커서를 사용하면 추가 SQL 메시지를 보내기 전에 모든 결과 행을 처리할 필요가 없다는 점에서 firehose와 다릅니다. 따라서 다섯 개의 행을 가져왔는데 데이터가 남아 있는 경우 서버로 업데이트를 보낼 수 있습니다.
빠른 앞으로 가기 전용 커서는 서버에서 일반 커서보다 빠르고 두 개의 추가 옵션을 지정할 수 있도록 합니다. 하나는 autofetch이고 하나는 autoclose입니다. autofetch는 열기에 대한 응답 메시지의 일부로 첫 번째 행 집합을 반환합니다. autoclose는 마지막 행이 읽혀진 후 자동으로 커서를 닫습니다. 이 커서는 앞으로 가기 전용 및 읽기 전용이므로 뒤로 스크롤할 수 없습니다. SQL Server는 커서가 닫혔음을 알리는 이 마지막 데이터 집합과 함께 메시지를 전달합니다. 빠른 앞으로 가기 전용 커서를 사용하면 적은 수의 행에 대한 통신을 동일한 하나의 라운드트립으로 할 수 있습니다. 행 수가 많으면 최소한 각 행 블록에 대해 추가 비용을 지불하기만 하면 됩니다. 빠른 앞으로 가기 전용 커서를 사용하면 커서 처리가 기본 결과 집합의 처리와 상당히 비슷하게 됩니다.
그림 4에서는 SQLExecDirect 모델의 흐름을 보여 줍니다.
그림 4. 클라이언트/서버 상호 작용
준비/실행 모델
ODBC에서 SQLExecDirect로 호출하는 실행 직접 모델 이외에 ODBC와 OLE-DB에서 볼 수 있는 실행 모델에는 준비/실행 모델이 있습니다. SQL이 실행되도록 정의하는 작업은 실제 SQL 실행과는 별도의 단계에서 이루어집니다. 다음 예제에서는 ODBC에서의 이러한 작업을 보여 줍니다.
SQLPrepare(hstmt, "SELECT * FROM parts where partid =?", SQL_NTS)SQLExecute(hstmt)
준비/실행은 SQL Server 7.0 이전의 SQL Server에 대한 원시 모델이 아닙니다. 7.0에는 원시 인터페이스를 제공하는 두 개의 의사 시스템 저장 프로시저가 있습니다. 준비 호출에 대해서는 다시 커서의 종류가 무엇인지 살펴본 다음 sp_prepare와 sp_cursorprepare 중 하나를 호출합니다. 준비/실행은 SQL이나 저장 프로시저의 컴파일 부분을 처리하지만 실제로 계획을 실행하지는 않습니다. 대신 의사 시스템 저장 프로시저에서 계획에 핸들을 반환합니다. 이제 예를 들어, 다시 컴파일할 필요 없이 응용 프로그램에서는 서로 다른 매개 변수 값을 전달하면서 반복적으로 SQL을 재실행할 수 있습니다.
SQL Server 6.5에는 원시 인터페이스가 없으므로 준비와 실행의 두 단계가 열거되어야 했으며 다음 두 가지 방법 중 하나로 이러한 작업이 이루어집니다. 첫 번째 방법에서는 준비 단계가 일어나지 않습니다. 메타데이터를 반한하기 위한 부분적인 실행만 있으므로(해당 설정 옵션 있음) SQL Server에서는 결과의 형식에 대한 설명을 다시 응용 프로그램으로 보낼 수 있습니다. 두 번째 방법에서는 SQL Server에서 실제로 개별 사용자에게는 개인적인 임시 저장 프로시저를 만들므로 계획을 공유하지 않습니다. 두 번째 방법을 사용하면 tempdb 데이터베이스가 모두 채워질 수 있으므로 대부분의 응용 프로그램 개발자는 ODBC 구성 대화 상자에서 해당 확인란 선택을 해제함으로써 두 번째 방법을 사용하는 옵션을 해제합니다.
SQL Server 7.0에서 준비/실행 메서드는 SQL Server의 원시 기능으로 SQL 문이 준비된 다음 실행됩니다. 기본 결과 집합의 경우 응용 프로그램 프로그래머가 준비 작업에서 제공된 핸들로 sp_execute를 호출한 다음 문이 실행됩니다. 커서의 경우 다른 커서 처리와 동일하게 보이며 커서가 빠른 앞으로 가기 전용 커서일 때에는 autofetch와 autoclose를 허용하는 등의 동일한 특징이 있습니다.
그림 5에서는 준비/실행 작업의 흐름을 보여 줍니다.
그림 5. 준비/실행 모델
저장 프로시저 호출
일반적으로 ODBC 표준 CALL 구문을 사용하여 프로시저를 호출하는 SQL Server로 SQL 문을 보냄으로써 ODBC와 OLE-DB로부터 저장 프로시저를 호출합니다. 이는 다음과 비슷합니다.
SQLExecDirect(hstm, "{call addorder(?)}", SQL_NTS)기본 결과 집합의 경우 RPC 메시지의 원래 의도대로 이 흐름은 단순합니다. 클라이언트에서 서버로 RPC 메시지를 보낸 다음 프로시저로부터의 결과를 다시 받습니다. 커서인 경우에는 약간 더 복잡합니다. 클라이언트에서 다른 커서와 마찬가지로 sp_cursoropen을 호출합니다. Sp_cursoropen에는 저장 프로시저에 하나의 SELECT 문만 있는지 검사하기 위해 내장된 논리가 몇 가지 있습니다. 하나의 SELECT 문만 있으면 해당 SELECT에서 커서가 열립니다. 프로시저에 있는 유일한 SELECT 문이 아니면 클라이언트에서는 "대신 열었지만 firehose로 결과를 다시 스트림할 것이므로 이 결과를 사용자에게 제공할 수 있습니다."라고 알리는 표시기와 함께 메시지를 되돌려 받습니다.
그림 6에서는 저장 프로시저 처리에 대한 실행 흐름을 보여 줍니다.
그림 6. 저장 프로시저 호출
SQL 관리자
이전에 살펴보았던 SQL 관리자는 여러 서버 작업에 있어 원동력이 되고 서버의 핵심에 있습니다. SQL 관리자는 모든 요청을 처리하여 저장 프로시저를 실행합니다. SQL 관리자는 프로시저 캐시를 관리하고 의사 시스템 저장 프로시저를 포함하며 임시 쿼리의 자동 매개 변수화 작업에 참여합니다. 이에 대해서는 간략하게 설명합니다. SQL 6.5나 그 이전 버전에 대해 이 글과 유사한 글을 읽은 적이 있더라도 SQL 관리자에 대한 설명은 없는 대신 SQL 관리자가 하는 작업의 일부를 수행하는 다른 구성 요소에 대한 설명은 있었을 것입니다. 그러나 SQL Server 7.0에서는 이러한 모든 구성 요소가 시스템에서 쿼리 처리를 추진하는 하나의 구성 요소로 통합되었습니다.
일반적으로 SQL Server에 작업을 요청하는 것과 마찬가지로 RPC 메시지를 사용하여 SQL 관리자를 호출합니다. 그러나 SQL 메시지를 통해 SQL 언어 문이 들어온 다음 엔진의 컴파일 쪽으로 들어가게 되면 SQL 관리자 또한 작업에 개입하게 됩니다. EXEC가 실제로 SQL 관리자를 호출하므로 프로시저나 배치에 EXEC 문이 있을 때 SQL 관리자가 개입될 수 있습니다. SQL 문이 이후에 논의할 항목인 자동 매개 변수화 템플릿을 전달하면 SQL 관리자가 호출되어 쿼리를 매개 변수화합니다. 캐시에 임시 쿼리를 배치해야 할 때에도 SQL 관리자가 호출됩니다.
컴파일 및 실행
이제 SQL Server 내에서 컴파일과 실행이 일어나는 방법에 대해 살펴봅니다. 컴파일과 실행은 SQL Server 내에서 별개로 일어나는 두 단계입니다. SQL Server가 쿼리를 컴파일하는 시간과 쿼리가 실행되는 시간 간의 간격은 몇 밀리초로 매우 작을 수도 있고 몇 초, 몇 분, 몇 시간, 며칠이 될 수도 있습니다. 최적화를 포함한 컴파일에서 우리가 컴파일의 일부로 사용할 수 있는 지식의 종류를 구분해야 합니다. 컴파일할 때 해당되는 사항이 실행할 때에도 해당되는 것은 아니므로 컴파일과 실행을 서로 다른 별도의 작업으로 생각해야 합니다. 임시 SQL 문으로 쿼리를 보낸 다음 즉시 실행하는 경우에도 컴파일과 실행은 별개의 작업입니다.
SQL Server에서 쿼리를 처리할 준비가 되면 SQL 관리자가 캐시에서 이 쿼리를 조회합니다. 캐시에 쿼리가 없으면 컴파일된 것입니다. 컴파일 프로세스에서는 몇 가지 작업을 수행합니다. 첫째로 구문 분석과 정규화 작업을 합니다. 구문 분석을 하면 SQL 문을 분석한 다음 컴퓨터에서 더 쉽게 처리할 수 있는 데이터 구조로 바꾸고 문이 올바른지 검사합니다. 테이블과 열 이름이 유효한지 여부를 확인하는 등의 작업은 분석에 포함되지 않고 정규화 작업을 하는 동안 수행됩니다. 기본적으로 정규화는 SQL 내에서 실제 특징이 무엇인지를 참조하는 항목이 데이터베이스에 있는지 확인한 다음 요청하는 의미론이 타당한지 여부를 확인하는 작업을 하도록 만들어졌습니다. 예를 들어, 테이블을 실행하려는 것은 의미론적으로 타당하지 않습니다.
그 다음 단계는 Transact-SQL 코드를 컴파일하는 것입니다. 일반 사용자는 Transact-SQL과 SQL 자체 간의 차이점을 분명하게 구별하는 것이 모호하고 Microsoft의 개발자들 또한 Transact-SQL과 SQL을 동일하게 사용합니다. 그러나 이 두 항목 사이에는 중요한 차이점이 있습니다. SQL은 INSERT, UPDATE, DELETE, SELECT와 같은 DML 문입니다. SQL Server에도 이러한 DML 문을 포함하는 Transact-SQL 또는 TSQL이라고 하는 언어가 있어 IF 문, WHILE 문, 지역 변수 선언 등의 프로시저 구문을 제공합니다. 서버 내에서 이러한 구문들은 서로 완전히 다르게 취급되고 프로시저 작업을 수행할 수 있는 엔진에서 TSQL의 프로시저 논리를 컴파일합니다.
SQL 문 자체는 기본 쿼리 최적화 프로그램에서 컴파일됩니다. 최적화 프로그램은 집합 기준 SQL 문의 비프로시저 요청을 프로시저로 변환하고 이 프로시저가 효율적으로 실행되어 원하는 결과를 반환할 수 있도록 합니다. 이후부터는 별도로 언급하지 않는 한 컴파일은 TSQL의 컴파일과 SQL 문의 최적화 둘 다를 의미합니다.
앞에서 컴파일과 실행은 쿼리 처리에 있어 별도의 두 단계임을 언급한 적이 있습니다. 따라서 최적화 프로그램에서 하는 작업 중 하나는 매우 안정적인 상태를 바탕으로 최적화를 수행하는 것입니다. SQL Server에서 충족되는 특정 기준에 따라 문을 다시 컴파일할 수도 있으므로 이 안정적 상태는 영구히 안정적인 것을 의미하는 것이 아니라 일정한 유입 상태에 있지 않는 상태를 의미합니다. 최적화 프로그램에서 매우 많이 그리고 아주 자주 변경되는 정보를 사용하면 동시 처리의 수나 잠금 수가 고정되므로 쿼리를 계속해서 다시 컴파일해야 되고 컴파일 진행이 느려집니다. 예를 들어, 1/100초로 실행되는 SQL 문을 컴파일하는 데 1/2초가 소요될 수 있습니다. 일단 SQL Server에서 이 문을 컴파일한 다음에는 SQL Server에서 모든 문마다 컴파일하게 하는 것보다는 몇 번이든지 직접 컴파일하는 것이 좋습니다.
마지막으로 컴파일 단계에서는 프로시저 캐시로 삽입되는 쿼리 계획을 만듭니다. 비용이 저렴한 임시의 SQL 계획은 실제로 캐시로 들어가지 않지만 여기서는 이에 대해 자세하게 설명하지 않습니다. 다시 사용하지 않을 내용으로 캐시를 넘치게 할 필요가 없으며 임시 SQL은 그 중 많은 부분을 차지하고 있고 해당 계획을 다시 사용하지 않을 가능성이 가장 높은 쿼리 유형입니다. 해당 문을 1/100초 미만으로 저렴하게 컴파일할 수 있으면 캐시에 계획을 저장하여 다시 사용하지 않을 계획으로 캐시를 채울 필요가 없습니다.
캐시에 계획이 삽입된 후 SQL 관리자는 계획을 실행하고 변경된 내용이 있는지와 계획을 다시 컴파일해야 하는지를 확인하는 작업의 관점에서 해당 논리를 다시 살펴봅니다. 컴파일과 실행을 분리하는 시간 간격은 마이크로 초에 불과하지만 그 동안 누군가가 중요 테이블에 색인을 추가하는 DDL(데이터 정의 언어) 문을 실행했을 수도 있습니다. 이러한 일이 일어날 가능성은 거의 없지만 일어날 수도 있으므로 SQL Server에서는 이를 감안해야 합니다. SQL Server가 저장된 계획을 다시 컴파일하게 하는 몇 가지 요인이 있습니다. 인덱스를 추가하거나 제거하는 등 메타데이터가 변경되는 것이 가장 주요한 원인입니다. 이때 서버에서는 사용된 계획이 현재 인덱스 상태를 반영하도록 해야 합니다.
컴파일을 다시 하는 또 다른 원인은 통계 변경에 있습니다. SQL Server에서는 처리하는 데이터에 약간의 히스토그램 정보를 유지합니다. 데이터 분산이 많이 변경된 경우 효율적으로 실행하려면 다른 쿼리 계획이 필요합니다. SQL Server는 데이터가 테이블에 삽입되거나 삭제되는 빈도를 추적하여 변경 횟수가 테이블 크기를 기준으로 변경되는 특정 임계값을 초과하면 다시 되돌아가 새 분산 정보를 바탕으로 계획을 다시 컴파일합니다.
그림 7에서는 컴파일과 실행의 흐름을 보여 줍니다.
그림 7. 컴파일 및 실행
실제 매개 변수 값의 변경 및 사용 가능한 메모리 양이나 이미 캐시에 있는 필요 데이터의 양 등 환경이 변함에 따라서는 계획이 다시 컴파일되지않는다는 점에 유의하십시오.
실행은 직선적 작업입니다. 따라서 "한 행 삽입"이나 고유 인덱스가 있는 테이블에서 하나를 선택하는 등의 매우 간단한 쿼리를 실행할 때는 그 처리 과정이 매우 단순합니다. 그러나 대부분의 쿼리를 효과적으로 실행하거나 최소한 메모리의 이점을 취하려면 많은 메모리가 필요합니다. SQL Server 6.5에서는 쿼리에서 사용할 수 있는 메모리 양을 0.5MB나 1MB로 제한함으로써 메모리 사용을 제어했습니다. SQL Server 6.5에는 쿼리에서 사용하는 메모리 양을 제어하는 구성 매개 변수인sort pages가 있었습니다. 이름을 통해 알 수 있듯이 이 매개 변수는 기본적으로 많은 양의 메모리를 사용해야 효과적으로 실행될 수 있는 작업을 정렬합니다. 그러나 많은 메모리를 필요로 하는 작업이라도 SQL Server 6.5에서는 특정 작업에 1MB 이상을 사용할 수 없었습니다. 메모리가 1GB나 2GB인 컴퓨터에서 작업하고 행이 수백만 개라 하더라도 마찬가지로 1MB 이상을 사용할 수 없었습니다. 따라서 복잡한 쿼리에는 효과적이지 않으므로 이에 따라 SQL Server 개발자들은 개별 쿼리에 많은 양의 메모리를 사용하도록 하는 기능을 SQL Server 7.0에 추가했습니다.
많은 메모리를 사용할 수 있게 됨에 따라 또 다른 문제가 발생하게 되었습니다. 일단 쿼리에서 많은 양의 메모리를 사용하기 시작하면 이 메모리를 사용할 여러 쿼리 간에 메모리를 조정할 방법을 결정해야 합니다. SQL Server에서는 이러한 문제를 다음과 같이 처리합니다. 쿼리 계획이 최적화되면 최적화 프로그램은 해당 쿼리의 메모리 사용에 대해 두 가지 정보를 결정합니다. 첫째, 쿼리를 효과적으로 실행하기 위해 필요한 최소 메모리 양을 선택하면 이 값이 쿼리 계획에 저장되고 최적화 프로그램에서 쿼리가 제대로 실행되기 위해 필요한 최대 메모리 양을 결정합니다. 예를 들어, 정렬하고 있는 전체 테이블이 100MB에 적합할 경우에는 2GB의 메모리를 제공할 필요가 없습니다. 실제로 필요한 메모리는 100MB이므로 이 값이 쿼리 계획에 저장되는 메모리 최대 사용량이 됩니다.
SQL Server에서 계획을 실행하려고 하면 이 계획이 메모리 부여 스케줄러라고 하는 루틴으로 전달됩니다. 부여 스케줄러는 몇 가지 흥미로운 작업을 수행합니다. 첫째, 부여 스케줄러에서 검토하고 있는 쿼리에 계획의 일부로 정렬이나 해시 작업이 없으면 SQL Server는 이 쿼리에서 많은 메모리를 사용하지 않는다는 것을 인지합니다. 이 경우 메모리 부여 스케줄러는 대기하지 않습니다. 계획은 즉시 실행될 수 있고 따라서 일반 트랜잭션 처리 요청에서는 이 메커니즘이 철저히 무시됩니다. 또한 메모리 부여 스케줄러에는 여러 크기의 요청을 처리하는 데 사용할 수 있는 몇몇 대기열이 있습니다. 메모리 스케줄러는 크기가 작은 요청에 우선 순위를 줍니다. 예를 들어, "최상위 10개 선택"이라는 쿼리가 있고 20개의 행만 정렬하려는 경우 이 쿼리는 메모리 부여 스케줄러를 통과해야 하지만 신속하게 스케줄러에서 나온 다음 빨리 실행되도록 일정이 만들어집니다. 서버에서는 이러한 여러 개의 작은 쿼리를 병렬로 또는 동시에 실행하려고 하기 때문입니다.
여러 개의 대규모 쿼리가 있는데 이 중 몇 개만 실행하려면 쿼리에서 필요한 메모리 양을 늘리도록 합니다. SQL Server에서는 이 값을 4X(시스템의 CPU 수)가 되도록 계산합니다. 가능하면 SQL Server에서는 각 쿼리에 효과적인 최소 메모리 크기가 주어지면서 이 수에 해당하는 쿼리를 동시에 실행합니다. 그러고도 남은 메모리가 있으면 일부 쿼리에서는 효과적인 최대 메모리를 사용할 수 있습니다. SQL Server에서는 쿼리에 사용할 수 있는 메모리 양을 최대화하는 동시에 시스템에서 많은 쿼리를 실행합니다.
효과적인 최대 메모리 사용 기능을 야간 배치 사이클 등의 작업에서 아주 유용하게 사용할 수 있습니다. 매우 큰 보고서를 생성하거나 인덱스를 다시 만들어야 하는 경우가 있습니다. 이러한 쿼리는 아주 많은 메모리를 사용할 수 있으므로 이 메커니즘이 필요에 따라 동적으로 조정합니다. 따라서 대기열에서 오래 대기하지 않는 경우 메모리 부여 스케줄러가 매우 자주 쿼리에 요청된 최대 메모리를 제공합니다. 주간에 복잡한 처리가 필요한 작업을 하는 경우 더 많은 쿼리를 동시에 실행하게 됩니다. 이러한 쿼리는 효과적으로 실행될 수 있도록 최소 메모리 할당만큼을 가져가지만 메모리는 공유 리소스보다 많이 사용하게 됩니다.
일단 스케줄러에서 요청에 메모리를 할당할 수 있음을 알리면 계획이 "열립니다". 즉, 실제 실행이 시작됩니다. 여기서부터 계획이 실행되기 시작하여 완료됩니다. 쿼리에서 기본 결과 집합 모델을 사용하면 모든 행이 만들어질 때까지 계획이 실행되고 모두 클라이언트로 다시 보내집니다. 쿼리에서 커서 모델을 사용하면 처리가 약간 달라집니다. 이 경우 전체 행이 아닌 하나의 행 블록에 대해 각 클라이언트 요청이 이루어집니다. 각 결과 집합이 클라이언트로 다시 보내지면 SQL Server는 클라이언트에서 다음 집합을 요청할 때까지 대기해야 하고 대기하는 동안 전체 계획이정지됩니다. 즉, 잠금 일부는 해제되고 일부 리소스는 포기되며 일부 위치 정보는 저장됩니다. 이 정보를 통해 SQL Server는 다음 행 집합이 요청될 때 원래 위치로 돌아가서 실행을 계속합니다.
프로시저 캐시
SQL Server의 프로시저 캐시 개념에 대해 몇 번 언급한 적이 있습니다. SQL Server 7.0의 프로시저 캐시는 이전 버전과는 완전히 다르다는 점을 이해하는 것은 아주 중요합니다. 이전 릴리스에서는 프로시저 캐시의 크기를 제어하는 효과적인 구성 값이 두 가지 있었습니다. 한 값은 SQL Server의 사용할 수 있는 총 메모리의 고정 크기를 지정하고 나머지 값은 고정된 값을 채운 다음 쿼리 계획을 저장하는 데만 사용될 메모리의 백분율입니다. 이전 버전에서도 임시 SQL 문에 대한 쿼리 계획은 캐시에 저장되지 않고 저장 프로시저에 대한 계획만 저장되었습니다. In SQL Server 7.0에서 메모리의 총 크기는 기본적으로 동적이며 쿼리 계획에 사용되는 공간 또한 매우 유동적입니다.
쿼리를 처리할 때 SQL Server 7.0에서 가장 먼저 물어보는 것 중 하나가 쿼리가 임시 쿼리인동시에컴파일하기에 저렴한지 여부입니다. 그렇다면 SQL Server에서는 쿼리를 캐시하지 않습니다. 비용이 더 많이 드는 계획이나 데이터 페이지의 메모리를 차지하기 보다는 나중에 계획을 다시 컴파일하는 것이 더 저렴하기 때문입니다. 쿼리가 임시 쿼리가 아니거나 컴파일 비용이 저렴하지 않으면 SQL Server에서는 버퍼 캐시에서 약간의 메모리를 가져온 다음 이 메모리에 계획을 저장합니다. SQL Server 7.0에서는 서버에서 필요로 하는 메모리의 99%가 버퍼 캐시에 있으므로 메모리는 버퍼 캐시로부터 나옵니다. SQL Server에서는 대량의 메모리를 운영 체제에서 직접 할당하는 경우도 가끔 있지만 보기 드뭅니다. 그 외의 경우는 관리가 중앙 집중화되어 있습니다.
쿼리를 컴파일함으로써 실제로 계획을 만드는 데 소요되는 비용을 반영하는 비용 요소와 함께 계획이 캐시에 저장됩니다. 임시 계획일 경우 SQL Server에서는 비용을 0으로 설정합니다. 즉, 이 계획은 프로시저 캐시에서 즉시 빠져 나갑니다. 임시 SQL의 경우 다시 사용할 수는 있지만 그 가능성은 매우 낮습니다. 시스템에 메모리 압력이 있으면 임시 문에 대한 계획이 먼저 나가도록 합니다. 따라서 임시 쿼리에 대한 계획은 캐시 밖에서 부팅 가능한 것부터 나가기 시작합니다. 쿼리가 임시 쿼리가 아니면 SQL Server에서는 쿼리를 컴파일하기 위한 실제 비용으로 비용을 설정합니다. 비용 단위는 디스크 I/O이고 디스크에서 하나의 데이터 페이지를 읽으면 그 비용은 1I/O입니다. 이 계획이 만들어지면 디스크에서 통계 및 쿼리 자체의 텍스트를 포함한 정보를 읽습니다. SQL은 추가 처리를 하고 이 처리 작업은 I/O 비용에 맞게 정규화됩니다. 이제 I/O 비용에 대해 프로시저 작성 비용이 부과됩니다. 데이터를 디스크 캐싱할 양에 비해 실제로 저장 프로시저로 들어갈 캐시의 양이나 쿼리 계획 종류를 관리하는 기능에서 비용이 많이 듭니다. 비용이 계산되면 계획이 캐시에 저장됩니다.
그림 8에서는 계획에 대한 비용이 계산되고 계획이 캐시에 저장되는 흐름을 보여 줍니다.
그림 8. 캐시에 계획 삽입
계획을 다시 사용할 수 있는 다른 쿼리가 있으면 SQL Server에서는 다시 계획의 유형을 확인합니다. 임시 계획이면 SQL Server에서는 비용을 1씩 증분합니다. 따라서 임의 계획을 정말로 다시 사용하는 경우 비용 요소가 증가함에 따라 캐시에 좀 더 오래 남아있게 됩니다. 계획이 자주 다시 사용되면 실제 작성 비용에 이를 때까지 비용이 계속해서 1씩 증가합니다. 이렇게 되면 비용이 가장 높게 설정된 것입니다. 그러나 여러 번 다시 사용되므로 같은 사용자나 다른 사용자가 계속해서 똑같은 SQL 텍스트를 제출하면 이 계획은 캐시에 남아있게 됩니다.
쿼리가 임시 쿼리가 아니면, 즉 저장 프로시저, 매개 변수화된 쿼리 또는 자동 매개 변수화된 쿼리이면 계획이 다시 사용될 때마다 비용이 다시 작성 비용으로 설정됩니다. 계획은 다시 사용되는 한 캐시에 남습니다. 얼마 동안 사용되지 않더라도 처음부터 다시 만들 때 소요되는 비용이 많으면 꽤 오랫동안 캐시에 남을 수도 있습니다.
그림 9에서는 캐시에서 계획을 가져오고 비용을 조정하는 흐름을 보여 줍니다.
그림 9. 캐시에서 계획 가져오기
lazywriter는 계획을 보관하고 필요할 때 캐시에서 제거하는 메커니즘입니다. 실제로 lazywriter는 저장소 엔진의 일부이지만 쿼리 처리 메커니즘에서 아주 중요한 역할을 하므로 여기서 논의합니다. SQL Server 7.0에서는 일반 버퍼 캐시에 계획이 저장되므로 lazywriter는 쿼리 계획에서 사용하는 메모리를 관리하기 위한 메커니즘과 동일한 메커니즘을 사용하여 페이지를 관리합니다. lazywriter는 시스템의 모든 버퍼 헤더를 조사합니다. 시스템에 메모리 압력이 거의 없으면 lazywriter는 매우 느리게 실행되는 것처럼 보입니다. 메모리 압력이 증가하면 lazywriter는 더 빈번히 실행되기 시작합니다. 실행되면서 lazywriter는 버퍼 헤더와 해당 버퍼의 페이지에 대한 현재 비용을 검토합니다. 비용이 0이면 lazywriter가 마지막 실행된 후 페이지가 사용되지 않았음을 나타내고 이 경우 lazywriter는 이 페이지를 해제하여 페이지 I/O나 다른 계획에 사용할 수 있도록 시스템의 메모리를 확보합니다. 또한 버퍼에 프로시저 계획이 있으면 lazywriter에서는 정리 작업을 하도록 SQL 관리자를 호출합니다. 마지막으로 다시 사용할 수 있도록 버퍼가 사용 가능 목록에 삽입됩니다.
버퍼와 관련된 비용이 0보다 크면 lazywriter에서 비용을 감소시키고 계속해서 다른 버퍼를 조사합니다. 그러면 이 비용은 실제로 어떤 항목이 다시 사용되지 않고 캐시에 남아 있는 경우의 lazywriter의 사이클 수를 나타냅니다. 개체가 저장 프로시저인 경우 SQL 관리자를 호출하는 단계를 제외하고 이 알고리즘은 캐시의 계획과 캐시의 데이터나 인덱스 페이지 간에 서로 다르지 않습니다. lazywriter는 프로시저가 되는 개체에 대해 알지 못하므로 이 알고리즘은 디스크 I/O에 대한 캐시 사용과 프로시저 계획에 대한 캐시 사용 간에 적절히 균형을 이룹니다.
컴파일 비용이 많이 드는 항목은 초기 비용이 많이 들기 때문에 오랫동안 사용되지 않더라도 캐시에 남아 있습니다. 자주 다시 사용되는 항목은 사용될 때마다 비용이 재설정되므로 캐시에 남게 되고 lazywriter에서는 이 값이 0이 되지 않습니다.
그림 10에서는 lazywriter의 캐시 처리 흐름을 보여 줍니다.
그림 10. lazywriter 캐시 처리 흐름
클라이언트의 SQL 처리
이제 SQL 문이 제출될 때 일어나는 처리 과정을 다시 한 번 살펴봅니다. 먼저 클라이언트에서 SQL Server로 RPC 이벤트가 들어가는 경우를 알아봅니다. SQL Server는 RPC 이벤트를 받아들이므로 이 이벤트가 일종의 매개 변수화된 SQL, 즉 준비/실행 모델이나 EXECUTESQL 중 하나라는 것을 인지하고 있습니다. 따라서 SQL Server에서는 이러한 SQL Server 텍스트를 식별할 캐시 키를 만들어야 합니다. 대신 SQL Server에서 실제 저장 프로시저를 처리하는 경우에는 자체 키를 만들 필요 없이 프로시저 이름을 사용하면 됩니다. RPC 호출을 통해 들어오는 SQL 텍스트인 경우 SQL 텍스트를 해싱하여 캐시 키가 만들어집니다. 또한 이 키는 ANSI 설정 등 특정 상태 정보를 반영해야 합니다. 같은 쿼리를 전달하더라도 모든 ANSI 설정이 ON으로 설정되어 있는 연결과 모든 ANSI 설정이 OFF로 설정되어 있는 연결에서는 같은 계획을 사용할 수 없습니다. 이 두 연결의 처리 과정은 서로 다릅니다. 예를 들어, 완전히 같은 SQL 텍스트를 실행하더라도 한 연결의 concat_null_yields_null 값이 ON으로 설정되어 있으면 concat_null_yields_null 값이 OFF로 설정된 연결에서 만들어진 결과와는 완전히 다른 결과가 만들어질 수 있습니다. 따라서 SQL Server에서는 계획의 복사본을 캐시에 여러 개, 사용 가능한 ANSI 설정의 각 조합에 대해 하나씩 유지합니다. 사용 가능한 옵션 집합은 키의 일부이며 이 키는 이러한 캐싱 메커니즘을 사용하여 데이터를 조회하는 핵심에 있으므로 SQL Server에서는 이 키를 만들어 캐시에서 조회할 때 사용합니다. 캐시에서 계획을 찾지 못하면 SQL Server에서는 이전에 설명한 대로 계획을 컴파일한 다음 해당 키와 함께 캐시에 저장합니다.
또한 SQL Server에서는 해당 명령이 준비 작업이어서 계획이 컴파일만 되고 실행되지는 않을 것인지 여부를 결정해야 합니다. 준비 작업이면 SQL Server에서는 나중에 클라이언트가 계획을 가져와서 실행할 때 사용할 수 있도록 핸들을 클라이언트로 다시 반환합니다. 준비 작업이 아니면 SQL Server에서는 캐시에서 원래 조회할 때 이 계획을 발견한 것처럼 계획을 가져와서 실행합니다.
준비/실행 모델은 캐시 관리에 복잡한 요소를 하나 추가합니다. 준비하면 나중에 계획을 실행할 때 사용할 수 있는 핸들을 제공하고 응용 프로그램에서는 정기적으로 계획을 실행하면서 이 핸들을 몇 시간 또는 며칠 동안 활성화 상태로 유지합니다. 캐시에 더 많은 활성 계획이나 데이터 페이지를 위한 공간을 만들어야 할 경우에는 이 핸들을 무효화할 수 없습니다. SQL Server에서 실제로 하는 일은 캐시에 계획을 삽입하는 것입니다. 그 외에 SQL Server는 SQL이 밀집된 공간에서 이루어지는 준비 작업에 참여하지 않도록 합니다. 메모리 압력이 있는 경우 계획에서 사용하는 공간에서 이전에 설명한 방법으로 여유 공간을 확보할 수 있지만 그래도 준비된 SQL 복사본이 남아 있습니다. 준비된 SQL을 실행하기 위해 클라이언트에서 작업을 시작하는데 캐시에 계획이 없으면 SQL Server가 텍스트를 가져와서 다시 컴파일한 다음 캐시에 삽입할 수 있습니다. 이런 방법으로 계획을 보유하기 위해 사용되는 캐시의 16KB 이상의 페이지를 다시 사용할 수 있으며 오랫동안 보유되는 이 공간은 다른 위치에 저장된 100바이트나 200바이트의 SQL 코드입니다.
또 다른 경우는 쿼리가 SQL 언어 이벤트로 들어올 때 클라이언트에서 받은 문을 처리하는 것입니다. 한 가지 경우를 제외하고는 첫 번째 흐름과 많이 다르지 않습니다. 이 경우 SQL Server에서는 자동 매개 변수화라고 하는 기술을 사용하려고 합니다. SQL 텍스트가 자동 매개 변수화 템플릿에 대해 일치됩니다. 자동 매개 변수화는 어려운 문제이므로 과거에 공유 SQL을 사용할 수 있었던 다른 데이터베이스 관리 제품에서는 일반적으로 이 옵션을 제공하지 않았습니다. 자동 매개 변수화에 있어 한 가지 문제점은 SQL Server에서 모든 쿼리를 자동으로 매개 변수화하면 일부 또는 대부분의 쿼리가 이후에 제출될 특정 값에 대해서는 그다지 적절하지 않은 계획을 갖게 된다는 점입니다. 프로그래머가 코드에 매개 변수 표식을 삽입한 경우에는 기대할 수 있는 값의 범위를 알고 있으므로 SQL Server에서 제공하는 계획을 기꺼이 받아들일 것입니다. 그러나 실제로 프로그래머가 특정 값을 제공했는데 SQL Server에서 이 값을 변경 가능한 매개 변수로 취급하면 한 값에 대해 작동하도록 생성된 계획이 뒤따르는 값에 대해서는 작동하지 않을 가능성이 있습니다. 저장 프로시저를 사용할 경우에는 프로그래머가 프로시저에 WITH RECOMPILE 옵션을 삽입하여 새 계획을 생성할 수 있습니다. 자동 매개 변수화를 사용할 경우에는 프로그래머가 각 새 값에 대해 새 계획을 개발해야 한다는 사실을 표시할 수 없습니다.
자동 매개 변수화와 관련된 부분에서 SQL Server는 매우 보수적으로 안전하게 자동 매개 변수화될 수 있는 쿼리 템플릿이 있으며 이 템플릿에 일치하는 쿼리에만 자동 매개 변수화가 적용됩니다. 예를 들어, 하나의 같음 연산자가 있고 조인은 없으며 열에 고유 인덱스가 있는 WHERE 절을 포함한 쿼리를 가정합니다. SQL Server에서는 하나 이상의 행이 반환되지 않을 것이며 계획에서 항상 이 고유 인덱스를 사용해야 한다는 점을 인지하고 있습니다. SQL Server에서는 검색을 고려하지 않을 것이며 실제 값은 어떤 방법으로든 계획을 변경하지 않습니다. 따라서 이러한 쿼리는 안전하게 자동 매개 변수화할 수 있습니다.
쿼리가 자동 매개 변수화 템플릿과 일치하면 SQL Server에서는 리터럴을 매개 변수 표식(예: @p1, @p2)으로 대체하며 이것을 sp_executesql 호출인 것처럼 서버로 보냅니다. SQL Server에서 해당 쿼리를 자동 매개 변수화하기에 안전하지 않다고 결정하면 클라이언트가 SQL Server에 임시 SQL로 리터럴 SQL 텍스트를 보냅니다.
그림 11에서는 클라이언트가 SQL Server로 요청을 보낼 때의 처리 흐름을 보여 줍니다.
그림 11. 클라이언트의 SQL 처리
컴파일
이제 컴파일과 최적화에 대해 좀 더 자세히 알아봅니다. 컴파일하는 동안 SQL Server에서는 문을 분석하고 이 문에 대한 내부 표현인 시퀀스 트리라는 것을 만듭니다. 시퀀스 트리는 SQL Server 6.5에서 SQL Server 7.0으로 넘어오면서 없어지지 않은 데이터 구조 중 하나입니다. 그런 다음 시퀀스 트리는 정규화됩니다. 정규화의 기본 기능은 바인딩입니다. 바인딩에는 테이블과 열이 있는지 확인하는 작업과 테이블과 열에 대한 메타데이터를 로드하는 작업이 있습니다. 필요한 암시적 변환에 대한 정보 또한 시퀀스 트리에 추가됩니다. 예를 들어, 쿼리가 숫자 값에 정수 10을 추가하려고 하면 SQL Server에서 트리에 암시적 변환을 삽입합니다. 정규화는 또한 뷰에 대한 참조를 뷰의 정의로 바꿉니다. 마지막으로 정규화는 몇 가지 구문을 기준으로 최적화를 수행합니다. 해당 문이 기본 SQL 문이면 SQL Server가 시퀀스 트리에서 해당 쿼리에 대한 정보를 추출한 다음 최적화 프로그램이 효율적으로 작동할 수 있도록 설정된 쿼리 그래프라고 하는 특수한 구조를 만듭니다. 그런 다음 쿼리 그래프가 최적화되고 계획이 만들어집니다.
그림 12에서는 컴파일 프로세스의 흐름을 보여 줍니다.
그림 12. 컴파일
최적화
SQL Server 최적화 프로그램은 별개의 부분으로 이루어져 있습니다. 첫 번째 부분은 일반 계획 최적화라고 하는 비용 외 기준 최적화 프로그램입니다. 일반 계획 최적화의 전체 개념은 SQL 문에 단 하나의 실행 가능한 계획이 있을 경우 비용 기준 최적화 실행에 비용이 많이 든다는 것입니다. 일반적으로 하나의 VALUES 절이 있는 하나의 INSERT 문으로 이루어진 쿼리가 있습니다. 이 쿼리에는 가능한 계획이 하나뿐입니다. 또 다른 예는 SELECT 문으로 이 문에서는 모든 열이 고유 포함 인덱스에 있고 이러한 열 집합을 갖는 다른 인덱스는 없는 경우입니다. 이와 같은 두 가지 경우 SQL Server에서는 단순히 계획을 생성할 뿐 여러 계획을 조사하여 더 나은 계획이 있는지 조사하려고 하지 않습니다. 일반 계획 최적화 프로그램은 매우 명백하고 별로 비용이 들지 않는 계획들을 찾습니다. 따라서 가장 단순한 쿼리가 최적화 프로세스에서 빨리 제거되므로 최적화 프로그램에서는 적절한 계획을 검색할 때 많은 시간이 들지 않습니다. SQL Server에서 해시 조인, 병합 조인, 인덱스 교점을 처리 기술 목록에 추가할 때 SQL Server 7.0의 잠재적 계획 수가 급증하므로 이러한 방법이 적절합니다.
일반 계획 최적화 프로그램에서 계획을 찾지 못하면 SQL Server가 단순화로 알려진 최적화의 다음 부분으로 들어갑니다. 단순화는 쿼리 자체의 문장론적 변형으로 재정렬될 수 있는 대체 속성 및 작업을 찾습니다. SQL Server에서는 지속적인 접기 및 비용을 검토하거나 더 효율적인 쿼리를 만들 수 있는 인덱스를 분석할 필요가 없는 다른 작업을 수행합니다. 그런 다음 SQL Server는 인덱스와 열로 통계 정보를 로드하고 비용 기준 최적화 프로그램인 마지막 주요 최적화 부분으로 들어갑니다.
비용 기준 최적화에는 세 단계가 있습니다. 첫 번째는 트랜잭션 처리 단계라고 하며 트랜잭션 처리 시스템 특유의 간단한 요청에 대한 계획을 찾습니다. 일반적으로 이러한 요청은 일반 계획 최적화 프로그램에서 처리하는 요청보다 훨씬 복잡하며 계획들을 비교하여 가장 저렴한 계획을 찾는 작업이 필요합니다. 트랜잭션 처리 단계가 완료되면 SQL Server에서 내부 임계값과 발견한 가장 저렴한 계획의 비용을 비교합니다. 이 임계값은 앞으로의 최적화 허용 여부를 결정할 때 사용됩니다. 계획의 비용이 임계값 미만이면 이미 발견된 계획을 실행하는 것보다 추가 최적화를 실행하는 데 비용이 더 많이 듭니다. 따라서 SQL Server에서는 최적화를 중단하고 트랜잭션 처리 단계에서 발견한 계획을 사용합니다.
트랜잭션 처리 단계에서 발견한 계획이 이 단계의 임계값에 비해 비용이 많이 들면 SQL Server는 두 번째 단계로 이동합니다. 이 단계를 종종 QuickPlan 단계라고 합니다. QuickPlan 단계는 웬만큼 복잡한 쿼리에도 적절한 계획을 선택할 수 있도록 적절한 계획에 대한 검색 범위를 확장합니다. QuickPlan은 가능한 범위의 계획을 조사하고 이 작업이 완료되면 최적 계획의 비용을 두 번째 임계값과 비교합니다. 트랜잭션 처리 단계와 마찬가지로 임계값보다 낮은 비용의 계획이 발견되면 최적화가 중단되고 이 계획이 사용됩니다. 일반적으로 SQL Server 6.5에서 쿼리가 사용하려고 했던 계획이 SQL Server 7.0에서도 최적의 계획이면 일반 계획 최적화 프로그램이나 비용 기준 최적화의 처음 두 단계에서 계획이 발견될 것입니다. 이렇게 되도록 규칙을 의도적으로 구성하였습니다. 이 계획에서는 단일 인덱스 및 중첩된 루프 조인을 사용합니다.
전체 최적화라고 하는 최적화의 마지막 단계는 복잡한 쿼리에서부터 아주 복잡한 쿼리에 이르기까지의 쿼리에 대한 적절한 계획을 세우기 위한 단계입니다. 복잡한 쿼리의 경우 QuickPlan에서 만든 계획은 더 나은 계획을 계속 검색할 때보다 훨씬 많은 비용이 들 수 있기 때문에, 전체 최적화가 수행됩니다. 전체 최적화에서는 실제로 별도의 두 가지 옵션을 사용할 수 있습니다. QuickPlan 단계에서 산출되는 최적 비용이 "병렬 처리를 위한 비용 임계값"의 구성 값보다 크고 서버가 다중 프로세서 컴퓨터이면, 최적화 프로그램의 마지막 단계에는 다중 프로세서에서 병렬로 실행될 수 있는 계획을 찾는 작업이 수반됩니다. QuickPlan 단계에서 제공한 최적 계획의 비용이 구성된 "비용 임계값 병렬 처리" 미만이면 최적화 프로그램은 직렬 계획만 고려합니다. 계획은 이 마지막 단계에서 반드시 발견되어야 하므로 전체 최적화 단계는 소모적인 작업이 되어 시간이 많이 소요될 수 있습니다. 최적화 프로그램은 모든 잠재적 계획의 비용을 최적화의 이 단계에 이르는 데 소요될 비용과 비교하고 여러 최적화를 계속 시도하는 데 소요될 비용을 추정하므로 모든 가능한 계획을 검토하지 않을 수도 있습니다. 어느 단계에 이르면 최적화 프로그램은 더 나은 계획을 검색하는 것보다는 현재 계획을 사용하는 것이 더 저렴하며 최적화를 계속함으로써 추가 컴파일 비용을 지불하는 것은 비용면에서 효율적이지 않다고 결정할 수도 있습니다. 이 마지막 단계에서 처리되는 쿼리의 계획은 일반적으로 한 번만 사용되므로 이후의 실행에서 계획이 다시 사용될 때 컴파일과 최적화에 추가 비용이 들 가능성이 거의 없습니다. 이후의 실행이 거의 일어나지 않기 때문입니다.
계획이 발견되면 이 계획은 최적화 프로그램의 출력이 되고 SQL Server에서 계획을 실행하기 전에 지금까지 논의한 모든 캐싱 메커니즘을 수행합니다. 전체 최적화 프로그램 단계에서 해당 쿼리에 대한 병렬 계획을 제공한다고 해서 반드시 다중 프로세서에서 이 계획이 실행되지는 않습니다. 컴퓨터 사용량이 많고 여러 CPU에서 하나의 쿼리를 실행할 수 없으므로 이 계획은 단일 프로세서를 사용합니다.
그림 13에서는 최적화 프로그램을 통한 처리 흐름을 보여 줍니다.
그림 13. 최적화
실행
쿼리 처리의 마지막 단계는 실행입니다. 실행에 대해서는 간략하게 설명합니다. 실행 엔진은 최적화 프로그램에서 만든 계획을 가져와서 실행합니다. 실제 실행하면서 실행 엔진은 이 프로세스에 대한 스레드가 실행되도록 일정을 만들고 스레드 간 통신을 제공합니다.
요약
지금까지 설명한 대로 SQL Server 내부 및 아키텍처를 모두 다루기에는 너무 광범위하고 이 문서에서 제공하지 못한 부분도 많습니다. 이 문서에서는 SQL Server가 클라이언트와 상호 작용하는 방법 및 SQL Server 관계형 엔진이 클라이언트의 요청을 처리하는 방법을 중점적으로 설명하였습니다. SQL Server에서 쿼리를 처리하는 방법, 쿼리를 컴파일하거나 다시 컴파일하는 방법과 그 시기를 알게 됨으로써 SQL Server 7.0의 성능과 정교함을 이용하여 보다 뛰어난 응용 프로그램을 작성할 수 있기를 바랍니다.
