Performance counters from sys.dm_os_performance_counters

• SQL Guy 1

  SSCoach

  Points: 15981

  More actions

  January 5, 2011 at 1:41 pm

  #243223

  I am trying to use view sys.dm_os_performance_counters as a rart of my performance troubleshooting. Does anybody have experience with it ?

  What counters are most necessary for performance testing ?

  Which of them are cumulative over time and which are not ?

  What means instance_name: 000 and 001 ? We have just one SQL Server instance on the server.

  Why such important counters as Disk Bytes/sec, Avg Disk sec/Read, ...Write, Available MBytes are missing?

  Is there a guideline or or scripts available for this ?

  Thanks

• SQLBuddy123

  One Orange Chip

  Points: 28294

  More actions

  January 5, 2011 at 2:36 pm

  #1269761

  AFAIK, the dmv only captures counters related to SQL Server , not OS level counters like Disk, Mem and CPU.

  That's the inherent limitation of this dmv and so it can't be a single source for collecting the performance data.

  I would recommend you to run a perfmon counter log for an acceptable period of time with both System and SQL Counters and then analyze that data.

  Thank You,

  Best Regards,

  SQLBuddy