MS SQL 병목 탐지

시스템 인프라 담당자에게는 미안한 말이지만, 시스템 파라메터 열심히 튜닝하는 것보다, 쿼리 한줄, DB 옵션 하나 수정하는게 훨씬 성능이 잘 나온다는 이야기 들이 있다. 맞는 말이다. (나도 시스템 엔지니어지만, 맞는 건 맞는 거니까...)

 

 그런데 솔직히 SQL 을 제대로 튜닝하는 DBA 를 만나기가 쉽지 않다. 튜닝은 고사하고 SQL 에 뭐거 문제인지 진단하는 방법론을 가지는 엔지니를 만나는 것도 꽤 어려운 일이다.

 

 그렇다면, MS SQL 을 다루는 사람들은 왜 이렇게 튜닝을 힘들어 할까? 물론 오라클 만큼이나 시장이 성숙하지 않은 것은 사실이지만, 적지 않은 기간 동안 운영되어 온 SQL 은 왜 이렇게 튜닝 파워가 낮을까?

 

 그 이유는 아마도 툴의 부재라고 보인다. (이건 아마도 가장 큰 책임은 MS 에 있지 않나 생각된다.)

 

 오라클에는 무수히 많은 툴들과 개발 환경을 만들어 내는 기업들이 존재한다. 개발 환경만 해도 Dell software 의 TOAD, ORANGE... 모니터링 튜닝 툴은 국산 툴만 해도 MaxGage, Shelpa... 해외 툴까지 하면, 이루 셀 수가 없다.

 

 하지만, MS SQL 의 개발 환경을 판매하는 기업은 없다. 그럴 수 밖에, 설치만 하게 되면 무제한 라이센스의 SQL Management studio 가 무재한으로 제공되고 그 품질 조차도 기가 막히게 좋은 마당에 엔진의 소스 코드를 가진 제조사에 도전할 솔루션 업체가 있다는게 말도 안되는 소리다.

 

하지만... 프로파일러가 치명적인 문제가 있는데,

 

 정원혁 씨의 이야기를 들어 보면 가장 강력하고 뛰어난 툴로서 프로파일러를 이야기 한다. 맞는 말이다. 프로파일러만 제대로 쓴다면, 그 어떤 문제에 대한 정보도 정확하게 찾아 낼 수 있다. 하지만... 프로파일러가 치명적인 문제가 있는데,

 

1. 상시적 수집을 할 경우 서버의 성능 저하, 저장 공간의 고갈 등의 이슈가 발생하므로 상시 수집이 불가능하다.

2. 수집된 Data 를 분석 가능하게 가공하는데 많은 시간과 노력이 투입된다.

3. 성능이 이슈가 되는 서버에 프로파일러를 적용하기 위해서는 매우 조심성 높은 모니터링이 요구된다.

 

프로파일러를 그냥 사용해도 문제가 없다구?

 

따라서 튜너가 사용하거나 재발 예측이 가능한 시점의 병목을 추적할 때 고수준의 DBA 가 사용하기에는 손색이 없지만, 재발 예측 가능, 고수준의 DBA 라는 두가지 조건을 만족하는 Tool 을 개발하기 어려운 것도 사실이다.

프로파일러를 그냥 사용해도 문제가 없다구?

• Run Profiler remotely instead of directly on server
• Avoid including events that occur frequently (e.g. Lock:Acquired) unless absolutely needed
• Include only event classes needed
• Specify limiting filters to reduce the number of events
• Avoid redundant data (e.g. SQL:BatchStarting and SQL:BatchCompleted)
• Avoid running large traces with Profiler; consider a server-side SQL Trace instead
• Limit server-side trace file size and manage space usage

이거 MS SQL books online 에 있는 말이다. 내 의견이 아니라...  

 

 '어제밤에 2시경에 갑자시 DB 가 느렸는데, 지금은 괞찮아.. 이유가 뭐야?'

 

 특히 아무리 뛰어난 DBA 라도

 '어제밤에 2시경에 갑자시 DB 가 느렸는데, 지금은 괞찮아.. 이유가 뭐야?'

 라는 질문에는 대답할 재간이 없다.

 

 Dell software Performance Analysis

 

 MS SQL 의 프로파일러가 튜닝의  처음이자 마지막이라고 생각하는 이여, Dell software 의 Performance Analysis 를 한번 보자. 국내 제품이 있긴 한데... 몇몇 제품은 Tracking Data 수집에 Profiler 를 통하여 수집하고 있기에 그 한계를 동일하게 지닌다. 그리고 협소한 개발 인력의 한계를 그대로 가지기도 하고...

다 떠나서 수집 방법이 기존의 프로파일러와 달라서 상시 수집이 가능하고, Data 를 따로 저장해서 충분히 사용이 가능한 형태로 가공된 Data 를 뿌려 준다.

(모든 그림은 누르면 커집니다.)

[통합 성능 관리 화면]

- 첫화면을 열면 통합 성능 관리 화면이 나온다. 에게? 이 정도는 성능 모니터도 하는데...

물론 그렇다. 그런 느낌이 든다면...Advisory Tab 을 클릭해 보자.

 

[Advisory Tab]

 

