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.

 

 

 

 

Step2:-

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,

 

 

select

a.counterid,CounterDateTime,b.ObjectName,b.CounterName,b.InstanceName,DefaultScale,CounterValue from CounterData a

JOIN

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