가늘어도 긴놈이 장땡

울회사 권병준 대리가 개발팀에 뿌린 가이드. 실수하기 쉬운 것임...

---------------------------------


안녕하세요. DB개발팀의 권병준입니다..

오늘도 찾아온 주의사항 한마디입니다.

역시나 길게 글 읽기 싫어하시는 분들을 위해 요약해서 한줄로 먼저 말씀드리겠습니다.

WHERE 조건에서 같은 컬럼에 대해 IS NOT NULL 조건과 <, >, <>, ANS SO ON.. 등의 조건을

같이 사용하지 마십시요.

-----------------------------------------------------------------------------

이제 설명 부분..

SP 검수하다보면 간간히 (A를 컬럼명이라고 가정합시다)

WHERE A IS NOT NULL AND A >= @YESTERDAY AND A <= @TODAY

와 같은 조건이 있는 경우가 있습니다.

물론, 해석은 A 가 NULL이 아니면서 어제와 오늘 사이의 값에 대한 조회.. 라는 건데,

저렇게 사용할 필요가 전혀 없습니다.

여기서 NULL 이라는 것에 대해 다시 한 번 재조명을 해보도록 하지요.

NULL 이라는 것은 값없음(이 아니라!) 미확정값이라는 뜻입니다.

아직 해당 컬럼에 값이 정해져있지 않다는 얘기죠. WHERE 조건에서

WHERE A >= @YESTERDAY 라고 조건을 주게 되면 당연히 어제 이후로의 확정된! 값에

대한 것만 조회를 해달라는 의미입니다. 여기서 미확정값은 당연히 제외가 되겠죠.

고로 저렇게 확정된 값에 대한 조회조건을 주면서 또다시 미확정된 값을 제외하라는 조

건을 주는건 CPU 부담만 증가시킬 뿐입니다. (Bookmark Lookup시에 꽤 부담을 주게 됩

니다.)

실제 간단한 실험을 통해서 비용적인 측면의 비교를 한 번 해 보겠습니다.

하나의 테이블을 만들고 여기에 테스트 값을 넣어줍니다.

CREATE TABLE NULL_TEST (
SEQ INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
NULL_COL INT NULL
)

DECLARE @CNT INT
SET @CNT = 1
WHILE @CNT < 5000
BEGIN
INSERT INTO NULL_TEST VALUES (NULL)
INSERT INTO NULL_TEST VALUES (@CNT)
SET @CNT = @CNT + 1
END

10000개의 데이터가 생성이 되었습니다. 여기서 제가 위에 말한 조회조건대로 IS NOT NULL이라는

조건을 추가로 준 것과 아닌 것에 대해서 각각 간단하게 조회를 해 봅니다.

SELECT * FROM NULL_TEST WHERE NULL_COL < 10 AND NULL_COL IS NOT NULL

SELECT * FROM NULL_TEST WHERE NULL_COL < 10

물론 결과값은 같습니다. 하지만 조회에 해당되는 비용은 다르게 나옵니다.

아래 그림을 봐 주십시요.

위의 쿼리와 아래의 쿼리 비용이 63.64% VS 36.36%로 차이가 나는 것을 보실 수 있을 겁

니다. 안 써도 되는 컬럼 추가로 인해 비용 차이가 이만큼이나 벌어졌습니다. 행 수가

많아지면 많아질수록 이 격차는 더욱 커집니다.

SP 작성하실 때에 여러분의 주의를 부탁드립니다. ^^

----------------------------------------------------------------------------

이건 좀 더 알고 싶으신 분들을 위해 추가합니다.

ANSI_NULLS 라는 옵션을 대부분 알고 계실 것입니다. 대부분의 DB에서는

(G마켓의 MS-SQL SERVER 포함) ANSI_NULLS ON 으로 사용합니다.

