Skip to content

Latest commit

 

History

History
246 lines (209 loc) · 16.9 KB

telegraf-sqlserver.md

File metadata and controls

246 lines (209 loc) · 16.9 KB
Raw

telegraf/sqlserver

Monitor Type: telegraf/sqlserver (Source)

Accepts Endpoints: Yes

Multiple Instances Allowed: Yes

Overview

This monitor reports metrics about Microsoft SQL servers. This monitor is based on the telegraf sqlserver plugin. More information about the telegraf plugin can be found here.

You will need to create a login on the SQL server for the monitor to use. You can create this login by executing the following commands in an a SQL client while logged in as an administrator.

USE master;
GO
CREATE LOGIN [signalfxagent] WITH PASSWORD = N'<YOUR PASSWORD HERE>';
GO
GRANT VIEW SERVER STATE TO [signalfxagent];
GO
GRANT VIEW ANY DEFINITION TO [signalfxagent];
GO

Troubleshooting:

On some Windows based SQL server distributions TCP/IP has been disabled by default. This behavior has been observed on Azure SQL server instances. You may need to explicitly turn on TCP/IP for the SQL server if you see error messages simillar to the following.

Cannot read handshake packet: read tcp: wsarecv: An existing connection was forcibly closed by the remote host.
  1. Verify agent configurations are correct.
  2. Ensure TCP/IP is enabled for the SQL server by going to Start -> Administrative Tools -> Computer Management
  3. In the Computer Management side bar, drill down to Services and Applications -> SQL Server Configuration Manager -> SQL Server Network Configuration
  4. Select Protocols for <YOUR SQL SERVER NAME>.
  5. In the protocol list to the right, right-click on the TCP/IP protocol and enable it.

Sample YAML configuration:

monitors:
 - type: telegraf/sqlserver
   host: hostname
   port: 1433
   userID: sa
   password: P@ssw0rd!
   appName: signalfxagent

Configuration

To activate this monitor in the Smart Agent, add the following to your agent config:

monitors:  # All monitor config goes under this key
 - type: telegraf/sqlserver
   ...  # Additional config

For a list of monitor options that are common to all monitors, see Common Configuration.

Config option Required Type Description
host yes string
port yes integer
userID no string UserID used to access the SQL Server instance.
password no string Password used to access the SQL Server instance.
appName no string The app name used by the monitor when connecting to the SQLServer. (default: signalfxagent)
queryVersion no integer The version of queries to use when accessing the cluster. Please refer to the telegraf documentation for more information. (default: 2)
azureDB no bool Whether the database is an azure database or not. (default: false)
excludedQueries no list of strings Queries to exclude possible values are PerformanceCounters, WaitStatsCategorized, DatabaseIO, DatabaseProperties, CPUHistory, DatabaseSize, DatabaseStats, MemoryClerk VolumeSpace, and PerformanceMetrics.
log no unsigned integer Log level to use when accessing the database (default: 1)

Metrics

