README.md

SQL Server plugin

This sqlserver plugin provides metrics for your SQL Server instance. It currently works with SQL Server versions 2008+. Recorded metrics are lightweight and use Dynamic Management Views supplied by SQL Server:

Performance counters  : 1000+ metrics from sys.dm_os_performance_counters
Performance metrics   : special performance and ratio metrics
Wait stats 			  : wait tasks categorized from sys.dm_os_wait_stats
Memory clerk		  : memory breakdown from sys.dm_os_memory_clerks
Database size         : databases size trend from sys.dm_io_virtual_file_stats
Database IO			  : databases I/O from sys.dm_io_virtual_file_stats
Database latency	  : databases latency from sys.dm_io_virtual_file_stats
Database properties   : databases properties, state and recovery model, from sys.databases
OS Volume             : available, used and total space from sys.dm_os_volume_stats
CPU				      : cpu usage from sys.dm_os_ring_buffers

Getting started :

You have to create a login on every instance you want to monitor, with following script:

USE master; 
GO
CREATE LOGIN [telegraf] WITH PASSWORD = N'mystrongpassword';
GO
GRANT VIEW SERVER STATE TO [telegraf]; 
GO
GRANT VIEW ANY DEFINITION TO [telegraf]; 
GO

Configuration:

# Read metrics from Microsoft SQL Server
[[inputs.sqlserver]]
  # Specify instances to monitor with a list of connection strings.
  # All connection parameters are optional. 
  # By default, the host is localhost, listening on default port (TCP/1433) 
  #    for Windows, the user is the currently running AD user (SSO).
  #    See https://github.com/denisenkom/go-mssqldb for detailed connection parameters.

  servers = [
	"Server=192.168.1.30;Port=1433;User Id=telegraf;Password=T$l$gr@f69*;app name=telegraf;log=1;",
    "Server=192.168.1.30;Port=2222;User Id=telegraf;Password=T$l$gr@f69*;app name=telegraf;log=1;"
	]

Measurement | Fields:

• 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
  • 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
• Performance counters
  • AU cleanup batches/sec | Value
  • ... 1000+ metrics See https://msdn.microsoft.com/fr-fr/library/ms190382(v=sql.120).aspx

Tags:

• All stats have the following tags:
  • servername (server name:instance ID)
  • type (type of stats to easily filter measurements)

Overview in Grafana:

General Activity

Memory

I/O

Disks

CPU

Full view