Friday, 11 October 2013

Store Perfom Data Collector data to SQL Server

As a DBA we frequently run perfmon on our servers to monitor performance of the SQL Servers, In this section I am going to write how to schedule a perfmon data collector to collect the perfmon data to a SQL server destination.
Step 1:-
Open Perfmon from the SQL server you want to collect the counters, Perfmon can also be collected remotely but i ave used local server in this exercise.




Right Click Data Collector Set->User Defined -> New -> Data Collector set


Step 3:-
Select a name for the collector and chose manual configuration as below

 Step 4:-
Choose Create data logs->Performance Counter


 Step 5:-
 Choose the Interval in which the data needs to be collected and Click Add to select the counters 

 Select the count you wish to monitor and Click OK

Step 6:- 
Click Next, Don't worry about the Root directory at this point


Step 7:-
Choose the  account in which the collector jobs needs to run, preferably the account which has access to SQL server and WMI and Finish the configuration



Now you will be able to see the Data Collector set up in the Perfmon


Step 8:-
Next step is to create a ODBC for SQL server where the data needs to be stored, Go to Start->Administrative Tools->ODBC Data Source and choose System DSN tab.



Step 9:-
Click Add to create a new data source and select SQL Server data source, Please do not choose the Native client data sources, there is a known error with the Native client accessing Permon SQL server data and usually error out as "Call to SQLExecDirect failed with %1" when you start the data sets so make sure you choose SQL Server and proceed to create the DSN.



 Choose the database to which the Perfmon data needs to be stored as default database.




Step 10:-
Now go back to Permon data Set, go to you collector set "SQL Server Usage" in my case
and right click on DataCollector01 and properties.



Step 11:-
In the Properties screen choose the format as "SQL" which should enable your Data Source Name drop down box, Check if the system DSN you created is available in the dropdown box and choose it as the data source and Click apply and OK.


 Step 12:-
Now all the setup has been completed just right click on your collector set and click start which should start collecting the permon data to SQL Server.


To Verify the SQL data go to the SQL database to which the data is collected and execute the following query,
a.counterid,CounterDateTime,b.ObjectName,b.CounterName,b.InstanceName,DefaultScale,CounterValue from CounterData a
CounterDetails b ON a.CounterID = b.CounterID
You can make this database as DW and use BI tools to create reports to SSRS and or any other reporting tools


  1. replica rolex orologi, combining elegant style and cutting-edge technology, a variety of styles of replica rolex gmt master ii orologi, the pointer walks between your exclusive taste style.

  2. If you don't mind, then continue this excellent work and expect more from your great blog posts
    machine learning masters

  3. This is my first time i visit here and I found so many interesting stuff in your blog especially it's discussion, thank you.

  4. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one.
    full stack web development course malaysia

  5. It is different from the data insight aspect. Algorithms are used to develop data, whereas the executives make better decisions about the product using data insight.
    data science course in lucknow

  6. This comment has been removed by the author.