☰

SSIS 프로세스가 더 많은 리소스를 소비하고 더 빠르게 실행되도록 하려면 어떻게 해야 합니까?

tag-icon • tag-icon sql-server performance sql-server-2008-r2 ssis
SSIS 프로세스가 더 많은 리소스를 소비하고 더 빠르게 실행되도록 하려면 어떻게 해야 합니까?

일일 보고서를 제공할 수 있도록 창고를 구축하는 SSIS의 일일 ETL 프로세스가 있습니다.

저는 두 개의 서버를 가지고 있습니다. 하나는 SSIS용이고 다른 하나는 SQL Server 데이터베이스용입니다. SSIS 서버(SSIS-Server01)는 8CPU, 32GB RAM 상자입니다. SQL Server 데이터베이스(DB-Server)는 또 다른8CPU, 32GB RAM 상자입니다. 둘 다 VMWare 가상 머신입니다.

지나치게 단순화된 형식에서 SSIS는 DB 서버의 단일 테이블에서 1,700만 행(약 9GB)을 읽고, 이를 4억 8,000만 행으로 피벗 해제하고, 몇 번의 조회와 수많은 계산을 수행한 다음 이를 다시 약 800만 행으로 집계합니다. 매번 동일한 DB 서버의 새로운 테이블에 기록됩니다(이 테이블은 일별 보고서를 제공하기 위해 파티션으로 이동됩니다).

한 번에 18개월 분량의 데이터, 즉 총 10년 분량의 데이터를 처리하는 루프가 있습니다. 저는 SSIS 서버의 RAM 사용량 관찰을 바탕으로 18개월을 선택했습니다. 18개월에는 27GB의 RAM을 소비합니다. 그보다 높으면 SSIS는 디스크에 대한 버퍼링을 시작하고 성능이 급락합니다.

내 데이터 흐름은 다음과 같습니다.http://img207.imageshack.us/img207/4105/dataflow.jpg

여기에 이미지 설명을 입력하세요

나는 사용하고있다Microsoft의 균형 잡힌 데이터 배포자8개의 병렬 경로로 데이터를 전송하여 리소스 사용을 극대화합니다. 집계 작업을 시작하기 전에 Union을 수행합니다.

다음은 SSIS 서버의 작업 관리자 그래프입니다.

대체 텍스트

다음은 8개의 개별 CPU를 보여주는 또 다른 그래프입니다.

대체 텍스트

이러한 이미지에서 볼 수 있듯이 점점 더 많은 행을 읽고 처리함에 따라 메모리 사용량이 약 27G까지 천천히 증가합니다. 그러나 CPU 사용량은 약 40%로 일정합니다.

두 번째 그래프는 우리가 8개 CPU 중 4개(경우에 따라 5개)만 사용하고 있음을 보여줍니다.

프로세스를 더 빠르게 실행하려고 합니다(사용 가능한 CPU의 40%만 사용하고 있음).

이 프로세스를 보다 효율적으로 실행하려면 어떻게 해야 합니까(최소 시간, 대부분의 리소스)?

답변1

bilinkc의 좋은 제안 이후 병목 현상이 어디에 있는지 알지 못한 채 몇 가지 다른 작업을 시도해 보겠습니다.

이미 언급한 것처럼 동일한 데이터 흐름에서 더 많은 데이터(월)를 처리하는 것이 아니라 병렬 처리를 수행해야 합니다. 이미 변환을 병렬로 실행했지만 소스와 대상(집계 포함)이 병렬로 실행되지 않습니다. 따라서 끝까지 읽고 CPU 성능을 활용하려면 병렬로 실행해야 한다는 점을 명심하십시오. 그리고 당신이 있다는 것을 잊지 마세요메모리 제한(한 배치에서 무한한 개월 수를 집계할 수 없음)이므로 이동 방법("확장")은 데이터 덩어리를 가져와서 처리하고 가능한 한 빨리 대상 데이터베이스에 넣는 것입니다.. 각 데이터 청크가 해당 공통 구성 요소의 속도로 제한되기 때문에 공통 구성 요소(하나의 소스, 하나의 통합 전체)를 제거해야 합니다.

소스 관련 최적화:

  • Balanced Data Distributor 대신 동일한 데이터 흐름에서 여러 원본(및 대상)을 사용해 보십시오. - 날짜 열에 클러스터형 인덱스를 사용하면 데이터베이스 서버가 날짜 기반 범위의 데이터를 빠르게 검색할 수 있습니다. 데이터베이스가 상주하는 서버와 다른 서버에서 패키지를 실행하면 네트워크 활용도가 높아집니다.

