关于prometheus:统一观测丨使用-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 中，执行查问都依赖于建设和保护客户端连贯。当须要保护 SQL Server 的可用性和高性能，监控连贯是运维工作的良好入口。当 SQL Server 并发连接数过多可能会使服务器超载。当连贯胜利建设，不论是否应用连贯，每个连贯都会产生开销。

存储指标

如上文所述，只管 SQL Server 将数据存储在磁盘中，但因为缓存池的存在，对 SQL Server 内存应用状况同样要重点关注。默认状况下，SQL Server 依据可用的系统资源动静治理其内存需要。如果 SQL Server 须要更多内存，它会查问操作系统以确定是否有可用的闲暇物理内存并应用可用内存。如果操作系统的可用内存有余，SQL Server 会将内存开释回操作系统，直到内存不足的状况失去缓解，或者直到 SQL Server 达到最小服务器内存限度。

性能指标

事务处理速率（TPS）

一个事务是指一个客户端向 SQL Server 发送申请而后 SQL Server 做出反馈的过程。客户端在发送查问申请时开始计时，收到 SQL Server 响应后完结计时，以此来计算应用的工夫和实现的事务个数。个别的，评估 SQL Server 性能均以每秒钟实现的客户端申请的数量来掂量。

批处理（T-SQL）速率

T-SQL (Transact-SQL) 是 Sybase 和 Microsoft 的一组编程扩大，它为结构化查询语言 (SQL) 增加了多种性能，包含事务管制、异样和错误处理、行解决和申明的变量。所有与 SQL Server 通信的应用程序都是通过向服务器发送 T-SQL 语句来实现的。

指标具体定义

零碎指标

读写指标

连贯指标

存储指标

性能指标

监控大盘

咱们默认提供了 SQL Server Overview 大盘。

总览

在该 panel 能看到 SQL Server 运行时须要重点关注的指标，在查看 SQL Server 状态时，首先查看总览中是否有异样状态，再查看具体的指标。

• 启动状态：绿色代表失常运行，红色代表异样运行
• 内存使用率：应用红黄绿色彩提醒，内存使用率在 80% 以下时为绿色，80%～90% 为黄色，90% 以上为红色

资源

内存是 SQL Server 的重点关注硬件资源，通过该 panel 能理解 SQL Server 的内存应用状况：

• 最大内存：提供内存整体状态
• 内存使用率 / 使用量：剖析内存应用的趋势
• 文件扩大次数：剖析操作数据量的趋势
• 数据库文件大小：查看数据库存储的数据量趋势，以及日志的存储量的趋势

性能

在以下 panel 能看到 SQL Server 的运行效率，分为以下三类：

• 事务处理速率：示意 SQL Server 每秒解决的事务数量，间接影响客户端查问申请的提早
• 批处理速率：示意 SQL Server 每秒能解决多少个 Transact-SQL
• 死锁次数：检测是否产生了因为数据库竞争而产生的死锁，重大影响性能

读写

在以下 panel 能看到 SQL Server 的运行效率：

• 读写页速率：监控读写页的速率趋势，是否达到预期速率，是否产生波峰波谷等
• 惰性写速率：定期输出脏页的速率趋势，监控是否安稳
• page 生命预期：page 的生命预期越高越好
• I/O 等待时间：SQL Server 期待读写文件的等待时间趋势
• 检查点速率：当产生检查点，须要监控运行速率

数据库 I/O Stall

除了监控 SQL Server 的整体 I/O 等待时间，还须要别离监控数据库的 I/O 等待时间。每个数据库存储的文件不同，存储的磁盘可能不同，须要做出针对性优化计划。

连贯

须要随时查看 SQL Server 的连贯状况，避免过高的并发影响性能。

• 总连接数：监控整体连接数的趋势
• 连贯谬误数：监控是否有客户端产生了连贯谬误，保证系统失常运行
• 各数据库的连贯用户：别离监控每个数据库连贯的客户端，以及每个客户端发动的连贯数量

数据库状态

监控各数据库是否失常运行，下表定义了数据库的状态。

