Schedule Data Collection with Windows Performance Monitor



By:
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".

schedule perfmon data collector

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.

schedule perfmon data collector

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.

schedule perfmon data collector

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.

schedule perfmon data collector

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.

windows task scheduler showing perfmon scheduled

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.

stop perfmon scheduled task

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.

perfmon data collection setting
Additional Information

Last Update: 3/13/2019





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


get free sql tips
agree to terms


Learn more about SQL Server tools