변환 관련 최적화:

  • Aggregate 전에 Union All을 수행해야 합니까? 그렇지 않은 경우 여러 목적지에 대한 목적지 관련 최적화를 살펴보십시오.
  • 세트키, KeyScale 및 AutoExtendFactor재해싱을 방지하기 위한 집계 구성 요소 - 이러한 속성이 잘못 설정된 경우 패키지 실행 중에 경고가 표시됩니다. 최적의 값을 예측하는 것은 무한한 수(예: 18개월 및 증가)보다 고정된 개월 수의 배치에 대해 더 쉽습니다.
  • SSIS 패키지에서 수행하는 대신 SQL Server에서 집계 및 피벗(해제)을 고려하십시오. SQL Server는 이러한 작업에서 Integration Services보다 성능이 뛰어납니다. 물론 변환 논리는 패키지에서 일부 변환을 수행하기 전에 집계를 금지하는 것과 같을 수 있습니다.
  • 예를 들어 데이터베이스의 월별 데이터를 집계(및 피벗/피벗 해제)할 수 있는 경우 원본 쿼리 또는 SQL을 사용하여 대상 데이터베이스에서 수행해 보세요. 환경에 따라 대상 데이터베이스의 별도 테이블에 쓰기, 인덱스 작성, SQL로 집계하는 SELECT INTO가 패키지에서 수행하는 것보다 빠를 수 있습니다. 이러한 활동을 병렬화하면 스토리지에 많은 부담이 가해집니다.
  • 마지막에 멀티캐스트가 있습니다. 거기에 도달하는 행 수는 모르지만 다음을 고려하십시오. 오른쪽 대상에 쓴 다음(스크린샷에서) SQL 쿼리에서 왼쪽 대상에 레코드를 채웁니다(두 번째 집계를 제거하고 리소스를 해제하기 위해 - SQL Server 아마 훨씬 더 빨리 할 거예요)

목적지 관련 최적화:

  • 사용SQL Server 대상가능하다면(패키지는 데이터베이스와 동일한 서버에서 실행되어야 하며 대상 데이터베이스는 SQL Server여야 합니다) 정확한 열 데이터 유형 일치가 필요합니다(파이프라인 -> 테이블 열).
  • 설정을 고려하다복구 모델대상(데이터 웨어하우스) 데이터베이스에서 단순으로
  • 대상 병렬화 - 모두 통합 + 집계 + 대상 대신 별도의 집계와 별도의 대상(동일한 테이블에 대한)을 사용합니다. 여기서 고려해야 할 사항파티셔닝대상 테이블 및 별도의 파일 그룹에 파티션 배치 월 단위로 데이터를 처리한다면 월 단위로 파티션을 만들어서 사용하세요. 파티션 전환

병렬 처리를 어떤 방식으로 진행해야 할지 불분명한 상태였던 것 같습니다. 당신은 시도 할 수 있습니다:

  • 단일 데이터 흐름에 여러 원본을 추가하려면 각 원본에 대한 변환 논리와 대상을 복사하여 붙여넣어야 합니다.
  • 각 데이터 흐름이 한 달만 처리하는 여러 데이터 흐름을 병렬로 실행
  • 각 패키지에는 한 달만 처리하는 하나의 데이터 흐름이 있는 여러 패키지를 병렬로 실행합니다. 그리고 각 (월) 패키지의 실행을 제어하는 ​​하나의 마스터 패키지 - 프로덕션에 들어가면 아마도 한 달 동안만 패키지를 실행할 것이기 때문에 이는 선호되는 방법입니다.
  • 또는 이전과 동일하지만 Balanced Data Distributor 및 Union All 및 Aggregate를 사용함

다른 작업을 수행하기 전에 빠른 테스트를 수행할 수 있습니다. 원래 패키지를 가져와서 1개월을 사용하도록 변경하고, 한 달 더 처리하는 정확한 복사본을 만들고 해당 패키지를 병렬로 실행합니다. 2개월 동안 처리된 원래 패키지와 비교해 보세요. 2개의 별도 6개월 패키지와 단일 12개월 패키지에 대해서도 동일한 작업을 수행합니다. 전체 CPU 사용량으로 서버를 실행해야 합니다.

대상에 여러 번 쓰기가 발생하므로 과도하게 병렬화하지 마십시오. 따라서 18개의 병렬 월별 패키지를 시작하는 것이 아니라 시작을 위해 3 또는 4개의 패키지를 시작하고 싶습니다.

