MSSQL 예상 실행 계획으로 프로시져(또는 쿼리) 개선하기.

MS-SQL에서 제공하는 예상 실행 계획을 통한 성능 개선을 모두 설명하자면 상당히 양이 많고, 아직 자세하게 다 알지도 못하는 지라, 단순히 MS-SQL DB를 사용하는 개발자라면 간단하게 예상 실행 계획을 보고, 인덱싱을 걸어 성능을 개선하는 방법 정도를 간단한 시나리오로 설명하고자 합니다.

 

나중에 기회가 되면 실행 계획의 케이스별로 개선하는 방법 등에 대해서도  좀 더 공부해서 게시물을 올리도록 하겠습니다.

 

 

MS-SQL을 사용하여 개발하는 개발자라면 어느정도 대부분 쿼리를 직접 짜는데, 항상 만족할 만한 성능이 나오지는 않습니다.

(정말 한번에 가져오는 데이터가 수백만 건이 넘거나, 상당히 복잡한 분석 데이터를 산출하는 프로시져를 개발하는 경우가 아니라면 대체적으로 수초 이내에 결과를 가져오는 쿼리를 만듭니다.)

 

만약 고객에게 제공하는 서비스에서 단순 통계 데이터를 가져오는데, 수십초, 수분이 걸린다면 아마 고객은 해당 서비스에 불만을 가지고, 다른 서비스로 옮겨갈지도 모릅니다.

 

데이터량이 수만 수십만으로 넘어가면서 성능이 급격하게 떨어지는 경우도 있고, 데이터가 별로 없는데도 검색에 상당한 시간이 걸리는 경우도 있습니다. 이러한 경우 대부분은 쿼리가 효율적으로 구현되어 있지 않거나 데이터가 효율적으로 사용되도록 저장되어 있지 않은 경우입니다.

 

 

아래는 프로시져의 성능을 개선하는 가상 시나리오를 써봤습니다. (쿼리는 임의로 짠 코드입니다.)

본 게시물은 MS-SQL에서 제공하는 실행 계획을 실행하고, 보는 방법에 대해 간단히 설명하기 위해 작성한 것으로 상세 케이스별 성능 개선 등에 대한 정보는 포함하지 않고 있으며, 실행 계획에서 보여주는 모든 항목에 대해서도 설명하지 않고 있습니다.

 

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

1) 프로시져를 만듭니다.

 - 학원의 과목별 정원, 수강생 수, 수강생 비율을 가져오는 쿼리를 만들고 이름을 아래와 같이 지었습니다.

   [USP_DATA_GET_CLASS_CAPACITY_STATUS]

  프로시져 내에는 임시 테이블도 만들고, INSERT, SELECT 등의 동작도 포함하고 있네요.

 

 

2) 만든 프로시져를 실행합니다.

 - 실행했는데, 데이터가 몇 천건 밖에 안되는데 속도가 너무 늦네요~ 가상의 조건으로 프로시져를 돌려보니 139건의 데이터를 가져오는데, 조회하는데 시간이 4~5초나 걸립니다.

  

 

잘 짠거 같은데, 무엇이 문제일까요?

쿼리가 이상한 것일까요? 데이터가 문제인 것일까요?

 

 

3) 예상 실행 계획을 봅니다.

 * 프로시져를 실행할 때, 아래 처럼 입력 후, F5키를 누르거나, 툴바의 !실행(X) 버튼을 클릭합니다.

  EXEC USP_DATA_GET_CLASS_CAPACITY_STATUS

 * 하지만 쿼리의 실행 계획을 보려면 Ctrl + L 를 누릅니다. (또는 메뉴의 - 쿼리 - 예상 실행 계획 표시(P)를 선택해도 됩니다.

 

 그러면 즉시 해당 프로시져가 어떠한 계획을 가지고 쿼리가 실행될 것인지에 대한 표를 보여줍니다. (단순히 프로시져 내의 쿼리들의 구조를 분석해서 보여주는 예상 계획이므로, 즉시 표시됩니다. 참고로 실행 계획은 예상된 계획이고, 반드시 표에서 나온대로 100% 실행됨을 보장하지는 않습니다. 하지만, 대체적으로 예상 계획대로 실행됩니다.)

  

 

 

 

복잡하네요. 단순 프로시져 하나 만들었는데, 하는 작업이 많군요. 연산도 있고, 정렬도 있고, 테이블 스캔도 있고, 이것저것 항목들이 많습니다. (잘 안보이시면 이미지를 클릭해서 크게 보세요~)

 

한 화면에 모두 담으니, 잘 보이지 않아, 특정 항목을 확대해 보겠습니다. 가장 우측 상단에 위치한 실행 계획에 대한 부분입니다.

 

 

 

개별 작업들 하단에 보면 비용: 00% 라는 부분이 있는데, 이는 이 쿼리(프로시져)에 포함된 모든 작업들이 실행될 때, 그 작업 로드(부하)에 대한 비율입니다.

즉 모든 작업들의 전체 합 비용을 100%로 보았을 때, 개별 작업들이 차지하는 비율을 뜻합니다. (소수점은 빠져있기 때문에 실제 합을 계산해보면 100%가 안될 수도 있습니다.)

 

즉, 실제 쿼리를 실행했을 때, 실제 DB에 저장되어 있는 데이터에 대한 것은 고려되지 않고 있기 때문에 절대적인 비용(시간, CPU, 메모리 사용량 등)은 알지 못합니다. 상대적인 비용만 알 수 있습니다.

 

 

4) 상대적인 비용과 쿼리 개선 대상 선정

