728x90
제 2강 : MSSQL2005에 등장한 각종 순위 함수

이름 : 최석준
Email : beatchoi@gmail.com
CJIG ANIMA 개발실 DBA

순위 함수의 부재로 인해 MSSQL2000에서는 실행될 수 없었지만 MSSQL2005에서 가능해진 기능을 소개하고자 합니다.

Transact-SQL은 다음과 같은 순위 함수를 제공합니다.

RANK NTILE
DENSE_RANK ROW_NUMBER

순위함수의 내용은 http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx 링크의 온라인북을 참고하셔도 됩니다.

MSSQL2000에서는 오라클에서 쓰는 ROW_NUMBER() 같은 함수를 쓸 수 없어 유저들의 불만이 많았습니다. 다음과 같이 동적으로 카운트를 하는 서브쿼리를 통하여 비슷하게 구현할 수 있었습니다. 다음의 예제를 통해서 MSSQL2000에서 여러 단계를 거쳐야 했던 방법을 MSSQL2005에서 한 문장으로 해결되는 예제를 소개합니다.

create table TEST1 (user_id char(3),num int)

insert TEST1 values('aaa',3)
insert TEST1 values('aaa',2)
insert TEST1 values('bbb',15)
insert TEST1 values('bbb',2)
insert TEST1 values('aaa',6)
insert TEST1 values('aaa',4)
insert TEST1 values('aaa',3)
리스트 1 기초 데이터 입력

구분되는 일련번호가 없는 데이터를 다음과 같은 결과물을 얻으려면 어떻게 해야 되는지에 대한 문제입니다. 데이터는 user_id로 구분이 되어 있고 구분이 없는 num 값으로 구성되어 있습니다.

user_id   num1        num2        num3        num4        num5       
------- ----------- ----------- ----------- ----------- ----------
aaa         3             2              6              4               3
bbb        15            2             NULL          NULL           NULL
리스트 2 원하는 결과

MSSQL 2000을 이용할 경우

리스트 2 의 결과 처럼 user_id 별로 num1~num5 컬럼으로 나누어 데이터를 가져오는 것입니다. 각 로우를 구분하기 위해 indentity()함수를 이용해서 임시테이블에 id 라는 일련번호 컬럼을 만듭니다.

--------------------------------------------------------
-- 일련번호생성하여 #temp_1 만들기

select identity(int,1,1) id,* into #temp_1 from TEST1
리스트 3 일련번호 생성하기 위해 identity()함수를 이용한 임시테이블 만들기

각 로우가 구분되는 id라는 컬럼이 만들어 졌으므로, user_id 별로 일련번호를 Group by및 case 문을 사용하여 원하는 결과를 쿼리합니다.

select user_id,
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count(*)
from #temp_1
where user_id = a. user_id
and id <= a.id) id,a.user_id,num
from #temp_1 a) t1
group by user_id
리스트 4 MSSQL2000의 임시테이블을 이용한 쿼리

/* user_id     num1    num2    num3    num4     num5
------- ----------- ----------- ----------- ----------- ----------
aaa             3           2          6           4           3
bbb           15          2        NULL       NULL       NULL

(2개행적용됨)
*/
리스트 5 MSSQL2000의 임시테이블을 이용한 쿼리 결과

MSSQL 2005 ROW_NUMBER( ) OVER 절 이용

ROW_NUMBER ( )  구문

ROW_NUMBER ( ) OVER
( [ <partition_by_clause> ] <order_by_clause> )
리스트 6 ROW_NUMBER ( )  구문

인수

<partition_by_clause>
from 절이 생성한 결과 집합을 ROW_NUMBER 함수가 적용되는 파티션으로 나눕니다.

<order_by_clause>
파티션에서 ROW_NUMBER 값이 행에 할당되는 순서를 결정합니다. 자세한 내용은 ORDER BY 절(Transact-SQL)를 참조하십시오. 순위 함수에 <order_by_clause>가 사용된 경우 정수는 열을 나타낼 수 없습니다.

MSSQL2000의 쿼리에서는 원하는 결과를 위해서 각 로우를 구분하는 id열을 가진 임시테이블을 만들고 또한, 구분되어진 id 열을 user_id에 따라 서브쿼리를 하고, count 하여야 원하는 결과를 구할수 있었습니다. MSSQL 2005에서 등장한 순위 함수를 이용하면, 다음과 같은 한 문장으로 원하는 결과를 구할수 있습니다.

select user_id,
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number()
over (partition by user_id order by user_id) id,* 
from TEST1) t1
group by user_id
리스트 7 MSSQL2005의 row_number() 함수를 이용한 쿼리

/*
user_id                                                
------- ----------- ----------- ----------- ----------- --------
aaa     3           2           6           4           3
bbb    15         2         NULL      NULL      NULL
경고: 집계또는다른SET 연산에의해NULL 값이제거되었습니다.

(2개행적용됨)
*/
리스트 8 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과

리스트 9 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과
리스트 9 MSSQL2000과 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과 실행계획

임시테이블을 생성하는 로직을 포함하지 않더라도, MSSQL2005의 순위 함수를 사용하는 것이 성능상 유리합니다.

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
select identity(int,1,1) id,* into #temp_1 from TEST1

SQL Server 구문분석및컴파일시간:
CPU 시간= 13ms, 경과시간= 13ms.
테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
CPU 시간= 0ms, 경과시간= 13ms.

(7개행적용됨)

select user_id,
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count (*)
from #temp_1
where user_id = a.user_id
and id <= a.id) id,a. user_id ,num
from #temp_1 a) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb    15         2         NULL       NULL       NULL

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

테이블'#temp_1'. 검색수8, 논리적읽기수16, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기 수0.

SQL Server 실행시간:
CPU 시간= 0ms, 경과시간= 1ms.

select user_id,
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number( )
over (partition by user_id order by user_id) id,*  from TEST1) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb     15          2           null        null        null

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
CPU 시간= 0ms, 경과시간= 1ms.
리스트 10 실행통계

