SQL Server 性能诊断（6）- 等待状态

丶Loaf
313 阅读18分钟

1. 概述

到目前为止， 介绍了 SQL Server 中常见的系统资源瓶颈问题，包括内存、磁盘 I/O、CPU 和锁资源阻塞。对于每种瓶颈，SQL Server 提供了特定的诊断和分析方法。

SQL Server 提供了强大的动态管理视图（DMVs），使管理员可以实时跟踪所有用户会话的状态。如果数据库遇到资源瓶颈，某些任务可能无法获得所需的资源，从而进入等待状态。通过这些管理视图，管理员可以查看当前会话的状态（如 sleeping、runnable 或 running），以及它们在等待哪些资源。这些信息是数据库管理员进行问题诊断的重要工具。

因此，管理视图是判断 SQL Server 系统资源瓶颈的一个重要起点，数据库管理员可以通过观察会话的等待状态，迅速切入问题并开展后续的分析与调优。

常用的三张动态管理视图：

sys.dm_exec_requests
  -- 返回有关在SQL Server中执行的每个请求的信息，包括当前的等待状态。
sys.dm_exec_sessions
  -- 对于SQL Server中每个经过身份验证的会话都返回相应的一行。sys.dm_exec_sessions是服务器范围的视图，显示了有关所有活动用户连接和内部任务的信息。此信息包含客户端版本、客户端程序名称、客户端登录时间、登录用户和当前会话设置等。使用sys.dm_exec_sessions，首先可以查看当前的系统负荷并标识相关会话，然后可以通过其他动态管理视图或动态管理函数了解有关该会话的详细信息。
sys.dm_exec_connections
  -- 返回与SQL Server实例建立的连接有关的信息及每个连接的详细信息，例如，连接所使用的验证方式，是Kerberos还是NTLM。

需要说明的是，之前存在的sys.sysprocesses的结构是向前兼容的，它不支持多个活动结果集（MARS）这个概念。在SQL Server 2000的时候，一个连接同时只能运行一个批处理指令。也就是说，一个连接在一个时间只能有一个请求在运行，但是在以后版本的SQL Server（如SQL Server 2012）中，如果使用了MARS技术，一个连接可以同时开启多个结果集。而sys.sysprocesses视图不能正确显示这种行为

下面常用的SQL脚本：

SELECT 
    s.session_id,                  -- 会话的唯一标识符
    s.status,                       -- 会话的状态（如 `running`、`suspended`）
    s.login_time,                   -- 会话登录时间
    s.host_name,                    -- 发起连接的客户端主机名
    s.program_name,                 -- 发起连接的客户端应用程序名称
    s.host_process_id,              -- 客户端主机的进程 ID
    s.client_version,               -- 客户端版本号
    s.client_interface_name,        -- 客户端接口类型（如 ODBC、ADO.NET 等）
    s.login_name,                   -- 登录的用户名
    s.last_request_start_time,      -- 上次请求的开始时间
    s.last_request_end_time,        -- 上次请求的结束时间
    c.connect_time,                 -- 连接的建立时间
    c.net_transport,                -- 网络传输协议类型（如 TCP、Named Pipes）
    c.net_packet_size,              -- 网络包大小（以字节为单位）
    c.client_net_address,           -- 客户端的网络地址（通常是 IP 地址）
    r.request_id,                   -- 请求的唯一标识符
    r.start_time,                   -- 请求开始时间
    r.status AS request_status,     -- 请求的状态（如 `running`、`suspended` 等）
    r.command,                      -- 当前执行的 SQL 命令（如 `SELECT`、`INSERT` 等）
    r.database_id,                  -- 正在执行的请求所连接的数据库 ID
    r.user_id,                      -- 执行请求的用户 ID
    r.blocking_session_id,          -- 如果当前请求被阻塞，显示阻塞的会话 ID
    r.wait_type,                    -- 请求正在等待的资源类型（如 `CXPACKET`、`LCK_M_S` 等）
    r.wait_time,                    -- 请求等待资源的时间（以毫秒为单位）
    r.last_wait_type,               -- 上次等待的资源类型
    r.wait_resource,                -- 当前请求等待的资源（如锁、页面等）
    r.open_transaction_count,       -- 当前会话中打开的事务数量
    r.transaction_id,               -- 当前请求涉及的事务 ID
    r.percent_complete,             -- 对于长时间运行的请求（如大批量导入等），显示完成百分比
    r.cpu_time,                     -- 请求执行所消耗的 CPU 时间（以毫秒为单位）
    r.reads,                        -- 请求读取的逻辑读取次数
    r.writes,                       -- 请求写入的次数
    r.granted_query_memory          -- 请求获得的内存（以字节为单位）