그렇다면 상대적인 비용만 안다고 해서 쿼리를 개선할 수 있을까요? 물론 있습니다. 데이터량의 규모에 상관없이 상대적인 비용이 높은 작업이 있다면 해당 부분의 작업에서 부하가 많이 걸릴 수 있음을 예측할 수 있습니다.

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

프로시져가 3개의 작업 A, B, C로 구성되어 있고, 개별 작업들이 차지하는 비용 비율의 총 합을 100%로 보았을 때. A : 20%, B : 10%, C : 70%로 구성되어 있다고 가정합니다.

실제 프로시져가 처리해야 하는 대상 테이블에 데이터가 1,000개가 있다고 이 프로시져가 이 데이터들을 모두 처리하는 데에 있어서 1.0 초의 시간이 걸린다고 가정하면, 1,000개의 데이터를 처리하는데에 A는 0.2초, B는 0.1초, C는 0.7초가 걸리게 됩니다. 데이터가 그대로 10배로 늘어나 10,000개의 데이터를 처리해야 한다면 A는 2초, B는 1초, C는 7초가 걸립니다.

 

이 말은 데이터량이 늘어나면 늘어날 수록 상대적인 비율이 큰 작업의 비용이 더 크게 증가함을 알 수 있습니다.

 

그렇다면 이 때, 가장 저렴한 비용으로 최고의 성능을 발휘할 수 있도록 성능을 개선하기 위해서는 어떻게 접근해야 할까요?

=> 상대적인 비용이 가장 큰 작업을 우선적으로 개선하는 것입니다. 그렇다면 위의 예에서는 C 작업을 개선할 수 있는지 검토하고, 접근하는 것이 가장 바람직합니다. 물론 B의 작업을 개선하여 성능을 높일 수도 있습니다. 하지만 B의 작업을 개선하여 B의 작업 비용을 절반으로 줄인다면 전체적인 성능에서 5% 정도의 효율을 높인 것이지만, C의 작업을 개선하여 C의 작업 비용을 20%만 개선해도 전체적인 성능에서 14%의 효율이 올라감을 알 수 있습니다.

(물론 시간적인 여유가 많아 A, B, C 작업 모두를 개선하여 성능을 높일 수 있지만, 시간적인 여유가 없거나 작업 공수가 맞지 않는다면 가장 효과를 볼 수 있는 작업을 우선적으로 하는게 맞는 것일 겁니다.)

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

 

이제 다시 원 시나리오로 돌아가보면 위의 스크린샷을 보면 다들 10% 미만인데, 혼자 떡하니 73%나 비율을 차지하고 있는 작업이 있습니다.

 

Clustered Index Scan (Clustered) 라는 작업인데, 이름으로 봐서는 뭔가 데이터를 스캔하는 작업인가 봅니다. (Indexing 이나 차후 Scan 관련해서는 별도의 포스팅을 하겠습니다. 여기서는 이런게 있다 정도로만 설명합니다.) 혼자 압도적인 비율을 차지하고 있네요.

Clustered Index Scan 참고 : http://technet.microsoft.com/ko-kr/library/ms175184(v=sql.105).aspx

 

 

5) 특정 작업의 상세 정보 보기

 한 번 73%나 차지하는 작업에 마우스 커서를 해당 작업 위에 올려 놓아 상세 정보를 보겠습니다.

 

 

'클러스터형 인덱스에서 전체 또는 특정 범위를 검색합니다.' 라고 나옵니다.

인덱스가 검색에 적용되면 속도가 빠를텐데, 인덱스가 걸려 있지 않아서 전체 검색을 합니다.

즉, 위의 내용대로 전체를 검색하게 된다면 수천건의 데이터라도 중첩 쿼리가 된다면 시간이 많이 소요될 것 같습니다. (Full Scan 만큼 시간이 걸릴지도 모르겠습니다. 실제 해당 테이블에 Indexing 작업을 하지 않았기 때문에 해당 쿼리는 전체 범위를 검색 할 것입니다. 그래서 실제 검색 속도가 많이 늦었죠)

 

3)번에 첨부된 전체 예상 실행 계획 표를 보면 Clusted Index Scan이 INSERT 라튼 작업의 하위 작업으로 포함되어 있음을 알 수 있습니다. 자신이 짠 쿼리라면 위의 실행 계획을 보면 어디인지 위치를 파악할 수 있습니다.

 

 