이상과 같이 실행통계의 성능을 통하여 MSSQL 2005를 이용하면 성능상 더 좋고 간단한 방법을 알아보았습니다. 다른 랭크 함수 (RANK,NTITLE,DENSE_RANK) 들의 이용과 응용은 여러분의 몫입니다.

'데이터베이스 > SQL Server' 카테고리의 다른 글

김민석 : SQL Server 2005의 Partitioned table 강좌(2)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(1)  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(1)  (0) 2008.05.07
테크니컬 칼럼 - DB별 대용량 데이터의 파티셔닝과 병렬 처리  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(2)  (0) 2008.05.07
728x90
제 1강 : SQL Server 재 컴파일

이름: 송혁
Email : hyok81@nate.com
넥슨 DSM팀 DBA로 근무


1. SQL Server의 재 컴파일이란?

SQL Server 는 비용 기반의 최적화(cost based optimizer)를 기반으로 쿼리의 실행 계획을 생성하며, 생성시 통계 정보, 테이블 스키마 정보 및 쿼리에 대한 내용을 바탕으로 실행 계획을 생성합니다. 이 작업은 대부분 CPU 리소스를 사용하고 있습니다.

매번 쿼리 요청이 될 때 마다 쿼리의 실행 계획을 계속 생성한다는 작업한다면 많은 성능 적 문제를 야기 할 수 있어 SQL Server에는 프로시져 캐시라는 메모리 공간에 쿼리가 수행된 실행 계획을 메모리에 적재 후 추후 동일한 쿼리요청이 들어온다면 캐시에 있는 내용을 재사용을 하게 됩니다. 이러한 것을 실행 계획 재사용이라고 부르며, 기존의 실행계획을 사용하면 안될 경우 및 사용할 수 없는 경우에 새로운 실행 계획을 만들기 위해 재 컴파일이라는 것을 하게 됩니다.

2. OLTP 서비스 환경에서의 재 컴파일이 주는 장점 및 단점

OLTP환경 즉 많은 트랙잭션이 빠른 시간 안에 처리되어야 하는 환경에서는 OLAP환경 보다 재 컴파일에 대해서 보다 민감하고, 성능에 많은 영향을 줄 수 있습니다.

위에서 설명 하고 있듯이 SQL Server는 프로시져 캐시라는 곳에 실행계획을 저장하고 있어 재사용을 할 수 있습니다. 재사용으로 인해 매번 실행계획을 다시 생성하지 않아도 되어 보다 적은 리소스로 쿼리에 대한 응답을 할 수 있습니다.

그러나 예전에 생성된 실행 계획을 재 사용할 경우 문제가 발생 할 수도 있습니다.

기존에 실행계획은 최초 실행계획의 생성시 받는 매개변수에 대해서 실행 계획을 만드는 것이 일반적이며, 기존에 실행계획을 생성한 매개변수와 나중에 수행되는 매개변수에 대한 카디널리티 편차가 크다면 실행 계획 재사용으로 인해 적절한 실행 계획을 찾지 못하고 기존의 실행 계획을 이용하여 성능상 문제를 야기 할 수 있습니다.

실행계획의 재사용이란 항상 좋은 것만이 아닌 필요에 따라서는 재 컴파일을 통해 새로운 실행 계획을 생성 하는 것이 성능상 이점을 가질 수 있습니다.

아래의 링크에서 이러한 실행계획의 재사용으로 인해서 발생 될 수 있는 문제에 대해서 간략히 설명 하고 있습니다.

프로시져 parameter sniffing에 의한 실행계획의 차이점

3. 강제 매개변수화, 자동 매개변수화란.

SQL Server 2005에서는 자동 매개 변수화 만이 아닌 강제매개변수화 라는 기능이 추가 되었습니다. 강제 매개변수화의 경우 실행 계획을 생성할 때 사용자가 설정한 매개변수로 해당 실행계획을 생성할 수 있습니다.

이러한 강제 매개변수화를 잘 사용한다면 특정 환경에서 카디널리티 편차가 심한 데이터에 대해서 재 컴파일로 최적의 실행 계획을 만드는 것이 아닌 변수의 분포도에 따라 몇 가지 실행 계획을 생성 하여 보다 효율적으로 작성 할 수 도 있습니다.

SQL Server 2005 예전 버전에도 존재하던 기능 중에 하나인 자동매개변수화(단순매개변수화)는 실행 계획을 재사용하기 위해서 단순한 구문 및 아래의 조건에 매칭되지 않는 경우에 대해서 상수를 변수로 변환하여 실행계획을 생성 할 수 도 있습니다.

이러한 내용들은 보면 최대한 재 컴파일을 막기 위한 SQL Server 개발진들의 노력이 보입니다. 이러한 노력을 보면 재 컴파일이 성능에 얼만큼 영향을 주는 부분인지 단편적으로 생각 해 볼 수 있을 것 같습니다.

자동 매개변수화가 될 수 없는 조건

   1. BULK INSERT

   2. IN 구문 또는 OR

   3. UPDATE 절에 SET @변수 = col

   4. SELECT 절에 UNION구문

   5. SELECT 절에 INTO구문

   6. SELECT/UPDATE 절에 FOR BROWSE

   7. SELECT/UPDATE/DELETE 에 OPTION 쿼리 힌트

   8. SELECT 절에 DISTINCT 구문

   9. SELECT/UPDATE/DELETE/INSERT절에 TOP구문

   10. WAITFOR 구문

   11. SELECT절에 GROUP BY, HAVING, COMPUTE 구문

   12. DELETE/UPDATE 절에 FROM CLAUSE 구문

   13. 전체 검색, 연결된 서버, 테이블 변수 참조

   14. 서브쿼리

   15. 조건 절에 <> 상수

   16. 조인이 포함된 구문

   17. 두 개 이상의 테이블을 참조

   18. DELETE, UPDATE 에 FROM절

4. SQL Server 2005에서 변경된 구문단위 재 컴파일

보통 SQL Server을 사용할 때 주로 프로시져 단위로 SQL구문을 작성 하여 개발 하는 경우가 많습니다. SQL Server 2000에서는 프로시져 내부에 재 컴파일이 해야 하는 경우 프로시져 내부에 존재 하는 모든 구문에 대해서 재 컴파일을 하였습니다.

