성능분석 12탄 – 문제 쿼리 식별

성능분석 12탄 – 문제 쿼리 식별

 

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

 

SQL Server에서는 sys.dm_exec_query_stats DMV를 사용하여 실행된 쿼리에 대한 통계를 확인 할 수 있다. DMV에서는 몇 개의 쿼리가 실행 되었는지, 전체, 최대, 최소. 마지막 활성 CPU 시간 등을 확인 할 수 있으며 전체, 최대, 최소, 마지막 읽기 쓰기에 대한 정보를 확인 할 수 있어 문제를 식별하는데 매우 좋은 정보이다.

select* from sys.dm_exec_query_stats

 

 

Large execute count

많은 실행 횟수는 자주 실행되는 쿼리로 성능에 가장 민감하다. 빈도가 높은 만큼 작은 개선이 전반적으로 상당한 성능에 이점을 가져올 수 있다.

 

 

Large logical reads

많은 논리적 읽기는 많은 양의 데이터를 검색하기 때문에 느린 쿼리일 가능성이 높다. 데이터 스캔이나 미싱인덱스 문제일 수 있다. 논리적 읽기가 높은 것은 긴 작업타임 이슈이지 CPU 이슈는 아니다. 통계가 오래되거나 잘못된 실행 계획, 잘못된 모델 설계, 인덱스 누락 등으로도 발생 할 수 있다.

 

 

Large Physical reads

큰 물리적 읽기는 논리적 읽기와 같은 문제이지만 추가적으로 서버에 충분한 RAM이 없음을 나타낸다. 다행이 이런 문제는 RAM을 추가하면 해결되는 쉬운 케이스이다. 하지만 여전히 큰 논리적 읽기는 발생 할 수 있다.

 

 

High worker time with low logical reads

높은 작업 시간과 낮은 논리적 읽기 현상은 흔하지 않지만 발생하는 현상이다. 이는 적은 데이터를 처리하기 위해서도 높은 CPU를 사용하는 작업을 나타낸다. 문자열 처리 및 XML 처리 등이 일반적으로 높은 CPU를 요구한다.

 

 

High elapsed time with log worker time

쿼리 작업 시간이 오래 걸리며 CPU 사이클을 활용하는 쿼리 차단을 나타낸다. 이는 뭔가를 기다리는 것에 병렬 처리에서 다른 스레드 처리 결과를 기다리는데 대부분의 시간을 소비 한다.

 

 

High total rows count

큰 결과 행을 요청하는 것은 어플리케이션 설계 문제에서 해결 해야 한다. 꼭 필요한 데이터만 가져 올 수 있도록 요구하는 것이 좋다. 문제가 되는 쿼리를 찾기 위해서는 DMV sys.dm_exec_query_stats을 사용 할 수 있으며 쿼리 텍스트 및 실행 계획을 함께 확인 하기 위해 sys.dm_exec_sql_text, sys.dm_exec_query_plan 을 조인하여 정보를 확인 할 수 있다.

 

select st.text,     pl.query_plan,     qs.*     from sys.dm_exec_query_stats qs     cross apply sys.dm_exec_sql_text(qs.sql_handle) as st     cross apply sys.dm_exec_query_plan(qs.plan_handle) as pl;

 

 

 

[참고자료]

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/

 

 

2014-03-25 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 실행계획, SET SHOWPLAN_XML_ON, 쿼리분석, sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_query_plan