Forum Discussion
SQL cluster performance counters
Sql is poorly designed when it comes to performance counters. Basically when you have SQL cluster you have to use named instance if I remember. Every named instance creates its own set of counters on the servers.
For example SQLSERVER:Access Methods becomes MSSQL$<instance name>:Access Methods
what you can do is to add the object name as MSSQL$*:Access Methods that way it will get the counter no matter what the name is of instance. so your end entry will be something like MSSQL$*:Access Methods(*)\Forwarded Records/sec
you get the idea for the rest of the counters.
I'm getting the configured SQL counters for all SQL servers except for the clusters (though I'm not yet entirely sure if that includes ALL clusters). The SQL counters include:
SQLAgent:Alerts Activated alerts
SQLAgent:Jobs Failed jobs
SQLAgent:Statistics SQL Server restarted
SQLSERVER:Access Methods Forwarded Records/sec
SQLSERVER:Access Methods Full Scans/sec
SQLSERVER:Access Methods Index Searches/sec
SQLSERVER:Buffer Manager Lazy writes/sec
SQLSERVER:Buffer Manager Page Writes/sec
SQLSERVER:Buffer Manager Page life expectancy
SQLSERVER:Buffer Manager Page lookups/sec
SQLSERVER:Buffer Manager Page reads/sec
SQLSERVER:Buffer Manager Total pages
SQLSERVER:Databases Active Transactions
SQLSERVER:Databases Backup/Restore Throughput/sec
SQLSERVER:Databases Data File(s) Size (KB)
SQLSERVER:Databases Log File(s) Size (KB)
SQLSERVER:Databases Log Flush Wait Time
SQLSERVER:Databases Log Flush Waits/sec
SQLSERVER:Databases Log Flushes/sec
SQLSERVER:Databases Log Growths
SQLSERVER:Databases Percent Log Used
SQLSERVER:Databases Transactions/sec
SQLSERVER:General Statistics User Connections
SQLSERVER:Latches Average Latch Wait Time (ms)
SQLSERVER:Latches Latch Waits/sec
SQLSERVER:Latches Total Latch Wait Time (ms)
SQLSERVER:Locks Lock Timeouts/sec
SQLSERVER:Locks Lock Waits/sec
SQLSERVER:Locks Number of Deadlocks/sec
SQLSERVER:Memory Manager Memory Grants Pending
SQLSERVER:Memory Manager Target Server Memory (KB)
SQLSERVER:Memory Manager Total Server Memory (KB)
SQLSERVER:SQL Statistics Batch Requests/sec
SQLSERVER:SQL Statistics SQL Compilations/sec
SQLSERVER:SQL Statistics SQL Re-Compilations/sec
SQLServer:Access Methods Table Lock Escalations/sec
SQLServer:Exec Statistics Distributed Query
SQLServer:Locks Number of Deadlocks/sec
SQLServer:SSIS Pipeline 13.0 Buffers spooled
SQLServer:SSIS Pipeline 13.0 Rows read
SQLServer:SSIS Pipeline 13.0 Rows written
Sql is poorly designed when it comes to performance counters. Basically when you have SQL cluster you have to use named instance if I remember. Every named instance creates its own set of counters on the servers.
For example SQLSERVER:Access Methods becomes MSSQL$<instance name>:Access Methods
what you can do is to add the object name as MSSQL$*:Access Methods that way it will get the counter no matter what the name is of instance. so your end entry will be something like MSSQL$*:Access Methods(*)\Forwarded Records/sec
you get the idea for the rest of the counters.
ScottAllison Yeah. It is more of SQL problem because there are similar problems when you deploy other services like reporting or integration services. Just bad architecture of their part. Thankfully LA data sources allows using wildcard as * to avoid adding counters per instance name. Not many people know that and I haven't blogged about it.