SQLDiag 유틸리티 사용으로 SQL Server 진단 데이터 수집

SQL Server 에서 특정 문제가 발생하였을 때, 필요한 로그를 수집하는 데 활용할 수 있는 SQLDiag 유틸리티에 대해서 아래와 같이 정리하였습니다. 일반적으로 보다 포괄적인 시스템 정보가 필요하여 MPSReport SQL Edition 을 사용합니다만.... ^^;

SQSLDiag 는 PSSDiag 툴이 이름이 변경 되어 SQL Server 2005 부터 제품에 포함되어 제공되며 아래와 같은 정보를 수집할 수 있습니다.

1. Windows 성능 로그
2. Windows 이벤트 로그
3. SQL Server 프로파일러 추적 정보
4. SQL Server 차단 정보
5. SQL Server 구성 정보

제공되는 다양한 Arguments 는 도움말을 살펴보시면 되고 Command Prompt 모드에서 'SQLDIAG' 라고 입력해 주시면 일괄적인 데이터 수집이 진행됩니다. 멀티 인스턴스가 설치되어 있더라도 별도의 설정없이 모든 인스턴스의 정보를 수집합니다.

충분히 데이터를 수집 하였다고 판단이 되시면 Ctrl+C 를 입력하여 수집을 중지합니다.


수집된 파일이 생성되어 모여 있는 곳은 아래 경로입니다.

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLDIAG

아무런 옵션을 주지 않고 기본적으로 수집되는 정보는 어떤 것들이 있는지 하나씩 살펴봤습니다.

1. MSINFO32.TXT - System Information Report
2. Trace Log (SQL Server 2005 이후부터 default 로 설정되어 있는 기본 추적 로그 파일 수집 *.TRC)
3. sp_sqldiag_Shutdown.OUT 
  가. 수집되는 정보는 대략(?) 아래와 같습니다.

ErrorLog, sp_configure, sp_who, sp_lock, sp_helpdb, xp_msver, sp_helpextendedproc, sysprocesses, sys.dm_exec_sessions, ::fn_virtualservernodes(), sysdevices, sysdatabases, sys.master_files, Non-NULL input buffers by SPID, tats for currently running queries, Head blockers, sys.dm_tran_database_transactions, sys.dm_tran_active_transactions, sys.dm_os_sys_info, sys.dm_os_schedulers, sys.dm_os_threads, sys.dm_os_workers, sys.dm_os_tasks, sys.dm_io_pending_io_requests, sys.dm_io_virtual_file_stats, sys.dm_os_latch_stats, sys.dm_os_spinlock_stats, sys.dm_os_sublatches, sys.dm_os_memory_pools, sys.dm_os_memory_clerks, sys.dm_os_memory_brokers, sys.dm_os_memory_nodes, sys.dm_os_wait_stats, sys.dm_os_waiting_tasks, sys.dm_os_loaded_modules, sys.dm_os_cluster_nodes, sys.dm_os_nodes, sys.dm_os_process_memory, sys.dm_os_sys_memory, sys.dm_io_cluster_shared_drives, sys.database_files, sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, sys.dm_db_task_space_usage, sys.dm_exec_query_optimizer_info, sys.dm_exec_query_resource_semaphores, sys.dm_broker_queue_monitors, ysperfinfo snapshot #1, sys.dm_fts_memory_pools, msdb..suspect_pages, Full text information 등

4. ##envvars.txt - 환경변수
5. ##files.txt - C:\Program Files\Microsoft SQL Server\100\Tools\Binn 디렉터리 파일 정보
6. ##SQLDIAG.LOG - SQLDiag 수집 로그
7. ##SQLDIAG.XML - 수집할 개체 대상 정보는 XML configuration file 형태로 제공되므로 변경하여 사용할 수 있음

이외에도 몇 가지가 더 수집됩니다만 대략 이 정도로 보입니다.  그런데 여기서 잠깐! Windows Event Log와 성능 로그도 수집된다고 하였는데 왜 여기선 수집되지 않았을까요?

수집 개체에 대한 configuration 파일인 ##SQLDIAG.XML 을 열어보면 기본값으로 해당 개체 Collector enabled 값이 false 로 지정되어 있음을 확인할 수 있습니다. 추가로 수집할 개체가 있다면 이 값을 수정하고 SQLDIAG /I ##SQLDIAG.XML 를 실행하시면 됩니다.


마지막으로 툴 사용에 대한 성능 부하는 어떨까요?
관련 문서에 따르면 거의 없는 것으로 알려져 있습니다. 대부분 상태 정보를 수집하는 수십개의 쿼리를 실행한 결과를 보여주는 것이니깐요. 실제 운영 머신에 적용하였을 때도 이 툴로 인해 영향을 줄 만큼 CPU, 메모리 사용량이 증가하는 현상은 아직 본 적이 없습니다. 수집할 때 작업 관리자를 통해 시스템 성능을 확인해 보시면 좋을 듯 합니다.  


[참고자료]
SQLdiag 유틸리티
http://msdn.microsoft.com/ko-kr/library/ms162833.aspx


작성자 : Lai Go / 작성일자 : 2009.11.06