데이터베이스 개발자 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
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1250
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1788
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2528
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1361
1786 메모리 관리 아키텍처 – 프로세스 주소 공간 jevida(강성욱) 2016.09.28 1360
1785 메모리 관리 아키텍처 – 메모리 아키텍처 jevida(강성욱) 2016.09.28 1858
1784 데이터 압축 상태에 대한 개체 크기 예상 jevida(강성욱) 2016.09.28 1357
1783 sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기 jevida(강성욱) 2016.09.28 2831
1782 SQL Server 쿼리 처리 아키텍처_분산 쿼리 아키텍처 jevida(강성욱) 2016.09.28 1142
1781 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 jevida(강성욱) 2016.09.28 1335
1780 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 처리 수준 jevida(강성욱) 2016.09.28 1836
1779 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 jevida(강성욱) 2016.09.28 1746
1778 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - Preparing SQL Statements jevida(강성욱) 2016.09.28 1007
1777 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 강제 매개 변수화 jevida(강성욱) 2016.09.28 994
1776 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 단순 매개 변수화 jevida(강성욱) 2016.09.28 875
1775 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 매개 변수 및 실행 계획 재사용 jevida(강성욱) 2016.09.28 1095
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1423
1773 DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 978
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 939
» SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1335





XE Login