SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인

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

– 분산형 분할 뷰(View) 확인

 

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

 

SQL Server 쿼리 프로세서에서는 분산형 분할 뷰의 성능을 최적화 한다. 분산형 분할 뷰 성능의 가장 중요한 측면은 멤버 서버 간에 전송되는 데이터의 양을 최소화 하는 것이다.

 

SQL Server에서는 분산 쿼리를 효율적으로 사용하여 원격 멤버 테이블의 데이터에 액세스하는 지능적이고 동적인 계획을 작성 한다

 

• 로컬 분할 뷰(Local Partition View) : 수평으로 여러 테이블로 분할 된다. 일반적으로 모두 같은 구조를 가지고 있다.
• 크로스 데이터베이스 분할 뷰(Cross Database Partitioned View) : 테이블이 동일한 서버 인스턴스에서 다른 데이터베이스 사이에서 분할.
• 분산형 분할 뷰(Distributed (across Server of instance) Partition View : 뷰에 참가하는 테이블이 다른 서버나 다른 인스턴스에 있는 데이터베이스에 상주.

 

쿼리 프로세서는 OLE DB를 사용하여 각 멤버 테이블에서 CHECK 제약 조건의 정의를 검색한다. 쿼리 프로세서는 이를 통해 멤버 테이블에 키 값을 분산하여 매핑 할 수 있다.

 

쿼리 프로세서는 SQL문 WHERE 절에 지정된 키 범위를 멤버 테이블에 행이 배포되는 방식을 보여주는 맵과 비교한다. 그런 다음 쿼리 프로세서는 분산 쿼리를 사용하여 SQL 문을 완료하는데 필요한 원격 행만 검색하는 쿼리 실행 계획을 작성 한다. 또한 실행 계획은 데이터 또는 메타데이터가 요청될 때까지 이러한 데이터를 얻기 위해 원격 멤버 테이블에 액세스하는 것을 연기하는 방식으로도 작성 된다.

 

예를 들어 Server1(1 ~ 3299999)까지의 ID), Server2(3300000 ~ 6599999), Server3(6600000 ~ 9999999)에 걸쳐 테이블이 분할 되는 시스템이 있다고 가정하자. Server1에서 실행되는 다음 쿼리에 대해 작성된 실행계획을 검토 한다.

SELECT * FROM CompanyData.dbo.Customers WHERE CustomerID BETWEEN 3200000 AND 3400000;

 

이 쿼리에 대한 실행 계획은 로컬 멤버 테이블에서 320000에서 329999까지의 키 값을 포함하는 행을 추출하고 분산 쿼리를 실행하여 Server2에서 330000에서 3400000까지의 키 값을 포함하는 행을 검색 한다.

 

SQL Server 쿼리 프로세서는 실행 계획이 작성되어야 할 때 키 값이 알려지지 않은 SQL문에 대해 동적 논리를 쿼리 실행 계획으로 작성 할 수 있다. 다음 스크립트를 예로 들어 보자.

CREATE PROCEDURE GetCustomer @CustomerIDParameter INT AS SELECT * FROM CompanyData.dbo.Customers WHERE CustomerID = @CustomerIDParameter;

 

프로시저가 실행 될 때마다 @CustomerIDParameter 매개 변수에서 어떤 키 값을 제공하는지 예측할 수 없다. 예측 할 수 없으므로 쿼리 프로세서는 어떤 멤버 테이블을 액세스 해야 하는지도 예측 할 수 없기 때문에 SQL Server는 어떤 멤버 테이블이 입력 매개 변수 값에 기반하여 액세스되는지 제어하기 위한 조건부 논리(동적 필터)를 포함하는 실행 계획을 작성 한다.

 

다음 스크립트는 위의 프로시저가 Server1에서 실행 되었을 때 실행 계획 논리를 난타 낸 것이다.

IF @CustomerIDParameter BETWEEN 1 and 3299999 Retrieve row from local table CustomerData.dbo.Customer_33 ELSEIF @CustomerIDParameter BETWEEN 3300000 and 6599999 Retrieve row from linked table Server2.CustomerData.dbo.Customer_66 ELSEIF @CustomerIDParameter BETWEEN 6600000 and 9999999 Retrieve row from linked table Server3.CustomerData.dbo.Customer_99

 

SQL Server에서는 매개 변수가 없는 쿼리에 대해서도 이러한 유형의 동적 실행 계획을 작성할 때가 있다. 실행 계획을 다시 사용할 수 있도록 최적화 프로그램이 쿼리를 매개 변수화 할 수 있다.

 

 

[실습 결과]

각 서버에 테이블을 생성하고 다음의 스크립트를 실행하여 데이터를 생성한다.

• 서버1

create table Tbl_X (id int) go   declare @min int set @min = 1   while @min <= 329999 begin     insert tbl_x values (@min)          set @min = @min + 1 end   create index CI_Tbl_X_ID on TBL_X (ID)

 

• 서버2

create table Tbl_X (id int) go   declare @min int set @min = 330000   while @min <= 659999 begin     insert tbl_x values (@min)          set @min = @min + 1 end   create index CI_Tbl_X_ID on TBL_X (ID)

 

• 서버3

create table Tbl_X (id int) go   declare @min int set @min = 660000   while @min <= 999999 begin     insert tbl_x values (@min)          set @min = @min + 1 end   create index CI_Tbl_X_ID on TBL_X (ID)

 

• 서버1

뷰를 생성 한다.

CREATE VIEW VIEW1   AS   SELECT ID FROM SW_TEST.DBO.TBL_X UNION ALL SELECT ID FROM SERVER2.SW_TEST.DBO.TBL_X UNION ALL SELECT ID FROM SERVER3.SW_TEST.DBO.TBL_X

 

• 서버1

데이터를 검색 한다.

SELECT * FROM VIEW1 WHERE ID BETWEEN 320000 AND 340000

 

 

 

• 서버1

다음의 스크립트를 실행 한다. 프로시저가 실행 될 때 마다 매개 변수에 어떤 키 값을 제공하는지 예측 할 수 없다.

CREATE PROCEDURE TEST_SP   @ID INT   AS   SELECT * FROM VIEW1 WHERE ID = @ID GO   EXEC TEST_SP 56000

 

 

 

최적화 프로그램이 분할된 뷰를 참조하는 쿼리를 매개 변수화하는 경우 최적화 프로그램에서는 지정된 기본 테이블에서 필요한 행이 나오는 것으로 간주하지 않게 되므로 실행 계획에서 동적 필터를 사용해야 한다.

 

[참고 자료]

매개변수 미 및 실행 계획 재사용 :

http://msdn.microsoft.com/ko-kr/library/ms175580(v=sql.105).aspx

단순 매개 변수화

http://msdn.microsoft.com/ko-kr/library/ms186219(v=sql.105).aspx

 

http://blogs.msdn.com/b/sqlcat/archive/2007/06/20/distributed-partitioned-views-federated-databases-lessons-learned.aspx

http://sqlblog.com/blogs/denis_gobo/archive/2007/06/21/1497.aspx

 

 

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