블로그 이미지

[DMV] 특정 프로시저의 실행계획이 바뀌었다?

2013.04.15 18:07 Posted by 건방진연이

DBA가 관리하고 있는 DBMS에 특정 저장프로시저가 재컴파일 되면서 뜻하지 않게 원하지 않는 플랜 즉, 잘못된 플랜이 만들어 지면서 서버의 리소스를 소비하는 현상을 경험한 적이 있는 DBA 분들이 있을꺼에요. (없다고요?? 그럼 경험해 보셔야 해요. ^^;;;)

아무튼 우리 회사에서도 특정 저장프로시저가 재컴파일 되면서 INDEX SEEK를 해야 함에도 INDEX SCAN으로 플랜이 만들어지고 이로 인해서 서버의 리소스가 사용률이 약간 상승하는 일이 비일비재 해요. 이를 해결하기 위하여 여러가지 방법이 있겠지만, 전 왠지 이런 방법은 없을까? 라는 생각을 해봤어요.


재컴파일 참고 URL : http://support.microsoft.com/kb/243586


특정 프로시저 즉, 관심대상이 되는 프로시저의 플랜을 저장해 두었다가 현재 캐쉬되어 실행되고 있는 플랜의 물리적 연산자와 저장해둔 플랜의 물리적 연산자가 틀려지게 되면 알 수 있는 방법이 없을까?

그래서 만들고 있지요.


- 특정 프로시저들의 플랜을 저장한다. (물론 테이블을 만들어서)

- 현재 캐쉬되고 실행되는 플랜의 물리적 연산자와 저장된 플랜의 물리적 연산자와 비교 한다.

- 동일하면 일치라는 메시지를 출력한다.

- 동일하지 않다면 현재 캐쉬되어 실행되고 있는 플랜의 물리적 연산자 중 SCAN 이라는 물리적 연산자가 있으면 몇 개가 있고 그 물리적 연산자로 출력되는 예상 행수가 몇 개인지 출력한다.



[위 스크린샷이 저장해 놓은 플랜과 현재 캐쉬되어 실행되고 있는 플랜과 동일할 경우 출력되는 메시지 입니다.]


[위 스크린샷이 저장해 놓은 플랜과 현재 캐쉬되어 실행되고 있는 플랜이 동일 하지 않다면 SCAN 정보를 출력하는 메시지 입니다.]


두번째 스크린샷의 연산자 해석 방법은 해당 플랜의 SCAN의 포함되어 있는 물리적 연산자는 Clustered Index Scan 과 Table Scan 이 있으며, Clustered Index Scan 은 해당 플랜에서 총 3번이 있으며 그 3번의 평균 예상행수는 26만건 이며, Table Scan은 해당 플랜에 총 1번 있으며 그 1번의 예상행수는 45건 이라는 의미입니다. 해당 로직을 사용해 보고 공유할 만한 스크립트가 만들어 지면 올리겠습니다. 근데 멋있지 않나요?? 저만 멋있는건가요 ㅜ.ㅜ;;