[MS-SQL] CPU 사용율과 메모리 사용율 관련

[MSSQL] CPU 사용율과 메모리 사용율 관련

인계받은 내용을 토대로 회사 소프트웨어 및 서버 등등에 대해 공부하며 모니터링하다가

Database 서버가 언젠가부터 CPU 혹은 메모리 과부화가 발생하기 시작했다..

원인 발생 여부를 파악하기 위해 여러 검색을 통해 알아봤으나

분석 후 알아낸점은... 동시다발적으로 여러 트랜잭션 및 클라이언트의 쿼리 처리 등등으로 인한

복합적인 문제로 낙인...ㅠㅠ.... 결국 프로그램에서 처리해주지 않으면 안될것 같아, 개발이슈로 띄우고

개발하기로 했으나.. 알아보던 과정이 너무 여기저기 널린 얘기들과 정확히 이렇다 하는 내용이 없었다..

정리해두기로..

 

 

 

캬.. 답이없다 답이 없어.

입사 2주만에 이런 화면을 보게될 줄이야. 어딜가든 일복하나는 인정하기로 했다...

Database 서버환경은 아래와 같다.

[서버환경]

Processor : Xeon CPU E5-2407 v2 2.40GHz

Memory : 32 GB

OS : Windows Server 2012 R2 64bit

Database : MS-SQL 2014 Enterprise

 모든 오류의 서론이 그렇듯.. 구글링으로 시작한다. (업무상의 편의도 있지만 모니터 3개가 빛을 발하는 순간)

메모리 관련된 내용은 추후 업데이트 하기로하고 당착한 CPU 사용율에 좀더 신경을 써보자.

1. 떠돌아 다니는 쿼리문 + 해결방법

 1) Sp_Who2 를 통한 문제 프로세스 검색 및 Kill

  - Management 실행 => sp_Who2

  - CPU 높은 놈을 찾자. Sp_Who2 내장 함수(?)의 경우 SQL 서비스단은 종료시키지 못한다.

   (Status 가 Background 라던지.. Background 라던지...)

  - 조건은 Status 가 RUNNABLE 상태, CPUTime 의 값이 유독 높은놈이 있다. 물론 해당 SPID를 가진 친구가 어떤 작업을 하는지

   확인정도는 당연히 해야겠다. =>  DBCC INPUTBUFFER([SPID])

  - 확인되었고 그놈을 죽여도 된다면.. 죽여버리자(?)! => KILL [SPID]

  - 정리 : sp_Who2 => DBCC INPUTBUFFER([SPID]) => KILL [SPID]

 2) 부하가 큰 쿼리문을 검색해서 프로그램 내 혹은 프로시저 등등에서 직접 수정하자!

  - 간단보기와 상세보기로 나뉜다. (아래가 상세보기)

SELECT TOP 20

 [Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 

         (CASE WHEN qs.statement_end_offset = -1 

            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 

          ELSE qs.statement_end_offset END - 

qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Total CPU used] DESC, [Average CPU used] DESC;

SELECT TOP 20 

 [Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_worker_time

,[Last CPU used] = last_worker_time

,[MAX CPU used] = max_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 

         (CASE WHEN qs.statement_end_offset = -1 

            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 

          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

,qs.creation_time

,qs.last_execution_time

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average CPU used] DESC; 

  3) 직접 모니터링해서 해당 프로세서를 죽이자!!!!! (!?)

   - 성능모니터 실행 => 성능-모니터링 도구-성능 모니터 => 카운터 추가

   - 사용 가능한 카운터에서 Thread 하위의 %Processor Time / ID Thread / Thread State / Thread Wait Reason 선택

   - 선택한 개체의 인스턴스에서 sqlserv 명칭 죄다 선택..

   - 모니터링...

2. 참조&결론

 위의 문제 도출, 해결 방법은 특정 구문의 오류로 발생시키는 CPU 오류도 있지만,

또 다른 접근으로 프로그램 내부에서 사용하는 레코드 수가 문제의 원인일 수도 있다고 한다.

포스팅한지 2시간만에 해당 접근적인 문제로 문제를 파악해서 문제를 해결중에 있으나, 쉽지만은 않을 것 같다.

DB 튜닝에 대해서도 알아봐야할 것 같다. 애초에 프로그래머니까 쿼리식부터 잘짜자-_-;;

참고자료 및 출처

참고자료1 : https://stackoverflow.com/questions/12458776/sql-server-100-cpu-utilization-one-database-shows-high-cpu-usage-than-others

참고자료2 : https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/

참고자료3 : http://blog.daum.net/hhjj7201/18

참고자료4 : http://www.sqler.com/bSQLQA/477700

참고자료5 : http://sqlsql.tistory.com/21

메모리 관련 포스트 : http://ideagarden.tistory.com/entry/MSsql-server-%EB%A9%94%EB%AA%A8%EB%A6%AC-%EA%B3%BC%EB%B6%80%ED%95%98-%EB%8C%80%EC%B2%98%EB%B2%95