데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
» SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1337
1770 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 973
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1447
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 933
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 1487
1766 DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1172
1765 SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1066
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1370
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1069
1762 SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1127
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1537
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1048
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1096
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1254
1757 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 801
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1130
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 975
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1457
1753 SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 3317
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2184





XE Login