(NULL을 본래 그대로의 뜻인 미확정값으로 사용하겠다는 의미입니다.

MS-SQL SERVER에서는 NULL을 확정값처럼 사용하도록 할 수 있습니다.

특이한 옵션인거죠? ㅎㅎ (오라클 사용하던 저로서는 마냥 신기해했던 옵션입니다)

그렇다면..

SET ANSI_NULLS OFF 로 해주고 위의 쿼리를 다시 돌려보면 결과가 어떻게 나올까요?

............ 한 10초쯤 생각해 보시기 바랍니다. ...........................

예상하셨다시피 NULL 값을 확정값처럼 DB에서 생각하기 때문에

위의 두 쿼리는 아래처럼 같은 비용을 내게 됩니다.

하지만, 울회사에서는 ANSI_NULLS 를 ON 으로 주고 사용하기 때문에 여러분들은 역시

SP 작성하실 때에 주의를 기울여 주시지 않으면 안된다는 겁니다. ^^

결론 : 여러분의 작은 관심이 시스템을 조금 더 안정적으로 만들 수 있습니다.

그럼 또 좋은 '꺼리' 생기면 찾아뵙겠습니다. ㅎㅎ

Posted by maceo

길다란 복합 인덱스가 능사가 아니다?

작성: 박노철(maceo.park@gmail.com)


오늘 기존 테이블에 대한 index tuning 을 하다가 발견한 현상.

테이블 T1 사이즈 : 1500만건
T1에는 클러스터드 인덱스가 있다고 가정하자.

이라는 쿼리가 있다고 치자. 이럴때는 보통

이렇게 잡는다.

그런데 다음 쿼리가 들어왔다.

그러면 c,d 에

복합인덱스를 잡으면 된다.

이제 우리는 a,b,c,d 와 c,d 에 대한 인덱스 두개를 가지고 있다. 일반적으로 위와 같이 해결 하지만 다른 방법도 생각해볼 수 있다. where조건을 구성하는 컬럼을 분해해서 다음과 같이 인덱스를 구성하는 것이다.


좀 이상하지 않은가?????

이렇게 하면 where a=1 & b=1 & c=1 & d=1 에 대해서 idx__a_b를 타게 될테니 idx__a_b_c_d가 있을 때보다 읽기수가 대폭 증가할 것이라고 예상이 된다. idx__a_b를 탄 후에 bookmark lookup 을 해서 c,d를 체크해야 할 테니. 하지만 이런 경우 SQL Server는 똑똑하게 동작한다. 옵티마이저는 bookmark lookup 의 비용이 과도하다고 판단하면 T1의 두개의 인덱스 idx__a_b와 idx__c_d를 동시에 Index Seek을 해서 join 을 해버린다. maxdop 가 높고 시스템 가용자원이 남아서 병렬 계획을 수립할 가능성이 높으면 더더욱 두개의 인덱스를 병렬로 seek하는 방향으로 실행계획이 유도된다. join 할 때는 각각의 인덱스 끝에 붙어 있는 클러스터드 인덱스 키를 이용하는 것 같다. a,b의 선택성이 좋아서 bookmark lookup 비용이 과도하지 않으면 저렇게 동작하지 않는 것 같다.

인덱스를 위와 같이 잡을 때의 이점은 명백하다. c,d 컬럼에 대해서 중복 인덱스를 잡을 필요가 없어진다. 그렇다고 해서 idx__a_b_c_d가 있을 때보다 읽기수가 엄청나게 증가하는 것은 아니다. 물론 늘어나긴 하지만 감당할만한 수준이다.


결론: 액세스가 빈번하지는 않으나 테이블 사이즈가 매우 커서 공간을 절약할 필요가 있는 경우 where조건을 구성하는 컬럼을 분해해서 각각의 index를 잡는 전략을 고려해볼 수 있다.

Posted by maceo

대상 테이블 1500만건. 데이터 사이즈 6.6G
작업 : 클러스터드 인덱스 생성작업
maxdop 2 일 때 걸린 시간 : 3분 4초
maxdop 4 일 때 걸린 시간 : 2분 17초
maxdop 16 일 때 걸린 시간 : 4분 28초
maxdop 32 일 때 걸린 시간 : 12분 30초

maxdop 가 커지면 스레드들끼리 gather stream 작업을 하는데 SQL Server 2000은
그 부분에서 매우 비효율적인 것 같다. 2005 에서는 얼마나 좋아졌으려나....

Posted by maceo

파티션 테이블, 병렬계획 & 성능 고려사항들

http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx
번역:박노철(maceo.park@gmail.com)

Q:SS2005 의 파티션 테이블에 쿼리를 날릴 때 어떤 종류의 병렬 계획이 나올 것이며 성능에는 어떤 영향을 미치는가?

A:먼저 파티셔닝에 대한 간략한 배경을 소개하겠다. SS2005의 테이블 파티셔닝은 관리와 가용성 측면에서 많은 향상을 가져왔다. 관리적 측면에서 보자면, 데이터 파티션에 대해서 메타데이터 스위치 인, 아웃을 가능케 했다(sliding window requirements를 지원한다. (뭔소리여? -_-)) 가용성 측면에는 온라인 인덱스 리빌드, 병렬 실행, 파일그룹의 piecemeal 리스토어가 가능하다.

이제 파티션 테이블이 성능에 어떤 영향을 미치는지 살펴보자. 파티셔닝을 사용하는지 아닌지에 관계없이 병렬 계획 선택여부는 CPU갯수, 쿼리 비용, 가용 메모리와 현재 workload 에 의해서 결정된다. 이 글에서 이야기하는 모든 것들은 병렬 계획이 가능할때만 유효한 것들이다.

쿼리가 하나의 파티션을 사용할 때 SS2005는 maxdop에 설정된 수치까지 여러개의 스레드를 사용해서 병렬로 데이터를 읽어올 수 있다. maxdop는 보통 시스템에 설치된 CPU갯수와 같은 값을 의미하는 0으로 설정되어 있다. 쿼리가 두개 이상의 파티션에서 데이터를 읽어올 때 파티션당 단 하나의 스레드만 사용될 수 있다.

만약 파티션 갯수가 maxdop와 같거나 더 적다면 데이터가 한쪽으로 치우침으로 인해서 CXPACKET 대기가 생길 수 있다. 파티션 갯수가 maxdop보다 크면 SS2005는 하나의 스레드가 특정 파티션에서 작업을 끝내면 자동적으로 다음 파티션으로 이동한다. 16개의 파티션을 가지고 있고 maxdop가 8이라면 첫 8개의 스레드는 파티션1-8에 대해서 작동한다. 작업이 끝난 첫번째 스레드는 파티션9에서 돌게 된다.

만약 8개 이상 CPU를 가진 기계라면, 최악의 경우는 두개의 파티션에 걸쳐있는 단일 SELECT문이다. (테이블1 참조) 테이블2에 노란색 강조 부분, 파티션 80,81과 Executes 컬럼의 스레드 숫자를 보면, 노란색 부분 이후의 단계에서 maxdop가 적용될 수 있음에도 불구하고 데이터를 읽어올 때는 파티션당 하나의 스레드만 돌아간다. (녹색 강조 참고)

Table 1: Retrieve 2 weeks of data

SELECT

SUM(Sales_Qty) as Sales_Qty,

SUM(Sale_Amt)  as Sales_Amount

FROM   SalesDB.dbo.Tbl_Fact_ Sales – Partitioned by week

WHERE  date_id between '20050703' and '20050716'

Table 2: Set Statistics Profile:  MAXDOP = 12

Rows	Executes	StmtText
1	1	SELECT SUM([Sales_Qty]) [Sales_Qty],SUM([Sale_Amt]) [Sales_Amount] FROM [SalesDB].[dbo].[Tbl_Fact_Sales] WHERE [date_id]>=@1 AND [date_id]<=@2
0	0	|--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [globalagg1008]=(0) THEN NULL ELSE [globalagg1010] END, [Expr1003]=CASE WHEN [globalagg1012]=(0) THEN NULL ELSE [globalagg1014] END))
1	1	|--Stream Aggregate(DEFINE:([globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009]), [globalagg1012]=SUM([partialagg1011]), [globalagg1014]=SUM([partialagg1013])))
2	1	|--Parallelism(Gather Streams)
2	12	|--Stream Aggregate(DEFINE:([partialagg1007]=COUNT_BIG([SalesDB].[dbo].[Tbl_Fact_Sales].[Sales_Qty] as [ss].[Sales_Qty]), [partialagg1009]=SUM([SalesDB].[dbo].[Tbl_Fact_Sales].[Sales_Qty] as [ss].[Sales_Qty]), [partialagg1011]=COUNT_BIG([SalesDB].[dbo].[Tbl_Fact_Sales].[Sale_Amt] as [ss].[Sale_Amt]), [partialagg1013]=SUM([SalesDB].[dbo].[Tbl_Fact_Sales].[Sale_Amt] as [ss].[Sale_Amt])))
20577235	12	|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006]) PARTITION ID:([PtnIds1006]))
2	12	|--Parallelism(Distribute Streams, Demand Partitioning)
2	1	|    |--Constant Scan(VALUES:(((80)),((81))))
20577235	2	|--Index Seek(OBJECT:([SalesDB].[dbo].[Tbl_Fact_Sales].[IX_Tbl_Fact_Sales_SKDteItmStrIDSalQtySalAmtDiscMkd] AS [ss]), SEEK:([ss].[SK_Date_ID] >= (20050703) AND [ss].[SK_Date_ID] <= (20050716)) ORDERED FORWARD PARTITION ID:([PtnIds1006]))