FROM 
    sys.dm_exec_requests r
RIGHT OUTER JOIN 
    sys.dm_exec_sessions s ON r.session_id = s.session_id
RIGHT OUTER JOIN 
    sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE 
    s.is_user_process = 1;         -- 只筛选用户会话（排除系统会话）

另外还有一个动态管理视图sys.dm_os_wait_stats，如表16-1所示，可以返回从SQL Server启动以来所有的等待状态总的等待数和等待时间。从这些累积值可以看出SQL Server经常会遇到哪一类等待。

sys.dm_os_wait_stats的字段定义

2. LCK_xx类型

在 SQL Server 中，阻塞是最常见的等待状态之一，通常涉及与锁资源相关的各种等待事件。以下是常见的以 LCK_ 开头的等待事件及其说明：

2.1.1. 常见的 LCK_ 等待事件

  1. LCK_M_BU
    • 正在等待获取 大容量更新（BU）锁，通常用于大量数据的更新操作。
  1. LCK_M_IS
    • 正在等待获取 意向共享（IS）锁，表明该事务打算对资源进行共享锁操作。
  1. LCK_M_IU
    • 正在等待获取 意向更新（IU）锁，表示事务意图对资源进行更新。
  1. LCK_M_IX
    • 正在等待获取 意向排他（IX）锁，表示事务打算对资源进行排他锁操作。
  1. LCK_M_RIn_NL
    • 正在等待获取 当前键值上的 NULL 锁 和 当前键与上一个键之间的插入范围锁。键上的 NULL 锁会立即释放。
  1. LCK_M_RIn_S
    • 正在等待获取 当前键值上的共享锁 和 当前键与上一个键之间的插入范围锁。
  1. LCK_M_RIn_U
    • 正在等待获取 当前键值上的更新锁 和 当前键与上一个键之间的插入范围锁。
  1. LCK_M_RIn_X
    • 正在等待获取 当前键值上的排他锁 和 当前键与上一个键之间的插入范围锁。
  1. LCK_M_RS_S
    • 正在等待获取 当前键值上的共享锁 和 当前键与上一个键之间的共享范围锁。
  1. LCK_M_RS_U
    • 正在等待获取 当前键值上的更新锁 和 当前键与上一个键之间的共享范围锁。
  1. LCK_M_RX_S
    • 正在等待获取 当前键值上的共享锁 和 当前键与上一个键之间的排他范围锁。
  1. LCK_M_RX_U
    • 正在等待获取 当前键值上的更新锁 和 当前键与上一个键之间的排他范围锁。
  1. LCK_M_RX_X
    • 正在等待获取 当前键值上的排他锁 和 当前键与上一个键之间的排他范围锁。
  1. LCK_M_S
    • 正在等待获取 共享锁，用于允许多个事务同时读取资源。
  1. LCK_M_SCH_M
    • 正在等待获取 架构修改锁，这种锁会阻止其他会话修改数据库架构。
  1. LCK_M_SCH_S
    • 正在等待获取 架构共享锁，允许多个会话读取架构，但不允许修改。
  1. LCK_M_SIU
    • 正在等待获取 共享意向更新锁，用于表示事务希望在资源上进行更新操作。
  1. LCK_M_SIX
    • 正在等待获取 共享意向排他锁，表示事务希望对资源进行共享和排他锁操作。
  1. LCK_M_U
    • 正在等待获取 更新锁，用于防止更新丢失，通常在事务更新数据时使用。
  1. LCK_M_UIX
    • 正在等待获取 更新意向排他锁，类似于 LCK_M_IX，但表示有意向更新锁。
  1. LCK_M_X
    • 正在等待获取 排他锁，用于阻止其他事务对资源进行任何操作，确保数据一致性。

2.1.2. 阻塞分析和解决

  • SQL Server 中的 LCK_ 等待状态通常发生在多个事务同时访问相同的资源时，尤其是当它们需要不同类型的锁（如共享锁、排他锁）时。阻塞通常会导致查询性能下降，甚至死锁的发生。
  • 可以使用 锁理论 和 锁排查 中介绍的方法来分析和解决阻塞问题，通常包括：
    • 通过 动态管理视图（DMVs） （如 sys.dm_exec_requests、sys.dm_exec_sessions 等）查看当前的等待事件。
    • 监控被阻塞和阻塞会话的锁情况。
    • 通过 查询优化、索引优化 和 锁粒度调整 等手段减少锁争用。
    • 在特定情况下，调整事务的隔离级别（如使用 READ COMMITTED SNAPSHOT）来减少锁争用。

