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

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

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

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

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

시작 및 중지 Console app Service 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

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

구성 파일 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)

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

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

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

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

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

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

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

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

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

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

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

User-defined Variables SQLDIAG /V foo=bar SQLDiag can prompt for variable values at runtime (console app only)

Server에 미치는 영향 Performance Support objects Registry entries 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

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

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

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

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?