만약 수만 줄의 쿼리 가 있었다면 이것을 재 컴파일 한다는 것은 그 작업만으로도 상당한 리소스를 소비 하게 됩니다. 그래서 이러한 경우를 대처 하기 위해서 자주 재 컴파일 되는 부분을 다른 프로시져로 만들고 원본 프로시져에서 호출 하는 방식을 사용하였습니다.

이러한 방법으로 전체 재 컴파일이 되는 것을 막을 수 는 있었지만 많이 번거로운 작업 이었습니다.

그러나 SQL Server 2005에서는 구문 단위의 재 컴파일이 도입 되어 SQL Serve 2000에서 사용하던 것처럼 따로 프로시져를 만들어 호출 하지 않아도 구문단위로 재 컴파일을 할 수 있습니다.

테스트!!

5. 프로시져 캐시 에서의 지연기록기의 역할

SQL Server에서는 프로시져 캐시라는 곳에 예전에 실행된 실행 계획을 가지고 있어 재사용한다고 하였습니다. 그렇다면 이 프로시져 캐시에 존재 하는 것들에 대해서 사용되지 않거나 사용빈도가 낮은 것에 대해서는 메모리에서 제거를 해야 하는 작업이 필요합니다.

만약 이러한 작업이 없다면 계속 프로시져 캐시의 증가로 인해 서버 메모리에 문제가 발생 할 수 있습니다. 그러나 SQL Server에서는 이러한 문제가 거의 발생 되지 않습니다.

지연기록기(LazyWriter)라는 백그라운드 프로세스가 존재 하기 때문입니다. 지연기록기 는 free page를 확보하기 위해 주기적으로 버퍼캐시를 검색 합니다.

이러한 작업을 수행 하면서 해당 조건에 보다 낮거나 같은 수치를 가지는 것을 캐시영역에서 제거 하여 free page를 확보 하게 됩니다.

그리고 지연기록기가 하는 일중 다른 하나는 운영체제의 메모리가 부족 하다면 지연기록기에서 SQL Server에서 사용되는 버퍼 풀 메모리의 일부분을 free하여 운영체제에게 반환하는 역할을 합니다.

SQL Server 2005에서 지원이 보다 강화된 NUMA(Non-Uniform Memory Access )에 대해서는 각 노드에 대해 지연기록기 프로세스가 존재 하게 됩니다.

NUMA(Non-Uniform Memory Access) 이해

(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/2a77699c-e06b-4377-8acf-4d565d225f3c.htm)

아래의 DMV를 이용하여 현재 재사용 되는 실행계획의 개수 및 재사용되는 상위 50개의 구문이 확인이 가능 합니다.

--Compare Single-Use and Re-Used Plans
declare @single int, @reused int, @total int
select @single=sum(case(usecounts) when 1 then 1 else 0 end), @reused= sum(case(usecounts) when 1 then 0 else 1 end),
     @total=count(usecounts)
from sys.dm_exec_cached_plans

select
'Single use plans (usecounts=1)'= @single,
'Re-used plans (usecounts>1),'= @reused,
're-use %'=cast(100.0*@reused / @total as dec(5,2)),
'total usecounts'=@total

select 'single use plan size'=sum(cast(size_in_bytes as bigint))
from sys.dm_exec_cached_plans
where usecounts = 1

--List Statements By Plan Re-Use Count
SELECT TOP 50
         qs.sql_handle
                 ,qs.plan_handle
                 ,cp.cacheobjtype
                 ,cp.usecounts
                 ,cp.size_in_bytes
                 ,qs.statement_start_offset
                 ,qs.statement_end_offset
                 ,qt.dbid
                 ,qt.objectid
                 ,qt.text
                 ,SUBSTRING(qt.text,qs.statement_start_offset/2,
                         (case when qs.statement_end_offset = -1
                         then len(convert(nvarchar(max), qt.text)) * 2
                         else qs.statement_end_offset end -qs.statement_start_offset)/2)
                 as statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
--and qt.dbid = db_id()
ORDER BY [Usecounts] DESC


6. 재 컴파일의 원인 및 프로시져 캐시 사라지는 경우

위에서 보듯이 쿼리의 자주 실행 되는 것이 아니라면 지연기록기에 의해 프로시져 캐시에서 실행계획이 제거 될 수 있습니다. 이렇게 되면 다시 쿼리가 호출 되면 다시 컴파일을 수행 하여야 합니다.

여기서는 지연기록기에 프로시져 캐시가 사라지는 경우가 아닌 다른 작업으로 사라지는 경우와, 기존의 실행계획이 있지만 다시 재 컴파일을 해야 하는 경우 대해서 알아보도록 하겠습니다.

재 컴파일의 원인은 아래와 같이 스키마 변경, 통계정보 업데이트 등으로 발생 될 수 있으며, ALTER DATABASE구문이나 DBCC CHECKDB구문 등으로 프로시져 캐시의 내용이 모두 사라져 모든 쿼리에 대해서 다시 컴파일을 하여야 합니다.

하나의 테이블에 통계정보가 변경되어 해당 테이블과 연관된 프로시져를 재 컴파일을 하는 것은 오히려 좋을 수 있습니다. 보다 최신의 통계정보를 바탕으로 오히려 예전 보다 더욱 효율적인 실행계획을 가지고 처리 할 수 있기 때문입니다.

그러나 ALTER DATABASE, RESTORE DATEBASE같은 경우는 서비스 운영 중에 많이 사용하고 있지 않는 구문이지만 만약 이 구문들이 사용된다면 전체 프로시져 캐시에 존재하는 실행계획이 사라져서 다시 모든 쿼리에 대해서 컴파일을 하여야 합니다.

많은 트랜잭션을 가지고 있는 사이트라면 순간적으로 CPU 리소스가 높이 사용 될 것입니다. 그러기에 이러한 동작을 유발 하는 구문에 대해서 서비스 중 작업이 필요하다면 충분한 주의가 필요 합니다.

