By: Ben Snaidero
Overview
Starting and stopping a data collector set manually is the simplest way to gather data, but not always the most convenient. There have been many times where I have encountered an issue on a server that always seemed to happen at the same time of day (usually the middle of the night). It's cases like this where you can use Performance Monitor to schedule the running of your data collector set so that you can look at the data gathered at a later time. When scheduling dates/times for your data collector set to run, it's important to note that you will need to also define a stop condition. Without this it would run indefinitely. To add a stop condition we can right-click on the data collector set name and select "Properties".
Once selected the following window will open where we can select the "Stop Condition" tab. In most cases I just set an overall duration as shown below. There is also the additional "Limits" section which allows you to define limits which gives you the ability to split up the data collection in multiple files, either by duration or file size.
Once that has been added we can go back to the "Schedule" tab and define when to start this data collector set. To create a new schedule we click the "Add" button as shown below.
Another window will then open which allows you to define when the data collection will start. In the example below, we are going to initiate a data collection every day at 1:00am for a week. Note that it is not required that to set an expiration date and if it is omitted the schedule will run indefinitely. Once you have the scheduled defined click "OK" to save it.
Stopping a Perfmon Scheduled Data Collector Set
In order to stop a scheduled data collector set that is currently running we have two different options. First, we can use the same method we used above when we started the data collector set on demand through Perfmon. The other method involves using the "Task Scheduler". If we open "Task Scheduler" we can see that under the "Task Scheduler Library" > "Microsoft" > "Windows" > "PLA" folder we have a scheduled task that is defined to run the data collector set as we specified in Perfmon.
To stop a running data collector set we simply right-click on the task name and select "End" (just as we would do for any other task defined in Task Scheduler). You can also disable tasks here or add other triggers. For example, we can schedule a startup trigger which is not available when scheduling through Perfmon and capture our counters for some time after a server reboot.
Frequency of Data Sample with Performance Monitor
Another important factor to consider with data collection is the frequency. If you are sampling very frequently you have to make sure to either set the duration of the data collector fairly short or set a limit on the file size. If the file gets too large it can become difficult to work with. You can read the following link, how often should PerfMon sample, which provides a good guideline for setting the sample interval based on the frequency of the issue you are trying to troubleshoot. To change the frequency with which you are sampling the data we need to open the "Properties" dialog of our data collector as shown below. You can see in this screenshot, circled in red, where we can update this sample interval.
Additional Information
- Automate Performance Monitor Statistics Collection for SQL Server and Windows
- Setup Performance Monitor to Always Collect SQL Server Performance Statistics
