SQLDiag 를 이용한 SQL Server 2005 성능진단

1. SQLDiag를 이용한SQL Server 2005 성능진단 김종구 과장(jkkim@microsoft.com) 한국마이크로소프트

2. SQLDiag란? • SQL Server 2005 진단 데이터 수집 서비스 • PSSDiag 툴이 SQLDiag로 이름이 변경. • SS2K/7.0에서 사용되던 SQLDiag가 동일한 이름으로 대체. • SQL Server관련 이슈에 대한 모든 진단 데이터를 한번에 수집할 수 있는 도구

3. History • 2001에 작성. • 고객기술지원부 내에서 SQL Server관련 케이스의 대다수에 사용되고 있음. • 다른 제품의 기술지원에도 사용. (Exchange 등) • 2003년에 일반에게 공개. • SQL Server 2005에서 서버에 내장. • SQL Server 2005에서부터 SQLDiag에 대한 기술지원 시작.

4. SQLDiag가 수집할 수 있는 것? • Profiler trace • Blocking script/trace • Perfmon/Sysmon logs • Server configuration and state info • SQL Server error logs • Event logs • MSInfo • Virtually any other diagnostic

5. Service vs. Console App • 두 가지 다 운영가능. • 서비스로 운영될 경우 무인 작동 가능. • 로그 파일과 이벤트 로그에 기록. • Supports self-registration/deregistration (/R, /U) • Supports service control (START, STOP, STOP_ABORT)

6. 시작 및 중지 • Console app • Run SQLDiag.exe to start it • Press Ctrl+C to stop it or create “stop file” • Press Ctrl+C a second time to stop ASAP • Service • SQLDIAG START to start it • SQLDIAG STOP to stop it • SQLDIAG STOP_ABORT to stop ASAP

7. 구성 파일 • XML과 INI format 지원 • /I옵션을 통해 지정 • text editor나 GUI configuration app로 설정 가능

8. 구성 파일 • SQLDiag에서 3가지 기본 파일 제공: • SQLDiag.XML (default) – snapshot of error logs, configuration and state info, and MSInfo • SD_General.XML – lightweight version of all diagnostics – Profiler, Perfmon, event logs, etc. • SD_Detailed.XML – heavier version of all diagnostics (stmt-level Trace events)

9. 대상 머신과 Instance • 구성파일에서 지정. • 기본값은 현재 머신의 모든 Instance에서 데이터를 수집 • 특정 머신이나 Instance를 지정 가능

10. 보안 • SQL Server sysadmin 그룹의 멤버여야 함. • 대상 Instance에 로그인 가능해야 함. • Administrators 그룹의 멤버여야 함. /G (Generic mode)인 경우에는 3번만 충족되면 됨.

11. Output • 기본 output folder …90\Tools\Binn\SQLDiag • /O 를 통해 변경가능 • Profiler와 Perfmon는 rollover 지원 • 데이터 수집 동안 /C1 옵션을 이용하여 NTFS 압축 가능

12. Output • 폴더는 자동으로 이름 변경이 되거나 덮어 쓰기를 할 수 있음. (/N) • Profiler trace는 서버의 Log folder에 기록된 후 이동. • Output file 이름은 머신과 Instance 이름을 포함 • Internal output file은 “##” prefix를 가짐

13. Scheduling • /X 옵션을 이용하여 해당 시점(snapshot)의 데이터 수집 후 중지 • /L 옵션을 이용하여 schedule된 중지 후 자동으로 재시작 • SQLAgent를 통해서도 Scheduling이 가능

14. 장기간의 데이터 수집 • 서비스로 등록하여 실행 • Target data collection을 위해 일정 사용 • Output folder renaming 및 auto-restart 사용 • .TRC 및 .BLG file들의 rolled over로 인한 공간을 절약하기 위해 /C1 옵션 사용 고려

15. Clusters • 아래 사항에 대해서 모든 가상 서버에 대해 데이터를 자동으로 추출: • 대상 서버 이름으로 “.”이 설정된 경우 (default) • Cluster node에서 수행이 되는 경우 • /G (generic mode)가 설정되지 않은 경우 (default) • Cluster의 가상 서버들을 확인하고, 각 가상서버의 모든 Instance에 대해 데이터를 수집함.

16. Remote Machines • Recommend approach is to install the service on remote machines • SQLDiag는 원격 서버에 대해 데이터 수집 가능 • TRC 파일은 해당 서버에 저장된 후 옮겨짐. • 다른 데이터는 SQLDiag가 수행되는 머신에 저장

17. Multiple Machines • 각 머신에 대한 Section을 구성파일에 추가 • 모든 수집된 데이터는 output folder로 같이 저장됨. • 각 머신은 각각의 worker thread를 할당받음. • 모든 성능 로그는 하나의 BLG 파일에 남겨짐.

18. Custom Tasks • Supports wide variety of custom tasks: • TSQL command • TSQL script (with parameters) • Registry query, backup • Copy file, list file • ActiveX scripts (VBScript, JScript, etc.) • Batch files, utilities, etc. • Groups and nesting

19. Custom Tasks • Support built-in and user-defined variables • Variables can be used within a task, embedded in TSQL scripts, or passed to sub-processes • Built-in: • startup_path, output_path • server, instance, server_instance • rsinstance, olapinstance • authmode, ssuser, sspwd • ssver, sspath, ssregroot, ssbatchrunner • output_name, internal_output_name • quietmode, compressionmode, genericmode, outputfoldermode • starttime, endtime • Environment variables

20. User-defined Variables • 구성파일에서 지정 또는 /V 옵션을 사용하여 지정 가능 SQLDIAG /V foo=bar • SQLDiag can prompt for variable values at runtime (console app only)

21. Server에 미치는 영향 • Performance • Minimal for collector itself (<10% CPU) • Usually equal to sum of selected diagnostics • Dominated by Profiler trace cost • Support objects • Created in tempdb and dropped afterward • Registry entries • None for console app • Deleted by service deregistration

22. GUI Configuration App • 구성파일에 대한 생성 및 편집 지원 • Collection 서비스에 대한 조절 지원 • Provides limited automatic problem diagnosis

23. SQLDiag vs. PSSDiag • Essentially the same tool (same code) • SQLDiag does not include support files for down level versions of SQL Server • SQLDiag does not support ZIP compression • SQLDiag can only be run by Administrators

24. Yukon SQLDiag vs. Sphinx SQLDiag • Yukon SQLDiag is a superset of Sphinx SQLDiag • Sphinx/Shiloh SQLDiag was a snapshot of error logs, configuration, etc. • No Perfmon, Profiler, blocking info, or customization • Snapshot collection only (no polling support) • No multi-instance or multi-machine support • No scheduling or built-in compression • Diagnostics collected serially rather than in parallel

25. FAQ What’s the perf impact of running this? Can I add new Perfmon counters or Profiler trace events? Can I filter the Profiler trace? Can I run SQLDiag via SQLAgent? Can I add my own diagnostics to the collection set? Can I collect diagnostics for Reporting Services, Analysis Services, Notification Services, etc.? Can I customize the built-in scripts SQLDiag runs? Can I run multiple instances of SQLDiag? Is a 64-bit version available? How do I troubleshoot problems with SQLDiag itself?