재 컴파일 원인

  • 스키마 변경(ALTER TABLE, ALTER VIEW, Alter Index)

  • 통계정보 변경

  • 지연된 컴파일

  • SET 옵션 변경

  • 임시 테이블 변경

  • OPTION(RECOMPILE) 쿼리 힌트

  • sp_recompile 호출

프로시져 캐쉬가 사라지는 작업

  • ALTER DATABASE [dbName] SET ONLINE

  • ALTER DATABASE [dbName] SET OFFLINE

  • ALTER DATABASE [dbName] SET READ_ONLY

  • ALTER DATABASE [dbName] SET READ_WRITE

  • ALTER DATABASE [dbName] MODIFY NAME = [SomeDB_Name]

  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 DEFAULT

  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_WRITE

  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_ONLY

  • ALTER DATABASE [dbName] COLLATE Collation_Name

  • DROP DATABASE [db_Snapshot_Name]

  • Restore database

  • Detach database

  • DBCC FREEPROCCACHE

  • DBCC FREESYSTEMCACHE

  • DBCC CHECKDB (참고 : SQL Server 2005 SP2 이전에는 DBCC CHECKDB를 수행하면 프로시져의 캐시가 사라지게 되나 SP2에서는 제거되지 않음.)

SQL Server 2005 SP2에서는 프로시져 캐시가 사라지는 작업 시 SQL 에러로그에 아래와 같은 메시지가 추가 되었습니다.

2007-01-19 14:29:07.39 spid54       Starting up database 'R'.
2007-01-19 14:29:23.84 spid54       Setting database option OFFLINE to ON for database R.
2007-01-19 14:29:24.06 spid54       SQL Server has encountered 13 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2007-01-19 14:29:24.06 spid54       SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 13 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 13 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.


7. 재 컴파일 발생 원인 및 빈도 찾기

이러한 재 컴파일이 문제가 될 수 있다는 것을 위에서 알아 보았습니다. 그러면 이제는 현재 운영하고 있는 사이트에서 얼마나 많은 재 컴파일이 발생하는지 그리고 이유는 무엇인지 확인할 필요가 있습니다.

많은 트랜잭션이 발생하지 않는다면, 요즘은 워낙 CPU사양도 좋고 해서 재 컴파일에 의한 문제는 그리 크게 사람에게 다가오지 않을 수도 있습니다. 그러나 많은 트랜잭션이 발생 하는 환경이라면 분명 확인 할 가치는 있을 겁니다.

보통 재 컴파일에 대한 문제로 원인 및 빈도를 확인 하기 위해서는 프로파일러와 성능 모니터를 통해 확인 합니다.

프로파일러의 경우 모든 SQL Server로 들어오는 쿼리에 대해서 재 컴파일이 발생 되었다면 SP: Recompile, SQL: StmtRecompile 이벤트 및 EventSubClass컬럼을 추가 하여 원인 및 빈도를 확인 할 수 있습니다.

그렇지만 프로파일러를 서비스중인 서버에 사용하는 것도 힘든 곳도 많을 것입니다. 그리고 구문단위까지 이벤트를 걸게 되면 추적 파일 크기는 금방 커질 것 입니다.

비쥬얼하게 보여지는 프로파일러 대신 SQLDIAG나 저장 프로시져를 사용하는 것이 서버의 리소스 소비에 대해서 보다 효과 적입니다.

SQLDIAG는 기존 SQL Server 2000에도 존재 하였지만 일반적인 에러로그 및 수행 당시의 시스템 테이블 정보 등 이었지만 SQL Server 2005의 SQLDIAG는 기존에 Microsoft PSS팀에서 사용하던 PSSDIAG의 기능을 포함 하고 있어 운영체제 이벤트 로그, 추적, 성능 카운터 등을 한번에 수집이 가능 하여 보다 쉽게 관련 데이터를 수집 할 수 있습니다. 그러나 이러한 이벤트나 카운터를 수정 해야 한다면 XML파일을 직접 수정 해야 하는 번거로움은 있습니다.

단순히 재 컴파일에 대해서 어떠한 원인으로 발생 되었는지 확인 하려면 아래와 같이 열 필터의 EventSubClass의 “값이 없는 행 제외”를 추가하여 추적하여 성능 모니터의 수치와 비교해 본다면 보다 쉽게 재 컴파일의 원인을 확인 할 수 있습니다.

성능 모니터를 통해 재 컴파일의 빈도를 찾기 위해서는 아래에 있는 카운터 중 SQL Re-Compilations/sec, Batch Requests/sec 을 추가하여 빈도를 확인 할 수 있습니다.

SQLDIAG, 저장 프로시져를 이용하여 추적 하는 방법은 온라인 설명서를 참조하시면 됩니다.

SQLdiag 유틸리티(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/sqlcmpt9/html/45ba1307-33d1-431e-872c-a6e4556f5ff2.htm)

SQL Server 프로파일러 저장 프로시저(Transact-SQL)(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/tsqlref9/html/8c99c3db-0b04-46c3-aa59-d6f680522fdd.htm)

<그림 : 열 필터 설정>

<그림: 열 필터 설정 후 프로파일러 모습>

EventSubClass 값

설명

1

스키마가 변경되었습니다.

2

통계가 변경되었습니다.

3

지연된 컴파일입니다.

4

SET 옵션이 변경되었습니다.

5

임시 테이블이 변경되었습니다.

6

원격 행 집합이 변경되었습니다.

7

FOR BROWSE 권한이 변경되었습니다.

8

쿼리 알림 환경이 변경되었습니다.

9

분할된 뷰가 변경되었습니다.

10

커서 옵션이 변경되었습니다.

11

OPTION (RECOMPILE)이 요청되었습니다.

<EventSubClass 컬럼 설명>

성능 모니터 (perfmon)의 SQL Statistics 카운터를 통해 분석 및 빈도를 확인 할 수 있습니다.

SQL Server SQL Statistics 카운터

설명

Auto-Param Attempts/sec

