Forum Discussion
SQL cluster performance counters
Good morning everyone,
Our organization is nearly done with our migration from SCOM to Azure Monitor--it's been quite a ride.
I've run into a challenge that I can't seem to find an answer for. We have some on-premise SQL clusters that we've added the MMA to and moved to Azure Monitor. However, they do not appear to be pulling SQL performance counters. I suspect this is due to the virtual nature of the SQL cluster:
SQL Server name = SERVERV001
Physical nodes = SERVERP001a and SERVERP002a
Since the virtual name--SERVERV001--is not added to Log Analytics, I have to rely on the physical nodes--SERVERP001a and SERVERP002a--to view performance counters--except SQL counters.
How does one circumvent this issue?
Thanks in advance!
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.
6 Replies
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 written