2.1.3. 总结

LCK_ 开头的等待状态是 SQL Server 中的常见阻塞类型，涉及各种锁类型。数据库管理员可以通过监控这些等待事件，识别并解决锁争用和阻塞问题，优化系统的并发性能。

3. PAGEIOLATCH_x与WRITELOG

在 SQL Server 中，Latch 是一种轻量级的同步机制，用于保护内存缓冲池中的数据页面，以提高并发性能，而 Lock 则用于同步用户对象（如表、行、索引等）。Latch 主要用于内部对象的同步，确保数据页面在内存中的安全访问，并防止多个用户同时读取或修改同一页面。

根据不同的等待资源，在SQL Server里等待的状态会是：

PAGEIOLATCH_DT : Destroy buffer page I/O latch
PAGEIOLATCH_EX : Exclusive buffer page I/O latch
PAGEIOLATCH_KP : Keep buffer page I/O latch
PAGEIOLATCH_NL : Null buffer page I/O latch
PAGEIOLATCH_SH : Shared buffer page I/O latch
PAGEIOLATCH_UP : Update buffer page I/O latch

这里来举一个最容易发生的等待——“PAGEIOLATCH_SH”，以它做例子，看看这种等待是怎么发生的。

在 SQL Server 中，PAGEIOLATCH_SH 等待状态通常发生在以下情况下：

  1. 用户请求：一个用户请求需要读取整个 X 表，由 Worker X 执行。
  2. 读取数据页面：Worker X 在执行表扫描时，发现需要读取的数据页面 1:100 不在内存的缓存中。
  3. 缓冲池操作：SQL Server 在缓冲池中找到一个空闲页面空间，并申请一个 EX（排他）Latch，以防止其他线程在数据从磁盘读取之前访问或修改该页面。
  4. 异步 I/O 请求：Worker X 发起一个异步 I/O 请求，将页面 1:100 从磁盘读取到内存。
  5. 等待 SH Latch：由于 Worker X 已经持有 EX Latch，其他请求读取此页面的操作需要申请一个 SH（共享）Latch，导致后续的共享访问被阻塞，Worker X 自己也会被阻塞，产生 PAGEIOLATCH_SH 等待。
  6. I/O 完成：当异步 I/O 完成后，数据被读取到内存，并通知 Worker X，释放 EX Latch。
  7. 获取 SH Latch：Worker X 释放 EX Latch 后，获得了 SH Latch，读取数据页面 1:100 完成。

主要类型的 PAGEIOLATCH 等待：

  • PAGEIOLATCH_SH：通常发生在用户请求读取数据页面时，而 SQL Server 正在从磁盘将该页面读取到内存。如果页面是频繁访问的，则问题往往是 内存不足，导致频繁的磁盘读取，形成磁盘 I/O 瓶颈。
  • PAGEIOLATCH_EX：通常发生在用户对数据页面进行了修改，SQL Server 要将页面回写到磁盘时。如果磁盘的写入速度跟不上，便会导致此等待状态。

其他相关等待：

  • WRITELOG：表明任务正在等待将日志记录写入磁盘，通常是磁盘写入速度慢的表现。

总结：

PAGEIOLATCH_SH 和 PAGEIOLATCH_EX 等待状态通常反映了磁盘 I/O 瓶颈，其中 PAGEIOLATCH_SH 与内存不足导致频繁读取磁盘有关，而 PAGEIOLATCH_EX 与磁盘写入速度过慢有关。若 SQL Server 经常出现这些等待状态，可能意味着磁盘 I/O 性能无法满足 SQL Server 的需求，从而成为性能瓶颈。

4. PAGELATCH_x