초당 자동 매개 변수화 시도 수입니다. 합계는 자동 매개 변수화의 실패한 횟수, 안전한 횟수 그리고 안전하지 않은 횟수의 합과 같습니다. 자동 매개 변수화는 유사한 여러 요청을 처리할 때 캐시된 결과 실행 계획을 다시 사용하기 위해 SQL Server 인스턴스가 일부 리터럴을 매개 변수로 바꾸어 Transact-SQL 요청을 매개 변수화할 때 일어납니다. Microsoft SQL Server 2000 에서는 자동 매개 변수화를 단순 매개 변수화라고도 합니다. 이 카운터에는 강제 매개 변수화는 포함되지 않습니다.

Batch Requests/sec

초당 받는 Transact-SQL 명령 일괄 처리 수입니다. 이 통계는 모든 제약 조건(I/O, 사용자 수, 캐시 크기, 요청의 복잡도 등)의 영향을 받습니다. 높은 일괄 처리 수치는 높은 처리 효율을 의미합니다.

Failed Auto-Params/sec

실패한 자동 매개 변수화의 초당 시도 횟수입니다. 이 값은 작을수록 좋습니다.

Forced Parameterizations/sec

초당 성공한 강제 매개 변수화 수입니다.

Safe Auto-Params/sec

안전한 자동 매개 변수화의 초당 시도 횟수입니다. 안전한 자동 매개 변수화는 여러 유사한 Transact-SQL 문 간에 캐시된 실행 계획을 공유할 수 있도록 하는 결정을 참조하며 SQL Server 가 시도하는 많은 자동 매개 변수화 중 일부는 성공하고 나머지는 실패합니다. SQL Server 2005 에서는 자동 매개 변수화를 단순 매개 변수화라고도 합니다. 강제 매개 변수화는 포함되지 않습니다.

SQL Attention rate

초당 주의 수입니다. 주의는 현재 실행 중인 요청을 종료하기 위한 클라이언트의 요청입니다.

SQL Compilations/sec

초당 SQL 컴파일 수입니다. 컴파일 코드 경로를 입력한 횟수를 나타내며 SQL Server 2005 에서 문 수준의 다시 컴파일에 의한 컴파일을 포함합니다. SQL Server 사용자 작업이 안정되면 이 값은 일정한 수준에 이릅니다.

SQL Re-Compilations/sec

초당 문 다시 컴파일 수입니다. 문 다시 컴파일이 트리거된 횟수를 나타냅니다. 일반적으로 다시 컴파일하는 횟수는 적을수록 좋습니다. SQL Server 2000 에서 다시 컴파일은 Microsoft SQL Server 2005 의 일괄 처리 범위가 아니라 문 범위 다시 컴파일입니다. 따라서 SQL Server 2005 와 이전 버전 간에 이 카운터 값을 직접 비교할 수 없습니다.

Unsafe Auto-Params/sec

안전하지 않은 자동 매개 변수화의 초당 시도 횟수입니다. 예를 들어 쿼리는 캐시된 계획을 공유하지 않고 자동 매개변수화하는 특성이 있습니다. 이러한 자동 매개변수화는 안전하지 않은 것으로 지정됩니다. 강제 매개 변수화 수는 여기에 포함되지 않습니다.


드디어 “SQL Server의 재 컴파일이란?” 아티클에 마지막입니다.

여기서 제가 가장 하고 싶은 말은 지연기록기가 어떠한 연산을 하여 실행계획을 제거되고 서버의 메모리 중 얼마만큼 프로시져 캐시로 사용할 수 있는 그러한 내용이 아닌 “환경에 따라 독이 될 수도 약이 될 수도 있는 것이 재 컴파일이다.” 입니다.

그러나 여기서는 재 컴파일이 발생 하지 않게 모니터링 방법 등만을 기술 하였습니다. 이것은 대부분의 환경에서는 재 컴파일이라는 것이 약 이 되는 경우가 많다는 것이며 항상 재 컴파일이 나쁘다는 것은 아닙니다.

그렇지만 재 컴파일이라는 것을 약 또는 독을 만드는 것은 사람이라고 생각합니다. 만약 독이 라면 어떻게 찾고 해결할까? 그렇지만 찾는 방법만 있지 이것을 여기서 해결 하는 방법은 기술 되지 않았습니다.

이 부분은 직접 테스트를 해보면서 배우고 느끼는 것이 가장 좋다고 생각하는 개인적인 의견입니다. ^^

저는 SQL Server라는 DBMS를 접하게 된지 그리 오래 되지 않았습니다. 그래서 아직은 배워야 할 것 도 많다고 생각합니다. 그래서 더욱 재미있다고 생각합니다. DBMS라는 것에 대해서는 무엇보다 사람이 중요성이 다른 무엇보다 크다고 생각하기 때문입니다.

나중에 SQL Server 3000이 나와서 아무리 옵티마이져가 똑똑해졌다고 한다고 하더라도 사람이 해야 할 부분까지 SQL Server단에서 처리 하기는 힘들 거라고 생각합니다. 환경 및 비즈니스 로직에 따라서 사람의 단 하나의 판단으로 많은 차이가 날 수 있을 수 있기 때문입니다.

길지 않은 아티클을 작성하는데 생각보다 오랜 시간이 걸린 듯합니다. 지금 다시 보면 별 내용이 없는 것 같아 괜히 부끄러워지지만, 그래도 짧은 시간 동안 많은 공부를 할 수 있었고 정리를 하는 시간을 가져서 참 좋았습니다.

솔직히 이 재 컴파일이 주제가 정하기 전 여러 주제에 대해서 생각을 해보고 작성도 해보았지만 마음에 들지 않아 주제를 변경하기도 여러 번..아마 그 주제 들은 어딘가의 폴더 안에서 곤히 잠들고 있을 겁니다.

만약 다음에도 아티클로 다시 뵐 수 있는 기회가 온다면 더욱 열심히 준비하고 공부해서 좋은 아니 괜찮은 아티클로 다시 찾아 뵙겠습니다.

'데이터베이스 > SQL Server' 카테고리의 다른 글

