엔터프라이즈의 SSIS 패키지 데이터 흐름 튜닝(SQL Server 비디오)

SQL Server 2008

적용 대상: Microsoft SQL Server Integration Services

작성자: 데이비드 누어, Microsoft Corporation

길이: 00:15:50

크기: 68.1MB

형식: WMV 파일

이 비디오 보기

관련 도움말 항목:

CAST 및 CONVERT(Transact-SQL)

방법: 조회 변환에 대한 캐시 만들기 및 배포

방법: 캐시 연결 관리자 변환을 사용하여 전체 캐시 모드에서 조회 변환 구현

데이터 흐름의 성능 향상

테이블 힌트(Transact-SQL)

TRUNCATE TABLE(Transact-SQL)

동기 및 비동기 변환 이해

관련 문서 및 블로그 게시물:

Windows에서 트래픽이 많은 네트워크 확장

SQL Server Integration Services에 대한 상위 10개 최선의 방법

데이터 로드 성능 지침

추가 비디오:

엔터프라이즈의 SSIS 패키지 성능 측정 및 이해(SQL Server 비디오)

SSIS 데이터 흐름 버퍼 이해(SQL Server 비디오)

병렬 처리를 위한 SSIS 패키지 디자인(SQL Server 비디오)

이 비디오에서는 Integration Services 패키지에서 데이터 흐름의 성능을 향상시키는 방법을 보여 줍니다. 다음과 같은 데이터 흐름 단계를 튜닝하는 방법을 배웁니다.

  • 추출
  • 변환
  • 로드

데이터 흐름을 디자인, 개발 및 실행할 때 이러한 성능 튜닝 팁을 적용할 수 있습니다.

SSIS: 성능 디자인 및 튜닝 SQL Server 비디오 시리즈 자료를 작성해 준 토마스 케저에게 감사를 표합니다. 이 비디오는 이 시리즈의 두 번째 편입니다.

또한 지침과 소중한 의견을 제공해 준 칼라 사보타와 더글라스 라우덴쉬라거에게 감사를 전합니다.

비디오 타임스탬프 오디오

00:00

안녕하세요. 데이비드 누어입니다. 저는 Microsoft SQL Server Integration Services의 수석 개발 팀장입니다. 엔터프라이즈의 SSIS 패키지 데이터 흐름 튜닝에 대한 비디오를 시청해 주셔서 감사합니다.

이 비디오는 전체 네 편으로 구성된 SSIS: 성능 디자인 및 튜닝의 두 번째 편입니다. 이 시리즈 1편에서는 데니가 SSIS 패키지의 성능을 측정 및 이해하는 방법을 설명했는데요. 이번에는 1편의 내용을 토대로 SSIS 패키지 데이터 흐름의 성능을 향상시키는 방법을 알아보겠습니다. 그럼 먼저, 모든 데이터 흐름에 있어서 공통되는 테마를 알아보고, 성능을 향상시키려고 할 때 먼저 작업해야 할 데이터 흐름 부분을 선택하는 방법에 대해 살펴보겠습니다. 성능 문제가 어디에 있는지 파악한 후에는 실행 속도와 효율성을 높이기 위해 데이터 흐름에서 다양한 조치를 취할 수 있는데요. 여기서는 데이터 흐름을 디자인, 개발, 실행할 때 적용할 수 있는 일련의 팁을 제시해 드리겠습니다.

자, 시작해 봅시다.

대부분의 패키지 데이터 흐름은 중요한 데이터를 추출, 변환, 로드하는 데 중점을 둡니다. 이를 ETL이라고도 하죠. 데이터 흐름을 튜닝한다는 것은 이러한 데이터 흐름의 각 단계, 즉 추출, 변환, 로드를 튜닝한다는 것을 의미합니다.

추출의 경우, 최상의 성능을 내고 원본 시스템에 미치는 영향을 최소화하기 위해 SSIS 원본 구성 요소와 실행되는 쿼리를 튜닝하는 방법에 대해 알아볼 것입니다.

데이터 흐름에서 변환을 튜닝한다는 것은 수행해야 할 작업을 파악하고 불필요한 단계를 없애 이 작업에 대한 최적의 방법을 선택하며, 때로는 변환 작업을 위해 원래의 원본 쿼리를 변경하는 것을 의미합니다.

