SQL server logman performance log

performance counter 和logman 都是Microsoft 上收集系統效能計數器的方式， 只是logman 是命令行，performance counter 是UI， 2個的效用相同， 用performance counter 也可以收集， 需要逐一的點選。

圖片來自：http://sqlserverplanet.com/sql-server-performance-tuning

一. 使用效能計數器收集方式

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

1. LogicalDisk 的所有計數器
2. Memory 所有的計數器
3. NetWork Interface 的所有計數器
4. Processor 所有的計數器
5. 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

• 建議設定相關的參賽

1. 抽樣區隔 : 15秒，30秒，60秒
2. 錄製時段 : 每天8點~20點， 包含週六週日
3. 保存成為csv 格式， 日誌文件比較小，不要保留位二進制格式（文件大小會太大）
4. 建議將OS 和SQL 的的計數器文件分開保存為不同文件。

二. 使用 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 的收集腳本

• 備註 其中的 -si 60 中可以依據具體需求， 可以調整為30秒或是15秒
• 創建文件名 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
bye

• 需要更改文件中的IP 地址， 用戶名和密碼

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

• 創建C:\bimap\ONSTART.bat

logman.exe start MSPOC
logman.exe start sqlPOC

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

• 創建SetSchedule.bat (英文版)，可以使用schtasks 啟動排程設定或是在圖型介面中設定排程啟動時間
• 可以設定每天啟動一次， 或是每小時啟動一次，依據實際的需求調整

%SystemRoot%\system32\schtasks.exe /create /tn bimap_Hourly /tr c:\bimap\collectSQL.bat /sc HOURLY/st 00:10 /ru system 
%SystemRoot%\system32\schtasks.exe /create /tn bimap_OnStart /tr c:\bimap\onstart.bat /sc onstart /ru system 
Schtasks /query /tn bimap_Hourly
Schtasks /query /tn bimap_Onstart

參考資料：

效能調校首重數據分析，透過數據分析進一步瞭解應用程式所遇到的效能瓶頸，最常使用的工具不外乎就是 效能監視器 ( Performance Monitor )，但由於有多台主機，每壹台都要重新選取一次這些 效能計數器 (Performance Counter) 實在很麻煩，所以若能透過指令列工具建立效能監視集合就會十分方便。

透過 logman 指令有效率的操作效能監視器 | The Will Will Web

Two Minute Drill: LOGMAN.EXE

Logman

Syntax Options

[-s computer_name] [-config FileName] [-b M/d/yyyy h:mm:ss[AM | PM]] [-e M/d/yyyy h:mm:ss[AM | PM]] [-m [start] [stop]] [-[-]r] [-o {Path | DSN!counter_log}] [-f {bin | bincirc | csv | tsv | SQL}] [-[-]a] [-[-]v [{nnnnn | mmddhhmm}]] [-[-] rc [FileName]] [-[-] max {value}] [-[-]cnf [[[hh:]]mm:]ss] [-c {Path [path …] | -cf FileName}] [-si [[hh:]mm:]ss] [-ln logger_name] [-ets] [-[-] rt] [-p {GUID | provider [(flags [,flags…]}] Level | -pf FileName}] [-[-] ul] [-bs value] [-ft [[hh:]mm:]ss] [-nb min max] [-fd logger name] [-[-]u user password] [-rf [[hh:]mm:]ss] [-y] [-mode {trace_mode [trace_mode …]}]