최석준 : Update되는 Top 뷰와 Common table expression(CTE)(1)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(2)  (0) 2008.05.07
테크니컬 칼럼 - DB별 대용량 데이터의 파티셔닝과 병렬 처리  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(2)  (0) 2008.05.07
master db 복구 SQL 2005  (0) 2008.05.07
728x90

조인의 효율적인 처리를 위한


DB별 대용량 데이터의 파티셔닝과 병렬 처리



파티셔닝은 용량이 큰 테이블을 보다 작은 단위로 나눔으로써 성능이 저하되는 것을 방지하고 관리를 수월하게 하고자 하는 개념이다. 하지만 무조건적인 파티셔닝으로는 원하는 목적을 달성하기 어렵다. 파티셔닝 기법에 대한 개념적 차이를 이해하고, 적절한 파티션 전략을 구사하는 방법을 정리했다.



장현호 h2chang@b2en.com｜비투엔컨설팅 기술이사. 신세계I&C와 엔코아컨설팅 등을 거쳐 현재 비투엔컨설팅 전문위원 컨설턴트로 근무하고 있으며, 컨설팅 3팀장을 맡고 있다. 다음, 한미르, SK Global, KT, 한국신용정보, 수출보험공사, CJ홈쇼핑 등 수많은 기업의 성능 최적화, 품질관리, 데이터 아키텍처 구축 컨설팅을 수행했다.



최근 10년간 데이터베이스 기술의 발전과 더불어 컴퓨터의 활용이 증가하면서 기업이 축적하고 있는 데이터 량도 상상할 수 없을 만큼 증가했다. 하드웨어와 소프트웨어기술의 발전은 사용자로부터 더 많은 정보요구를 수용하게 만들었고 이렇게 축적된 정보량은 기하급수적으로 증가하면서 대용량 데이터를 보다 안전하고 효율적으로 관리하기 위한 파티셔닝 방안이 도입됐다. 파티셔닝은 용량이 큰(지속적으로 증가하는) 테이블을 파티션이라는 보다 작은 단위로 나눔으로써 성능 저하를 방지하고 관리를 보다 쉽게 하고자 하는 개념이다. 그렇지만 대용량의 데이터를 무조건 파티셔닝만 한다고 해서 목적이 이뤄지는 것은 아니다. RDBMS의 데이터 저장구조, 파티셔닝 기법에 따라 파티셔닝의 개념적 차이가 존재하기 때문에 이를 이해하고 적절한 파티션 전략을 구사해야 관리 용이성과 성능을 확보할 수 있다.


파티션 키의 선정


앞서 언급한 것처럼 무조건 파티션을 나눈다고 파티션이 가지고 있는 이점을 모두 얻을 수 있는 것은 아니다. 잘못된 인덱스가 처리 속도에 악영향을 미치듯 파티션 키를 어떻게 구성하느냐에 따라 비효율을 초래할 수도 있다. 따라서 파티션 키의 선정은 다음과 같은 기준에 따라 전략적인 관점에서 고려돼야 한다.

첫째, 성능향상을 위해 어떤 부분을 고려해야 하는가?

데이터를 처리하는 방법은 크게 인덱스를 경유하는 랜덤 액세스 방법과 인덱스를 경유하지 않고 전체 데이터를 스캔 하는 두 가지가 있다. 데이터의 분포도가 좋아서 인덱스를 사용할 수 있는 상황이라면 문제될 것이 없지만 분포도가 나빠서 인덱스를 사용할 수 없다면 인덱스를 사용하지 않고도 필요한 부분만 액세스를 할 수 있도록 파티셔닝이 이뤄져야 한다. 즉, 액세스 유형에 맞게 파티셔닝이 이뤄질 수 있도록 파티션 키를 선정해야 한다.

둘째, 용이한 데이터 관리를 위해 어떤 부분을 고려해야 하는가?

이력을 관리하는 데이터는 데이터 관리 전략과 업무규칙에 따라 그 수명이 다하면, 별도 저장장치에 기록되고 데이터베이스에서 삭제된다. 이력 데이터는 활용가치에 따라 생성주기와 소멸주기가 결정되고 주기에 따라 데이터베이스를 정리해야 한다. 만약 사용자가 사용하지 않는 데이터를 삭제해야 되는데 그 데이터가 여러 파티션에 분산돼 있다면, 그 데이터를 추출해 삭제하는데 많은 노력과 시간이 필요할 것이다. 하지만 파티션이 데이터의 생성이나 소멸주기와 일치한다면 파티션을 대상으로 작업이 이뤄지므로 관리가 용이하다. 그러면 파티션 키의 선정에 따라 RDBMS에서 조인을 선정하는 방식과 성능적인 부분에 대해 알아보자.


파티션키가 동일한 경우의 조인


대부분의 상용 RDBMS는 조인에 참여하는 두 테이블의 파티션 형태에 따라 다양한 형태의 파티션 조인을 지원하지만, 의미적으로는 크게 파티션 키가 동일한 경우와 파티션 키가 다른 경우이기 때문에 이 두 가지 형태만 알아보자.

DB2 환경에서 파티션 키가 동일한 경우

DB2에서 파티션 키가 동일한 경우 두 테이블은 같은 데이터베이스 파티션 그룹에 공동 배치되고, 조인 또는 서브쿼리를 위해 둘 이상의 테이블에 액세스할 때, 조인할 데이터가 동일한 데이터베이스 파티션에 위치돼 있는지를 인식한다. 이 때 동일한 파티션에 존재하는 경우 데이터가 저장된 데이터베이스 파티션에서 조인하거나 서브쿼리를 수행한다. 이러한 조인방식을 Collocated Join이라 하며, 데이터가 상주하는 파티션 내에서 로컬 조인이 수행되고 파티션 조인이 완료된 후 데이터를 코디네이트 노드로 보낸다(<그림1> 참조).



오라클 환경에서 파티션 키가 동일한 경우

오라클에서 파티션 키가 동일한 경우에는 파티션끼리만 조인을 시도하는 Partition-wise join방식을 사용한다. 이 방식은 조인되는 2개의 테이블이 모두 조인 컬럼으로 동일하게 파티션 돼 있고 두 테이블 모두 Parallel 대상일 경우, 동일 범위 파티션끼리만 조인을 시도하는 Full partition-wise join을 수행한다.