예를 들어 월단위로 파티션된 테라바이트급 매출 테이블이 있다고 하자. 일반적인 쿼리 패턴이라면, 이달과 전달 또는 이달과 1년전을 비교할 것이다.

Table 3:  MONTHLY Partitions

	WHERE clause	Partitions	Retrieval Parallelism
Single SELECT statement	SELECT …. WHERE DateCol BETWEEN ’10/1/2005’ and ‘11/30/2005’	2	1 thread per partition *
SELECT UNION SELECT [UNION SELECT]	Select …. Where DateCol between ’10/1/2005’ and ‘10/31/2005 UNION Select …. Where DateCol between ’11/1/2005’ and ‘11/30/2005	1 per select	MAXDOP per partition

Table 4:  WEEKLY Partitions:  Sales for November 1-15

	WHERE clause	Partitions	Retrieval Parallelism
Single SELECT statement	SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/1/2005’ and ‘11/15/2005’	3	1 thread per partition *
SELECT UNION SELECT [UNION SELECT]	SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/1/2005’ and ‘11/5/2005’ UNION SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/6/2005’ and ‘11/12/2005’ UNION SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/13/2005’ and ‘11/15/2005’	1 per select	MAXDOP per partition

비록 관리나 가용성 측면에서 어려움이 있긴 하지만 거대한 단일 테이블이 성능상 더 좋은 경우도 있다. 예를 들어 1TB 파티션 테이블에 두개의 파티션을 읽어야 하는 쿼리가 있다고 하면, SS2005는 파티션당 1개의 스레드만 할당한다. 하지만 SS2005는 1TB짜리 단일 테이블에 대해서는 maxdop를 적용하여 데이터를 읽어온다.

Table 5:  Monolithic BigSalesTable

	WHERE clause	Partitions	Retrieval Parallelism