마지막으로, 데이터 흐름에서 로드를 튜닝한다는 것은 SSIS 대상 구성 요소, 네트워크, 잠금, 그리고 데이터를 로드할 때 데이터 흐름의 진행률을 저하할 수 있는 기타 요인을 튜닝하는 것을 의미합니다.

이 비디오에서는 데이터 흐름의 이러한 세 단계 모두에서 튜닝하는 데 사용할 수 있는 실질적인 팁을 제공해 드립니다.

이러한 팁을 살펴보기 전에, 성능 문제를 다룸에 있어 만능해결책은 없다는 점을 기억하시기 바랍니다. 여기서 제시하는 팁 하나로 데이터 흐름에 나타나는 모든 성능 문제를 해결할 수는 없습니다. 여기서 제시하는 팁은 대부분의 경우에 효과가 있지만 문제에 대해 여러 가지 팁을 모두 적용해 보는 것은 바람직하지 않습니다. 가장 좋은 방법은 데이터 흐름의 의도를 항상 파악하고, 그러한 목표를 충족할 수 있도록 디자인하며, 현재 성능을 측정하는 한편, 데이터 흐름을 반복적으로 변경하여 변경을 통해 성능이 향상되는지 여부를 측정하는 것입니다.

또한 성능 문제를 다룰 때 특정 기술이나 구성 요소에 집착하기 쉽습니다. 그러한 기술이나 코드에 익숙하기 때문인 경우가 많은데요. 그러나 이유가 무엇이든간에 여러 관계형 데이터베이스, 네트워크, 운영 체제, 파일 시스템 및 기타 부분을 포괄하는 전체 에코시스템 맥락에서 데이터 흐름을 살펴보는 것이 중요합니다. 이러한 전체 에코시스템에 대해 더 많이 알수록 특정 부분의 실제 성능 문제를 보다 완전하게 파악할 수 있으므로, 더욱 효과적이고 위험이 적은 방식으로 변경을 수행할 수 있을 것입니다.

03:38

데이터 흐름의 추출 단계부터 살펴보도록 하겠습니다. 대량의 인터페이스를 원본으로 사용하는 SQL Server 또는 기타 데이터베이스를 사용하는 경우 패킷 크기를 늘려가며 시험해봐야 합니다. 일반적으로 SQL Server 기본값인 4096을 사용하면 제대로 작동하지만 추출을 통해 대량의 데이터를 이동할 것이므로 이 값을 올리는 것이 효과가 있을 수 있습니다. 이 방법을 적용하려면 네트워크 관리자에게 요청하여 네트워크에서 "점보 프레임"을 사용하도록 설정해야 합니다. 또한 패키지에서도 이 설정의 효과를 테스트해 보아야 합니다. OLE DB 원본과 같은 대량 작업과 OLE DB 명령과 같은 단일 행 작업에 동일한 연결 관리자를 사용하고 있다면, OLE DB 명령 작업에 두 번째 연결 관리자를 만들고 보다 낮은 패킷 크기를 사용하는 것이 좋습니다.

위에서 설명한 것처럼 Windows 2008에서 ETL을 실행하고 여러 NIC를 사용하는 다중 코어 컴퓨터를 사용하는 경우, 개별 코어에 대해 네트워크 카드의 선호도를 설정하면 네트워크 성능이 훨씬 좋아질 수 있습니다. 자세한 내용은 MSDN 사이트의 블로그 게시물인 Windows에서 트래픽이 많은 네트워크 확장을 참조하십시오.

추출 튜닝에서 가장 쉬운 것 중 하나는 사용하는 쿼리를 튜닝하는 것입니다. 추출할 테이블을 선택하고 모든 열이 검색되도록 할 수 있지만 필요한 열만 선택할 경우 보다 나은 결과를 얻을 수 있습니다. 필요한 열만 선택하면 네트워크 트래픽과 메모리가 적게 사용될 뿐만 아니라 데이터베이스 서버에서 훨씬 적은 양의 입/출력을 사용하게 됩니다.

여기에 나와 있는 대로 SQL Server에서는 선택 중에 공유 잠금을 실행하지 않음을 힌트를 사용하여 나타낼 수 있습니다. 그러면 쿼리가 잠재적으로 커밋되지 않은 데이터를 읽게 됩니다. 단, 이 작업은 반드시 최상의 성능이 필요한 경우에만, 그리고 커밋되지 않은 데이터를 읽어도 ETL 작업에 절대 문제가 없는 경우에만 수행해야 합니다.

