Integration Microsoft SQL Server (agentless)

There are 2 steps to follow after selecting Infrastructure / MS SQL in your admin interface:

1. add your MS SQL server - please check the listener port if non-default or dynamic (no support for dynamic ports). More information can be found here: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port
2. Click Load/Reset MS SQL Dashboard to automatically import the Microsoft: Windows Host Overview Dashboard
   
   

When adding the SQL Server using the embedded connector of the Metrics & Logs appliance (agentless MS SQL monitoring), make sure to leave the default YES in the top line.

Make sure to create a MS SQL user having sufficient permissions:

USE master;
GO
CREATE LOGIN pamsql WITH PASSWORD = N’mystrongpassword’;
GO
GRANT VIEW SERVER STATE TO pamsql;
GO
GRANT VIEW ANY DEFINITION TO pamsql;
GO

That’s it

Collected metrics

Wait stats

• Wait time (ms) | I/O, Latch, Lock, Network, Service broker, Memory, Buffer, CLR, XEvent, Other, Total
• Wait tasks | I/O, Latch, Lock, Network, Service broker, Memory, Buffer, CLR, XEvent, Other, Total

Memory clerk

• Memory breakdown (%) | Buffer pool, Cache (objects), Cache (sql plans), Other
• Memory breakdown (bytes) | Buffer pool, Cache (objects), Cache (sql plans), Other

Database size

• Log size (bytes) | databases (included sysdb)
• Rows size (bytes) | databases (included sysdb)

Database IO

• Log writes (bytes/sec) | databases (included sysdb)
• Rows writes (bytes/sec) | databases (included sysdb)
• Log reads (bytes/sec) | databases (included sysdb)
• Rows reads (bytes/sec) | databases (included sysdb)
• Log (writes/sec) | databases (included sysdb)
• Rows (writes/sec) | databases (included sysdb)
• Log (reads/sec) | databases (included sysdb)
• Rows (reads/sec) | databases (included sysdb)

Database latency

• Log read latency (ms) | databases (included sysdb)
• Log write latency (ms) | databases (included sysdb)
• Rows read latency (ms) | databases (included sysdb)
• Rows write latency (ms) | databases (included sysdb)
• Log (average bytes/read) | databases (included sysdb)
• Log (average bytes/write) | databases (included sysdb)
• Rows (average bytes/read) | databases (included sysdb)
• Rows (average bytes/write) | databases (included sysdb)

Database properties

• Recovery Model FULL | databases (included sysdb)
• Recovery Model BULK_LOGGED | databases (included sysdb)
• Recovery Model SIMPLE | databases (included sysdb)
• State ONLINE | databases (included sysdb)
• State RESTORING | databases (included sysdb)
• State RECOVERING | databases (included sysdb)
• State RECOVERY_PENDING | databases (included sysdb)
• State SUSPECT | databases (included sysdb)
• State EMERGENCY | databases (included sysdb)
• State OFFLINE | databases (included sysdb)

OS Volume

• Volume total space (bytes) | logical volumes
• Volume available space (bytes) | logical volumes
• Volume used space (bytes) | logical volumes
• Volume used space (%) | logical volumes

CPU

• CPU (%) | SQL process, External process, SystemIdle

Performance metrics

• Point In Time Recovery
• Available physical memory (bytes)
• Average pending disk IO
• Average runnable tasks
• Average tasks
• Buffer pool rate (bytes/sec)
• Connection memory per connection (bytes)
• Memory grant pending
• Page File Usage (%)
• Page lookup per batch request
• Page split per batch request
• Readahead per page read
• Signal wait (%)
• Sql compilation per batch request
• Sql recompilation per batch request
• Total target memory ratio