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
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 528
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2260
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2567
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1750
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1568
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1942
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 848
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1646
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 5124
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1114
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1305
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1075
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1223
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1366
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1278
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1668
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1800
» DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4742
1809 DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1183
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1318
1807 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2381





XE Login