How to store performance monitor data into SQL Server using Reliability and Performance Monitor

Exactly what the title says. I spent some time on figuring this out since there is not so much information on this.

Hopefully this will save you some time.

What you need to do first is to create a System DSN to you SQL Server.

Simply run odbcad32.exe from Start->Run. This opens the “ODBC Data Source Administrator”

Then select the “System DSN” tab and select “Add”, select the appropriate driver and call it, for example, “PerfLogDSN”.

Finally supply the name of the server you wish to store the data in and fill in the rest of the values as you see fit.

So, let’s get down to business.

Start the “Reliability and Performance Monitor”, this is can be done by typing “perfmon.exe” from Start->Run.

Then expand the node called “Data Collector Sets” and right click the sub node called “User Defined” and select New -> Data Collector Set.

Give it a name, for example, “PerfDataToSql” and select “Create manually” then Next.

Now select “Performance Counters” under the “Create Data logs” radio button then Next.

Select and add the performance counters you are interested in and then OK and then Next.

Leave the Root directory as it is, then Next.

Leave the “Save and Close” radio button selected and then Finish.

Now you should have your “PerfDataToSql” data collector set listed under the “User Defined” node and it should be in the Stopped state.

Select the “PerfDataToSql” node, this should list “DataCollectory01” in the right pane.

Right click “DataCollectory01” and select Properties and then the “Performance Counters” tab.

Change the “Log format” drop down to SQL, this should enable the “Data Source name” drop down, so select your create System DSN (“PerfLogDSN” in this case).

Select Apply/OK.

Now select the “PerfDataToSql” item in the left pane and right click and select “Start” or hit the big green arrow in the toolbar.

Finally, you may get different errors such as “Call to SQLExecDirect failed with %1”, this is most likely since the “PerfDataToSql” is set to run as System or any

other account that doesn’t have access to the SQL Server. If this is the case, then right click “PerfDataToSql” and select properties.

This will give you the option to change the Run As account, do this and use an account that has the proper rights, for example your windows account.

Happy tracing.

I did this from a Windows Server 2008 machine but I believe the steps are the same for Windows Vista and Windows 7 as well.

Comments

• Anonymous
  August 17, 2012
  Exactly what I needed to know.  Thank you.

• Anonymous
  March 10, 2013
  I had this working on Server 2003 and needed to know haow to do it in Server 2008. Thank you very much!

• Anonymous
  January 24, 2014
  Hi! After changing Run As account to my windows account credentials, I am still getting  “Call to SQLExecDirect failed with %1” error. Tables has been created my database but there is no data in them due this error. Kindly Help. Thanks in advance.