B i M A P

Logman|How to collect Windows SqlServer Performance Log

logman collect windows log


Performance Counter 和 Logman 都是Microsoft 上收集系統效能計數器的方式, 差別是 Logman 為指令,Performance Counter 有提供 UI, 2 個的功用相同, 用 Performance Counter 也可以收集, 但需要逐一的點選。


使用效能計數器收集方式

建議收集的五大類 OS 內容包含

  • LogicalDisk 的所有計數器
  • Memory 的所有計數器
  • Network Interface 的所有計數器
  • Processor 的所有計數器
  • Paging File 的所有計數器


建議收集不同的SQLserver 的相關計數器,包含如下:

\SQLServer:Buffer Manager\Buffer cache hit ratio 
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:SQL Statistics\Batch Requests/Sec
\SQLServer:SQL Statistics\SQL Compilations/Sec
\SQLServer:SQL Statistics\SQL Re-Compilations/Sec
\SQLServer:General Statistics\User Connections
\SQLServer:Locks(_Total)\Lock Waits/Sec
\SQLServer:Access Methods\Page Splits/sec
\SQLServer:General Statistics\Processes blocked
\SQLServer:Buffer Manager\Checkpoint pages/sec
\SQLServer:Buffer Manager\Page reads/sec
\SQLServer:Buffer Manager\Page writes/sec
\SQLServer:Buffer Manager\Lazy writes/sec
\SQLServer:Databases(*)\*
\Process(sqlservr)\% Privileged Time
\Process(sqlservr)\% Processor Time
\Process(sqlservr)\% User Time
\Process(sqlservr)\IO Data Operations/sec
\Process(sqlservr)\Page Faults/sec
\Process(sqlservr)\Private Bytes
\Process(sqlservr)\Thread Count
\Process(sqlservr)\Virtual Bytes
\Process(sqlservr)\Working Set


建議設定的相關參數

  • 抽樣區隔 : 15秒,30秒,60秒
  • 錄製時段 : 每天0點~24點, 包含週六週日
  • 保存成為csv 格式, 日誌文件比較小,不要保留位二進制格式(文件大小會太大)



使用 logman 命令收集流程

  • 將 bimap_mssql.ZZZ 檔案改名為 bimap_mssql.ZIP。
  • 將 bimap_mssql.ZIP 解壓縮到 C 槽根目錄 (C:\),解壓縮完成後的目錄結構,應該會有 C:\bimap (內有 UPLOAD, SENT 兩個子目錄,以及 9個檔案)。
  • 執行 createSQL.cmd 開始採集資料,並檢查 C:\ bimap 中是否有 2 個 csv 的文件產生。
  • 修改 C:\bimap\ftp-csv.txt 中的 IP 地址,用戶名和密碼, 以便自動發送 log 到 ftp server 中。
  • 將設定和執行自動排程,如果 OS 是 Windows 中文版,請執行 02SetScheduleC.bat 如果 OS 是 Windows 2003/2008/2012,請執行 02SetSchedule.bat。
  • 每天凌晨 00:10,Windows Schedule 會自動將當日採集的 log,並自動 FTP 到 ftp Server 中。



使用 logman 部署腳本說明

1. 啟動 Logman 的 OS 和 SQL 的收集腳本

創建文件名 C:\bimap\createSQL.cmd

logman.exe stop MSPOC 
logman.exe delete MSPOC
logman.exe create counter MSPOC -o C:\bimap\%COMPUTERNAME% -f csv -v mmddhhmm -c "\LogicalDisk(*)\*" "\Memory\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\Processor(*)\*" -si 60
logman.exe start MSPOC
logman.exe stop SqlPOC
logman.exe delete SqlPOC
logman.exe create counter SqlPOC -o C:\bimap\%COMPUTERNAME%_SQL -f csv -v mmddhhmm -cf C:\bimap\SqlServerPerformanceIndicator.txt -si 60
logman.exe start SqlPOC

其中的 -si 60 中可以依據具體需求,可以調整為 30 秒或是 15 秒


2. 選擇需要收集的SQL 相關參數

  • 收集SQLServer 需要的收集的計數器, 可以依據實際的需求, 進行調整
  • 創建文件名:C:\bimap\SqlServerPerformanceIndicator.txt
