parallel 강의에서 사용된 soft numa 설정법 입니다.

안녕하세요 김민석 입니다.

이번 SQLTAG 2차 Online Study 에서는 SQL Server 의 Parallel 에 대한 이해 입니다.

먼저 http://blogs.msdn.com/b/craigfr/archive/2007/04/17/parallel-query-execution-presentation.aspx 에서 소개된 ppt 를 죽 설명하면서 parallel 의 이해를 해 보았습니다. Craig Freedman 은 SQL Server Query Team 에 있는 Optimizer 개발자 입니다. SQL Server 뿐만 아니라 다른 상용 데이터베이스의 Optimizer 에 대해서도 개발한 전력이 있어 보였습니다.

1. Scalability Terminology 를 먼저 이해야해 하고 SQL Server Parallel 은 공짜가 아니라는 점을 설명 합니다.
2. SQL Server 가 기본적으로 Parallel 하게 풀기 위해서는 affinity mask, max degree of parallelism, cost threshold for parallelism, max worker threads 의 올바른 설정이 필요 합니다.  
3. CPU Utilization 에서는 SQL Server 2000 과 2005의 차이점을 설명 합니다.
4. Parallelism Operator 에서 producer 와 consumer 의 개념을 설명 합니다.
5. Gather Stream Repartition Streams, Distribute Streams 의 개념을 설명 합니다.
6. Routing 기법중 Broadcast, Hash, Round Robin, Demand, Range 를 설명 합니다.
7. Order preserving Non-order preserving 혹은 merging non-merging exchange 를 설명 합니다.
8. Parallel Scan 에 대해 설명 합니다.
9. Query Plan 예제를 설명 합니다.
10. Non-Parallelizable "Stuff" 에 대해 설명 합니다.
    UDF, CLR, Object_id, Error_number, @@trancount, Dynamic Cursors, System table scans, Sequence Functions, Top, Backward Scan, Recursive queries, TVSs, Global scalar aggregate, Multi-cumsumer spool

위 개념을 이해하면 Parallel 에 대한 기본적인 것을 알 수 있습니다. merge (Order preserving) 이 일어나는 Gather Stream 이나 Merge Join 이 일어날 때 Parallel Thread 가 높은 CPU 사용량을 쓰는 CPU 에 할당되게 되면 그 속도가 대단히 느려지는 이유를 정확하게 이해 할 수 있습니다. 기본적으로 대단히 높은 CPU 사용율을 보이는 프로세서에 thread 가 같이 할당되면 처리해야하는 데이터베이스 page 숫자만큼 os 에 자신이 처리될 수 있도록 요청하는 현상이 일어나게 되고 그 주기는 select os_quantum from sys.dm_os_sys_info 에 기술된 ms 만큼 소요 됩니다. 그러므로, 어떤 한 코어가 100% 사용율을 보일때 maxdop 옵션을 줄여 튜닝하게 되면, 때때로 운 없이 바쁜 core 에 Thread 가 할당되어 쿼리 응답시간을 예측가능하게 디자인 할 수 없습니다. 우리가 튜닝할때 가장 중요하게 생각하는 것은 쿼리의 응답시간이 예측 가능해야 한다는 것입니다. 역시 sqlworkshops.com 의 R. Meyyappan 도 결론적으로 어떻게 튜닝해야 한다는 해법은 제공해 주지 않았습니다.

이러한 것을 풀 수 있는 방법은 언듯 생각하기에 3가지 정도 방법이 있을듯 합니다.

1. Resouce Governor ( http://msdn.microsoft.com/en-us/library/bb933866.aspx )를 이용
2. Soft Numa 와 TCP Port Mapping 기법
3. Merge join의 경우 Hash Join 으로 변경이 가능한지 쿼리를 non-order preserving 하게 만들수 있는지 여부 검사

Soft-Numa 와 TCP Port Mapping 기법 입니다.

1. Soft-Numa 의 이해 
   http://blogs.msdn.com/b/psssql/archive/2010/04/02/how-it-works-soft-numa-i-o-completion-thread-lazy-writer-workers-and-memory-nodes.aspx

2. Soft-numa Registery 설정
    http://msdn.microsoft.com/en-us/library/ms345357.aspx

3. Numa 와 TCP/IP 의 연결 
   http://msdn.microsoft.com/en-us/library/ms345346.aspx

간단한 스크린샷 입니다.

soft_numa_example.reg

   

다양한 Test 를 하기위해 모든 경우의 수 생성
20001[1],20002[2],20003[3],20004[4],20005[5],20006[6],20008[8],20009[9],20010[10],20011[11],20012[12],20013[13],20014[14],20015[15]

역시 테스트 하시다 모르는게 있으면 언제든지 email, phone, www.sqltag.org qna 로 연락 주시면 아는데 까지 설명 드리겠습니다. 질문 하실때는 반드시 전화번호를 같이 남겨 주세요.

즐거운 SQL 시간 되세요.