PAGELATCH_x类型的Latch是SQL Server在缓冲池里的数据页面上经常加的另一类Latch。请注意， PAGELATCH_x和PAGEIOLATCH_x是完全不一样的。因为长得像，所以经常会被搞混。

  1. PAGEIOLATCH_x 与 PAGELATCH_x 的区别：
    • PAGEIOLATCH_x：与磁盘交互有关，用于处理磁盘与内存间的 I/O 操作。
    • PAGELATCH_x：完全在内存中，用于同步多个任务对数据页面的修改，防止页面内容被破坏。
  1. 数据页面的结构：
    数据页面分为三部分：
    • 页头：记录页面属性，如编号和空闲区域起始位置。
    • 页尾偏移量：记录每条数据行的起始位置。
    • 数据存储部分：保存具体的数据。
  1. PAGELATCH_x 的作用：
    当多个任务同时修改同一页面时，PAGELATCH 用于确保修改的先后顺序。例如：
    • 用户 A 插入数据 (3,300)，用户 B 插入数据 (4,400)。
    • 两者在逻辑上互不冲突，但物理存储时需要明确顺序。
    • SQL Server 为每个任务申请 EX Latch，先获得 Latch 的任务先修改页面，后释放资源，另一个任务再执行。
  1. PAGELATCH 等待问题：
    • PAGELATCH 的生存周期非常短，修改完成即释放资源。
    • 如果 PAGELATCH_x 成为频繁等待的资源，说明系统可能存在并发争用过多的问题。
      • SQL Server没有明显的内存和磁盘瓶颈。
      • 应用程序发来大量的并发语句在修改同一张表格里的记录，而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面，或者数量不多的几个页面上。这些页面有的时候也称为Hot Page。这样的瓶颈通常只会发生在并发用户比较多的、典型的OLTP系统上。
      • 这种瓶颈是无法通过提高硬件配置解决的，只有通过修改表格设计或者业务逻辑，让修改分散到尽可能多的页面上，才能提高并发性能。
  • 如何解决？
    • 分区是一个比较好的方法（经典的热点页问题）

5. tempdb上的PAGELATCH

在SQL Server中，不仅数据页修改会加Latch，系统页面（如SGAM、PFS、GAM）在修改时也会加Latch，这可能成为系统瓶颈。

问题产生原因：

  1. 创建新表时，SQL Server需要修改SGAM、PFS、GAM页面以标记页面为已使用。
  2. 对一般数据库，这种操作影响较小，但在tempdb中，频繁创建和删除临时表会导致这些系统页面的Latch竞争，特别是在高并发环境下。

解决方法：

为tempdb创建与服务器CPU数量相等且大小相同的数据文件。

  • 任务将均匀分布到多个数据文件中。
  • 每个时间点只有一个任务会修改同一数据文件上的系统页面，从而避免PAGELATCH瓶颈。

这里可以总结出tempdb最佳实践：

  • 根据SQL Server使用的CPU数量，为tempdb创建相同数量的数据文件。
  • 确保这些文件大小相同。
  • 避免tempdb空间耗尽，防止触发自动增长，以免任务集中在单一文件上形成瓶颈。

最后看一下其他等待状态：

5.1. LATCH_x

Latch等待的原因

  1. 孤儿Latch：
    • 某任务因访问越界（Access Violation）异常被强制终止，但未释放所有Latch资源，导致其他任务申请相同Latch时被阻塞。
  1. 其他资源瓶颈：
    • 内存不足、线程调度问题或磁盘性能不足导致Latch等待成为衍生问题。
  1. 文件自动增长：
    • 数据库文件空间用尽时，自动增长仅允许单个任务执行，其他任务需等待。
  1. SQL Server并发同步算法问题：
    • SQL Server的并发处理未优化，导致Latch等待。某些补丁已修复类似问题。

解决方案

  1. 检查异常日志：
    • 查看SQL Server日志（Errorlog），确认是否有访问越界异常。
  1. 确保系统资源充足：
    • 检查并解决内存、线程调度、磁盘等资源瓶颈。
  1. 优化文件管理：
    • 避免频繁触发文件自动增长，提前规划文件空间。
  1. 保持SQL Server健康：
    • 确保SQL Server无其他异常。
  1. 升级SQL Server版本：
    • 更新到最新版本，修复潜在的Latch处理问题。
  1. 重启SQL Server（如必要）：
    • 如果Latch孤儿问题无法解决，可通过重启清理Latch状态。

5.2. ASYNC_NETWORK_IO（NETWORK_IO）

ASYNC_NETWORK_IO等待的原因

  1. 客户端接收速度慢：
    • SQL Server准备结果集速度快于客户端接收速度，导致输出缓存被占满，任务进入ASYNC_NETWORK_IO等待状态。
  1. 网络瓶颈：
    • 网络传输速度不足以满足结果集的传输需求。
  1. 结果集过大：
    • 应用程序设计不合理，返回了超出用户需求的大量结果集（如几万或几十万行）。
  1. 客户端性能问题：
    • 客户端确认接收包速度慢，可能是由于：
      • 只取开头数据，未及时取完全部结果集。
      • 客户端CPU占满或运行缓慢（如Hang住）。
      • 内存或磁盘瓶颈。
  1. 分布式死锁：
    • SQL Server内阻塞导致长时间ASYNC_NETWORK_IO等待，与分布式死锁相关。