그런데 Full Partition-wise Join은 사용자가 아무리 많은 Parallel Degree를 지정하더라도 파티션 개수 이상의 parallel process가 실행되지 않는다. 파티션 개수만큼의 Parallel Process가 실행됐다면 파티션 1개당 parallel process 1개가 할당되고 각 파티션 별로 해당 파티션끼리만 조인을 한 후 결과를 반환한다.



이런 처리 방법은 파티션 키가 조인키이므로 조인 시 다른 파티션들의 데이터를 고려할 필요가 없어 가능하다.

이 조인 방법은 플랜의 “PARTITION RANGE ALL”이 각각의 “TABLE ACCESS FULL” 위에 나타나지 않고 “JOIN” 위에 나타나는 것으로 확인할 수 있으며, 이것은 파티션 별로 조인한 후에 결과를 합친다는 의미다. DB2와 오라클은 데이터의 저장구조가 다르고 파티션 구현 방식이 차이가 나기 때문에 파티션 조인을 수행하는 방식에 약간의 개념적인 차이가 존재한다. 그렇지만 파티션 키가 동일한 경우에는 조인 수행 시에 파티션 키가 동일한 파티션끼리 쌍을 이뤄 동일한 파티션 내에서 조인을 수행하고 그 결과를 코디네이트 노드로 전송하는 방식으로 조인을 수행하기 때문에 파티션 조인 중 가장 좋은 성능을 낼 수 있다.




파티션 키가 다른 경우의 조인


파티션 키가 다른 경우의 조인 방식은 기본적으로 한쪽의 파티션 키를 기준으로 파티션 한 후 다른 테이블의 파티션이 존재하는 파티션으로 전송, 조인을 수행한 후 그 결과를 다시 코디네이터 노드로 전송하는 방식으로 조인한다. 이때 다른 파티션 키를 가진 테이블의 크기나 상태에 따라 파티션을 하고 데이터를 전송하는 방식에 약간의 차이는 있지만 기본적인 처리 방식은 거의 비슷하므로 가장 일반적인 사례를 통해 처리 방식을 알아보도록 하겠다.

DB2 환경에서 파티션 키가 다른 경우

DB2에서 파티션 키가 다른 경우 두 테이블은 같은 데이터베이스 파티션 그룹에 존재하지 않는다. 따라서 조인 시에 Outer 테이블의 각 행은 Inner 테이블의 파티션 속성에 기초해 Inner 테이블의 한 파티션으로 보내지고, 그 파티션에서 조인이 수행된다(<그림 3> 참조).



오라클 환경에서 파티션 키가 다른 경우

조인 수행 시에 하나의 테이블만 조인 키에 의해 파티션 돼 있고 다른 하나는 파티션 돼 있지 않는 경우에 병렬 처리가 수행될 경우 오라클은 수행 속도 향상을 위해 partial parition-wise join 방식으로 조인을 수행한다. 이때 오라클은 파티션 조인을 수행하기 위해 파티션 되지 않은 다른 테이블의 파티션 키를 기준으로 동적으로 파티션하고, 각각의 파티션 쌍끼리 조인이 가능하도록 데이터를 구성한다.



이 조인 방법을 수행을 위해서는 PARALLEL Execution Server를 두 SET로 나누고, 첫 번째 SET 쿼리 서버에서는 파티션 되지 않은 테이블을 병렬로 읽어 파티션 키 별로 구분해 두 번째 SET 쿼리 서버에 분배하고, 두 번째 SET 쿼리 서버에서는 SET1에서 받은 로우와 자신에게 할당된 파티션과 조인을 수행하게 된다. 예를 들어 SALE 테이블의 파티션1과 매칭하는 CUSTOMERS의 모든 로우들은 SET2의 서버 1로 보내지게 되고, SET2의 서버들은 SET1 서버들로부터 받은 로우들과 자신에게 소속된 파티션과 조인을 수행한다.

이런 메커니즘 때문에 partial partition-wise join으로 수행되는 경우에는 DOP*2개의 프로세서가 수행된다. 하지만 파티션 테이블의 각 파티션에는 하나의 프로세서만 할당할 수 있어 조인 시에 일을 하게 되는 프로세서의 수는 파티션의 수로 한정된다. 조인 시 SQL의 실행계획에서 PARTITION RANGE ALL 오퍼레이션이 파티션 테이블의 위에 나타나고 파티션 되지 않은 다른 테이블의 DISTRIBUTION 컬럼에 “PART(KEY)”라는 항목이 나타나면 Partial partition-wise join으로 수행된다고 생각하면 된다.




파티션 정책 수립


파티셔닝은 논리적 데이터 모델에서 물리적 데이터 모델로 전환하면서 이뤄지는 작업으로 논리적 데이터 모델의 초기 단계에서 데이터 볼륨이 큰 엔터티들에 대해 수직적/수평적 분할을 고려하게 되는데 이 엔터티들이 향후 파티션으로 구현될 후보가 된다. 그렇다고 관리자가 임의로 파티셔닝 여부를 결정할 수는 없으며 아래와 같은 작업을 거쳐 업무 담당 및 현업부서의 동의를 도출해야 한다. 파티션 정책수립의 초기단계에서 관리자가 해야 할 일은 크게 두 가지로 나뉜다.

1.  시스템의 스토리지 구성을 고려한 데이터 파일구성의 제약사항(한계) 값 등을 조사해야 한다. 즉, 아래와 같은 시스템 한계점(bandwidth)들을 기준으로 관리자가 파티션을 적용할 절대 기준을 마련한다.

• DBMS가 지원하는 파티션 개수의 한계
• DBMS가 지원하는 테이블 크기의 한계
• DBMS 버전이 가진 파티션 사용의 제약사항
   예 1> key update 과부하 등의 예를 2개 정도 든다.
   예 2> split
• 현재 사용하고자 하는 버전의 데이터베이스에서 지원하는 파티션 관리 기능 중 대표적인 관리 명령어를 3개 정도 예를 든다.


