SQL Server xp_readerrorlog의 CPU 100% 점유 현상

SQL Server xp_readerrorlog의 CPU 100% 점유 현상

   

-      Version : SQL Server 2005, 2008

   

[문제 발생]

  SQL Server의 CPU 사용량이 비정상적 증가.

  요청 받은쿼리의 응답 속도가 저하.

   

[증상 확인]

성능 모니터, sysprocesses, SQL Serverer 에러로그등을 통하여 문제 진단.

   

-- blocking select t1.resource_type as [lock type] ,db_name(resource_database_id) as [database] ,t1.resource_associated_entity_id as [blk object] ,t1.request_mode as [lock req] ,t1.request_session_id as [waiter sid] ,t2.wait_duration_ms as [wait time] ,( select db_name(qt.dbid) from sys.dm_exec_requests r with(nolock) cross apply sys.dm_exec_sql_text(r.sql_handle) as qt where r.session_id = t1.request_session_id ) as waiter_db ,( select object_name(objectid, dbid) from sys.dm_exec_requests r with(nolock) cross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id ) as waiter_sp ,( select text from sys.dm_exec_requests r with(nolock) cross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id ) as waiter_batch ,( select substring(qt.text,r.statement_start_offset/2 + 1, ((case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end - r.statement_start_offset)/2) + 1) from sys.dm_exec_requests r with(nolock) cross apply sys.dm_exec_sql_text(r.sql_handle) as qt where r.session_id = t1.request_session_id ) as waiter_stmt ,t2.blocking_session_id as [blocker sid] ,( select db_name(qt.dbid) from sys.sysprocesses p with(nolock) cross apply sys.dm_exec_sql_text(p.sql_handle) as qt where p.spid = t2.blocking_session_id ) as blocker_db ,( select object_name(qt.objectid, qt.dbid) from sys.sysprocesses p with(nolock) cross apply sys.dm_exec_sql_text(p.sql_handle) as qt where p.spid = t2.blocking_session_id ) as blocker_sp ,( select qt.text from sys.sysprocesses p with(nolock) cross apply sys.dm_exec_sql_text(p.sql_handle) as qt where p.spid = t2.blocking_session_id ) as blocker_stmt from sys.dm_tran_locks as t1 with(nolock) inner join sys.dm_os_waiting_tasks as t2 with(nolock) on t1.lock_owner_address = t2.resource_address

   

-- Query, 현재수행(2005) select r.session_id ,status ,db_name(qt.dbid) as db_name ,object_name(qt.objectid, qt.dbid) as object_name ,r.command ,r.cpu_time as 'cpu(ms)' ,r.total_elapsed_time as 'elapsed_time(ms)' ,r.logical_reads ,r.reads ,r.writes ,r.blocking_session_id ,r.wait_type ,r.wait_time ,r.wait_resource ,r.transaction_isolation_level as t_i_level ,percent_complete as 'complete [%]' ,estimated_completion_time / 1000 as seconds_remain ,substring(qt.text,r.statement_start_offset/2, (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end - r.statement_start_offset)/2) as query_text --- this is the statement executing right now ,qt.text as full_query_text from sys.dm_exec_requests r with(nolock) cross apply sys.dm_exec_sql_text(sql_handle) as qt where r.session_id > 50 order by seconds_remain desc, r.session_id asc

   

[문제 원인]

  SQL Server Agent에서 실행하는 특정 SP의 실행 시간이 24시간이 지나도록 실행 되고 있음.

  추적 결과 Agent에서 호출한 SP내에xp_readerrorlog를 실행하는 부분에서 종료되지 않는 문제 발생.

   

   

[해결 방법]

1.     해당 Agent 작업을 Kill 하였으나 프로세스에서는 종료되지 않고 계속 실행 됨.

2.     긴급 점검을 통하여 SQL Server 재시작.

3.     해당 Agent가 실행되지 않도록 Disable 진행.

4.     안정적 운영

   

[권고 사항]

  SQL Server 2005, 2008에서 발생한 버그로 SQL Server2005 Sp3 Cu5, SQL Server 2008 SP1 CU4 에서 릴리즈 됨.

  SQL Server 업데이트 진행 할 것.

   

[참고 자료 ]

http://support.microsoft.com/kb/973524/en-us

   

2013-01-22 / 강성욱 / http://sqlmvp.kr