Performance Monitor on Windows Server 2008R2

Monitor SQL Server using Performance Monitor on Windows Server 2008 R2

Table of Contents

1. Open Performance Monitor (AKA perfmon)
2. Setup the Data Collector Set
3. Start the Data Collector Set
4. Stop the Data Collector Set

Step 1: Open Performance Monitor (AKA perfmon)

1. Press the Start + R keys to get the Run dialog.
2. Type “perfmon”:
    
3. Click the “OK” button.

Step 2: Setup the Data Collector Set

1. Left click the arrow found to the left of "Data Collector Sets" to expand the tree:
   
2. Right click "User Defined", and select "New > Data Collector Set".
3. Specify a unique name for the Data Collector Set:
   
4. Select the "Create manually (Advanced)" radio button.
5. Click the "Next" button.
6. Check off "Performance counter", found under the "Create data logs":
    
7. Click the "Next" button.
8. Click the "Add" button.
9. OPTIONAL: If using Performance Monitor to watch a remote host.
   1. Type the hostname into the “Select counters from computer” text field:
       
   2. Press the Tab key to trigger the dialog to query the remote host specified for available counters, and refresh the list of counters (can take a couple of minutes).
10. Scroll to "MSSQL$[appropriate instance name]: General Statistics".
11. Click the "+" to expand the node (found to the right of "MSSQL$[appropriate instance name]: General Statistics)
     
12. Select "User Connections".
13. Click the "Add" button.
14. Scroll to "MSSQL$[appropriate instance name]: Locking".
15. Click the "+" to expand the node (found to the right of "MSSQL$[appropriate instance name]: Locking)
     
16. Select "Average Wait Time (ms)".
17. Select "Database" for "Instances of selected object".
18. Click the "Add" button.
19. Click the "OK" button.
20. OPTIONAL: Change the log location.
21. Click the "Next" button.
22. OPTIONAL: Change the user account this Data Collector Set will use for credentials.
23. Click the "Finish" button.

Step 3: Start the Data Collector Set

1. Right click on the Data Collector Set created in Step 2.
2. Select "Start".

Step 4: Stop the Data Collector Set

1. Right click on the Data Collector Set created in Step 2.
2. Select "Stop".