Perfmon is one of the very reliable and handy tool for server-Admin and DBA. Most of the time DBA have to perform analysis on server resources like processor, memory, disk including SAN and other different sql server perfromance counters. As a DBA, I always find it handy to save all these counters in sql server database instead of other files. You can generate all kind of reports if you save in sql tables.
To save the performance counter in sql server, you have to perform following steps:
Step I(Create DNS ODBC Data Source)
1. Create new empty database MYDB in the SQL Server – ServerA
2. Create new system DNS ODBC Data Source from ODBC Data Source Administrator(Start-> All Programs ->
Administrative Tools -> DataSource ODBC and go System DNS tab)
3. Click Add and in pop-up, select SQL Server for driver.
4. Go next page and type sql Server Name and Name the DataSourceName – ‘PerfMonData’.
5. Next and keep default
6. Check the default database to: and select “MYDB” database from dropdown and Next.
Step II(Create Perfmon user Defined Data Collector)
1. Run Perfmon.exe (open RUN and type perfmon and enter)
2. Create New Data Collector Set under user Defined and right click User Defined and create New Data
Collector Set. In Data Collector set, provide Name and Select “Create Manually”
3. Check Performance Counter in ‘What type of data do you want to include?’
4. Add the Counter you are interested in, for this I just added Processor Time.
5. Next Page finsh.
6. Open the DataCollectory property and in Log format select ‘SQL’ and in Data source name select Data Source you created earlier ‘PerfMonData’
7. click apply.
8. Right click the New Data Collector Set ServerPerformance and click start.
9. voila!! performance counter started collecting in database MYDB.(I will post another thread for some of the important performance counters for sql server and how to generate reports out of these counters) Cheers!!