SQL Server 可观测最佳实践

2024-10-28
上海
本文字数：3321 字
阅读完需：约 11 分钟

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

登录观测云控制台，点击「集成」 -「DataKit」 - 「Windows」，复制 PowerShell 安装命令，在主机中可以一键安装。

创建 SQLServer 帐号

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

USE master;GOCREATE LOGIN [guance] WITH PASSWORD = N'yourpassword';GOGRANT VIEW SERVER STATE TO [guance];GOGRANT 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 查询的性能，包括批处理请求的频率和执行计划的效率，以优化数据库的响应时间并减少查询延迟。
缓冲缓存：跟踪缓冲缓存的命中率和页面寿命，确保数据访问主要在内存中完成，减少对磁盘操作的依赖。
锁：监控锁等待和进程阻塞情况，识别并解决数据库并发操作中的问题，保持事务处理的流畅性。
索引：监控索引的使用情况和性能，包括索引的碎片化和页面分裂，以维持查询效率和数据访问速度。
连接：监控数据库的连接数量和连接稳定性，确保应用程序和服务的持续可用性和性能。
资源使用情况：监控内存使用情况、磁盘空间使用以及数据库和日志的读写量，评估数据存储的效率和进行容量规划。

重点指标说明：

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 每秒锁等待数：衡量的是数据库引擎锁定管理器每秒需要调用者等待的锁请求的次数。这个指标用于衡量锁请求的等待频率，可以帮助数据库管理员了解数据库中的并发情况以及可能存在的锁争用问题。如果值很高，这可能表明数据库中的许多操作因为锁而不得不等待。这并不一定意味着有性能问题，但可能表明存在大量的并发事务，或者某些事务持有锁的时间过长，导致其他事务等待。