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
- sys.dm_exec_query_stats : http://msdn.microsoft.com/ko-kr/library/ms189741.aspx
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
