search
数据采集 采集源配置 SQL Server 监控指标采集

SQL Server 监控指标采集

简介

采集 sqlserver 指标上报到 DataFlux 中

前置条件

  • 已安装 DataKit(DataKit 安装文档
  • 您必须使用以下脚本在要监视的每个实例上创建登录名:
USE master;
GO
CREATE LOGIN [dataway] WITH PASSWORD = N'mystrongpassword';
GO
GRANT VIEW SERVER STATE TO [dataway];
GO
GRANT VIEW ANY DEFINITION TO [dataway];
GO
  • 对于 Azure SQL 数据库,您需要具有“查看数据库状态”权限。
CREATE USER [dataway] WITH PASSWORD = N'mystrongpassword';
GO
GRANT VIEW DATABASE STATE TO [dataway];
GO

配置

进入 DataKit 安装目录下的 conf.d/db 目录,复制 sqlserver.conf.sample 并命名为 sqlserver.conf。示例如下:

[[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, in particular, tls connections can be created like so:
##   "encrypt=true;certificate=<cert>;hostNameInCertificate=<SqlServer host fqdn>"
# servers = [
#  "Server=192.168.1.10;Port=1433;User Id=<user>;Password=<pw>;app name=telegraf;log=1;",
# ]

## Optional parameter, setting this to 2 will use a new version
## of the collection queries that break compatibility with the original
## dashboards.
## Version 2 - is compatible from SQL Server 2012 and later versions and also for SQL Azure DB
query_version = 2

## If you are using AzureDB, setting this to true will gather resource utilization metrics
# azuredb = false

## Possible queries
## Version 2:
## - PerformanceCounters
## - WaitStatsCategorized
## - DatabaseIO
## - ServerProperties
## - MemoryClerk
## - Schedulers
## - SqlRequests
## - VolumeSpace
## - Cpu
## Version 1:
## - PerformanceCounters
## - WaitStatsCategorized
## - CPUHistory
## - DatabaseIO
## - DatabaseSize
## - DatabaseStats
## - DatabaseProperties
## - MemoryClerk
## - VolumeSpace
## - PerformanceMetrics

## A list of queries to include. If not specified, all the above listed queries are used.
# include_query = []

## A list of queries to explicitly ignore.
exclude_query = [ 'Schedulers' , 'SqlRequests']

其中 server 的格式:"Server=<host>;Port=<port>;User Id=<user>;Password=<pw>;app name=dataway;log=1;"。默认情况下,服务器host为localhost,默认监听端口TCP1433。

[[inputs.sqlserver]]
  interval = "300s"
  servers = ['Server=server;Port=port;User Id=user;Password=password;app name=telegraf;log=1;']
  query_version = 2
  [inputs.sqlserver.tags]
    instance_name='DBA团队自建SQLServer单实例'
    project='SuperCloud'

配置好后,重启 DataKit 即可生效

采集指标

指标集 sqlserver_cpu

tags

名称 描述
sql_instance

fields

名称 描述 类型
other_process_cpu int
sqlserver_process_cpu int
system_idle_cpu int

指标集 sqlserver_database_io

tags

名称 描述
database_name
file_type
logical_filename
physical_filename
sql_instance

fields

名称 描述 类型
read_bytes int
read_latency_ms int
reads int
rg_read_stall_ms int
rg_write_stall_ms int
write_bytes int
write_latency_ms int
writes int

指标集 sqlserver_memory_clerks

tags

名称 描述
clerk_type
database_name
sql_instance

fields

名称 描述 类型
size_kb int

指标集 sqlserver_performance

tags

名称 描述
counter
counter_type
database_name
instance
object
sql_instance

fields

名称 描述 类型
value int

指标集 sqlserver_requests

tags

名称 描述
command
database_name
host_name
program_name
query_hash
query_plan_hash
session_db_name
sql_instance
statement_text
status
stmt_db_name
transaction_isolation_level
wait_type

fields

名称 描述 类型
blocking_session_id int
cpu_time_ms int
granted_query_memory_pages int
logical_reads int
open_transaction int
percent_complete float
request_id int
session_id int
total_elapsed_time_ms int
wait_time_ms int
writes int

指标集 sqlserver_schedulers

tags

名称 描述
cpu_id
database_name
scheduler_id
sql_instance

fields

名称 描述 类型
active_workers_count int
context_switches_count int
current_tasks_count int
current_workers_count int
is_idle bool
is_online bool
load_factor int
pending_disk_io_count int
preemptive_switches_count int
runnable_tasks_count int
total_cpu_usage_ms int
total_scheduler_delay_ms int
work_queue_count int
yield_count int

指标集 sqlserver_server_properties

tags

名称 描述
database_name
hardware_type
sku
sql_instance
sql_version

fields

名称 描述 类型
cpu_count int
db_offline int
db_online int
db_recovering int
db_recoveryPending int
db_restoring int
db_suspect int
engine_edition int
server_memory int
uptime int

指标集 sqlserver_volume_space

tags

名称 描述
sql_instance
server_name

fields

名称 描述 类型
available_space_bytes int
total_space_bytes int
used_space_bytes int

指标集 sqlserver_waitstats

tags

名称 描述
database_name
sql_instance
wait_category
wait_type

fields

名称 描述 类型
max_wait_time_ms int
resource_wait_ms int
signal_wait_time_ms int
wait_time_ms int
waiting_tasks_count int