- To open Performance Monitor tool press “Windows” + “R” and type “perfmon” in the command prompt, then click “OK”:
- To create new Data Collector Set go to the “Data Collector Sets” -> “User Defined” item, right click and point to “New”, click “Data Collector Set”:
- The Create new Data Collector Set Wizard starts, enter a name for your new data collector, for example, “Base Performance Monitoring” and select “Create manually (Advanced)”option, click “Next”:
- In the next window select create data logs for Performance counter, click “Next”:
- In the new window change “Sample interval” to 5 seconds and click “Add” to open “Add Counters” dialogue box:
- To add counters click on the small arrow near the category name on the left pane, select counter name and click on the “Add” button. After that your counter should appear on the right pane “Added counters” like on the picture below:
Include following counters:
- Memory -> Available Mbytes;
- Network Interface -> Bytes total /sec (All instances);
- Network segment -> % Net Utilisation;
- Paging file -> % Usage (pagefile.sys instance);
- Physical Disk -> Avg. disk sec/read (_Total instance)
-> Avg. disk sec/write (_Total instance)
-> Disk reads/sec (_Total instance)
-> Disk writes/sec (_Total instance) - Processor -> % Processor Time (_Total + each core separately);
- SQL Server: Buffer Manager -> Page life expectancy;
- SQL Server: General Statistics -> User connections;
- SQL Server: Memory Manager -> Memory Grants Pending
-> Target Server Memory
-> Total Server Memory; - SQL Server: SQL Statistics -> Batch requests/sec
-> SQL Compilations/sec
-> SQL Recompilations/sec; - System -> Processor Queue Length;
*Note that if your SQL Server instance was not installed as default, category names for SQL Server will contain your SQL Server instance name with sign $, for example MSSQL$SQL2012: Buffer Manager.
Click “OK”.
Click “Next”, here you can review the path to the root directory which will contain data collected by the Data collector Set. Change this setting if you want to store your Data Collector Set data in a different location than the default. Browse to and select the directory, or type the directory name, then Click “Next”:
- After clicking “Next”, you can configure the Data Collector Set to run as a specific user. Click the Change button to enter the user name and password for a different user than the default listed.
- Select “Start this data collector set now” option if you want to start monitoring immediately, otherwise select “Save and close” and click “Finish”. After that newly created data collection set should appear in the “User Defined” folder:
- You can start/stop data collection by clicking on the green arrow/red square buttons or by clicking on the Start/Stop in the Action item in the Main Menu.
From my SQL Server DBA handbook.