SQL Server 可观测最佳实践

原创

可观测技术 2024-10-28 17:09:29 ©著作权

文章标签 sql server 文章分类 运维

©著作权归作者所有：来自51CTO博客作者可观测技术的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL Server 简介

SQL Server 是微软公司开发的一款关系数据库管理系统，支持企业 IT 环境中的各种事务处理、商业智能和分析应用程序。它支持多种操作系统平台，而无论是物理还是虚拟形式，自建部署环境还是在云环境中，运行的操作系统是 Windows 还是 Linux，我们都需要关注 SQL Server 的重点指标。

本文主要讲解如何监控自建的 SQL Server。监控云上的 RDS SQL Server 服务，可以参考这些文章：https://docs.guance.com/integrations/aliyun_rds_sqlserver/

监控 SQL Server 可以帮助我们及时识别数据库性能瓶颈和活动，确保数据库的健康和安全。通过及时预警和资源优化，提升整体性能和数据完整性，甚至可以为企业的业务决策提供有力支持。

观测云介绍

观测云是一个统一实时监测平台，它提供全面的系统可观测性解决方案，帮助用户快速实现对云平台、云原生、应用及业务的监控需求。观测云的核心功能包括：基础设施监测，日志采集和分析，用户访问监测（RUM），应用性能监测（APM），服务可用性监测（拨测），安全巡检，智能监控等等。

操作步骤

下面是在 Windows 主机中，部署 DataKit 并开通 SQL Server 采集器的示例。DataKit 自身提供 SQL Server 指标和日志的采集，安装好 DataKit 之后，开通 SQL Server 采集器，即可采集 SQL Server 指标和日志到观测云。

部署 DataKit

创建 SQLServer 帐号

创建一个用户，用于采集指标数据。

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

开启 SQLServer 采集器（指标）

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

[[inputs.sqlserver]]
  ## your sqlserver host ,example ip:port
  host = ""

  ## your sqlserver user,password
  user = ""
  password = ""

  ## Instance name. If not specified, a connection to the default instance is made.
  instance_name = ""

  ## Database name to query. Default is master.
  database = "master"

  ## by default, support TLS 1.2 and above.
  ## set to true if server side uses TLS 1.0 or TLS 1.1
  allow_tls10 = false

  ## connection timeout default: 30s
  connect_timeout = "30s"

  ## parameters to be added to the connection string
  ## Examples:
  ##   "encrypt=disable"
  ##   "certificate=/path/to/cert.pem"
  ## reference: https://github.com/microsoft/go-mssqldb?tab=readme-ov-file#connection-parameters-and-dsn 
  #
  # connection_parameters = "encrypt=disable"

  ## (optional) collection interval, default is 10s
  interval = "10s"


  ## Set true to enable election
  election = true

  ## configure db_filter to filter out metrics from certain databases according to their database_name tag.
  ## If leave blank, no metric from any database is filtered out.
  # db_filter = ["some_db_instance_name", "other_db_instance_name"]


  ## Run a custom SQL query and collect corresponding metrics.
  #
  # [[inputs.sqlserver.custom_queries]]
  #   sql = '''
  #     select counter_name,cntr_type,cntr_value
  #     from sys.dm_os_performance_counters
  #   '''
  #   metric = "sqlserver_custom_stat"
  #   tags = ["counter_name","cntr_type"]
  #   fields = ["cntr_value"]

  # [inputs.sqlserver.log]
  # files = []
  # #grok pipeline script path
  # pipeline = "sqlserver.p"

  [inputs.sqlserver.tags]
  # some_tag = "some_value"
  # more_tag = "some_other_value"

开启 SQLServer 采集器（日志）

如需采集 SQL Server 的日志，可在 sqlserver.conf 中将 files 打开，并写入 SQL Server 日志文件的绝对路径。比如：

[[inputs.sqlserver]]
    ...
    [inputs.sqlserver.log]
        files = ["C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/Log/ERRORLOG"]

开启日志采集以后，默认会产生日志来源（source）为 sqlserver 的日志。

重启 DataKit

以管理员身份运行 PowerShell，执行以下命令：

datakit service -R

关键指标

监控 SQL Server 时，主要需要从以下维度进行，通过综合监控这些维度，可以确保 SQL Server 的高效运行和稳定性：

系统的运行情况：包括 CPU 内存磁盘以及网络相关信息，这些在主机或者容器层面的监控中包含，本文不再赘述
T-SQL 查询：即 Transact-SQL，是 SQL Server 的查询和编程语言。监控 T-SQL 查询的性能，包括批处理请求的频率和执行计划的效率，以优化数据库的响应时间并减少查询延迟。
缓冲缓存：跟踪缓冲缓存的命中率和页面寿命，确保数据访问主要在内存中完成，减少对磁盘操作的依赖。
锁：监控锁等待和进程阻塞情况，识别并解决数据库并发操作中的问题，保持事务处理的流畅性。
索引：监控索引的使用情况和性能，包括索引的碎片化和页面分裂，以维持查询效率和数据访问速度。
连接：监控数据库的连接数量和连接稳定性，确保应用程序和服务的持续可用性和性能。
资源使用情况：监控内存使用情况、磁盘空间使用以及数据库和日志的读写量，评估数据存储的效率和进行容量规划。

