Monitor SQL Server using Performance Monitor on Windows Server 2008 R2

Skip to end of metadata

• Attachments:7
• Added by SSI KB Admin, last edited by SSI KB Admin on Feb 27, 2012  (view change)

Go to start of metadata

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
5. HOWTO: Change the User for the Data Collector Set
6. HOWTO: Change the Log file Location
7. HOWTO: Create a Data Collector Set Template
8. HOWTO: Use a Custom Template to Create a 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".

HOWTO: Change the User for the Data Collector Set

1. Open Performance Monitor (See Step 1).
2. Expand the "Data Collector Sets" node.
3. Expand the "User Defined" node.
4. Right click the Data Collector Set for which you wish to change the user.
5. Select "Properties".
6. Under the "General" tab, click the "Change" button found in the "Run As" section.
7. Provide a username and password for the account that will be running the Data Collector Set.
8. Click the "OK" button.
9. Click the "Apply" button.
10. Click the "OK" button.

HOWTO: Change the Log file Location

1. Open Performance Monitor (See Step 1).
2. Expand the "Data Collector Sets" node.
3. Expand the "User Defined" node.
4. Right click the Data Collector Set for which you wish to change the user.
5. Select "Properties".
6. Under the "Directory" tab, click the "Browse" button.
7. Select the folder location in the "Browse for Folder" dialog.
8. Click the "OK" button.
9. Click the "Apply" button.
10. Click the "OK" button.

HOWTO: Create a Data Collector Set Template

Once a custom Data Collector Set has been created, it can be saved as a template:

1. Open Performance Monitor (See Step 1).
2. Expand the "Data Collector Sets" node.
3. Expand the "User Defined" node.
4. Right Click on the Data Collector Set name that will be used as a template.
5. Left click "Save Template...".
6. Provide a file name.
7. OPTIONAL: Browse to a folder location where the template will be stored.

The template is stored as XML, making it easily editable in any text editor (IE: Notepad, Wordpad, etc). However, the counters are not entirely editable and will likely require recreation.

HOWTO: Use a Custom Template to Create a Data Collector Set

1. Open Performance Monitor (See Step 1).
2. Expand the "Data Collector Sets" node.
3. Right click "User Defined", and select "New > Data Collector Set".
4. Specify a unique name.
5. Select the "Create from a template (Recommended)" radio button.
6. Click the "Next" button.
7. Click the "Browse" button.
8. Navigate to the folder where the custom template is located.
9. Select the template file.
10. Click the "Open" button.
11. Click the "Next" button.
12. OPTIONAL: Change the log location.
13. Click the "Next" button.
14. OPTIONAL: Change the user account this Data Collector Set will use for credentials.
15. Click the "Finish" button.