2.  파티션이 필요한 테이블들을 조사하기 위해서 현업을 대상으로 교육, 조사할 가이드 및 리서치 자료를 만든다. 이 단계에서는 우선 파티셔닝이 필요한 테이블들의 특성을 크게 아래 세 개의 범주로 나누고 대상 테이블들의 특징을 <표 1>과 같은 양식으로 조사한다.

• 용량이 큰 이력 관리용 테이블

- 이력 데이터는 아니지만 절대적으로 용량이 커서 문제가 될 만한 테이블의 경우 설계단계에서 로우 사이즈가 확정되면 관리자는 각 테이블의 예상 데이터 건수와 보관주기를 바탕으로 도출한다. 이력 데이터의 경우 현업이 필요로 하는 온라인 보관주기와 유효보유기간이 지난 데이터의 처리방안을 결정해야 하며, 지난 이력 데이터들이 수정되는 경우가 발생하는지도 확인해야 한다.

• 대용량 병렬 배치작업의 대상이 되는 테이블

- 테이블 상세설계가 끝나고 프로그램 상세설계단계에 도출이 어려울 수도 있는데 이 경우에는 통합 테스트 시에 I/O 컨텐션이 발생하는 큰 테이블 위주로 선정하면 된다.

• 여러 노드로부터 동시적인 데이터 액세스를 일으켜 LOCK 컨텐션이 예상되는 테이블

- 이 경우는 각 노드에 분산되는 업무의 특성과도 밀접한 관련이 있으며 각 노드의 업무적인 특성을 반영시켜 LOCK을 최소화해 구성해야 한다. 필요에 따라 각 노드의 서버 프로그램 목록을 업무적 기준으로 분류, 각 프로그램이 액세스하는 테이블의 I/O형태를 파악한다.




<표 1>에 취합된 테이블 중에서 테이블 크기가 DBA가 나름대로 정한 절대 기준치를 초과하거나 디스크 I/O 컨텐션이 예상되는 테이블들을 파티션 작업 대상 테이블로 선정한다.


테이블별 파티션 정책


테이블별 파티션 정책을 수립하기 위해서는 파티션 종류의 결정, 파티션 키의 선정, 파티션 수의 결정과 같은 단계를 거쳐야 한다. 여기에서는 각 RDBMS에서 지원하고 있는 파티션의 종류의 특징에 대해 별도로 언급하지 않았지만, 어떤 파티션을 적용할 것인가에 대해 판단하려면 사용하고자 하는 RDBMS의 파티션 종류별 특징의 이해는 필수다. 파티션 키의 선정은 파티션 정책에서 가장 중요한 단계로 파티션의 성공이 여기에 달려 있다고 해도 과언이 아니다. 앞서 이 부분에 대해 별도로 언급했지만 그것은 단지 단일 테이블의 파티셔닝 기준에 불과하다.

효율적으로 파티션 키를 선정하기 위해서는 자신의 액세스 패스 외에 파티션 키가 조인에 미치는 영향도 즉, 조인 효율을 고려해야만 한다. 이것은 인덱스 전략 수립 시 하나의 액세스 패스만 보고 인덱스를 결정하지 않는 것과 같은 이치다. 앞서 파티션 정책에 관한 두 단계에 대해 이미 언급했기 때문에 여기에서는 파티션 수의 결정에 대해서만 생각해 보자. 만약 업무 담당자와 면담에서 다음과 같은 요건이 정의된 테이블이 있다면 2년 간의 데이터를 몇 개의 파티션으로 나눠 관리하는 것이 적절할까?

파티션의 수를 몇 개로 할 것인가를 결정할 다양한 외부 영향 요소들을 면밀히 분석해 봐야 한다. 결국 이것은 데이터베이스 가용성과 성능 사이의 고민이 될 것이며 또한 파티션 데이터의 관리, 백업, 재구성이 용이하도록 파티션의 수를 결정하는 것이 효과적이다. 파티션의 수를 결정하는 중요 고려 사항은 다음과 같다.

• DBMS가 지원하는 데이터파일의 최대 개수

• DBMS가 지원하는 한 데이터파일의 최대 사이즈

• I/O 집중의 단위

- 예를 들면 동일시점 데이터에 대해서 지점별, 사원별, 상품별 등의 단위로 동시 트랜잭션이 집중적으로 모이는 경우 적절한 I/O분산을 고려한 개수.

• 관리단위

- 위 표와 같은 경우 변경이 일어나는 현 시점 데이터와 변경이 일어나지 않는 과거 데이터는 일단 분리하고 조회용으로만 사용되는 과거 데이터에 대해서는 일정 단위로 등분해 저장한다.


결론


지금까지 조인에 참여하는 두 테이블의 파티션 키 상태에 따른 병렬처리 방식과 조인 효율에 대해 알아봤다. 파티셔닝은 논리적 단계에서 물리적 단계로 전환하는 시점에 이뤄지기 때문에 물리적 특성을 반영하는 것이 파티션 정책에서 매우 중요하다. 하지만 이러한 접근은 자칫 나무를 보고 숲을 보지 못하게 되는 오류를 범할 수 있다. 즉, 개별적인 테이블 단위의 파티션 정책 수립은 데이터베이스 전체적인 관점의 효율에는 크게 도움이 되지 않을 수도 있다. 때문에 개별적인 파티션 정책 수립 후에는 파티션의 목적에 따라 파티션 키를 통합하고 액세스 패스를 일치시키는 등과 같은 전략적인 차원의 조율이 이뤄져야 한다.




제공 : DB포탈사이트 DBguide.net

[출처] 테크니컬 칼럼 - DB별 대용량 데이터의 파티셔닝과 병렬 처리|작성자 주디

'데이터베이스 > SQL Server' 카테고리의 다른 글

최석준 : Update되는 Top 뷰와 Common table expression(CTE)(2)  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(1)  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(2)  (0) 2008.05.07
master db 복구 SQL 2005  (0) 2008.05.07
SQL Server의 기본 인스턴스를 단일 사용자 모드로 시작하는 방법 SQL 2005  (0) 2008.05.07

+ Recent posts

티스토리툴바