6) 프로시져 쿼리 수정하기

 

그럼 프로시져로 가서 위의 Scan 작업을 하는 코드를 찾습니다. 아래는 해당 게시물 작성을 위해 제가 임의로 짠 프로시져 내의 코드이므로 자세하게 보시지 않으셔도 됩니다.

 

분명히 위의 실행 계획에서 73%를 차지하고 있는 작업을 찾은 것 같습니다.!!

INSERT 구문 내에 Scan이 있을리가 없는데, 보니 SELECT가 있고, 게다가 내부 SELECT 쿼리가 있군요!! 여기입니다!

 

 INSERT INTO #temp_report
 SELECT 

    class_name

  , class_day

    ...

      (생략)

    ...

  , (SELECT COUNT(*) FROM TEST_SUB A

     WHERE A.cls_id = OA.cls_id AND
   (CONVERT(VARCHAR, A.data_term_begin, 112) BETWEEN @date_begin AND (LEFT(@date_begin, 6) + '99') OR CONVERT(VARCHAR, data_term_end, 112) BETWEEN @date_begin AND (LEFT(@date_begin, 6) + '99'))

     AND A.data_refunddate IS NULL AND A.data_change = '0')

  , OA.cls_capacity
  , '0.0'
 FROM TEST_CLASS OA
 WHERE OA.cls_group = @cls_group

 

그럼 위의 쿼리를 어떻게 개선할까요.

쿼리는 크게 문제가 없는 것 같으니, 개선이 어려울 것 같고, 그럼 테이블 컬럼에 인덱스를 걸어 개선해 볼 수는 있을까요?

 

인덱스는 보통 WHERE 절에서 비교 대상으로 사용되는 컬럼에 사용합니다. (인덱스를 걸면 왜 검색 성능이 개선되는지는 별도 포스팅하겠습니다. 안그래도 게시물이 지루하게 너무 길어지고 있네요.;;)

 

서브 쿼리에 WHERE A.cls_id = OA.cls_id 와 같은 조건 구문이 있는 것 같으니, 서브 쿼리 내에서 비교 대상이 되고 있는 TEST_CLASS 테이블의 cls_id 에 인덱스를 걸어보겠습니다.

 

참고로 어느 테이블(TEST_CLASS 또는 TEST_SUB)에 인덱스를 걸더라도 성능 개선 효과는 있습니다. 단, 인덱스가 어느 테이블에 걸려 있느냐에 따라 성능 개선의 방식이 달라질 수 있습니다.

 

 

7) 테이블 컬럼에 인덱싱 설정.

 TEST_CLASS 테이블의 cls_id 컬럼에 인덱스 키를 생성하고, 테이블을 저장합니다.

 

 

 

 

8) 프로시져 실행

다시 프로시져를 실행해 보겠습니다.

동일한 조건으로 다시 실행시켰는데, 1초에 처리가 완료되었습니다. 실제 조회된 결과도 동일합니다.

 

 

변경된 점이라면 예상 실행 계획을 보고, 특정 테이블의 컬럼에 인덱스를 걸어준 것이 전부입니다.

 

 

9) 예상 실행 계획 다시 보기

그럼 다시 예상 실행 계획을 실행시켜 볼까요? 개선한 부분만 보겠습니다.

 

 

 

 

 성능은 5배 이상 빨라졌는데, 실제 Clusted Index Scan이 전체 프로시져 작업에서 차지하는 비용은 5%밖에 감소하지 않았네요. 왜일까요?

 이는 앞서 설명드린대로 예상 실행 계획이 작업의 상대적인 비용을 나타내기 때문입니다. 해당 작업을 개선하여 성능이 대폭 향상된 이후의 작업 비용 대비해서도 70%나 차지 한다는 것은

 

* 비용이 큰 작업을 개선하면 성능이 대폭 향상된다는 것.

* 애초부터 다른 작업들은 그 성능 부하가 심하지 않다는 것. (즉, 다른 작업 들은 성능 개선을 하더라도 그 효과가 미비할 것으로 추측됨)

 

을 알 수 있습니다.

 

참고로,

성능 개선 이후에도 여전히 작업 비용이 높게 나타나는 작업은 추가 개선 여지가 있는 경우가 많습니다.

그리고 성능 개선 자체가 거의 불가능하거나 효과가 미비한 작업(INSERT, UPDATE와 같은 DML을 사용한 작업, 연산 작업, Full Scan 등)들도 존재하며, 예상 실행 계획을 통한 성능 개선은 대부분 SELECT와 연관된 작업에서 많이 이루어집니다.

 

 

그리고!!!

마지막 팁!

 

회사가 어느정도 규모가 있으면 대부분 전문 DBA가 있습니다.

간단히~~

DBA에게 쿼리 튜닝, 성능 개선을 요청하면됩니다. ㅎㅎ;;;;;