데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

DMV를 이용한 CPU 사용량 높은 쿼리 찾기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

 

SQL Server를 운영하는데 어떤 쿼리가 CPU를 많이 사용하는지 확인할까? 프로파일러를 통해서 성능자료를 수집할 수도 있지만 프로파일러의 경우 시스템에 오버헤드가 발생하기 때문에 실제 운영중인 서버에서는 가급적 권장하지 않는 분위기 이다.

 

SQL Server 2005에서부터 도입된 DMV를 활용하여 CPU를 많이 사용하는 쿼리를 확인하는 방법을 알아보자.

 

사용된 DMV는 sys.dm_exec_query_stats 으로 캐시된 쿼리 계획에 대한 통계를 반환 한다. 이 뷰의 정보를 바탕으로 컴파일 시간, CPU 실행시간, 물리적 읽기 수, 실행계획 등 다양한 정보를 확인 할 수 있다.

 

다음 스크립트는 상위 50개의 CPU 사용률이 높은 쿼리 정보를 확인한다.

SELECT

    TOP 50

    qs.creation_time, qs.execution_count, qs.total_worker_time as total_cpu_time,

    qs.max_worker_time as max_cpu_time,    qs.total_elapsed_time, qs.max_elapsed_time,

    qs.total_logical_reads, qs.max_logical_reads, qs.total_physical_reads,

    qs.max_physical_reads, t.[text], qp.query_plan, t.dbid, t.objectid,

    t.encrypted, qs.plan_handle, qs.plan_generation_num

FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t

    CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

ORDER BY qs.total_worker_time DESC

 

 

스크립트를 실행하여 수집된 쿼리와 이 쿼리의 실행계획(XML 실행계획 제공)을 바탕으로 쿼리 튜닝 및 오버헤드의 원인을 찾을 수 있다.

 

아래 목록은 일반적으로 CPU 사용을 높이는 동작이다. 물론 이외에도 많은 변수가 있다.

  • High cost operations
  • Index scans
  • Multiple executions of scans/seeks
  • Bookmark Lookups
  • Operations with very high rowcount
  • Sort operations
  • Implicit conversions
  • Estimated rows/executions that do not match actual rows/executions (which could indicated out of date statistics

 

 

수집된 정보를 활용하여 비용이 높은 인덱스가 있는지, 통계 정보는 최신으로 반영되어 있는지 실행 계획은 문제가 없는지 등 판단하여 튜닝을 진행 할 수 있도록 하자.

 

[참고자료]

  • High CPU Troubleshooting with DMV Queries :

http://blogs.msdn.com/b/psssql/archive/2013/06/17/high-cpu-troubleshooting-with-dmv-queries.aspx

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
» DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4426
1809 DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1157
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1286
1807 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2281
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2175
1805 DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 3781
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1251
1803 SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1130
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1071
1801 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2435
1800 access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1051
1799 Hot Add CPU jevida(강성욱) 2016.09.29 851
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1013
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1722
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1402
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1231
1794 메모리 관리 아키텍처 – NUMA 지원 방법 jevida(강성욱) 2016.09.29 1554
1793 메모리 관리 아키텍처 – NUMA(Non-Uniform Memory Access)이해 jevida(강성욱) 2016.09.29 1417
1792 메모리 관리 아키텍처 – Hot Add 메모리 jevida(강성욱) 2016.09.28 929
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1056





XE Login