2014년 4월 29일 화요일

현재 어떤 쿼리들이 실행중인가?

현재 실행중인 쿼리들에 대한 정보를 보려면 어떻게 해야 할까요?
간단히 sys.sysprocesses의 status 열이 'runnable'인 행을 조회해보면 됩니다.
그런데 BOL에서 sysprocesses에 대해 찾아보면
Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다.
새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 응용 프로그램은 수정하십시오.
라는 경고가 나옵니다. SQL SERVER 2014에서도 sysprocesses테이블을 사용할 수 있지만 어쩐지 찝찝하네요. Microsoft에서는 시스템테이블 보다는 시스템뷰를 사용하기를 권장하므로 시스템 뷰를 이용하여 구현해보았습니다. 세션에서 실행중인 실제 쿼리를 확인하기 위해 sys.dm_exec_sql_text 함수를 사용하였습니다. 일부 작업의 예상 종료 시간을 확인하기 위해 sys.dm_exec_requests 함수를 사용하였습니다. 다음 작업들의 경우 예상 종료시간과 현재 작업이 얼마나 진행되었는지를 알 수있습니다.
ALTER INDEX REORGANIZE
ALTER DATABASE의 AUTO_SHRINK 옵션
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE 
ROLLBACK
TDE ENCRYPTION
아래는 전체 소스입니다.
SELECT A.session_id
       , text = ISNULL(ISNULL(CONVERT(VARCHAR(MAX), object_name(D.objectid, D.dbid)), D.text),'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(10), A.session_id) + ')')
       , D.objectid
       , dbname = db_name(D.dbid)
       , D.dbid
       , A.status
       , A.cpu_time
       , A.memory_usage
       , A.total_scheduled_time
       , A.total_elapsed_time
       , A.last_request_start_time
       , A.last_request_end_time
       , worker_time = DATEDIFF(SECOND, A.last_request_start_time, CASE WHEN A.status = 'running' THEN GETDATE() ELSE A.last_request_end_time END)
       , completion_time = C.estimated_completion_time / 1000.0
       , C.percent_complete
       , A.reads
       , A.writes
       , A.logical_reads
       , transaction_isolation_level = CASE A.transaction_isolation_level WHEN 0 THEN '0'
                                                                        WHEN 1 THEN '1 - READ UNCOMMITTED'
                                                                        WHEN 2 THEN '2 - READ COMMITTED'
                                                                        WHEN 3 THEN '3 - REPEATABLE READ'
                                                                        WHEN 4 THEN '4 - SERIALIZABLE'
                                                                        WHEN 5 THEN '5 - SNAPSHOT' END
       , A.lock_timeout
       , A.deadlock_priority
       , A.row_count
       , A.prev_error
       , B.num_reads
       , B.num_writes
       , B.last_read
       , B.last_write
       , B.net_packet_size
       , C.command
       , C.blocking_session_id
       , C.wait_type
       , C.wait_time
       , C.last_wait_type
       , C.wait_resource
       , C.granted_query_memory
       , A.host_name
       , A.program_name
       , A.client_interface_name
       , A.login_name
       , B.client_net_address
  FROM sys.dm_exec_sessions A
       LEFT OUTER JOIN sys.dm_exec_connections B ON A.session_id = B.session_id
       LEFT OUTER JOIN sys.dm_exec_requests C ON A.session_id = C.session_id
       OUTER APPLY sys.dm_exec_sql_text(B.most_recent_sql_handle) D
 WHERE A.status = 'running'

댓글 없음:

댓글 쓰기