sys.dm_os_performance_counters Explained

See link for more details: http://rtpsqlguy.wordpress.com/2009/08/11/sys-dm_os_performance_counters-explained/
and another nice one: http://kswain.blogspot.com/2008/04/sysdmosperformancecounters-dynamic.html
The Five Values Of Cntr_Type
1. Cntr_Type = 65792
This is the easiest one, because what you get is the counter’s actual value, the whole value, and nothing but the value. For example, the SQLServer:Buffer Manager / Total pages counter is of this type. It shows the number [...]

Performance Counters

SQL Server 2005 Database Engine Performance Counters

SQLServer:Access Methods
SQLServer:Buffer Manager
SQLServer:Buffer Node
SQLServer:Buffer Partition

AU cleanup batches/sec                          
AWE lookup maps/sec                       
Database pages                                         
Free list empty/sec                               

AU cleanups/sec                                      
AWE stolen maps/sec                        
Foreign pages                                             
Free list requests/sec                          

By-reference [...]

Highest IO Queries in SQL Server

SELECT TOP 50
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
substring (qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -    qs.statement_start_offset)/2)
as query_text,
qt.dbid,
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
ORDER BY [Avg IO] DESC

SQL Server Performance Counters Analaysis

Great document.
http://publib.boulder.ibm.com/tividd/td/ITMD/SC23-4852-00/en_US/HTML/mssql511rg02.htm#ToC_94

Performance Counters 2

SQL Server Buffer Manager provides the following counters:
AWE lookup maps / sec
Shows the number of times per second data pages requested by SQL Server were found in the buffer pool and then mapped as AWE memory and made part of server’s virtual address space.
AWE stolen maps / sec
Shows the number of times data pages were [...]

Performance Counters

SQL Server Performance Counters

Performance Counter Name

Comments

1

SQLServer:Access Methods - Full Scans / sec

Value greater than 1 or 2 indicates that we are having table / Index page scans. We need to analyze how this can be avoided.

2

SQLServer:Access Methods - Page Splits/sec

Interesting [...]