Single SELECT statement	SELECT …. WHERE DateCol BETWEEN ’10/1/2005’ and ‘11/30/2005’	N/A	MAXDOP

Best Practices:

파티션과 병렬 실행의 퍼포먼스에 관한 위의 커멘트는 DW, 배치 프로세싱, 리포팅에 적용가능하다. 모든 DW가 병렬 쿼리를 허용하지는 않을 것이다. 병렬 계획은 시스템에 실행되는 쿼리가 몇개 없고 실행 시간을 최소화 하기 위해 가능한 많은 리소스를 사용하기를 위할 때 가장 효과적이다. 만약 DW가 이미 동시성이 매우 높은 환경이라면, 병렬 계획은 throughput이나 응답 시간을 향상시켜주지 않을 것이다. 이미 수많은 단일 스레드 쿼리들이 가용 자원을 대부분 소비하고 있을 것이기 때문이다. 최고의 퍼포먼스를 위해서, 동시성이 높은 OLTP시스템에서도 병렬 계획을 원하지는 않을 것이다.

파티셔닝 정밀도(일별, 주별, 월별)를 결정할 때 사용자들의 일반적인 쿼리 패턴을 고려해야 하며 CPU8개 이상의 시스템에서 최고의 성능을 위해서는 최소한 maxdop개의 파티션을 사용하도록 해야 한다. 테이블 3,4에서 보여준바와 같이 SQL문을 여러개의 단일 파티션 쿼리로 재작성해야 최고의 성능을 얻을 수 있다.

Posted by maceo

1TB 를 한시간안에 로드하기

http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx
번역 : 박노철(maceo.park@gmail.com)

이 프로젝트는 SQL Server 2005 엔터프라이즈 에디션 (SP1 beta) 에서 60개의 병렬 입력파일에 대해서 BULK INSERT 를 사용해서 수행되었다. 각 파일의 크기는 약 16.67GB 이다. 최고 기록은 50분이다. HP는 TPC-C 테스트에 사용하는 기계를 하룻밤 빌려주었다. 1.6GHZ Itanuum2 CPU 64개가 달린 수퍼돔이었다. insert 테스트를 위해서 256GB 램을 장착했다. HP SAN 설정은 초당 14GB의 스루풋을 보여주었다. http://tpc.org/tpcc/results/tpcc_result_detail.asp?id=103082701 에서 상세한 설정을 찾아볼 수 있다.

BEST PRACTICES and LESSIONS LEARNED

• 최대한 많은 수의 CPU를 사용하라. 32개의 CPU가 있으면 32개를 동시에 로딩하라. 8개면 8개를 로딩하라.
• 만약 입력 파일을 생성하는 것을 조절할 수 있는 권한이 있으면 병렬로 로드하려는 스레드 갯수로 나눈 수만큼 파일크기가 동일하게 조절하라. switch 파티션 전략을 사용하고 싶으면 모든 레코드들이 하나의 파티션에 속하는 것을 확인하라.
• 만약 작업을 SQL Server 머신에서 수행한다면 BCP 대신에 BULK INSERT 를 사용하라.
• 추가적인 8~10% 의 속도개선을 위해서 테이블 파티셔닝을 사용하라. 단, 입력 파일들이 파티셔닝 함수에 적합하다는 것이 보장되어야 한다. (하나의 파일에 있는 모든 레코드가 같은 파티션에 있어야 함을 의미)
• TABLOCK 을 사용하라.
• 만약 복수개의 스트림을 하나의 테이블에 로드한다면 ROW PER BATCH = 2500 이나 그 근방의 값을 사용하라.
  

TEST SETUP

파일들은 database 와 동일한 SAN에 존재했다. 디스크 어레이는 달랐다. 하나의 드라이브 어레이에 12개의 파일들이 있었고 60개의 입력파일들을 핸들링하기 위해서 5개의 디스크 어레이가 필요했다. DB는 두개의 파일그룹과 96개의 파일을 가지고 있다. insert 테스트를 위해 사용된 테이블은 LINEITEM 이고, 자기 자신의 파일그룹에 존재하고 있다. 디스크상에는 바깥쪽 트랙에 위치한다.

DB는 BULK LOGGED 모드였다. 따라서 트랜잭션 로깅은 거의 일어나지 않았다. 익스텐트 할당과 관련된 메타데이터는 로깅되었지만 개개의 페이지 변화나 레코드 인서트는 기록되지 않았다.

입력파일을 | 를 컬럼 델미미터로 사용하는 문자열 파일이다. 이러한 flatfile 들은 DBGen 을 이용해 만들어졌다. DBGen 은 TPC 협회에서 제공하는 표준 도구다. flatfile 는 깨끗한 것으로 간주할 수 있다. 행길이는 가변 길이였는데, 평균 130바이트 정도 된다.

최종 설정에서 60개의 job을 만들었고, 각각은 하나의 BULK INSERT 를 담고 있었다. 굳이 여러개의 쿼리 윈도우를 열지않고 작업을 컨트롤하기 위해서 이렇게 했다. 각각의 job 을 컨트롤하기 위해서 sp_startjob 이 사용되었다.