要害告警规定

在对 SQL Server 进行告警规定配置时，咱们举荐基于以上采集失去的指标，从以下几个方面进行告警规定的配置，别离是运行状况、资源应用状况、连贯应用状况。一般来说，咱们默认生成影响 SQL Server 失常应用的告警规定，优先级较高。读写速率等与业务相干的告警则由用户自定义。以下是一些举荐的告警规定。

运行状况

SQL Server 停机

SQL Server 停机是 0/1 阈值的告警规定。一般来说，部署在 ACK 等阿里云环境的 SQL Server 服务具备高可用的能力，当一个 SQL Server 实例进行，其余的实例会持续工作。本报警引发的起因可能是所有的 SQL Server 都无奈失常启动，或者 Exporter 谬误无奈获取数据。咱们默认设定 5 分钟内 SQL Server 无奈复原的告警。

SQL Server 重启

SQL Server 重启是 0/1 阈值的告警规定绝大部分状况下 SQL Server 因为有日志的存在，不存在数据失落的状况。但 SQL Server 重启之后缓存池的内容被清空，造成临时的查问迟缓。正在执行的事务要回退给客户端，引发一系列临时的谬误，须要客户端从新发动申请。

资源应用状况

内存使用率过高

SQL Server 对服务器内存的应用策略是用多少内存就占用多少内存，不加限度的话有可能把节点的所有内存资源占用。当内存使用率过高，SQL Server 无奈失常运行。咱们设定的内存应用阈值为：危险值 80%，告警值 90%。当内存使用率为 80% 时，节点高负荷运行，但个别不影响失常应用。当内存长时间使用率为 90% 时，将收回告警，提醒运维资源紧缺，尽早解决。

产生死锁

SQL Server 产生死锁是 0/1 阈值的告警规定，不思考死锁的个数。一零碎中遇到的死锁数通常很少，而一旦产生死锁，须要终止被线程执行的以后批处理，回滚死锁牺牲品的事务，回退给客户端一个错误信息。

连贯应用状况

产生连贯谬误

SQL Server 产生连贯谬误是 0/1 阈值的告警规定。产生该谬误可能有很多起因，如：近程主机强制敞开现有连贯、超时过期。操作实现之前已过的超时期限或服务器未响应、无奈生成 SSPI 上下文等，须要登录数据库并查看日志查看谬误起因。

典型问题场景及其排查 / 解决办法

SQL Server 性能差

SQL Server 性能差体现在事务处理速率（TPS）、批处理（T-SQL）速率低的指标上，性能差有许多起因可能导致，咱们须要分割多个指标进行排查。

查看内存使用率

• 起因：内存不足的状况下，缓存池无奈缓存所有的热点数据，导致屡次数据拜访申请发送到磁盘
• 排查办法：查看大盘中的内存使用率 panel，查看内存使用率是否始终都很高。查看告警历史，查看是否提醒内存资源有余。
• 解决办法：优化对应节点的磁盘资源；

查看 I/O 等待时间

• 起因：I/O 等待时间较长表明存在 I/O 问题和磁盘流动忙碌。
• 排查办法：查看大盘中的 I/O 等待时间 panel，查看 I/O 等待时间是否始终都很高。查看告警历史，查看是否提醒内存资源有余。
• 解决办法：可能是呈现大量的波峰拜访，忽然增大磁盘拜访，思考优化 SQL Server 架构；进一步查看节点的文件系统是否有 I/O 问题；优化对应节点的磁盘资源；

查看日志增长次数

• 起因：当数据库中执行任何批改时，SQL Server 会将批改写入日志缓冲区，而后将该缓冲区数据写入磁盘。当写入数据过多，日志内容来不及刷入磁盘。
• 排查办法：查看大盘中日志增长次数的 panel，查看是否有某段时间日志增长次数忽然升高。
• 解决办法：为日志文件抉择的磁盘必须在程序读写吞吐量和最小提早方面体现良好。

查看检查点（checkpoint）

