Setting up Windows Performance Monitor Templates

To log valuable information about your database server such as disk, processor, memory and network, it is important to configure and schedule the performance counter logging. In the following steps, you will configure the database server for performance counter logging. This information will be logged to performance counter log files.

First we can setup the template for the SQL Server:

1. If you are using a Named instance of SQL Server, other than default MSSQLSERVER, follow these steps, otherwise go to step 6:
2. Browse to the Server2008_SQL_Named_Instance.xml file under [DynamicsPerf 2.00\DynamicsPerf\Windows Perfmon Scripts]
3. Open the file in Notepad
4. Replace [InstanceName] with the actual name of your named SQL instance: <Counter>\MSSQL$InstanceName:Access Methods\Forwarded Records/sec</Counter>
5. Save the file
6. Start > Run > Perfmon
7. Expand Data Collector Set
8. Right click User Defined and select New > Data Collector Set
9. 
10. Name it “SQL Server Performance”
11. Select the “Create from a template” option.
12. Click Next
13. Select “System Performance” and click Browse to browse to the Server2008_SQL_Default_Instance .xml file if you are using a Default SQL Server or the Server2008_SQL_Named_Instance.xml file if you are using a Named SQL Server
14. 
15. Click Finish
16. Right-click on the SQL Server Performance data collector set and click Properties
17. By default, the performance counter logs will log to C:\perflogs. If you wish to change this path, follow these steps. Otherwise, go to step 18:
18. Select the Directory tab
19. Browse to a local root directory to where you want to store the performance counter logs.
20. Select the Schedule tab
21. Select beginning date as of today and leave the rest as default so it will run continuously without an end date.
22. 
23. Click OK to close Folder action window
24. Ensure All schedules enabled checkbox is checked
25. Click OK to close window
26. You can also set up a condition to stop the logging with specific condition on file size and duration. For example, you can click Stop Condition and choose the restart the logging if the file size reaches 500,000 MB.
27. 
28. If you right-click the Template [SQL Server Performance] and select Data Manager, you can also change the default settings.