解决方案

  1. 优化应用程序设计：
    • 确保只返回客户端真正需要的数据，避免返回大结果集。
    • 优化程序逻辑，减少不必要的查询。
  1. 检查客户端性能：
    • 检查客户端CPU、内存、磁盘状况，确保正常运行。
    • 避免只取部分数据而未及时完成数据处理。
  1. 提升网络性能：
    • 确保网络带宽满足需求，减少网络延迟。
    • 适当增加SQL Server的Network Packet Size（需评估是否增加SQL Server内存分配负担）。
  1. 排查分布式死锁：
    • 检查阻塞情况，确认是否涉及分布式死锁并进行相应优化。
  1. 确认SQL Server运行健康：
    • ASYNC_NETWORK_IO通常是应用端问题，但确保SQL Server自身资源正常也是必要的基础。

5.3. 和内存相关的等待状态

当用户申请内参时无法申请到时，就可能会存在如下的等待状态，这时需要确认是否存在内存瓶颈。

CMEMTHREAD
SOS_RESERVEDMEMBLOCKLIST
RESOURCE_SEMAPHORE_QUERY_COMPILE

5.4. SQLTRACE_x

在繁忙的SQL Server上，开启SQL Trace（尤其是直接使用SQL Server Profiler）可能会影响性能，甚至成为瓶颈。当观察到因跟踪导致的等待时，应立即停止此类跟踪操作

6. 许多任务处于RUNNABLE状态

如果SQL Server没有遇到线程调度问题，没有报出17883/17884之类的警告，出现非常多的RUNNABLE任务通常有两种可能的原因。

  1. SQL Server CPU使用率已经接近100%，真的是没有足够的CPU资源来及时处理用户的并发任务
  2. SQL Server CPU使用率并不很高，小于50%
    1. 这是因为spinklock导致（2008之前可能经常发生，之后的版本基本上不会碰到）

发生时可以使用dbcc查看

DBCC SQLPERF(SPINLOCKSTATS)

解决方案：

DBCC FREESYSTEMCACHE(TokenAndPermUserStore)

知识库列表：

http://support.microsoft.com/kb/927396

7. 小结

下面来总结一下一个用户请求在其生命周期中，大致会经过哪些阶段，以及在各个阶段可能需要等待的资源。

  1. 客户端向SQL Server发出请求指令，指令经过网络层，SQL Server接收到
    • 客户端指令发送速度、网络传输性能以及应用程序自身性能问题都会影响SQL Server的接收速度和整体性能。
  1. SQL Server对收到的指令进行语法、语义检查，编译，生成新执行计划，或者找到缓存的执行计划重用
    • CPU资源：
    • 内存：

如果内存紧张时可能出现的等待事件

CMEMTHREAD
SOS_RESERVEDMEMBLOCKLIST
RESOURCE_SEMAPHORE_QUERY_COMPILE
    • 表格上的架构锁（Schema Lock）
    • 在SQL Server确认是否有现成的执行计划可用时，要在内存中进行搜索，这时候可能会有一些自旋锁
  1. 运行指令
    • 为指令运行申请内存。
    • 将所需数据从磁盘加载到内存（如果数据已经在内存中，就不需要做这一步），并整理内存空间。
    • 按执行计划扫描或查找内存中的数据页面。
    • 执行连接、计算（如Sum、Max、Sort等）。
    • 在tempdb创建临时对象（如表、变量）支持操作。
    • 修改内存缓冲区中的数据页面。
    • 提交事务前，将日志顺序写入日志文件。
    • 将结果集返回客户端。

最后一步将结果集返回客户端存在等待的话，等待状态就是ASYNC_NETWORK_IO。

以上这些动作都要在SQLOS里首先拿到一个Worker（也就是Thread），然后这个Worker还要能排上Scheduler，在CPU上运行。每一步可能存在的等待状态以及原因如下：

  • THREADPOOL：
    • 所有Worker繁忙且达到最大值，任务等待空闲Worker，而sys.dm_os_schedulers.``work_queue_count > 0。
  • RUNNABLE：
    • 任务有Worker但等待Scheduler，runnable_tasks_count > 1。
  • RUNNING：
    • 任务获得Scheduler运行；若任务耗CPU，可观察到某CPU使用率较高。
avatar
文章
阅读
粉丝