[MSSQL] 서버에서 지연되는(처리시간이 오래 걸리는) 쿼리(Query)찾기

MS-SQL에서 어떤 쿼리를 처리할 때 처리시간이 너무 오래 걸리는 경우가 있습니다. 이는 서버 전체의 처리시간을 늘려 장애를 발생시키는 요인이 될 수 있는데 이런 경우 아래 쿼리를 SSMS에서 날려 처리시간이 오래 걸리는 쿼리를 찾아낼 수 있습니다.

Select Distinct dm_ws.wait_duration_ms, dm_t.[TEXT], dm_ws.session_ID
From sys.dm_os_waiting_tasks As dm_ws Inner Join sys.dm_exec_requests As dm_r
On dm_ws.session_id = dm_r.session_id Inner Join sys.dm_exec_sessions As dm_es
On dm_es.session_id = dm_r.session_id Cross Apply sys.dm_exec_sql_text (dm_r.sql_handle) As dm_t Cross Apply sys.dm_exec_query_plan (dm_r.plan_handle) As dm_qp
Where dm_es.is_user_process = 1 And DB_NAME(dm_r.database_id) = '[대상DB]'
Order By dm_ws.wait_duration_ms Desc;

wait_duration_ms를 보면 정상적인 경우 아주 작은 시간대가 나오거나 아니면 아예 나오지 않을것입니다. 처리 시간이 오래 걸리는 경우 천 단위 이상의 시간대나 그 이상의 시간대가 나올 수 있는데, 위험하다고 판단되면 해당 쿼리의 session_ID를 확인하고 kill [session_ID] 형식으로 해당 세션을 죽여야 합니다.

kill xx;

대부분의 경우 select 구문에서 시간이 지연되는 경우가 많으므로 다음처럼 조건을 걸어 찾을 수도 있을 것입니다.

Select Distinct dm_ws.wait_duration_ms, dm_t.[TEXT], dm_ws.session_ID
From sys.dm_os_waiting_tasks As dm_ws Inner Join sys.dm_exec_requests As dm_r
On dm_ws.session_id = dm_r.session_id Inner Join sys.dm_exec_sessions As dm_es
On dm_es.session_id = dm_r.session_id Cross Apply sys.dm_exec_sql_text (dm_r.sql_handle) As dm_t Cross Apply sys.dm_exec_query_plan (dm_r.plan_handle) As dm_qp
Where dm_es.is_user_process = 1 And DB_NAME(dm_r.database_id) = '[대상DB]'
And dm_t.[TEXT] Like '%Select%'
Order By dm_ws.wait_duration_ms Desc;

그러면 실제 시간 지연이 오래 걸리는 쿼리가 정말 문제가 되는가? 하는 부분에서는 의구심을 가져야 합니다. 물론 정말 쿼리가 잘못되어 성능을 저해하는 부분이 있다면 이를 튜닝하여 문제를 해결해야 하지만 꼭 그렇지 않은 경우도 있습니다.

 

예컨대 응용프로그램에서 DB서버에 쿼리를 던진 이 후 그 결과가 되돌아오면 응용프로그램에서는 그 결과를 모두 받아 DB와의 연결을 해제하고 난 다음 자체적인 처리를 진행해야 하는데 어떤 경우는 결과를 한건씩 받으면서 내부적으로 전체 결과의 가공이 다 완료될 때까지 계속 DB와의 연결을 지속하는 경우가 있습니다.

 

이런 경우 DB는 응용프로그램에서 연결을 해제하지 않는 이상 쿼리에 대한 처리를 계속 지속하게 되는데 이런 사정을 모르면 쿼리에 문제가 있다고 판단하고 쿼리에만 집중하려는 경향이 있습니다. 하지만 정작 문제가 되는 쿼리를 실행시켜 보면 응답속도가 꽤 나오는 경우도 있는 것이죠.

 

결론적으로 쿼리에 문제가 없다는 확신이 들면 그 쿼리를 수행시키는 프로그램을 들여야 봐야 합니다.