统一观测丨使用 Prometheus 监控 SQL Server 最佳实践

SQL Server 简介

SQL Server 是什么？

Microsoft SQL Server 是 Microsoft 推出的关系型数据库解决方案，支持企业 IT 环境中的各种事务处理、商业智能和分析应用程序。Microsoft SQL Server 是市场领先的数据库技术之一。

SQL Server 特点

• 稳定：针对企业的应用需求，制定出适应环境的解决方案，保证了企业的数据安全和顺利运行。
• 易用：提供了丰富的图形化管理工具，方便用户快速搭建数据库系统。
• 兼容：原生适配 Windows 系统，提供丰富的 API 访问。
• 性能：多种数据库引擎优化算法，支持大量数据查询存储。

SQL Server 核心概念

关系引擎：关系引擎控制存储引擎对数据的处理，并提供 SQL Server 组件来准确确定应如何执行查询。关系引擎由三个主要部分组成。CMD 解析器（parser）主要负责识别和消除语义和语法错误，并生成查询树。优化器（Optimizer）通过消除冗余任务和寻找最优计划，确保所请求的查询响应尽可能高效。查询执行器（Query Executoe）会生成数据获取逻辑的行为。

存储引擎：当数据由存储引擎存储时，会从存储系统（例如 SAN 或磁盘）检索数据。存储引擎中存在三种类型的文件：主文件、辅助文件和日志文件。访问方法（Access Method）负责在缓存管理器和事务日志之间交换数据。缓存管理器（Buffer Manager）缓存当前的执行计划和页。事务管理器（Transaction Manager）使用日志和锁管理器对事务进行管理。

协议层：该层支持客户端-服务器架构以及流。协议层支持 3 种类型的客户端服务器架构：共享内存、TCP/IP、命名管道。

主要适用场景

Microsoft SQL Server 凭借其可视化界面及其所具有的选项和工具，非常适合在关系数据库中存储所有所需的信息，以及轻松管理此类数据。

• 事务处理：SQL Server 支持事务处理，通过使用事务，用户可以将一系列数据库操作组合在一起，并确保它们要么全部成功执行，要么全部回滚到初始状态。这对于处理银行交易、在线购物和库存管理等需要保证数据一致性的应用程序非常关键。
• 通过大数据集群对所有数据进行智能分析：SQL Server 提供了强大的数据仓库和商业智能功能。用户可以使用 SSIS 将数据从不同的数据源中提取、转换和加载到数据仓库中。然后，您可以使用 SSAS 创建多维数据模型和立方体，以支持复杂的数据分析和报表需求。此外，SQL Server还提供了数据挖掘和预测分析功能，帮助组织发现数据中隐藏的模式和趋势。
• 可扩展性：SQL Server 提供了广泛的开发和编程功能，以支持应用程序开发人员。SQL Server 还支持水平和垂直扩展，用户可以在需要时增加服务器硬件资源或在多个服务器之间进行数据分区和分布，以处理大规模数据和高并发负载。SQL Server 允许用户轻松地将数据库管理系统与任何设备和 Azure 服务集成，以获得更好的数据性能和分析能力。

主要版本介绍

SQL Server 2022：安全、性能、可用性等加强；查询存储和智能查询处理

SQL Server 2019：数据虚拟化和大数据群集；智能数据库、智能查询；内存数据库

SQL Server 2017：图形数据库功能、动态管理视图、内存优化等

SQL Server 2016：内存OLTP、Stretch Database、集成 Hadoop 等

监控关键指标

这里介绍监控 SQL Server 服务中常见的关键指标。

系统指标

运行状态

启动状态是监控 SQL Server 最基础的指标，表示 SQL Server 实例是否在正常运行，或是否重启。SQL Server 重启时，没有 commit 的数据会丢失，小概率产生错误。

版本/实例时间

监控启动的 SQL Server 实例是否符合预期，是否是业务要求的 SQL Server 版本。保证 SQL Server 的本地时间与客户端保持一致，否则数据库返回的时间有可能发生错误。

读写指标

页指标

页读写：页是 SQL Server 存储引擎磁盘管理的最小单位，为数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行。也就是说，SQL Server 读取或写入所有数据页。因此对页的读写监控尤为重要。通过读写页数量的指标，可以计算出页读写的速率，从而判断 SQL Server 的执行性能。

页在缓存的停留时间：所有数据库软件的主要设计目标之一是尽量减少磁盘 I/O，因为磁盘的读取和写入操作占用大量资源。SQL Server 在内存中生成缓存池，用于保存从数据库读取的页。我们需要监控页在缓存池的生命时长，页在缓存池中存在的时间越长，表示其被命中的可能性越大，也就是说访问该页时不需要访问磁盘。

惰性写（lazy write）：在缓冲区缓存中修改页后，不会将其立即写回磁盘；而是将其标记为“脏”。也就是说在将页物理写入磁盘之前，可以将其逻辑写入多次。在正常运行的情况下，脏页定期地刷入磁盘。而当不断地有新数据写入 SQL Server 且缓存不够用的情况下，大量的脏页会被移出缓存。

检查点（checkpoint）：当检查点发生时，SQL Server 要求刷新所有脏页至磁盘，此时 SQL Server 性能受到一定影响。我们需要监控检查点的速度，确保检查点的刷新速度达到预期。

页错误（page fault）：当页错误发生时，表示需要的页在 SQL Server 可管理的内存区域之外。当遇到页面错误时，程序执行停止并设置为等待状态。操作系统在磁盘上搜索所请求的地址。当找到该页面后，操作系统将其从磁盘复制到空闲 RAM 页面中。操作系统允许程序随后继续执行。

日志成长次数

SQL Server 数据库引擎为数据库中的每个操作写入日志记录，其中包括执行数据修改过程时、创建或删除数据库表或索引时以及每次分配或删除页面后开始或结束 SQL 事务。日志助于在系统或硬件发生故障时将数据库恢复到特定时间点。在具有过多日志记录操作的事务性很强的系统中，SQL Server 事务日志文件将快速增长，直到达到其最大大小，从而生成错误号 9002。如果启用自动增长选项，底层磁盘驱动器将耗尽可用空间。

I/O 等待（stall）时间

I/O 等待时间是一个可用于检测 I/O 问题的指标。SQL Server 将数据写入文件或是从文件读数据时，都需要等待较长的时间，表示为 I/O 等待时间，停顿时间较长表明存在 I/O 问题和磁盘活动繁忙。文件 I/O 属于数据库的关键路径，等待的时间直接反应到客户端读写 SQL Server 的延迟。每个数据库存储的文件不同，使用的存储介质也可能不同。因此除了监控整体的 I/O 等待时间，还需要监控各个数据库的 I/O 等待时间，以便运维做出针对性的优化。

连接指标

在 SQL Server 中，执行查询都依赖于建立和维护客户端连接。当需要维护