오잉? 특정한 Table 에 읽기가 너무 발생하니, 인덱스를 좀 잡으시오? 오호... 누구나 알 수 있는 작업이지만, 이렇게 추천만 해 줘도 작업이 훨씬 쉬워지고, 노가다가 없어진다.. 야호!!!

 뿐만 아니라 경합, 비정상적으로 증가한 부하 분석도 같이 진행해 준다.

 

[Real time]

 말 그대로 현재 시점이다. 어떤 리소스가 가장 많이 사용되고 있는지, 그로 인한 병목은 어디서 발생하는지.. 등등등... 하지만, 여기까지는 별로 볼 게 없다. 그리고 노련한 운영자라면 벌써 보고 있을지도 모른다.

[History]

-Advisory

 벌써 본 advisory  다. 하지만 이 advisory 는 현재 시점을 이야기 하는 것이 아니라, 이전 시점의 주요 정보를 알려 준다. 기본은 지금 부터 특정 시간 이전을 보여 주지만 -- 부터 -- 까지를 자유롭게 탐색해 가며 성능적인 이슈와 해당 시점의 advisory 를 분석할 수 있다.

-Overview

 전체 overview 는 real time 부분을 해당 시점 정보로 분석할 수 있는 뷰를 제공한다.

 CPU, I/O, Network, Lock 등의 다양한 리소스에 대하여 점유와 병목 분석 화면을 시점 기간 별로 분석할 수 있다. 당연히 보다 시피 리소스 별 MS SQL 의 주요 점유 대상을 정의하고 있다.

 

-SQL Statement

 개인적으로 가장 활용도가 높아 보이는 화면...

 특정 시점의 SQL 문의 리소스 점유 비율을 자세히 보여 준다. 해당 시점의 SQL 중 리소스를 가장 많이 점유한 SQL 이 어떤 SQL 이며, 전체의 얼마에 점유율을 보였는지... 그리고 몇 회나 실행되고, 해당 시점의 리소스 점유 시간이 어떠했는지를 한눈에 볼 수 있다.

 뿐만 아니라, 전체 리소스를 나타내는 workload 라는 항목에서 아래 처럼 CPU, Lock, Latch 등 다양한 리소스로 Break down 하여 분석할 수 있다.

 아래를 보면 현재 서버는 CPU 병목으로 인한 속도 저하가 발생할 확률이 높다는 것을 알 수 있다. 이 경우 CPU 를 많이 소요하는 Sort, Join 등을 수정해 주여야 할 수 있음을 알 수 있다.

-병목 리소스 분석

 

-개별 SQL 분석

 전체 중에 특정 SQL 이 차지하는 리소스를 분석하기 위한 기능이다. 위의 경우는 가장 많은 리소스를 점유하는 SQL 이외에는 별로 신경 쓸 대상이 아니라는 것을 알 수 있다.

 가장 중요한 최상위 SQL 를 튜닝하기 위해 노력하자, 경험 상으로 악성 쿼리를 2~3 개만 튜닝하여도 전반적인 성능에 대하여 2배 이상 체감적인 향상을 경험할 수 있다.

 

 늦다는데, 뭐가 어떻게 얼마나 늦어 졌는지, 뭐가 더 모자라서 또는 평소보다 뭘 더 써서 그런지...

 

-Compare

 운영자에게 내려 준 Dell software  의 선물... Compare 기능...

 늦다는데, 뭐가 어떻게 얼마나 늦어 졌는지, 뭐가 더 모자라서 또는 평소보다 뭘 더 써서 그런지... 도무지 모를 때, 그리고 개선 요청을 한 이후에 SQL 이 얼마나 달라졌는지, 얼마나 빨라 졌는지를 알 수 없을 때...

 비교 분석을 리포트 작성을 지원한다.

 리소스별, 쿼리별 Stored procedure  별로 모두 특정 기간 대비 분석이 가능하다.

 기가 막히지 않는가? 더 중요한 건, 이 작업을 아무런 추가적인 설정없이 바로 수집이 가능하다는 것...

 업무의 30% 이상 부하가 줄 것이다.

 

[Report]

 

 당연히 리포트는 지원된다. 리소스 사용량 부터 특정 기간 가장 많은 리소스를 점유하는 사용자, 세션, SQL 문까지, 그리고 앞에 말했듯이 기존보다 더, 덜 쓰는 리소스 그리고, 사용자 분석까지 완벽하게 지원하며, 특히 리소스의 경우 평소에 통상적으로 점유하는 리소스 범위를 스스로 학습해서 통계적으로 정하고, 그 범위를 벗어나는 것을 직관적으로 알 수 있는 그래프를 제공한다. 

그래프는... 위 화면 처럼 다양하게 제공한다. 그리고, 주기적인 리포트 발행 스케쥴링 기능과 함께 PDF, 엑셀 형식의 파일을 제공하여 쉽게 편집을 하여 보고, 분석 자료로 활용할 수 있다.

 

  이거 어디서 구하냐구?

 http://support.quest.com  에서 Performance Analysis 로 찾아 보자.

 당연히 유료 제품이나 그다지 고가의 제품이 아니다. (국산보다 비교도 안되게 싸다.) 게다가 지금 한참 프로모션 중이니, 한번 신청해 보자. 영업 담당자에게 요청하면 시리얼 키 한달 정도는 어렵지 않게 받을 수 있다.