조회는 추출 또는 변환으로 생각할 수 있습니다. 그러나 어떤 쪽으로 생각하든 앞의 슬라이드에서 보여준 것과 같은 방법을 사용해야 합니다. 여기서는 네트워크 최적화뿐 아니라 메모리 내 캐시 최적화를 위해 필요한 열만 선택하는 것이 훨씬 더 중요합니다.

SQL Server 2008를 사용하는 경우 최적의 성능을 위해 새로운 공유 조회 캐시를 추가할 수 있습니다. 조회 캐시를 공유하면 참조 데이터를 한 번 인출한 다음 파일에 캐시를 저장하여 해당 패키지 내는 물론 다양한 패키지에 걸쳐 여러 조회 작업에 재사용할 수 있습니다. 여러 조회 구성 요소가 동일한 테이블을 참조하는 경우 이 새로운 옵션을 사용해 보면 패키지 속도가 크게 향상되는 것을 경험할 수 있습니다. 이 옵션을 사용하는 방법은 매우 간단합니다. 패키지에서 캐시 연결 관리자를 만들고 새로운 캐시 변환을 사용하여 캐시를 채운 다음 참조 데이터에 대해 이 캐시 연결을 참조하도록 조회를 변경하면 됩니다.

06:29

두 번째 ETL 단계인 변환을 살펴보겠습니다. 변환은 데이터 흐름에 이미 로드되어 있는 데이터를 가지고 로드 전에 올바른 셰이프로 만드는 것입니다. SSIS에서는 모든 변환이 디자인으로 끌어놓을 수 있는 개체로 나타나지만 모든 변환이 동일하게 이루어지는 것은 아닙니다. SSIS에 있는 세 가지 종류의 변환을 살펴보겠습니다.

  • 동기 또는 행 기반 변환은 현재 위치 데이터의 버퍼에서 작동합니다. 변환이 진행될 때 데이터 버퍼나 행의 복사본을 만들지 않고 버퍼의 데이터를 직접 변환하기만 합니다. 따라서 동기 변환은 속도가 상당히 빠릅니다. 동기 변환의 예로는 데이터 변환, 파생 열 및 조회가 있습니다.
  • 부분 차단 비동기 변환은 이와는 다릅니다. 이 변환 작업을 수행하려면 데이터가 들어올 때 데이터를 보유하고 있어야 합니다. 그러기 위해서는 들어오는 데이터를 내부 버퍼로 복사해야 하므로 이 작업에는 메모리가 사용되며 때로는 많은 메모리가 사용되기도 합니다. 데이터가 계속해서 들어오면 변환 작업에서 자동으로 출력 데이터를 쓰기 시작할 수 있습니다. 이 과정을 통해 보유하고 있던 내부 메모리를 비우게 됩니다. 데이터 흐름이 완료되면 사용한 모든 메모리가 비워집니다. 그러나 데이터 흐름이 완료될 때까지는 상당한 메모리를 사용하게 됩니다.
    비동기 변환의 예로는 병합, 병합 조인, Union All이 있습니다. 이러한 비동기 변환이 데이터 흐름에 있는 경우 최적화할 방법을 찾아보시기 바랍니다. 중복된 복사본이 있습니까? 원본 시스템에 “밀어넣을” 수 있는 병합 조인이나 Union이 있습니까? 원본 쿼리에서 조인을 수행하는 대신, OLE DB 원본을 사용하여 두 테이블의 모든 데이터를 인출한 다음, 패키지에서 병합 조인을 사용하여 조인을 수행하는 패키지를 본 적이 있습니다. 이 경우 원본에서 SQL 조인을 작성했다면 데이터베이스가 쿼리를 최적화할 수 있어 속도가 훨씬 빨라졌을 것입니다. 비동기 변환 수를 통합하고 줄일 수 있는지 알아보시기 바랍니다.
    SQL Server 2008에서는 복잡한 데이터 흐름의 성능을 높이고 사용 가능한 CPU를 보다 효율적으로 사용할 수 있도록 데이터 흐름 태스크 스케줄러를 상당히 개선했습니다. SQL Server 2005에서 SSIS를 사용한 경우 실행 트리를 인위적으로 분할하고 병렬 처리하기 위해 데이터 흐름에 Union All 변환을 적용했을 수 있습니다. 이제는 그렇게 할 필요가 없습니다. SQL Server 2008의 향상된 기능 덕분에 이러한 작업은 더 이상 필요하지도, 권장되지도 않습니다. 인위적인 Union All 구성 요소를 제거했으므로 향상된 성능을 경험하실 수 있습니다.
  • 세 번째 변환 방법인 차단 비동기 변환은 부분 차단 비동기 변환의 극단적인 경우로서, 들어오는 데이터를 모두 보유하고 있어야만 출력 데이터를 쓸 수 있습니다. 대용량 일괄 처리 데이터의 데이터 흐름에서 이러한 변환을 사용할 경우 데이터 흐름 속도가 상당히 느려질 수 있습니다. 대량 데이터 흐름에 이 변환을 사용할 경우 중복해서 사용하지 않도록 주의하시기 바랍니다. 동일한 흐름의 동일한 데이터에 두 개의 집계 또는 정렬이 있는 경우 하나가 되도록 패키지를 수정해야 합니다.

