sys.dm_os_performance_counters

I'll admit, PerfMon is a little high-maintenance at times.  Of course, I can use it, but I have always preferred the native SQL approach.  If I can use tSQL to find what I'm looking for, I'm going to do it.  When I began preparing for the upgrade from v2000 to v2008, and I learned of the DMVs, I could only say 'wow'.    

DMV - Dynamic Management Views (and Functions), new in v2005 and later, return server state information that can be used to monitor the health of a SQL instance, diagnose problems, and aid with performance tuning.  (http://msdn.microsoft.com/en-us/library/ms188754.aspx)

There are tons of them, folks.  Server-scoped, database-scoped, I/O related, database mirroring related, and so on.  (We'll reference the Mirroring DMVs later.)  But, from a performance perspective, take a look at sys.dm_os_performance_counters.  With a single select here, you will have sight on several of the PerfMon counter statistics for SQL Server.  Take a look at the twenty-seven different types of statistics available:

SELECT

      [object_name] 

FROM

      sys.[dm_os_performance_counters] 

GROUP BY

      [object_name]

ORDER BY

      [object_name];

These are instance and database-specific, just like you might be watching in Performance Monitor.  Buffer Manager, Access Methods, Latches, Locks, Memory Manager.... and many more.

There are many different uses of this particular DMV.  I use it a lot for tracking memory usage, like this:

SELECT

      object_name,

      counter_name,

      instance_name,

      cntr_type,

      cntr_value

FROM

      sys.dm_os_performance_counters

WHERE

      counter_name = 'Total Memory (KB)'

      OR counter_name = 'Target Memory (KB)';

NOTE:  The Total value returned is not how much SQL is using.  It is how much SQL is using for the buffer pool.  Also, the Target value returned is the maximum that SQL can use. 

I actually use that one on a scheduled basis, courtesy SQL Server Agent.  It just helps me monitor how SQL Server is handling, or managing memory.  As I said, there are many uses of this DMV.  Here's another example that reports the log space used in MB, for the given database:

SELECT

      cntr_value/1024.0 [LogSpaceUsed MB]

FROM

      sys.dm_os_performance_counters

WHERE

      counter_name = 'Log File(s) Used Size (KB)'

      AND instance_name = 'databasename' <<< You'll need to change this.

Check out BOL.  This DMV is very, very good, and there are far more ways to use it.