How to Schedule a job to collect SQL Server Performance Monitor (PerfMon) counters?
Please follow below steps if you would like to schedule a job to collect SQL Server (or any other OS related) performance counter over the spam of hours/days/etc. Here we will create a manual data collector set and will schedule it to run as per our requirement.
NOTE:- These steps will work on Windows Server 2008 or higher edition only
- Go to Start –> Run –> type “Perfmon” –> Enter. Below Screen will open.
- Expand Data Collector Sets à right click on “User Defined” –> New à Data Collector Set
- Give name to new data collector set and select “Create Manually”. Click Next.
- Select Create data logs and Performance counter. Then click Next.
- On this screen, select how frequently you want to collect the data. Then Click on Add button to add counters which need to be collected.
- This will open below window where you can select the counters to be collected.
In this example we will collect below events/counters to check SQL Server performance:
Selected Buffer cache hit ratio, Free pages, Lazy writes/sec and Page life expectancy events under SQLServer:Buffer Manager counter.
- Once the counters are selected Click OK.
- Here you can view the counters that will be collected. Add/Remove if required. Then click next when ready to go to next step.
- Select directory where you want to save the log and click next.
- You can set this data collector set to run under different account if required. Here we will just leave it default and just save this new data collector set. Click Finish.
- Now you will be able to see this new data collector set under “Data Collector Sets”.
- To start/stop Test_1, just right click on Test_1 and select Start/Stop. You can also schedule this by going to the properties of TEST_1 data collector set.