• 起因：执行检查点时，SQL Server 将内存中的所有脏页刷新到磁盘，会影响整体数据库性能，给磁盘带来较大压力。
• 排查办法：查看检查点 panel，查看是否有检查点速率的数据，示意在该时间段进行了检查点操作。
• 解决办法：设计进行检查点的机会，尽量避开顶峰时间段。

监控体系搭建

自建 Prometheus 监控 SQL Server 的痛点

通常咱们以后的 SQL Server 都是部署在 ECS 上，因而自建 Prometheus 监控 SQL Server 时，咱们将面临的典型问题有：

1. 因为平安、组织治理等因素，用户业务通常部署在多个互相隔离的 VPC，须要在多个 VPC 内都反复、独立部署 Prometheus，导致部署和运维老本高。
2. 每套残缺的自建监控零碎都须要装置并配置 Prometheus、Grafana、AlertManager 等，过程简单、施行周期长。
3. 短少与阿里云 ECS 无缝集成的服务发现（ServiceDiscovery）机制，无奈依据 ECS 标签来灵便定义抓取 targets。如果自行实现相似性能，则须要应用 Golang 语言开发代码（调用阿里云 ECS POP 接口）、集成进开源 Prometheus 代码、编译打包后部署，实现门槛高、过程简单、版本升级艰难。
4. 罕用开源 Grafana SQL Server 大盘不够业余，短少联合 SQL Server 原理 / 特色和最佳实际进行深刻优化。
5. 短少 SQL Server 告警指标模板，须要用户自行钻研、配置告警项，工作量大。

用阿里云 Prometheus 进行自建 SQL Server 的监控

• 登录 ARMS 控制台 [1]。
• 在左侧导航栏抉择 Prometheus 监控 > Prometheus 实例列表，进入可观测监控 Prometheus 版的实例列表页面。
• 单击指标 Prometheus 实例名称，进入集成核心页面。
• 单击 SQL Server 卡片的装置

• 配置相干参数，并单击确定，实现组件接入。

已接入的组件会显示在集成核心页面的已装置区域。单击该组件卡片，在弹出的面板中能够查看 Targets、指标、大盘、告警、服务发现配置、Exporter 等信息。

如下图所示，您能够看到目前可观测监控 Prometheus 版提供的要害告警指标。

您能够在大盘页签，单击大盘缩略图，查看对应 Grafana 大盘。

您能够面板中单击告警页签，查看 SQL Server 的 Prometheus 告警。您还能够依据业务需要新增告警规定。创立 Prometheus 告警规定的具体操作，请参见 Prometheus 告警规定 [2]。

自建 Prometheus 与阿里云可观测监控 Prometheus 版监控 SQL Server 优劣比照

可观测监控 Prometheus 版针对产品新用户，提供三个月每日 2000w 自定义指标上报额度，点击浏览原文立刻收费试用。

参考链接：

[1] https://learn.microsoft.com/zh-cn/sql/sql-server/what-s-new-i…

[2] https://www.sqlshack.com/sql-server-troubleshooting-disk-i-o-…

[3] https://learn.microsoft.com/en-us/sql/relational-databases/pe…

[4] https://learn.microsoft.com/zh-cn/sql/relational-databases/pa…

[5] https://learn.microsoft.com/zh-cn/troubleshoot/sql/database-e…

[6] https://learn.microsoft.com/zh-cn/sql/relational-databases/me…

[7] https://www.sqlshack.com/sql-server-memory-performance-metric…

[8] https://www.sqlshack.com/sql-server-transaction-log-growth-mo…

[9] https://blog.csdn.net/Superman7658/article/details/130799559

[10] https://learn.microsoft.com/zh-cn/sql/relational-databases/da…

相干链接：

[1] ARMS 控制台

https://account.aliyun.com/login/login.htm?oauth_callback=htt…

[2] Prometheus 告警规定

https://help.aliyun.com/zh/arms/prometheus-monitoring/create-…

作者：啃唯

点击立刻收费试用云产品 开启云上实际之旅！

原文链接

本文为阿里云原创内容，未经容许不得转载。