Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Setup Performance Monitor to always collect SQL Server performance statistics


By:   |   Read Comments (4)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


Problem

Very often when performance problems arise there are a few tools that I always first look at using to help troubleshoot SQL Server issues. One of these tools is Performance Monitor. This tool is usually run interactively and only for a short duration which does not give you a full picture of what is going on with your server. In addition, it is often the case that when you needed to collect the information the time has already passed and therefore the opportunity is gone. How can we setup Performance Monitor to collect data all of the time, so we can go back and review the data when needed?

Solution

Performance Monitor (perfmon) is a powerful tool that comes with all Microsoft operating systems. There are many other third party tools, but why not use the Performance Monitor tool which is included for free with your operating system? There are many different ways to configure and use this tool, but I would like to share the method that I have been using and it how it has worked for me.

One thing to remember is that there is not exact threshold for a lot of the counters to determine if there is an issue or not your server, so it is very important to collect performance counter data while the server is running healthy, so you can give yourself a baseline to measure against.


Start Perfmon
There are many ways to start perfmon and here are a few methods. One of my favorite is "Click Start, click Run, and then type perfmon.msc or perfmon in the Open box" . When it starts you will get a screen like the following.

Click the plus sign next to "Performance Logs and Alerts" to expand.

Click "Counter Logs"

On the menu select Action -> New Log Settings and enter a name that makes sense to you. I usually put either the server name or a generic name like "PerfMon". If you store these files in a central place for several servers, it helps to use the server name as the filename. For this example, I am just using a generic name and clicking "OK".

Now it will open a new window as below.


Setup PerfMon: There are three areas that you have to setup.

  1. Counters to monitor: what are the areas that you want to monitor

  2. Log file type: format for storing the data

  3. Schedule: how often you want to track and store the collected data


Setting up counters to monitor

Let's add some counters. Click "Add Counters..." to choose individual counters or if you wish you can choose "Add Objects...". One thing to note is that if you select "Add Objects..." you may add counters that you never need and it will not only waste storage, but also resources from the system, so I don't recommend it.

So, let's choose three counters for our example.

Counter 1:
Performance objects: Processor
Select counters from list: % Processor Time
Choose Select instance from list: _Total

Counter 2:
Performance objects: Memory
Select counters from list: Pages/sec
Choose Select instance from list: empty

Counter 3:
Performance objects: PhysicalDisk
Select counters from list: Avg. Disk Queue Length
Choose: All instances

If you are not sure what information the counter will give you, you can click "Explain", it will open another window and offer an explanation of the counter that you chose. Here is the example of Counter 3, Avg Disk Queue Length.

Once you are done, your screen should like like the below screenshot.

For production servers, obviously you don't want to collect data every 15 seconds which may put too much pressure on your server and also waste storage as well. Depending on what you do, you may want to choose to collect every 1 min to every 15min. I do not recommend setting longer then 10 min though, because that may not capture enough information to troubleshoot an issue. I will choose every 1 minute for this example.

Now, if you are collecting to the server locally, you do not need to set "Run As:" but if you are collecting data from a remote server, then you need to set the service account, which has proper permissions to collect the data.. The service account needs to have at least "Profiler system performance" rights from Local Security Settings. For more information, you can read this article: http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/gp/551.mspx?mfr=true


Setting "Log Files" type.
This will decide how the data will be stored once it is collected. For this example I chose "Text file (Comma delimited)" and chose "End file names with: yyyymmdd"

And then choose "Configure".

Tip> I strongly recommend changing "Locations" to a drive other than the C drive. I see this often where people set this up and then run out of space on the C drive which then impacts the O/S.


Schedule
This is how I usually setup my schedules.

Start Log At: 12:00:00 AM on FOLLOWING DAY and Stop Log After 1 days and choose "Start a new log file"

Here is the example, assume I set this up on 5/1/2008, here is the example how it will look like.

This will create one file each day with a filename like "D:\PerfLogs\PerfMon_20080502.csv".

Click "OK" to finish this step.

Tip> Before you setup the schedule, you can first run it manually by clicking this icon . This will let you make sure you are collecting the correct data before scheduling it to run.


For the test, check to make sure it created the file correctly.

To look at the data using Excel, just double click on the CSV file that was created to see if you are collecting the counters correctly.

Tip> Once you are done testing, go back to the "Schedule" section and reset the schedule correctly once again especially "start a new file" section.

Tip> Also setup a script to delete old files, so you don't run out of space on your file system.


Next Steps
  • This is the first step in collecting this information. Once you have this data collected it makes it a lot easier to troubleshoot issues in the future.
  • Now that you have setup Perfmon, you need to know how to read the counters, so look for future tips on how to use Perfmon data


Last Update:


signup button

next tip button



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips
Related Resources





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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, July 18, 2012 - 1:56:20 PM - Tariq Back To Top

Nice and easy. Thanks!


Wednesday, October 20, 2010 - 11:09:14 AM - Kun Lee Back To Top
Hi,

Thank you for the feedback and sorry about late reply. In my tip screenshot, there is a place that you can set "Run As:" and have you tried to set the service account to run that perfmon and make sure the service account has permission to write?

And regarding the 2nd question, you can't save as excel but you can save as ".csv" file and than you can open by using Excel.

I hope that answers to your questions.

Regards,

Kun

 


Thursday, October 14, 2010 - 10:50:44 AM - Pete Back To Top
great and easy to follow tip, thanks. One question, instead of sending the report to, as you say the D: drive, i have a multi server network, and i am trying to send the report to another server where files are kept, but it keeps saying unable to find path, yet i know it can see it. Also it only saves in notepad, how can i save to excel like in the instructions. Many thanks for any advice


Thursday, June 04, 2009 - 6:04:33 AM - kundratpavol Back To Top
Really nice article. We are monitoring our production server this way. However, perfmon database size is growing rapidly. That is why I am searching for some ideas how to cut down the size of the database with perfmon data. The question is what range of data to keep (originaly counters are monitored each minute), what kind of information to keep (average, min, max...) and after what period process data (2 month, 3 month..), what kind of means use for processing (trigger, job...) Regards. P.K.

Learn more about SQL Server tools