지금까지 기본적인 내용을 살펴보았는데요. 이제 몇 가지 구체적인 팁을 알려드리겠습니다.

  • 데이터 흐름에서 열의 데이터 형식을 변환해야 하는 경우가 흔히 있습니다. 이러한 작업은 한 번만 수행하도록 하고, 형식을 최대한 적게 사용하여 데이터 버퍼 크기를 최소화하는 것이 좋습니다. 데이터 캐스팅에는 CPU 시간도 사용됩니다. 따라서 전체 데이터 흐름에서 열에 한 가지 형식을 사용할 수 있는 경우, SQL의 CAST 또는 CONVERT 함수 또는 이에 대응하는 SQL 언어를 사용하여 데이터 원본에서 열을 캐스팅하는 것이 좋습니다.
  • 앞의 슬라이드에서 언급했지만 변환이 이동할 위치를 현명하게 판단하여 변환을 최적화할 수 있습니다. 원본에서 SQL을 효율적으로 사용하는 것이 중요합니다. 예를 들어 원본 데이터에서 정렬 작업을 수행하는 경우 원본의 SQL에 ORDER BY 절을 밀어넣어 보십시오. 데이터 흐름보다 훨씬 더 효율적으로 정렬된 데이터를 반환할 수 있을 것입니다. 여러 원본에서 함께 병합된 데이터를 정렬해야 하는 경우를 위해 이 정렬 구성 요소를 저장해 두십시오. 마찬가지로 원본에서 GROUP BY 및 SQL 집계 함수를 사용하여 일부 집계 작업 속도를 높일 수 있습니다.
  • SQL Server 2008를 사용하고 SCD(느린 변경 차원) 구성 요소를 사용하는 경우 SQL Server의 새로운 MERGE 기능을 살펴보십시오. MERGE 기능을 통해 보다 적은 네트워크 왕복으로 SCD와 동일한 양의 작업을 수행할 수 있습니다.
  • SQL의 INSERT INTO 기능 유틸리티 또한 중요합니다. 데이터 흐름이 단순하고 원본과 대상이 동일한 데이터베이스 인스턴스에 있는 경우, 단일 SQL 문을 통해 동일한 데이터 이동을 훨씬 더 빨리 수행할 수 있으므로, 데이터 이동이 데이터베이스 전체에서 이루어집니다. INSERT INTO는 데이터가 서버를 벗어날 필요가 없으므로 데이터 흐름보다 훨씬 더 빠르게 실행될 수 있습니다.
  • 마지막으로, 증분 로드를 수행하는 경우 다시 로드하는 방법 외의 다른 방법을 생각해 보는 것이 좋습니다. 일부 시스템에서는 데이터 다시 로드를 방지하기 위해 델타 검색에 많은 시간을 들이지만, 이를 위해 사용되는 입출력과 CPU 때문에 결과적으로 작업 속도가 느려지는 것을 보았습니다.

11:59

이제 로드 단계를 살펴보겠습니다.

SQL Server에 로드할 때 두 가지 옵션을 사용할 수 있습니다.

  • 첫 번째 옵션은 SQL Server 대상 구성 요소입니다. 이 구성 요소는 데이터 흐름과 데이터베이스 엔진 사이에 공유 메모리를 사용하여 데이터를 빠르게 로드하지만 데이터 흐름이 SQL Server 자체와 같이 항상 동일한 컴퓨터에서 실행되는 경우에만 작동합니다. 또한 SQL Server 대상에는 오류 처리에 대해 일부 문서화된 제한 사항이 있습니다.
  • SQL Server에 빠르게 로드하기 위한 다른 옵션은 OLE DB 대상으로 SQL 대상과 거의 같은 속도로 실행됩니다.

