Using Performance Monitor to Record Counters

If you don’t have a centralized monitoring tool, you can log Performance Monitor counters to a file or to a SQL Server database. I recommend using a comma delimited file so that you can open it in your favorite program at a later date or even import it into a SQL Server database.

In the New Log Settings dialog box that opens, enter a name for the group of settings and click OK.

When the dialog box opens with the name you entered in step 3, select the General tab, click Add Counters, and add all the counters you want to capture.

Set the interval to a reasonable amount of time. The default is to sample the data every 15 seconds, which might be too often. Every 30 seconds to a minute should be fine—much more often than that might give you an inaccurate picture of your server.

Click the Log Files tab, and select a log file type of Text File (either comma delimited or tab delineated—just make a note of which one you select). If you’re logging a lot of counters to a database, some events might be missed. To change the location and base name for the incremented file, click Configure.

Select the Schedule tab. To start logging immediately (or at a specified time), select At in the Start log section and enter the time to start. In the Stop log section, you can opt to stop the logging manually (meaning the file will grow indefinitely), after a certain period of time (measured in seconds, minutes, hours, or days), or at a certain time. Keep in mind that if you’re logging to a file, you want to keep the file sizes manageable. I suggest setting the logging to stop after a certain period of time. Select After in the Stop log section, then select the Start a new log file checkbox, as Figure 1 in the main article shows.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More