分类	指标集	指标	描述	单位
T-SQL	sqlserver_performance	batch_requests	Batch requests/sec，数据库每秒的批量请求数，衡量数据库引擎每秒接收的批处理请求数量	count
	sqlserver_performance	sql_compilations	SQL compilations/sec，每秒 SQL 编译数，记录 SQL Server 每秒编译的批处理数量，理想情况下应尽量减少编译次数	count
	sqlserver_performance	sql_re_compilations	SQL recompilations/sec，每秒 SQL 重新编译数，衡量重新编译的频率，频繁的重新编译可能影响性能	count
缓冲缓存	sqlserver_performance	buffer_cache_hit_ratio	缓存命中率，衡量缓冲管理器从缓冲缓存中拉取页面的频率，较高的命中率表示更有效的内存使用	percent
	sqlserver_performance	page_life_expectancy	页面寿命预期，页面在缓冲缓存中预期停留的时间，较高的值表明数据库可以有效地在内存中处理读写操作	s（秒）
	sqlserver_performance	checkpoint_pages	检查点页面数量，在检查点期间从缓冲缓存写入磁盘的页面数量，帮助优化缓冲管理器	count
锁指标	sqlserver_performance	lock_waits	Lock waits/sec，每秒锁等待数，每秒导致调用事务等待锁的请求数	count
	sqlserver_performance	processes_blocked	Processes blocked，被阻塞的进程数，帮助识别潜在的死锁问题	count
索引	sqlserver_performance	page_splits	Page splits/sec，每秒页面拆分数，过多的页面拆分可能影响查询性能	count
连接	sqlserver_performance	User connections	用户连接数量，当前活动的用户连接数量	count
内存	sqlserver	committed_memory	已提交给内存管理器的内存量	Bytes
		target_memory	内存管理器可以使用的内存量。当此值大于已提交的内存时，内存管理器将尝试获取更多内存；当此值小于已提交的内存时，内存管理器将尝试减少已提交的内存量。	Bytes
数据库读写	sqlserver_database_io	reads	数据读取数量，file_type 分为 Rows（数据库）和 Log（日志）	count
	sqlserver_database_io	reads_bytes	数据读取大小，file_type 同上	Bytes
	sqlserver_database_io	writes	数据写入数量，file_type 同上	count
	sqlserver_database_io	write_bytes	数据读取大小，file_type 同上	Bytes

重点指标说明：

Batch requests/sec 每秒批量请求数：记录了服务器每秒接收的 T-SQL 命令批处理的数量。一个批处理可以包含单个或多个 SQL 语句，并且当整个批处理执行完毕后才返回结果。这个指标可以帮助数据库管理员（DBA）了解服务器的负载情况以及可能存在的瓶颈，一般来说，需要结合其他性能指标如 CPU 使用率、I/O 性能等一起分析：如果这个指标的值异常高，可能意味着服务器正在处理大量的请求，这可能会影响服务器的其他性能指标；相反，如果这个指标的值很低，也不一定是好事，它可能意味着系统存在瓶颈，导致 SQL Server 无法充分利用其全部潜力。
Buffer cache hit ratio 缓存命中率：衡量的是从 SQL Server 的缓冲区高速缓存中直接找到数据页的次数与所有尝试查找数据页的次数的比例。这个比例显示了 SQL Server 如何有效地使用其缓冲区高速缓存。对于需要更高性能的系统，一般希望命中率是在 95% 以上；如果这个值较低，则可能表明存在内存问题，可能需要增加内存或者优化数据库和查询以减少对内存的需求。
Page life expectancy 页面寿命预期：衡量的是一个数据页在缓冲池中保持不被引用的时间长度（以秒为单位）。这个指标反映了内存中页面的稳定性，如果一个页面在缓冲池中停留的时间越长，说明它被重用的可能性越大，这样就减少了对磁盘的访问次数，提高了数据库的查询性能、一般来说，PLE的值如果低于 300 秒，可能表明 SQL Server 的缓冲池内存不足，需要更多的物理内存或可能需要优化查询和索引以减少内存需求；相反，如果PLE的值很高，这通常意味着内存充足，数据页可以长时间保留在缓冲池中，从而提高数据库的读取性能。
Lock Waits/sec 每秒锁等待数：衡量的是数据库引擎锁定管理器每秒需要调用者等待的锁请求的次数。这个指标用于衡量锁请求的等待频率，可以帮助数据库管理员了解数据库中的并发情况以及可能存在的锁争用问题。如果值很高，这可能表明数据库中的许多操作因为锁而不得不等待。这并不一定意味着有性能问题，但可能表明存在大量的并发事务，或者某些事务持有锁的时间过长，导致其他事务等待。