These are the metrics available for this monitor. Metrics that are categorized as container/host (default) are in bold and italics in the list below.

  • sqlserver_database_io.read_bytes (gauge)
    Bytes read by the database.

  • sqlserver_database_io.read_latency_ms (gauge)
    Latency in milliseconds reading from the database.

  • sqlserver_database_io.reads (gauge)
    Number of reads from the database.

  • sqlserver_database_io.write_bytes (gauge)
    Bytes written to the database.

  • sqlserver_database_io.write_latency_ms (gauge)
    Latency in milliseconds writing to the database.

  • sqlserver_database_io.writes (gauge)
    Number of writes to the database.

  • sqlserver_memory_clerks.size_kb.bound_trees (gauge)
    Size in KB of bound trees memory clerk.

  • sqlserver_memory_clerks.size_kb.buffer_pool (gauge)
    Size in KB of buffer pool memory clerk.

  • sqlserver_memory_clerks.size_kb.connection_pool (gauge)
    Size in KB of connection pool memory clerk.

  • sqlserver_memory_clerks.size_kb.general (gauge)
    Size in KB of general memory clerk.

  • sqlserver_memory_clerks.size_kb.in-memory_oltp (gauge)
    Size in KB of in in-memoory oltp memory clerk.

  • sqlserver_memory_clerks.size_kb.log_pool (gauge)
    Size in KB of log pool memory clerk.

  • sqlserver_memory_clerks.size_kb.memoryclerk_sqltrace (gauge)
    Size in KB of sql trace memory clerk.

  • sqlserver_memory_clerks.size_kb.schema_manager_user_store (gauge)
    Size in KB of user store schema manager memory clerk.

  • sqlserver_memory_clerks.size_kb.sos_node (gauge)
    Size in KB of sos node memory clerk.

  • sqlserver_memory_clerks.size_kb.sql_optimizer (gauge)
    Size in KB of SQL optimizer memory clerk.

  • sqlserver_memory_clerks.size_kb.sql_plans (gauge)
    Size in KB of sql plans memory clerk.

  • sqlserver_memory_clerks.size_kb.sql_reservations (gauge)
    Size in KB of sql reservations memory clerk.

  • sqlserver_memory_clerks.size_kb.sql_storage_engine (gauge)
    Size in KB of sql storage engine memory clerk.

  • sqlserver_memory_clerks.size_kb.system_rowset_store (gauge)
    Size in KB of system rowset store memory clerk.

  • sqlserver_performance.active_memory_grant_amount_kb (gauge)
    Amount of active memory in KB granted.

  • sqlserver_performance.active_temp_tables (gauge)
    Number of active temporary tables.

  • sqlserver_performance.background_writer_pages_sec (gauge)
    Rate per second of pages written in the background.

  • sqlserver_performance.backup_restore_throughput_sec (gauge)
    Rate per second of backup/restore throughput.

  • sqlserver_performance.batch_requests_sec (gauge)
    Rate per second of batch requests.

  • sqlserver_performance.blocked_tasks (gauge)
    Number of blocked tasks.

  • sqlserver_performance.buffer_cache_hit_ratio (gauge)
    Buffer cache hit ration.

  • sqlserver_performance.bytes_received_from_replica_sec (gauge)
    Rate per second of bytes received from replicas.

  • sqlserver_performance.bytes_sent_to_replica_sec (gauge)
    Rate per second of bytes sent to replicas.

  • sqlserver_performance.bytes_sent_to_transport_sec (gauge)
    Rate per second of bytes sent to transports.

  • sqlserver_performance.checkpoint_pages_sec (gauge)
    Rate per second of checkpoint pages.

  • sqlserver_performance.cpu_limit_violation_count (gauge)
    Number of cpu limit violations.

  • sqlserver_performance.cpu_usage_pct (gauge)
    CPU usage percentage.

  • sqlserver_performance.cpu_usage_time (gauge)
    CPU usage time.

  • sqlserver_performance.data_file_size_kb (gauge)
    Size in KB of data files.

  • sqlserver_performance.disk_read_bytes_sec (gauge)
    Rate per second of bytes from disk.

  • sqlserver_performance.disk_read_io_sec (gauge)
    Rate per second of read operations from disk.

  • sqlserver_performance.disk_read_io_throttled_sec (gauge)
    Rate per second of throttled read operations.

  • sqlserver_performance.disk_write_bytes_sec (gauge)
    Rate per second of bytes written to disk.

  • sqlserver_performance.disk_write_io_sec (gauge)
    Rate per second of write operations to disk.

  • sqlserver_performance.disk_write_io_throttled_sec (gauge)
    Rate per second of write operations throttled.

  • sqlserver_performance.errors_sec (gauge)
    Rate of errors per second.

  • sqlserver_performance.flow_control_sec (gauge)
    Rate per second of flow control.

  • sqlserver_performance.flow_control_time_ms_sec (gauge)
    Rate per second of ms of flow control time.

  • sqlserver_performance.forwarded_records_sec (gauge)
    Rate per second of record forwarding.

  • sqlserver_performance.free_list_stalls_sec (gauge)
    Rate per second of stalled free list.

  • sqlserver_performance.free_space_in_tempdb_kb (gauge)
    Free space in KB of tempdb.

  • sqlserver_performance.full_scans_sec (gauge)
    Rate per second of full scans.

  • sqlserver_performance.index_searches_sec (gauge)
    Rate per second of index searches.

  • sqlserver_performance.latch_waits_sec (gauge)
    Rate per second of latch waits.

  • sqlserver_performance.lazy_writes_sec (gauge)
    Rate per second of lazy writes.

  • sqlserver_performance.lock_timeouts_sec (gauge)
    Rate per second of lock timeouts.

  • sqlserver_performance.lock_wait_count (gauge)
    Number of lock waits.

  • sqlserver_performance.lock_wait_time (gauge)
    Lock wait time.

  • sqlserver_performance.lock_waits_sec (gauge)
    Rate per second of lock waits.

  • sqlserver_performance.log_apply_pending_queue (gauge)
    Size of the log apply pending queue.

  • sqlserver_performance.log_apply_ready_queue (gauge)
    Size of log apply ready queue.

  • sqlserver_performance.log_bytes_flushed_sec (gauge)
    Rate per second of log bytes flushed.

  • sqlserver_performance.log_bytes_received_sec (gauge)
    Rate per second of log bytes received.

  • sqlserver_performance.log_file_size_kb (gauge)
    Size in KB of log file.

  • sqlserver_performance.log_file_used_size_kb (gauge)
    Size in KB of log file used.

  • sqlserver_performance.log_flush_wait_time (gauge)
    Time spent flushing the log.

  • sqlserver_performance.log_flushes_sec (gauge)
    Rate per second of log flushes.

  • sqlserver_performance.log_send_queue (gauge)
    Size of the log send queue.

  • sqlserver_performance.logins_sec (gauge)
    Rate of logins per second.

  • sqlserver_performance.logouts_sec (gauge)
    Rate of logouts per second.

  • sqlserver_performance.memory_broker_clerk_size (gauge)
    Size of memory broker clerk.

  • sqlserver_performance.memory_grants_outstanding (gauge)
    Number of outstanding memory grants.

  • sqlserver_performance.memory_grants_pending (gauge)
    Number of pending memory grants.

  • sqlserver_performance.number_of_deadlocks_sec (gauge)
    Rate of deadlocks per second.

  • sqlserver_performance.page_life_expectancy (gauge)
    Page life expectancy.

  • sqlserver_performance.page_lookups_sec (gauge)
    Rate of page look ups per second.

  • sqlserver_performance.page_reads_sec (gauge)
    Rate of page reads per second.

  • sqlserver_performance.page_splits_sec (gauge)
    Rate of page splits per second.

  • sqlserver_performance.page_writes_sec (gauge)
    Rate of page writes per second.

  • sqlserver_performance.pct_log_used (gauge)
    Percentage of log used.

  • sqlserver_performance.processes_blocked (gauge)
    Number of blocked processes.

  • sqlserver_performance.query (gauge)
    User settable performance counters

  • sqlserver_performance.queued_request_count (gauge)
    Number of queued requests.

  • sqlserver_performance.queued_requests (gauge)
    Average number of queued requests.

  • sqlserver_performance.readahead_pages_sec (gauge)
    Rate per second of read ahead pages.

  • sqlserver_performance.receives_from_replica_sec (gauge)
    Rate receives from replicas per second.

  • sqlserver_performance.recovery_queue (gauge)
    Size of recovery queue.

  • sqlserver_performance.redone_bytes_sec (gauge)
    Rate of redone bytes per second.

  • sqlserver_performance.reduced_memory_grant_count (gauge)
    Number of reduced memory grants.

  • sqlserver_performance.request_count (gauge)
    Number of requests.

  • sqlserver_performance.requests_completed_sec (gauge)
    Rate of completed requests per second.

  • sqlserver_performance.resent_messages_sec (gauge)
    Rate of resent messages per second.

  • sqlserver_performance.sends_to_replica_sec (gauge)
    Rate of sends to replicas per second.

  • sqlserver_performance.sends_to_transport_sec (gauge)
    Rate of sends to transports per second.

  • sqlserver_performance.sql_compilations_sec (gauge)
    Rate of sql compilations per second.

  • sqlserver_performance.sql_re-compilations_sec (gauge)
    Rate of sql recompilations per sec.

  • sqlserver_performance.target_server_memory_kb (gauge)
    Size of target server memory in KB.

  • sqlserver_performance.temp_tables_creation_rate (gauge)
    Rate of temporary table creations.

  • sqlserver_performance.temp_tables_for_destruction (gauge)
    Number of temporary tables marked for destruction.

  • sqlserver_performance.total_server_memory_kb (gauge)
    Total server memory in KB.

  • sqlserver_performance.transaction_delay (gauge)
    Number of delayed transactions.

  • sqlserver_performance.transactions_sec (gauge)
    Rate of transactions per second.

  • sqlserver_performance.used_memory_kb (gauge)
    Used memory in KB.

  • sqlserver_performance.user_connections (gauge)
    Number of user connections.

  • sqlserver_performance.version_store_size_kb (gauge)
    Size of the version store in KB.

  • sqlserver_performance.write_transactions_sec (gauge)
    Rate of write transactions per second.

  • sqlserver_performance.xtp_memory_used_kb (gauge)
    Size of xtp memory used in KB.

  • sqlserver_server_properties.available_storage_mb (gauge)
    Available storage in MB.

  • sqlserver_server_properties.cpu_count (gauge)
    Number of cpus.

  • sqlserver_server_properties.db_offline (gauge)
    Number of offline databases.

  • sqlserver_server_properties.db_online (gauge)
    Number of online databases.

  • sqlserver_server_properties.db_recovering (gauge)
    Number of databases recovering.

  • sqlserver_server_properties.db_recoverypending (gauge)
    Number of databases pending recovery.

  • sqlserver_server_properties.db_restoring (gauge)
    Number of databases restoring.

  • sqlserver_server_properties.db_suspect (gauge)
    Number of suspect databases.

  • sqlserver_server_properties.engine_edition (gauge)
    Sql server engine edition version.

  • sqlserver_server_properties.server_memory (gauge)
    Amount of memory on the sql server.

  • sqlserver_server_properties.total_storage_mb (gauge)
    Amount of storage in MB of the sql server.

  • sqlserver_server_properties.uptime (gauge)
    Uptime of the sql server.

  • sqlserver_waitstats.max_wait_time_ms (gauge)
    Maximum time in millisecond spent waiting.

  • sqlserver_waitstats.resource_wait_ms (gauge)
    Time in milliseconds spent waiting on a resource.

  • sqlserver_waitstats.signal_wait_time_ms (gauge)
    Time in milliseconds waiting on a signal.

  • sqlserver_waitstats.wait_time_ms (gauge)
    Time in milliseconds waiting.

  • sqlserver_waitstats.waiting_tasks_count (gauge)
    Time in milliseconds

Non-default metrics (version 4.7.0+)

The following information applies to the agent version 4.7.0+ that has enableBuiltInFiltering: true set on the top level of the agent config.

To emit metrics that are not default, you can add those metrics in the generic monitor-level extraMetrics config option. Metrics that are derived from specific configuration options that do not appear in the above list of metrics do not need to be added to extraMetrics.

To see a list of metrics that will be emitted you can run agent-status monitors after configuring this monitor in a running agent instance.

Legacy non-default metrics (version < 4.7.0)

The following information only applies to agent version older than 4.7.0. If you have a newer agent and have set enableBuiltInFiltering: true at the top level of your agent config, see the section above. See upgrade instructions in Old-style whitelist filtering.

If you have a reference to the whitelist.json in your agent's top-level metricsToExclude config option, and you want to emit metrics that are not in that whitelist, then you need to add an item to the top-level metricsToInclude config option to override that whitelist (see Inclusion filtering. Or you can just copy the whitelist.json, modify it, and reference that in metricsToExclude.