Thursday, February 19, 2009

Performance Monitor Counters commonly used for SQL Server 2000 / 2005 / 2008 performance analysis / measurement / monitoring

I'm reading: Performance Monitor Counters commonly used for SQL Server 2000 / 2005 / 2008 performance analysis / measurement / monitoringTweet this !
A consolidated list of performance counters that can be classified for use with SQL Server 2000 / SQL Server 2005 / SQL Server 2008 is as below:

Memory
Network Interface
Paging File
LogicalDisk
PhysicalDisk
Process
Processor
System
SQL Server:Access Methods
SQL Server:Backup Device
SQL Server:Broker Activation
SQL Server:Broker Statistics
SQL Server:Broker TO Statistics
SQL Server:Broker /DBM Transport
SQL Server:Buffer Manager
SQL Server:Buffer Node
SQL Server:Buffer Partition
SQL Server:Cache Manager
SQL Server:Catalog Metadata
SQL Server:CLR
SQL Server:Cursor Manager by Type
SQL Server:Cursor Manager Total
SQL Server:Database Mirroring
SQL Server:Databases
SQL Deprecated Features
SQL Server:Exec Statistics
SQL Server:General Statistics
SQL Server:Latches
SQL Server:Locks
SQL Server:Memory Manager
SQL Server:Plan Cache
SQL Server:Replication Agents
SQL Server:Replication Dist.
SQL Server:Replication LogReader
SQL Server:Replication Merge
SQL Server:Replication Snapshot
SQL Server:Resource Pool
SQL Server:SQL Errors
SQL Server:SQL Statistics
SQL Server:SSIS Service
SQL Server:Transactions
SQL Server:User Settable
SQL Server:Wait Statistics
SQL Server:Workload Group Stats


Special SQL Server 2008 Performance Counters:

SQL Server Databases: Tracked transactions/sec - Number of committed transactions recorded in the commit table for the database.

SQL Server Databases: Write Transactions/sec - Number of transactions which wrote to the database in the last second.

SQL Server Buffer Node: Remote node page lookups/sec - Number of lookup requests from this node which were satisfied from other nodes.

SQL Server Buffer Node: Local node page lookups/sec - Number of lookup requests from this node which were satisfied from this node.

SQL Server General Statistics: Connection reset/sec - Total number of connection resets per second.

SQL Server General Statistics: Tempdb recovery unit id unit - Number of duplicate tempdb recovery unit id generated

SQL Server General Statistics: Tempdb rowset id - Number of duplicate tempdb rowset id generated

SQL Server SQL Statistics: Misguided plan executions/sec - Number of plan executions per second in which a plan guide could not be honored during plan generation

SQL Server SQL Statistics: Guided plan executions/sec - Number of plan executions per second in which the query plan has been generated by using a plan guide.

Deprecated Features: Usage – this is a really nice counter which lists feature usage since last SQL Server startup



References: BOL, Benjamin-Wright Jones Blog, Jimmy-May Blog

1 comment:

Anonymous said...

How to monitor that a MSSQL replication is working ? Are there any performance counter to do so ?

Related Posts with Thumbnails