그리고 마지막으로, 저는 메모리와 목적지 I/O에 대한 압박이 제거되어야 한다고 굳게 믿습니다.

진행 상황을 알려주세요.

답변2

사용프로세스 탐색기좀 더 많은 리소스 사용량(메모리 및 IO)을 공개합니다. 그래프의 최고점은 종종 하드 드라이브 캐싱 기능으로 인해 발생하므로 디스크 IO 그래프는 약간 오해의 소지가 있을 수 있습니다. 따라서 디스크 IO가 병목 현상일 때 항상 그래프에 즉시 나타나지는 않습니다.

어떤 경우에는 램 드라이브를 설치하고 임시 디렉토리를 거기에 두는 것이 도움이 될 수 있습니다. 성공적으로 사용했습니다이 하나우리의 빌드 머신이 전체 야간 빌드를 수행하고 테스트를 실행하는 데 사용되는 시간을 줄이기 위해. 그래도 SSIS가 도움이 될지는 잘 모르겠습니다.

답변3

(초기 응답을 다시 게시했지만 BDD를 고려하지 않았습니다)

결국 모든 처리는 네 가지 요소 중 하나에 의해 구속됩니다.

  • 메모리
  • CPU
  • 디스크
  • 회로망

첫 번째 단계는 제한 요소가 무엇인지 파악한 다음 해당 요소에 영향을 줄 수 있는지 여부(더 많이 확보하거나 사용량을 줄임)를 결정하는 것입니다.

구성 요소 선택

18개월 이상 수행할 때 서버의 메모리가 부족해지는 이유는 처리하는 데 시간이 오래 걸리는 이유와 관련이 있습니다. 그만큼피벗 및 집계 변환비동기 구성 요소입니다. 소스 구성 요소에서 들어오는 모든 행에는 N바이트의 메모리가 할당되어 있습니다. 동일한 데이터 버킷이 모든 변환을 방문하고 해당 작업이 적용되며 대상에서 비워집니다. 해당 메모리 버킷은 계속해서 재사용됩니다.

비동기 구성 요소가 경기장에 들어오면 파이프라인이 분할됩니다. 파이프라인을 완료하려면 해당 데이터 행을 전송하던 버킷을 이제 새 버킷으로 비워야 합니다. 실행 트리 간 데이터 복사는 실행 시간과 메모리 측면에서 비용이 많이 드는 작업입니다(두 배로 늘어날 수 있음). 이는 또한 엔진이 비동기 작업이 완료되기를 기다리는 동안 일부 실행 기회를 병렬화할 기회를 줄입니다. 변환의 특성으로 인해 작업 속도가 더욱 느려집니다. Aggregate는 완전히 차단되는 구성 요소이므로모두변환이 다운스트림 변환에 단일 행을 릴리스하기 전에 데이터가 도착하여 처리되어야 합니다.

가능하다면 피벗 및/또는 집계를 서버로 푸시할 수 있습니까? 그러면 데이터 흐름에 소요되는 시간과 소비되는 리소스가 줄어듭니다.

엔진이 선택할 수 있는 병렬 작업의 양을 늘려볼 수 있습니다.제이미의 기사,SQL CAT의 기사

데이터 흐름에서 시간이 어디에 소요되는지 알고 싶다면 실행을 위해 OnPipelineRowsSent를 기록하세요. 그러면 이것을 사용할 수 있습니다질문그것을 찢어 버리기 위해 (sysdtslog90을 sysssislog로 대체한 후)

네트워크 전송

그래프에 따르면 두 상자 모두 CPU 또는 메모리에 세금이 부과되는 것으로 나타나지 않습니다. 원본 서버와 대상 서버가 단일 상자에 있지만 SSIS 패키지는 다른 상자에서 호스팅 및 처리된다고 표시하신 것 같습니다. 해당 데이터를 유선으로 전송하고 다시 전송하는 데는 적지 않은 비용이 소요됩니다. 원본 서버에서 데이터를 처리할 수 있나요? 해당 상자에 더 많은 리소스를 할당해야 하며, 엄청나게 큰 VM이므로 문제가 되지 않습니다.

이것이 옵션이 아닌 경우 설정을 시도해 보십시오.패킷 크기연결 관리자의 속성을 32767로 설정하고 점보 프레임이 귀하에게 적합한지 네트워크 운영 담당자에게 문의하세요. 두 가지 팁 모두 네트워크 조정 섹션에 있습니다.

디스크 카운터는 별로지만 대기 유형이 디스크와 관련이 있는지 확인할 수 있어야 합니다.

관련 정보