최초의 테스트는 ROWS PER BATCH=2500 으로 해서 하나의 테이블에 대해서 수행되었다. 40개의 스레드가 시작되기 전에는 블러킹이 거의 없었다. 배치 사이즈가 2500이었기 때문에 락 유지시간은 상당히 짧았다.  Data Management View 를 통해서 보니 하나의 테이블에 대한 익스텐트 할당때문에 락이 잡혔다. 이 테스트를 조금씩 바꿔가면서 해보았는데 대체로 65분 안에 끝났다. 익스텐트 할당으로 인한 대기를 피하기 위해서 다음 테스트는 복수개의 테이블에 대해서 수행되었다.

다음 테스트는 각각의 입력에 대해서 각각의 테이블을 사용하는 것으로 수행되었다. 각 테이블은 하나의 파티션을 가지고 있었고 최종적으로 파티션들은 60개의 파티션을 가진 하나의 거대한 테이블로 switch in 되었다. (스위칭은 SQL 2005 테이블 파티셔닝에서 사용되는 새로운 용어이다) 스위칭은 메타데이터 스위치에 불과하기 때문에 매우 빠르다. 테스트결과 한번 스위칭하는데 평균 22~35ms 정도 걸렸다. 여기서 어려운 점은 각 입력파일이 정말로 그 파티션에 속하는지를 확인하는 작업이다. 이 테스트에서 배치사이즈는 테스트결과에 어떤 차이를 만들어내지 못했다. 왜냐하면 테이블당 하나의 BULK INSERT 스레드가 사용되기 때문이다. TABLOCK 은 잠금 관리를 회피하기 위해서 매우 중요하다.

최종결과를 볼 때 각각의 테이블로 분리함으로써 8~10%의 향상을 이룰 수 있었다. 이게 바로 1TB를 로드하는데 50분이 걸린 시나리오다. 인덱스 빌드나 파티션 검증을 위한 제약조건 생성을 위한 시간은 없았다. 다음 테스트로 미룬다.

CPU BOTTLENECK

주된 제약조건은 CPU였다. IO가 아니었다. SAN은 초당 14GB의 스루풋을 가진다. SQLIO를 사용해서 드라이브의 최대 스루풋을 측정해볼 수 있다. 우리의 테스트에서 BULK INSERT를 돌리는 60개의 스레드에 대해서 최대 초당 333MB의 쓰기 작업이 일어났다. 이는 초당 210만개 조금 넘게 BULK COPY하는 것을 의미한다. PERFMON을 사용하여 이 카운터를 관찰했다.

하나의 BULK INSERT 스레드는 유휴 CPU 를 99~100% 사용한다. 다음 job은 다른 CPU를 사용했다. 한참 바쁜 CPU에 스케쥴링되지는 않는 것으로 보였다.

TEST PROCESS

첫번째 테스트는 한번에 하나의 job 을 시작해서 정상상태에서 도달해서 작동하도록 내버려 두는 것으로 시작했다. 모든 측정은 기록되었고 다른 job이 시작되었다. 관찰하고 있던 카운터값의 증가는 매우 예측가능한 수준이었다. 그러나 정확하게 선형적이지는 않았다. 즉, 두번째 job이 첫번째 job이 도는 속도로 돌지는 않았다. 사실, 로그곡선의 형태로 판명되었고 60개의 job이 동시에 돌 때는 아주 평평했다.

첫번째 테스트는 BCP가 CPU를 몇%정도 더 사용한다는 것을 알아채기 전까지는 BCP를 사용해서 진행되었다. 55개의 job이 실행될 때까지 CPU 총사용량은 100%였다. 그래서 BULK INSERT 로 바꿨는데, 이렇게 하면 import 스레드가 SQL Server process 안에서 돌아간다. BULK INSERT로 바꾼 후에 60개의 job을 동시에 돌릴 수 있었고, 60개를 돌릴때까지 스루풋은 계속 증가했다. 이때 CPU사용량은 95%를 유지했다. 61~64개의 job은 60개일때와 스루풋이 거의 똑같다.

첫번째 테스트 도중, 8~9GB짜리 파일이 96개 있었다. 동시에 60개의 job을 돌리지만 않는다면, 첫번째 48개의 파일들을 30분안에 로딩할 수 있었고 다른 49개도 마찬가지였다. 60분에 1TB를 로딩하는 것은 꽤 훌륭한 결과다.

일단 CPU가 제약조건이라는 것을 안 이후로, 60개의 파일을 16.67GB로 만들었다. 이렇게 하니까 최고 50분이 나왔다. 모든 고객들이 입력파일에 대한 세세한 권한을 가질 수는 없기 때문에 이런 테스트는 만들어진 측면이 있다. 초반부에 언급했듯이 입력파일들은 큰 테이블을 위해서 설계된 최종 파티셔닝 scheme 에 정확하게 들어맞았다.

