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.
ScottAllison Not Sure if this is feasible yet since I have read somewhere this is not supported yet.
However I am book marking this thread for further information.
- Naturally 🙂
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 writtenSql 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.
