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