Automating Performance Monitor Statistics Collection for SQL Server and Windows

여러서버를 관리하는 환경에서 불특정 몇몇서버 혹은 전체서버에 대해 성능모니터링을 해야하는 상황입니다.
윈도우의 성능모니터링을 전체서버에 배포해야 하는 상황일때,(또한 특정 성능 카운트가 추가되거나 변경돼야 하는 상황이라면..)
윈도우의 logman utility를 이용해 좀 더 수월한 작업을 할 수 있습니다.
logman util은 "성능로그 및 경고" 서비스를 관리하여 이벤트추적세션로그및 성능로그를 만들고 관리합니다

성능카운트 항목이 포함된 "SQL2008BaselineCounters.config" 파일을 생성합니다. 
파일은 시스템의 I/O 이슈가 적은 디스크에 위치시킵니다. "C:\PerfLogs" 에 위치시키겠습니다.
(config 파일의 SQL$MSSQLSERVER2008 는 해당서버의 인스턴스명으로 변경해 주시면 됩니다)
-- SQL2008BaselineCounters.config --

 "\Memory\Available MBytes"
"\Memory\Free System Page Table Entries"
"\Memory\Pages Input/sec"
"\Memory\Pages/sec"
"\MSSQL$MSSQLSERVER2008:Access Methods\Full Scans/sec"
"\MSSQL$MSSQLSERVER2008:Access Methods\Page Splits/sec"
"\MSSQL$MSSQLSERVER2008:Access Methods\Workfiles Created/sec"
"\MSSQL$MSSQLSERVER2008:Access Methods\Worktables Created/sec"
"\MSSQL$MSSQLSERVER2008:Buffer Manager\Buffer cache hit ratio"
"\MSSQL$MSSQLSERVER2008:Buffer Manager\Checkpoint pages/sec"
"\MSSQL$MSSQLSERVER2008:Buffer Manager\Free pages"
"\MSSQL$MSSQLSERVER2008:Buffer Manager\Lazy writes/sec"
"\MSSQL$MSSQLSERVER2008:Buffer Manager\Page life expectancy"
"\MSSQL$MSSQLSERVER2008:Buffer Manager\Page reads/sec"
"\MSSQL$MSSQLSERVER2008:Buffer Manager\Page writes/sec"
"\MSSQL$MSSQLSERVER2008:Buffer Manager\Stolen pages"
"\MSSQL$MSSQLSERVER2008:General Statistics\Logins/sec"
"\MSSQL$MSSQLSERVER2008:General Statistics\Logouts/sec"
"\MSSQL$MSSQLSERVER2008:General Statistics\User Connections"
"\MSSQL$MSSQLSERVER2008:Latches\Average Latch Wait Time (ms)"
"\MSSQL$MSSQLSERVER2008:Locks(_Total)\Average Wait Time (ms)"
"\MSSQL$MSSQLSERVER2008:Locks(_Total)\Lock Requests/sec"
"\MSSQL$MSSQLSERVER2008:Locks(_Total)\Number of Deadlocks/sec"
"\MSSQL$MSSQLSERVER2008:Memory Manager\Target Server Memory (KB)"
"\MSSQL$MSSQLSERVER2008:Memory Manager\Total Server Memory (KB)"
"\MSSQL$MSSQLSERVER2008:SQL Statistics\Batch Requests/sec"
"\MSSQL$MSSQLSERVER2008:SQL Statistics\SQL Compilations/sec"
"\MSSQL$MSSQLSERVER2008:SQL Statistics\SQL Re-Compilations/sec"
"\Paging File(_Total)\% Usage"
"\Paging File(_Total)\% Usage Peak"
"\PhysicalDisk(_Total)\Avg. Disk Read Queue Length"
"\PhysicalDisk(_Total)\Avg. Disk sec/Read"
"\PhysicalDisk(_Total)\Avg. Disk sec/Transfer"
"\PhysicalDisk(_Total)\Avg. Disk sec/Write"
"\PhysicalDisk(_Total)\Avg. Disk Write Queue Length"
"\Process(sqlservr)\% Privileged Time"
"\Process(sqlservr)\% Processor Time"
"\Processor(_Total)\% Privileged Time"
"\Processor(_Total)\% Processor Time"
"\System\Context Switches/sec"
"\System\Processor Queue Length"

커맨드 창을 열어 logman 명령어로 카운터 수집기를 생성합니다.

logman create counter SQL2008Perf -f csv  -b 11/09/2010 00:00:05  -E 11/09/2010 23:59:00  -si 05 -v mmddhhmm -o "C:\perflogs\SQL2008Perf" -cf "C:\perflogs\SQL2008BaselineCounters.config" -u heesugo *

마지막 * 실행 후 계정에 대한 비밀번호를 입력합니다.

아래는 각 옵션에 대한 설명입니다.

• SQL2008Perf - name of the counter log 
• -f bin - binary format, with a .blg extension , csv
• -b 11/09/2010 00:00:05 - begin data and time, input a datetime to start the counter log at a later time, if you input a datetime that is past, the counter log will start immediately 
• -E 11/09/2010 23:59:00 - end date and time
• -si 05 - 5 second interval 
• -v mmddhhmm - filename will be named as SQL2005Perf_mmddhhmm.blg 
• -o "C:\perflogs\SQL2008Perf" - output folder name 
• -cf "C:\perflogs\SQL2008BaselineCounters.config" - config file name with the counters 
• -u domain\username * - the * will prompt for the password for the domain user account.
  

  Perfmon을 실행하면 예약된 시간에 실행하고 있습니다.
  

  

   

  아래 명령어로 설정한 모니터링을 수동으로 시작하고 중지할수 있습니다.

  Logman start SQL2008Perf
  Logman stop SQL2008Perf
  Logman delete SQL2008Perf -- 설정한 SQL2008Perf 을 삭제합니다.
  Logman query  -- 카운터 수집기를 확인 할 수 있습니다.
  

  
  성능모니터링 데이터에 대한 수집 자동화도 가능합니다. StopCopyStart.bat 배치파일을 아래와 같이 생성할수 있습니다.

  logman stop sql2008perf
  sleep 1
  move C:\perflogs\SQL2008Perf\*.blg \\Servername\C$\perflogs\SourceServerName
  sleep 1
  logman start sql2008perf
  

  
  배치파일을 이용해 스케쥴러에 등록해 자동화 시킬수도 있습니다.

  schtasks /create /tn "Stop Copy And Start SQL2008Perf Perfmon Counter Log" /tr C:\perflogs\StopCopyStart.bat /sc daily /st 23:59:58 /ed 01/01/2020 /ru system
  

   

  schtasks 명령어로 윈도우 예약작업에 등록된걸 확인 할 수 있습니다. 아래는 각 옵션에 대한 설명입니다.

  • schtasks /create - create a new scheduled task /tn "Stop Copy And Start SQL2005Perf Perfmon Counter Log" - scheduled task name
  • /tr E:\perflogs\StopCopyStart.bat - run this batch file
  • /sc daily /st 23:59:59 - schedule time
  • /ed 01/01/2099 - end date
  • /ru system - user account 

     

참고: http://www.mssqltips.com/tip.asp?tip=1776