Welcome to the Codenotary Help Center 👋

Integration Microsoft SQL Server (agentless)

Microsoft SQL monitoring
D
Written by Dennis
Updated 8 months ago

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
Did this answer your question?