최종 테스트는 CPU 밸런싱이 만족할만한 수준에 있는지 확인하기 위해 60개의 job을 동시에 돌리는 것이었다. 결과는 괜찮았다. 이는 동시에 두개의 BULK INSERT 스레드가 하나의 CPU를 사용하지 않음을 의미한다. SQL product team의 개발자들은 UMS스케줄러당 하나의 bulk command만 수행하도록 UMS스케줄러를 특별히 체크한다고 이야기해주었다. 물론 CPU갯수보다 더 많은 명령을 돌리면 CPU당 복수개의 bulk command를 할당할 것이다.

NOT FINISHED YET

여기에서 우리는 모든 데이트를 60개의 분리된 테이블에 가지고 있었다. 이것은 로컬 파티션드 뷰를 사용하기 원하는 사람들에게는 좋은 시나리오다. (테이블들에 대해서 union all 을 하는 하나의 뷰를 만드는 것) 

테이블 파티셔닝을 사용하기 원하는 고객들은 모든 파티션들을 하나의 테이블로 모으기 위해서 몇가지 단계를 더 거쳐야 한다. 다음과 같다.

1. Create a File Scheme 파일 Scheme 을 생성
2. Create a Partition Function   파티션 함수를 생성
3. Create a clustered index on each file  각 파일에 클러스터드 인덱스를 생성
4. Create the check constraint that will be used for partitioning.  파티셔닝을 위해 사용되는 제약조건을 생성
5. Switch the partitions into the final large table.  각 파티션들을 하나의 큰 테이블로 스위칭

제약조건을 걸기전에 인덱스를 생성하는 이유가 있다. 우리가 이 두가지 작업의 순서를 바꾸게 되면, 제약조건에 맞는지 검사하기 위해서 테이블 스캔이 유발될 것이다. 클러스터드 인덱스를 먼저 생성함으로써 제약조건은 모든 행들이 제약조건에 맞는지 검사하기 위해서 첫번째 값과 마지막 값만 검사하면 된다.

NEXT TESTS

다음 테스트로 무엇을 해볼 것인지 수많은 아이디어가 있다 하지만 이걸 하기 위해서 TPC-H run 사이에 하룻밤만 시간을 빌려왔기 때문에 머신이 사용가능해지는 다음 시간까지 기다려야만 한다. 하지만 주어진 시스템 가용성 한도 내에서 HP는 이런 테스트를 할 수 있도록 해주었다. 실제로는 패턴이 매우 예측가능하기 때문에 다른 테스트는 패턴과 타이밍을 보기 위해서 더 적은 CPU를 가진 머신에서 이뤄질 수도 있다. 물론 많은 CPU를 가진 것이 아니라면 1TB를 로딩하는데 1시간에 끝나지는 않을 것이다.

