locked
Perfmon RRS feed

  • Question

  • I want to create a report of PERFMON and save it in an xls sheet and mail it at regular intervals. I know we can schedule the PERFMON, it gets saved in ' Performance Monitor File (blg) format'. Is there a way where I can get this reading in an XLS file and get it mailed to an email id..

    Thursday, January 2, 2014 3:37 PM

Answers

  • Hello Sudhakar,

    If you want to do this one method i can suggest id Use DMV

    select * from Sys.dm_os_performance_counters filter out your counters using In clause or where clause and look for column cntr_value .Send the output in excel sheet this might help.

    example

    select * 
    FROM sys.dm_os_performance_counters
    WHERE OBJECT_NAME = 'SQLServer:Databases'
    AND counter_name = 'Transactions/sec'
    AND instance_name = 'db_name' ...database name
    
    


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Friday, January 3, 2014 1:16 PM
    • Marked as answer by Sofiya Li Monday, January 13, 2014 9:14 AM
    Thursday, January 2, 2014 4:12 PM
  • All counters for the Objects : Memory, Physical Disk and System.. is there a way...

    Hello,

    Below link would be helpful

    http://simplesqlserver.com/2013/08/13/sys-dm_os_perfomance_counters-demystified/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by Sofiya Li Monday, January 13, 2014 9:14 AM
    Friday, January 3, 2014 1:42 PM

All replies

  • Hello Sudhakar,

    If you want to do this one method i can suggest id Use DMV

    select * from Sys.dm_os_performance_counters filter out your counters using In clause or where clause and look for column cntr_value .Send the output in excel sheet this might help.

    example

    select * 
    FROM sys.dm_os_performance_counters
    WHERE OBJECT_NAME = 'SQLServer:Databases'
    AND counter_name = 'Transactions/sec'
    AND instance_name = 'db_name' ...database name
    
    


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Friday, January 3, 2014 1:16 PM
    • Marked as answer by Sofiya Li Monday, January 13, 2014 9:14 AM
    Thursday, January 2, 2014 4:12 PM
  • Thank you very much @shanky... These are all SQL Server counters, I would also like to have counters for Memory, Physical Disk and System, is there a way where I can get these readings.
    Friday, January 3, 2014 4:54 AM
  • Thank you very much @shanky... These are all SQL Server counters, I would also like to have counters for Memory, Physical Disk and System, is there a way where I can get these readings.

    What counters specifically can you please tell do you want to monitor SQL server or windows ?

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Friday, January 3, 2014 5:16 AM
  • All counters for the Objects : Memory, Physical Disk and System.. is there a way...
    Friday, January 3, 2014 1:25 PM
  • All counters for the Objects : Memory, Physical Disk and System.. is there a way...

    Hello,

    Below link would be helpful

    http://simplesqlserver.com/2013/08/13/sys-dm_os_perfomance_counters-demystified/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by Sofiya Li Monday, January 13, 2014 9:14 AM
    Friday, January 3, 2014 1:42 PM
  • I know we can schedule the PERFMON, it gets saved in ' Performance Monitor File (blg) format'. Is there a way where I can get this reading in an XLS file and get it mailed to an email id..

    You can use the command-line relog.exe utility to transform the binary pmon file to a CSV file (which Excel can open).  Type "relog /?" at a command prompt for details and examples.  I think all of the necessary steps can be done in an SSIS package via a scheduled job.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, January 3, 2014 1:45 PM
  • Thank you for the reference to my blog here.  Unfortunately, I feel that this method may not cover everything he's looking for.  For instance, you can't get the CPU load on the box through sys.dm_os_performance_counters. 

    Sudhakar, if you are looking for data that's not in sys.dm_os_performance_counters then you may be able to set up a powershell script to query WMI and save that information in SQL Server without going through the extra steps of having PERFMON query WMI, save it to its file, read that file, then put it in SQL.

    Friday, January 3, 2014 3:32 PM
  • Thank you for the reference to my blog here.  Unfortunately, I feel that this method may not cover everything he's looking for.  For instance, you can't get the CPU load on the box through sys.dm_os_performance_counters. 

    Sudhakar, if you are looking for data that's not in sys.dm_os_performance_counters then you may be able to set up a powershell script to query WMI and save that information in SQL Server without going through the extra steps of having PERFMON query WMI, save it to its file, read that file, then put it in SQL.

    You are welcome I actually found it interesting and though it might be helpful to OP.I agree with you that you did not covered all counters.

    Sudhakar  I also want to mention there is no point in capturing each and every Perfmon counter you might not be able to deduce ,you migth get confused so just focus on specific counters


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, January 3, 2014 4:02 PM