Forum Discussion

ScottAllison's avatar
ScottAllison
Iron Contributor
Sep 30, 2019

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!

  • ScottAllison 

    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.

  • GouravIN's avatar
    GouravIN
    Brass Contributor

    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.

      • ScottAllison's avatar
        ScottAllison
        Iron Contributor
        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

Resources