設定收集效能計數器記錄檔，建立SQL Server 效能基準線
from my MSDN blog - September 5, 2016
1.啟動效能監視器
Perfmon2.新增收集器
Data Collector Sets>User Defined>New>Data Collector Set
3.輸入名稱 (例如 SQLPerfLogBaseline)並選擇 Create from a template (Recommended)
4.範本Template，選擇System Performance
5.指定blg檔存放的根目錄
6.選擇Open Properties for this data collector set
7.按下Finish之後，則出現此視窗
8.在schedule頁籤，新增一個排程，例如每天上午08:00啟動 或 每天 上午12:00啟動
9.在Stop Condition頁籤，設定停止條件，例如勾選Overall Duration 16 hours，在Limit限制的區塊，勾選 Restart the data collector set at limits.，勾選 Maximum Size:設定300MB
可以考慮改用Task Schedule來取代此設定，請參考這篇
Taming Perfmon: Data Collector Sets
也就是在Stop Condition頁籤都不要設定，改去Task Scheduler
Task Scheduler Library > Microsoft > Windows > PLA 找到你的Performance log排程，將If the task is already running, then the following rule applies: 設定為Stop the existing instance就可以自動每天重啟收集。
10.按下OK之後，回到主視窗，若不需要可將NT Kernel trace刪除
11.在Performance Counter按右鍵選擇 Properties
12.修改預設選取的Performance counters，只留下需要的Performance Counter
建議最少需要收集以下效能計數器
\Processor(*)\% Processor Time
\Processor(*)\% User Time
\System\Processor Queue Length
\Memory\Available Mbytes
\Memory\Pages/sec
\Memory\Pages Input/sec
\Memory\Page Reads/sec
\Process(*)\% Processor Time
\Process(sqlservr)\Private Bytes
\Process(sqlservr)\Working Set
\Process(sqlservr)\Working Set Peak
\Process(sqlservr)\Virtual Bytes Peak
\Process(sqlservr)\Virtual Bytes
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:Buffer Manager\Total pages
\SQLServer:Buffer Manager\Target pages
\SQLServer:Buffer Manager\Database pages
\SQLServer:Buffer Manager\Reserved pages
\SQLServer:Buffer Manager\Stolen pages
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Total Server Memory (KB)
\SQLServer:General Statistics\Logins/sec
\SQLServer:General Statistics\User Connections
\SQLServer:General Statistics\Transactions
\SQLServer:Databases(*)\Percent Log Used
\SQLServer:Databases(*)\Transactions/sec
\SQLServer:Access Methods\Full Scans/sec
\SQLServer:Access Methods\Index Searches/sec
\SQLServer:Access Methods\Page Splits/sec
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\SQLServer:Plan Cache(*)\Cache Hit Ratio
\SQLServer:Plan Cache(*)\Cache Pages
\SQLServer:Plan Cache(*)\Cache Object Counts
\SQLServer:Plan Cache(*)\Cache Objects in use
\SQLServer:Transactions\Free Space in tempdb (KB)
\LogicalDisk(*)\% Disk Time
\LogicalDisk(*)\Avg. Disk Queue Length
\LogicalDisk(*)\Avg. Disk sec/Transfer
\LogicalDisk(*)\Avg. Disk sec/Read
\LogicalDisk(*)\Avg. Disk sec/Write
\LogicalDisk(*)\% Idle Time
\PhysicalDisk(*)\Current Disk Queue Length
\PhysicalDisk(*)\% Disk Time
\PhysicalDisk(*)\Avg. Disk Queue Length
\PhysicalDisk(*)\Avg. Disk sec/Transfer
\PhysicalDisk(*)\Avg. Disk sec/Read
\PhysicalDisk(*)\Avg. Disk sec/Write
\PhysicalDisk(*)\% Idle Time
\Network Interface(*)\Bytes Total/sec
\Network Interface(*)\Packets Received/sec
\Network Interface(*)\Packets Sent/sec
\Network Interface(*)\Bytes Received/sec
\Network Interface(*)\Bytes Sent/sec
\Network Interface(*)\Current Bandwidth
Tempdb相關
\SQLServer:Databases(tempdb)\Data File(s) Size (KB)
\SQLServer:Databases(tempdb)\Log File(s) Size (KB)
\SQLServer:Databases(tempdb)\Log File(s) Used Size (KB)
\SQLServer:Transactions\Free Space in tempdb (KB)
13.修改與確認blg黨與報表檔保留期間
在SQLPerfLogBaseline右鍵選擇Data Manager
14.修改限制設定
15.在Action頁籤，調整成自己要想要保留的規則，就完成所有設定
PS.這裡第一個設定預設為1 Day(s)就會把blg包成cab檔並刪除blg檔，所以若要用Performance Monitor來看Reports時就會看不到Report，可以考慮改成2 Day(s)，這樣就最少有一天的blg檔可以直接透過Reports節點來檢視。
16.可將目前的設定另存成XML的範本檔template
Reference:
Creating Data Collector Sets
https://technet.microsoft.com/en-us/library/cc749337(v=ws.11).aspx
Use SQL Server Objects
https://technet.microsoft.com/en-us/library/ms190382.aspx
沒有留言:
張貼留言