• SQL Agent와 SQL Server가 파일을 직접 읽지 않아도 되도록 BCP나 BULK insert 를 다른 클라이언트 머신에서 수행.
• 네트웍이 끼어들어가면 패턴이 어떻게 변하는지 모기 위해서 입력파일을 다른 컴퓨터로 이동.
• SSIS의 bulk insert task를 이용해서 테스트. 이것을 SQL Server 기계의 CPU부담을 낮춰주기 위해서 다른 기계에서 수행. 만약 이것을 SQL Server 머신에서 수행한다면 BCP를 사용하는 것과 거의 동일한 결과가 나올 것임.
• 파일에 인덱스를 생성.
• 더 작은 기계에서 수행. 모든 고객들이 64-way 머신을 가지고 있지는 않을 것이기 때문.
• 입력파일들을 미리 정렬해놓고 로딩전에 클러스터드 인덱스를 먼저 만듦. 만약 미리 정렬이 되어 있다면 인덱스 생성 시간을 절약해줄 것임.
• NUMA 설정이 어떤 영향이 있는지 관찰. CPU 병목현상이 있다면 그다지 도움될 것 같지는 않아 보임. 하지만 해당 노드에 foreign memory(NUMA아키텍쳐에서 다른 셀의 CPU를 뜻함)가 할당된다면 더 느려질 것이다. foreign memory 할당을 피하는 방법이 있다. 슬라바 오크의 블로그를 참조.(http://blogs.msdn.com/slavao)

감사의 말씀 : MS SQL Server product group 의 Sunil Agarwal 와 그의 팀, HP의 Mike Fitzner 에게 감사드립니다.

enjoy -- Kevin

Published Friday, May 19, 2006 9:58 PM by kevincox
Filed Under: Performance and Scalability

Comments
# Microsoft SQL Server Development Customer Advisory Team - Load 1TB in less than 1 hour @ Sunday, May 21, 2006 7:46 PM
Professional Association for SQL Server (PASS) SIG

# SQL Server 2005: Load 1TB in less than 1 hour @ Tuesday, May 23, 2006 5:50 AM
STEFANO DEMILIANI WeBlog

# re: Load 1TB in less than 1 hour @ Wednesday, May 24, 2006 3:45 PM
흥미롭습니다. 추가적인 테스트를 기대합니다 (특히 SSIS테스트요) 그런데 다음 사항에 대해서 더 상세하게 설명해주시겠어요?

“Use ROWS PER BATCH = 2500, or something near this if you are importing multiple streams into one table.”

어떻게 이 값이 제일 좋은 값이라고 결론내신건지요? 테스트에 사용된 데이터에만 해당하는 수치는 아닌가요? 제가 큰 데이터를 마이그레이션 테스트할 때는 정확한 배치 사이즈는 테이블의 데이터 타입을 관찰함으로써 결정할 수 있었습니다. SQL 2000 에서 최대 로그 블럭은 60KB입니다. 따라서 저는 배치당 60KB 넘게 insert 하지 않도록 배치 사이즈를 조절합니다.(의역했음)

60KB limit에 도달하기 위해서는 몇개의 익스텐트 할당이 필요한가요?(뭔 소린지 잘 모르겠음. 익스텐트당 64KB아닌가..?)

당신의 테스트에서는 익스텐트 할당 대기와 관련된 여러 이슈들을 언급한 것 만큼은 이건 크게 고려되지 않은 것 같네요. 하지만 더 작은 시스템에서는 60KB 로그 플러쉬와 관련해 BULK INSERT 사이즈를 조정하는 법을 이해하는 것이 좋을 것 같습니다.

Bert

# re: Load 1TB in less than 1 hour @ Wednesday, May 24, 2006 8:23 PM
Amazing statistics.. Interesting that disk IO on the SAN configuration did NOT turn out to be the bottleneck in this case. What was the memory consumption during this test?
Ted Malone

# re: Load 1TB in less than 1 hour @ Wednesday, May 24, 2006 9:50 PM
Response to Bert: 배치당 2500 rows 는 lock escalation 을 피하기 위해서 사용되었습니다.

잘 모르겠습니다. 그리고 걱정할만큼 중요한 속도저하인지도 잘 모르겠군요.

불행하게도 디스크 공간 확보를 위해서 perfmon 로그를 지웠습니다. 256GB 메모리의 거의 다 사용하지는 않았던 것 같습니다. 메모리 대기가 있다면 더 주의하도록 하지요.

kevincox

# re: Load 1TB in less than 1 hour @ Monday, May 29, 2006 12:46 PM
NUMA에 따른 영향에 대해서 관심이 있습니다. SQL Server는 몇개의 log 기록 스레드를 사용할 수 있나요? 만약 하나 이상이라면 affinity bw(???) 를 조정할 수 있나요? 만약 로그 기록 스레드가 동일한 NUMA노드나 동일한 프로세서/코어에 존재하는 것이 어떤 효용이 있다요? 컨텍스트 스위칭과 스레드간의 통신의 트레이드 오프는 어떤가요?(???) 만약 하이퍼 스레딩 머신이라면 로그 기록 스레드가 insert스레드와 다른 논리 프로세서에 있는 것이 어떤 효용이 있습니까?

# re: Load 1TB in less than 1 hour @ Tuesday, May 30, 2006 9:29 AM
SQL Server 2005 에서 최대 페이지 사이즈는 얼마인가요? 변경가능한가요?

Francisco

Posted by maceo

오늘 마이그레이션 테스트를 하다가 알아낸 사실.

다음과 같은 쿼리가 있다고 하자.

VERY_BIG_TABLE 의 데이터 갯수는  1억건, BIG_TABLE 의 갯수는 1천만건이다.
데이터 마이그레이션 중이므로 당연히 CPU 4개를 써서 한꺼번에 읽어서 hash join 으로 처리하기를 기대하고 위와 같이 만들었다. 그런데 돌려보니 평소에 걸리던 것보다 시간이 엄청 오래 걸린다. 실행계획을 보니 병렬 계획을 세우지 못하는게 아닌가!

도대체 이게 무슨 일인가 하여 테이블을 하나하나 빼가면서 여러 모로 살펴본 결과 원인은 두가지였다.

믿기지가 않겠지만 정말로 일어난 일이다. SQL Server 2000 sp4 에서 발생했다.

1번에 대해서는 그럴수도 있겠다는 생각이 든다. SQL Server 는 아주 가끔 병렬 계획을 세워서 쿼리를 수행하다가 Intra Query Paralleism Deadlock 을 일으킨다. 즉, 복수개의 CPU 들이 쿼리를 처리하다가 지들끼리 데드락을 잡아서 쿼리가 실패하는 경우다. 아무래도 복수개의 CPU가 같은 테이블을 self-join 해야 하는 상황이 되다보니 옵티마이저가 데드락을 우려하여 알아서 병렬 계획을 안세운 것이 아닌가 하는 생각이 든다.

2번에 대해서는... 전혀 알 수 없다. 도대체 function 하고 병렬 계획하고 무슨 상관이람-_-;
function 은 실행계획에 아예 나오지도 않는데 뭐 그런 문제랑 관련이 있는건지.. 흠....

Posted by maceo

일러두기. 본문서는 기배포된 중첩 트랜잭션 처리 가이드와 함께 읽으면 좋습니다.

1. @@error 와 @@rowcount 를 이용한 에러 처리

@@error : SQL문 실행도중 어떤 이유로 에러가 발생했을 때 0 이 아닌 값으로 세팅되는 시스템 함수. 에러가 발생했다가 단 한줄이라도 다음 문장이 성공하면 0 으로 세팅됨.

@@rowcount : select, insert, delete, update, set 문이 성공한 후에 몇 행을 처리했는지 세팅되는 함수.

일반적으로 SP 안에서 에러처리는 다음과 같은 패턴으로 이루어집니다.

자주 저지르는 실수!

위의 코드는 에러가 발생하면 @@error 와 @@rowcount 를 별도의 변수에 저장하고 에러를 처리하는 코드입니다. 그런데 위의 에러처리 루틴은 결코 실행되지 않습니다.  @result_code 를 세팅하기 전에 set @processed_rows = @@rowcount 가 성공하면서  @@error 가 0으로 되고 @result_code 에는 언제나 0 이 세팅되기 때문입니다.

그렇다면 코드를 이렇게 바꾸면 어떨까요?

update 문을 실행하자마자 @@error 를 @result_code 에 넣었으므로 정상적인 값이 들어갈 것입니다. 그러면 @@rowcount 는 어떨까요? DEPT 테이블에 AX라는 부서가 없으므로  @@rowcount 는 0 이 되어야 합니다. 하지만 실행시켜보면 @processed_rows 에 1 이 들어가 있습니다. 이게 어찌된 일일까요???

원인은 set @result_code = @@error 때문입니다. @@rowcount 는 set 문에 의해서도 영향을 받습니다. 따라서 set @result_code = @@error 가 성공하면 한 개의 데이터가 변수에 저장되었으므로 @@rowcount 는 1이 되는 것입니다.

(참고 : http://msdn2.microsoft.com/ko-kr/library/ms187316.aspx)

정확한 코드는 다음과 같습니다.

select 문도 변수에 값을 저장할 수 있습니다. select 문을 이용해서 한꺼번에 값을 저장하면 정확하게 원하는 값이 저장됩니다. SQL Server 2000 은 C++ 이나 Java, C# 에서 제공되는 try ~ catch 구문을 제공하지 않는다. (2005에서는 제공합니다) 따라서 에러 발생의 소지가 있는 각 insert, delete, update 문 마다 위와 같은 에러 처리 루틴을 넣어야 합니다.

(참고 : set 문으로 변수를 저장하는 것 보다 select 로 저장하는 것이 더 느리다는 개발자의 경험사례 보고가 있었습니다. 반복 테스트가 필요하겠습니다. 테스트 후 결과를 올리도록 하겠습니다.)

2. rollback, commit 할 때는 언제나 @@trancount 를 체크하자

@@trancount : 트랜잭션의 중첩 수준을 나타내는 시스템 변수. Begin tran 문에 의해 1씩 증가하고 commit tran 에 의해 1씩 감소한다. rollback tran 을 만나면 모두 롤백하면서 어떤 값을 가지고 있더라도 0 으로 변한다.

sp를 작성할 때 데이터의 정합성을 보장하기 위해서 트랜잭션을 사용하게 됩니다. 트랜잭션을 시작하면 언제나 begin tran, commit tran의 짝이 일치해야 하는 것은 당연하지요. 즉, 트랜잭션이 중첩되어서 begin tran 을 세번하게 되면 세번 commit 이 있어야 합니다. 단 rollback 은 한번에 @@trancount 를 0 으로 만들고 rollback 하기 때문에 rollback 하기 전에 언제나 @@trancount 를 체크하면서 rollback 해야 합니다.

rollback 이나 commit 하기 전에 @@trancount 를 체크하는 것은 sp들간에 복잡하게 호출하고 각 sp들이 자체적으로 transaction 을 사용할 때 위력을 발휘합니다. 중첩 트랜잭션 처리에 관한 사항은 기배포한 중첩 트랜잭션 처리 가이드를 참고해주세요.

Posted by maceo

오늘 sp 검수도중 발견.

[CODE]remote svr.에서 create table aaaa (    col1 int identity ) local svr. 에서 insert into remote.pubs.dbo.aaaa (col1) values (0) [/CODE]

위를 실행하면 다음과 같은 OLEDB에러를 낸다.


linked server 는 역시 조심해서 사용해야 함.

Posted by maceo

여기저기 SQL2005 관련 아티클, 세미나 찾아다닐 필요없이 이거 하나만 보면 된다.

http://www.microsoft.com/events/series/technetsqlserver2005.mspx

수십시간 분량의 웹캐스트가 제공되는데, 2005의 거의 모든 영역을 다 커버한다. 영어의 압박이 있긴 하지만 어차피 스크린캐스트가 제공되기 때문에 이해하는데 무리는 없다. 이제 OLTP에서 왠만한 튜닝이슈는 거의 다 겪어봐서 따분하던 차에 공부할꺼리가 왕창 생겨서 마구 흥분중이다. ㅎㅎㅎ

(DW/OLAP/BI 쪽은 언제쯤 손댈 수 있으려나...)

Posted by maceo

http://blogs.msdn.com/queryoptteam/rss.aspx

이제야 옵티마이저에 대해 궁금한 것들을 마구 물어볼 수 있을 것인가~!!!

Posted by maceo