\SQLServer:Buffer Manager\Buffer cache hit ratio 
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:SQL Statistics\Batch Requests/Sec
\SQLServer:SQL Statistics\SQL Compilations/Sec
\SQLServer:SQL Statistics\SQL Re-Compilations/Sec
\SQLServer:General Statistics\User Connections
\SQLServer:Locks(_Total)\Lock Waits/Sec
\SQLServer:Access Methods\Page Splits/sec
\SQLServer:General Statistics\Processes blocked
\SQLServer:Buffer Manager\Checkpoint pages/sec
\SQLServer:Buffer Manager\Page reads/sec
\SQLServer:Buffer Manager\Page writes/sec
\SQLServer:Buffer Manager\Lazy writes/sec
\SQLServer:Databases(*)\*
\Process(sqlservr)\% Privileged Time
\Process(sqlservr)\% Processor Time
\Process(sqlservr)\% User Time
\Process(sqlservr)\IO Data Operations/sec
\Process(sqlservr)\Page Faults/sec
\Process(sqlservr)\Private Bytes
\Process(sqlservr)\Thread Count
\Process(sqlservr)\Virtual Bytes
\Process(sqlservr)\Working Set


3. 設定自動收集發送log命令

  • 將要收集的方式, 自動化收集 SQL 等
  • 創建 C:\bimap\collectSQL.bat 命令, 以自動化收集 MS 的log
logman.exe stop MSPOC 
logman.exe stop SqlPOC
move /Y C:\bimap\*.csv C:\bimap\UPLOAD
logman.exe start MSPOC
logman.exe start SqlPOC
ftp -s:C:\bimap\ftp-csv.txt
move /Y c:\bimap\UPLOAD\*.csv c:\bimap\SENT 
FORFILES /P c:\bimap\SENT /M *.csv /C "cmd /c Del @PATH" /d -10

如果無須ftp 發送,改成 rem ftp -s:C:\bimap\ftp-csv.txt 即可


4. 創建 ftp 自動上傳

創建 C:\bimap\ftp-csv.txt

Open 192.168.xxx.xxx 
AAAA (Username)
BBBB (Password)
prompt
bin
lcd c:\TTPOC\UPLOAD mput *.csv
mput *.csv
bye


5. 設定伺服器重啓時,自動收集 log

創建 C:\TTPOC\ONSTART.bat

logman.exe start MSPOC 
logman.exe start sqlPOC


6. 啓動定時任務執行 (英文版)

  • 創建 SetSchedule.bat (英文版),可以使用 schtasks 啟動排程設定,或是在圖型介面中設定排程啟動時間
  • 可以設定每天啟動一次, 或是每小時啟動一次,依據實際的需求調整
%SystemRoot%\system32\schtasks.exe /create /tn TTPOC_Daily /tr c:\TTPOC\collect.bat /sc daily /st 00:10 /ru system 
%SystemRoot%\system32\schtasks.exe /create /tn TTPOC_OnStart /tr c:\TTPOC\onstart.bat /sc onstart /ru system
Schtasks /query /tn TTPOC_Daily
Schtasks /query /tn TTPOC_OnStart


7. 啓動定時任務執行 (中文版)

  • 創建 SetScheduleC.bat
  • 和 SetSchedule.bat 原理相同 ,只要需要把 daily 改成每日即可
%SystemRoot%\system32\schtasks.exe /create /tn TTPOC_Daily /tr c:\TTPOC\collect.bat /sc 每日 /st 00:10 /ru system 
%SystemRoot%\system32\schtasks.exe /create /tn TTPOC_OnStart /tr c:\TTPOC\onstart.bat /sc onstart /ru system
Schtasks /query /tn TTPOC_Daily
Schtasks /query /tn TTPOC_OnStart


結論

通常 BiMAP 最常與企業討論兩件事,一為如何收集 Log,二為收集哪些數據才有用?

因為層層資安的問題,導致在收集 Log 時常會遇到,無法安裝 agent 的問題,此時 Windows 中的 logman 則是一個很好的方法,在搭配收集必要的數據於 Elasticsearch 中進一步分析,盡可能的縮小問題範圍。




延伸閱讀
winstonlu的大頭照
ELK 達人

我們致力於 ELK 的各種應用,協助企業建置相關服務。我們也提供基於 ELK 的各種解決方案,有任何問題,歡迎加入我們的官方 Line,或來信詢問,期待與您面對面的機會。