두 옵션 모두에서 커밋 크기를 0으로 설정하면 로드 속도가 가장 빨라집니다.

대규모의 로드 작업에서는 일반적으로 대상 시스템에서도 인덱스가 삭제됩니다. 그러나 인덱스의 삭제 시기를 알려주는 지침을 사용하는 것이 좋습니다. 많이 사용되는 지침은, 로드로 인해 예상되는 비율 증가와 테이블에 사용한 인덱스 종류에 따라 인덱스를 삭제하는 방법입니다.

  • 테이블에 단일 클러스터형 인덱스가 있는 경우에는 인덱스를 삭제하지 마십시오. 테이블 데이터는 이 키를 기준으로 정렬되며, 삭제, 삽입, 재작성에 드는 시간이 현재 위치의 클러스터형 인덱스를 통해 로드하는 데 드는 시간보다 적게 걸리지 않을 것입니다.
  • 테이블에 단일 비클러스터형 인덱스가 있는 경우에는 로드로 인해 데이터 크기가 100% 가량 증가할 경우에만 인덱스를 삭제하는 것이 좋습니다. 물론 정확한 수치에 의한 것이 아니라 경험에 의한 것이지만, 테이블 크기를 두 배로 늘리려는 경우가 아니라면 인덱스를 삭제하고 재작성하는 것은 그다지 좋은 방법이 아닙니다.
  • 테이블에 인덱스가 여러 개 있는 경우에는 경험상의 수치를 알려드리기가 좀 어려운데요. 저는 보통 10% 증가 범위로 생각합니다. 예를 들어 현재 볼륨의 10% 미만을 로드하는 경우 인덱스를 그대로 두어도 됩니다. 그러나 직접 시험해 보고 측정해 보는 것이 가장 좋은 방법일 것입니다.

다른 작업이 진행 중인 테이블에 로드하는 경우에는 전략이 필요합니다. 대량 로드의 경우 대상 테이블 전체에 로드하지 않을 경우 데이터의 대부분이 잠길 수 있습니다. 따라서 데이터가 잠겨도 괜찮은지 확인하거나, 아니면 분할을 사용해야 합니다. 운영 데이터베이스에 로드해야 할 경우, 파티션을 설정하여 로드하면 현재 작동 중인 데이터를 활성 상태로 유지할 수 있습니다. 로드 속도가 느려질 경우에는 테이블이나 파티션에 다른 작업이 수행되는지 살펴보고 다른 작업으로 인해 방해 받지 않도록 해야 합니다.

대량 로드 및 파티션 성능을 높이는 것에 대한 훌륭한 지침은 MSDN의 SQLCAT 문서 데이터 로드 성능 지침(페이지는 영문일 수 있음)을 참조하십시오.

또한 다시 로드를 수행할 때 삭제가 트랜잭션 기반으로 이루어지지 않도록 DELETE가 아닌 TRUNCATE를 사용해야 합니다.

네트워크 연결을 사용하는 대상의 경우 앞에서 설명한 네트워크 문제가 발생할 수 있습니다. 로드할 때 최적의 성능을 얻으려면 네트워크에서 패킷 크기를 늘리고 "점보 프레임"을 사용하는 방법을 고려해 보시기 바랍니다.

15:02

이것으로 성능 시리즈의 2편을 마치겠습니다. 이 비디오 시리즈의 기본 내용을 제공해 준 토마스, 그리고 시리즈 작성에 도움을 준 칼라, 더글라스에게 감사를 표합니다. 이러한 항목에 대한 자세한 내용은 SQL Server Integration Services에 대한 상위 10개 최선의 방법(페이지는 영문일 수 있음)을 참조하십시오.

비디오를 시청해 주셔서 감사합니다. 이 비디오 시리즈 SSIS: 성능 디자인 및 튜닝의 나머지 세 편도 들어 보십시오. 이 비디오가 도움이 되었기를 바라며, 비디오에 대한 여러분의 의견을 기다리겠습니다. 비디오 홈 페이지의 오른쪽 위에 있는 링크를 통해 평가 및 의견을 보내실 수 있습니다.

커뮤니티 추가 항목

추가
표시: