SQL Server 性能诊断（1） - 内存1. 概述首先了解一下Windows的一些内存术语： Virtual A

1. 概述

首先了解一下Windows的一些内存术语：

Virtual Address Space（虚拟地址空间）

- 它是一个应用程序能够申请访问的最大地址空间。在32位的服务器上，由于地址总线的长度是32位，所以寻址空间最大也就是232，换算成十进制，就是4 294 967 269B，也就是4 GB。在64位的服务器上，目前的系统限制是8 TB，可以说对于现有的应用来说，这个地址空间是无限的。需要澄清的是，虚拟地址空间里存放的数据信息不一定都在物理内存里。Windows会根据其使用情况，决定它们什么时候放在物理内存里，什么时候放在页面文件里（Page File）

Physical Memory（物理内存）

- 顾名思义，就是内存芯片提供的物理存储空间。访问存放在物理内存里的数据通常比访问硬盘要快得多。对SQL Server来讲，要频繁访问的数据对象必须能长时间地缓存在物理内存里，才能达到最优化的效率。

Reserved Memory（保留内存）

- 应用程序可以通过调用一些的Windows API，首先保留（Reserve）一块内存地址空间，以供将来使用。如果某块地址已经被其他对象保留，你去访问它，就会收到一个访问越界（Access Violation）错误。像Win32的VirtualAlloc和VirtualAllocEx之类的函数，就能提供这类功能。需要说明的是，这里保留的只是虚拟地址空间上的一段地址，而不是真正的物理内存空间，甚至根本没有分配空间/建立页面映射。

Committed Memory（提交内存）

- 将预先保留（Reserve）的内存页面正式提交（Commit）使用。提交的页面在访问时最终转换到物理内存中的有效页面。也就是说，正式在物理内存中申请一段空间，向页面中存入数据。
- 分两步来预留和提交内存，通过推迟页面提交来减少物理内存的使用。这对于需要潜在、大量和连续的内存缓冲区的应用程序是很有用的。地址空间可以被保留，在需要的时候再提交，而不是为了整个区域提交页面。

Shared Memory（共享内存）

- Windows提供了在进程和操作系统间共享内存的机制。共享内存（Shared Memory）可以定义为对一个以上的进程都是可见的内存，或存在于多个进程的虚拟地址空间。例如，如果两个进程使用相同的DLL，只把DLL的代码页装入内存一次，其他所有映射这个DLL的进程只要共享这些代码页就可以了。SQL Server作为一个Windows程序，也有一定的共享内存使用，但是总数不会很多。

Private Bytes

- 某个进程提交的地址空间（Committed Memory）中，非共享的部分。

Working Set（工作集）

- 进程中提交的内存，并且存放在物理内存的那一部分。

Page Fault（页面访问错误）（Soft/Hard）

- 访问一个存在于虚拟地址（Virtual Address Space）空间，但不存在于当前进程的工作集（Working Set）的页面，就会发生一次Page Fault。Windows内存管理组件会处理每个页面访问错误。首先它要判断是不是访问越界（Access Violation）。如果不是，会有两种情况。一种目标页面存在于硬盘上（例如，在Page File里），这种访问会带来一次硬盘读/写，我们称其为Hard Fault。另一种页面已经在物理内存中，但是还没有直接放在这个进程的Working Set下，需要Windows重新定向一次。这种访问不会带来硬盘操作，我们称为Soft Fault。一般来讲，Soft Fault带来的性能影响对SQL Server来讲可以忽略。管理员须要关心的只是Hard Fault。

System Working Set

- 像普通的进程一样，Windows系统也需要使用物理内存，它也有Working Set。其中主要有这几种：System Cache、Paged Pool、Non Page Pool，以及System Mapped Views。整体的使用可以通过性能监视器里的Memory:Cache Bytes来监控。发生在系统内存上的Page Fault可以通过Memory: Cache Faults/sec看到。

System Cache（系统高速缓存）

- 用于映射在系统高速缓存中打开的文件页面，以提高磁盘I/O任务的速度。可以通过性能监视器里的Memory: Cache Resident Bytes来监控。

Non Paged Pool（非页交换区）

- 包括一定范围的系统虚拟地址的内存交换区，可以保证在任何时候它都驻留在物理内存中，这样可以在没有I/O调页的情况下从任何地址空间访问。非页交换区可以在系统初始化时创建，并且被内核模式组建用来分配系统内存。可以通过性能监视器里的Memory: Pool Nonpaged Bytes来监控。这一块缓存可以被所有的进程共享，一个最常见的用途是存放所有对象的指针（Object Handles）。

Paged Pool（页交换区）

- 系统空间中可以调入或调出系统进程工作集（Working Set）的虚拟内存区域。页交换区在系统初始化时创建，被内存模式组件用来分配系统内存。可以通过性能监视器里的Memory: Pool paged Bytes和Memory: Pool Paged Resident Bytes来监控。

Stack（栈）

- 每个线程有两个栈，一个给内核模式（Kernel Mode），另一个给用户模式（User Mode）。每个栈是一块内存空间，存放线程运行的过程或函数的调用地址，以及所有参数的值。

In Process

- 运行在同一个进程的地址空间里。例如，一个进程需要加载一个DLL文件，这个DLL文件里的代码也会去申请内存。如果运行在同一个进程的地址空间里，最大的好处就是速度快，不需要做上下文切换（Context Switch）。但是缺点也很明显，如果DLL在内存使用上管理不善，出现严重的错误，反过来会影响整个进程的安全性。

Out of Process

- 运行在不同的进程地址空间里。以上面的DLL为例，像OLEDB这样的驱动程序可以配置成运行在DLLHOST.exe的进程空间里。

Memory Leak（内存泄漏）

- 一直不断地保留（Reserve）或提交（Commit）内存资源，哪怕它们不再被使用，也不释放给其他用户重用。SQL Server的内存泄漏有两种。一种是SQL Server作为一个进程，不断地向Windows申请内存资源，直到整个Windows内存耗尽。另一种是在SQL Server内部，某个SQL Server组件不断地申请内存，直到把SQL Server能申请到的所有内存都耗尽，使得其他SQL Server的功能组件不能正常使用内存。由于SQL Server完善的内存管理机制，前一种内存泄漏非常少见，而且问题的根本原因往往不是SQL Server自身。常见的是后一种泄漏，而且这种泄漏又往往和客户端的操作有直接关系。

在32位的 Windows 操作系统中，虚拟地址空间总共为 4GB，其中 2GB 分配给用户态，剩余 2GB 分配给核心态。过去，常见的做法是通过修改 Boot.ini 文件，将用户态的内存扩展到 3GB，这样就会将内核态的内存压缩至 1GB。尽管这种方法可以增加用户态可用的内存，但它也会压缩内核态的内存使用，可能导致内核操作不稳定，从而影响系统的整体稳定性，尤其是可能会对 SQL Server 等应用程序的运行造成不良影响。

另外一种被广泛使用的技术，就是Address Windowsing Extensions（地址空间扩展，AWE）。这是一种允许32位应用程序分配64 GB物理内存，并把视图或窗口映射到2 GB虚拟地址空间的机制。使用AWE解决了2 GB这个地址空间的限制，使得一个应用程序能够访问最多达64 GB的物理内存。SQL Server 2005/2008的企业版和标准版支持这个技术

各版本的Windows上支持的最大内存数

在分析一台服务器内存时，建议以下几点：

首先确认服务器是32位的还是64位的，SQL Server是32位的还是64位的
观察计数值的趋势和相互之间的关系，切忌用一两个值就做出结论
分析从检查内存使用开始
别忘记了检查Windows系统自己的内存使用
观察应用进程的内存使用
分析内存使用对CPU和I/O使用的影响

在 Windows 系统上，SQL Server 也受到虚拟地址空间的限制，特别是在内存使用上。默认情况下，SQL Server 会动态管理内存，根据系统的可用内存情况进行调整。SQL Server 中有两个重要的内存计数器：Total Server Memory 和 Target Server Memory，它们用于反映 SQL Server 的内存分配情况。

Total Server Memory

- 定义：这是 SQL Server 为 Buffer Pool（缓冲池）分配的内存总量（2012以前）。
- 作用：表示 SQL Server 实际上正在使用的内存，反映了 SQL Server 的内存使用量。

Target Server Memory

- 定义：这是 SQL Server 目标内存大小，表示 SQL Server 期望或希望使用的最大内存量。
- 作用：SQL Server 会根据当前系统的可用内存量，动态调整目标内存大小。Target Server Memory 是一个理想的内存大小，SQL Server 会努力达到或接近这个目标。

内存管理机制：

内存分配过程：

- SQL Server 会定期查询系统的可用物理内存，依据系统内存的使用情况动态调整内存分配。
- Total Server Memory 与 Target Server Memory 会进行对比，来判断 SQL Server 是否有足够的内存用于缓存新的数据。

当 Total Server Memory 小于 Target Server Memory 时：

- SQL Server 认为系统还有足够的内存来继续分配。因此，它会继续分配内存，以缓存更多的数据。此时，Total Server Memory 会逐渐增大。

当 Total Server Memory 等于 Target Server Memory 时：

- SQL Server 认为自己已经使用了系统可以提供的所有内存空间，无法继续分配更多内存来缓存新的数据。
- 如果 SQL Server 需要缓存新的数据，它会进行内存清理，从现有的内存空间中腾出空间来存放新的数据。

当 Total Server Memory 大于 Target Server Memory 时：

- 这是因为 SQL Server 在感知到 Windows 层面的内存压力后，调低了 Target Server Memory 的大小。
- 此时，SQL Server 会进行内存清理，将多余的内存释放出去，以减少自身的内存使用，确保系统不会因内存过度消耗而崩溃。

SQL Server 内存相关的可调参数

Min Server Memory
Max Server Memory
Set Working Set Size（慎用）
AWE Enabled（32位专属，SQL Server2012 废弃）
Lock pages in memory（企业版自动开启）

SQL Server 内存使用分类：

按用途分类

1. Database Cache
2. 各类Consumer
3. 线程内存
4. 第三方代码申请的内存（COM、XP等）

按申请方式分类

1. 先Reserve在做Commit
2. 直接Commit（Stolen方式）

按申请大小分类

1. Single Page Allocation（一次一个8K页面）
2. 大于8KB的申请叫做Multi-Page（以前叫MemToLeave），Multiple Page Allocation

各种内存分类方法之间的关系

SQL Server在不同服务器配置下各部分内存的最高使用上限（无AWE）

SQL Server在不同服务器配置下各部分内存的最高使用上限（有AWE）

一些内存使用错误理解：

Windows 上还有很多物理内存没有被使用，就意味着SQL Server不缺内存
SQL Server进程的Private Bytes（或Working Set）在不停地向上涨，说明SQL Server有内存泄漏的问题
Max Server Memory的值，就是SQL Server内存使用量的最大值。超过这个值就不正常
SQL Server的内存使用总量，就是性能监视器里面的SQL Server:Memory Manager - Total Server Memory的值
当系统有内存压力的时候，SQL Server总是会自动释放内存
SQL Server有办法将自己的内存绑定在物理内存里
增加MemToLeave的大小可以提高SQL Server的性能
增加物理内存一定会提高SQL Server的性能
Stolen的内存真的是偷来的吗

SQL Server 内存使用状况分析方法：

首先确认服务器是32位的还是64位的，SQL Server是32位的还是64位的，检查是否开启了AWE

SELECT 
    name, value, value_in_use
FROM 
    sys.configurations
WHERE 
    name = 'awe enabled';

2. SQL性能计数器

1. 整体分析
2. Windows自身使用分析
3. system pool 使用分析
4. process使用分析
5. SQL Server服务器内存总体使用情况的计数器
6. SQL Server内存用在了哪里

内存动态管理视图（DMV）
SQL Trace（本次不介绍）
错误日志

2005/2008 即使设置了Max Server Memory 值也可能控制不住内存，因为这个参数只针对Buffer Pool的上线，并不会对Muti Page（MemtoLeave）做限制。

2. 性能监视器收集（PerfMon）

-- （1）整体分析
Memory
  Committed Bytes
  Commit Limit
  Available MBytes
  Page File:% Usage和Page File:% Usage Peak
  Pages/sec
-- （2）Windows自身使用
Memory
  Cache Bytes（包含System Cache Resident Bytes、System Driver Resident Bytes、System Code Resident Bytes和Pool Paged Resident Bytes）
  System Cache Resident Bytes (System Cache)
  Pool Paged Resident Bytes
  System Driver Resident Bytes
  System Code Resident Bytes
-- （3）system pool

-- （4）process使用情况
Process
  % Processor Time
  Working Set  (includes shared pages)
  Virtual Bytes
  Private Bytes
  Page Faults/sec
  Handle Count
  Thread Count
  Pool Paged Bytes
  Pool Nonpaged Bytes
-- （5）监视SQL Server服务器内存总体使用情况的计数器
SQL Server:Memory Manager 
  Total Server Memory
  Target Server Memory
  Target Server Memory
  SQL Cache Memory
  Lock Memory
  Connection Memory
  Granted Workspace Memory
  Memory Grants Pending
-- （6）分析SQL Server内存用在了哪里
SQL Server:Buffer Manager
  Buffer Cache Hit Ratio
  Checkpoint pages/sec
  Database pages
  Free pages
  Lazy writes/sec
  Page life expectancy
  Page reads/sec
  Page writes/sec
  Stolen pages
  Target Pages
  Total Pages

性能监视器分析思路：

-- （1）整体分析

如果Committed Bytes已经接近或等于Commit Limit，说明系统的内存使用已经接近极限。
Available MBytes不断下降
Page File:% Usage和Page File:% Peak Usage越大说明物理内存数量和实际需求量差距巨大，性能越差
关注Pages/sec（类似Linux SWAP）

-- （2）Windows自身使用

Windows自身内存使用情况

- 32位 Windows 系统：正常情况下，Windows 自身的内存使用约为几百 MB。
- 64位 Windows 系统：在正常使用下，Windows 的内存使用可能达到 1GB 至 2GB，但如果出现内存泄漏或进行一些特殊操作，Windows 可能消耗数 GB 或十几 GB 的内存

性能计数器

- Memory: Cache Bytes
  监控系统的工作集（Working Set），即系统使用的物理内存总量。这包括高速缓存、页交换区、可调页的 ntoskrnl.exe 及驱动程序代码等。

- - 如果该值过高，表示系统占用了大量的物理内存，可能会导致 SQL Server 可用的内存受到挤压。

- Memory: System Cache Resident Bytes
  监控系统高速缓存使用的物理内存。高速缓存主要用于提升文件读写速度。

- - 如果此项值过高，意味着系统内存大部分用于缓存，这可能导致 SQL Server 可用内存不足，尤其在磁盘I/O负载高时可能更加明显。

- Memory: Pool Paged Resident Bytes
  监控页交换区占用的物理内存。

- - 如果该值过高，可能表示系统正在频繁地将内存页交换到磁盘，可能导致内存不足，影响 SQL Server 性能，尤其是内存紧张时。

- Memory: System Driver Resident Bytes
  监控设备驱动程序的内存使用。

- - 如果该值异常高，可能说明驱动程序存在问题，导致系统内存被过度占用，进而影响 SQL Server 和其他应用程序的运行。

- Memory: System Code Resident Bytes
  监控 ntoskrnl.exe 中的可调页代码使用的内存。

- - 该项的高使用可能表示系统内核的内存占用过高，可能会影响内存的整体使用，导致 SQL Server 可用内存减少。

-- （3）system pool

- Non-Paged Pool：监控此区域是否存在泄漏或异常占用。非分页内存不能被交换到磁盘，消耗过多会导致内存不足，影响系统和 SQL Server 的性能。
- Paged Pool：如果该区域的内存使用量过大，表示系统频繁进行内存分页操作，可能导致性能下降，特别是在 SQL Server 内存需求较高时。

-- （4）process使用情况

Process: % Processor Time

- 定义：目标进程消耗的 CPU 时间，包括用户态和核心态的时间。
- 判断：如果某个进程的 CPU 使用率持续过高（如达到 80% 或以上），说明该进程可能过于频繁地占用 CPU，导致系统响应缓慢，进而可能影响其他进程（包括 SQL Server）性能。高 CPU 使用可能与内存瓶颈相关，尤其是在处理大量内存请求时。

Process: Page Faults/sec

- 定义：目标进程发生的页面错误（Page Faults）数目。
- 判断：如果进程的页面错误频繁发生，说明该进程频繁地访问未加载到物理内存的虚拟内存，通常是由于物理内存不足，导致操作系统需要从磁盘中加载内存页。这种情况通常表明系统内存紧张，或者该进程在内存中占用了过多的虚拟空间。频繁的页面错误会导致系统性能下降，尤其是在 SQL Server 等内存密集型应用中。

Process: Handle Count

- 定义：目标进程打开的句柄数量，句柄是指向对象（如文件、内存区域、事件等）的指针。
- 判断：如果进程的句柄数量不断增加而没有相应地释放，可能发生了句柄泄漏（Handle Leak）。这会导致系统资源被浪费，进而可能耗尽系统句柄池，最终导致其他进程无法创建新句柄或系统崩溃。句柄泄漏在数据库应用（如 SQL Server）中尤为重要，因为它们可能影响文件、网络连接、内存块等的正常操作。

Process: Thread Count

- 定义：目标进程的线程数量。
- 判断：如果进程持续创建新线程，而不释放老线程，可能会导致线程泄漏（Thread Leak）。线程泄漏会消耗系统资源，特别是当线程不再被使用时，仍占用内存和 CPU。大量无用线程可能导致系统负担加重，进而影响其他进程的性能，特别是内存密集型进程。

Process: Pool Paged Bytes

- 定义：目标进程所使用的Paged Pool（页交换区）大小。
- 判断：Paged Pool 中的内存可以被分页到磁盘。如果此计数器值过高，表示该进程大量使用了系统的页交换区。这可能是因为进程的数据在内存不足时频繁被交换到磁盘，导致系统内存变得更加紧张，进而影响性能。尤其在 SQL Server 等对内存需求较高的应用中，这会导致严重的性能瓶颈。

Process: Pool Nonpaged Bytes

- 定义：目标进程所使用的Non-Paged Pool（非页交换区）大小。
- 判断：Non-Paged Pool 中的内存不能被分页到磁盘，因此当该区域的使用量过高时，会导致物理内存迅速被占满，影响系统性能。如果 Non-Paged Pool 的使用量持续增加，可能是由于进程存在内存泄漏或异常行为，特别是当进程持续分配而不释放内存时。对于内存敏感型的应用程序（如 SQL Server），这个值过高可能是导致内存不足和系统崩溃的一个信号。

Process: Working Set

- 定义：进程的工作集，指进程在物理内存中驻留的部分，即当前加载到内存中的虚拟地址空间。
- 判断：如果工作集过大，且没有被适时释放，说明进程占用了过多的物理内存，可能导致其他进程（例如 SQL Server）无法获取足够的内存。如果工作集的大小不断增加而无法回收，可能导致内存不足，甚至发生内存溢出（OutOfMemory）。需要检查该进程的内存管理是否合理，确保内存不会被无效地占用。

Process: Virtual Bytes

- 定义：进程所申请的虚拟地址空间大小，包括保留内存（Reserved Memory）和提交内存（Committed Memory）。
- 判断：如果虚拟字节数过大，说明进程申请了大量的虚拟内存，可能远超其实际需要。这可能会导致虚拟内存分配不足，特别是在 32 位操作系统中，虚拟地址空间有限。如果该值不断增加，可能意味着进程的内存需求过高，或者存在内存泄漏等问题。

Process: Private Bytes

- 定义：进程提交的地址空间（Committed Memory）中，非共享部分的大小。
- 判断：如果 private bytes 数量过大，表示进程分配了大量非共享的内存，这可能导致系统内存压力增大，特别是在 SQL Server 等共享内存较多的应用程序中。Private Bytes 的增加通常意味着进程在占用物理内存或虚拟内存，这可能会导致内存瓶颈，特别是在内存分配不足时。

如何判断内存瓶颈：

- - 内存占用过高：监控进程的 Working Set 和 Virtual Bytes。如果这些值过高，表示进程在物理内存或虚拟内存上占用了过多的资源，可能影响其他应用程序的性能。
  - 频繁的页面错误：高频率的 Page Faults/sec 表示进程频繁访问未加载到内存的页面，这通常是由于内存不足或虚拟内存过度使用引起的。
  - 资源泄漏：监控 Handle Count 和 Thread Count，如果这些值不断增加而不释放，可能是句柄泄漏或线程泄漏的迹象，导致系统资源耗尽，影响性能。
  - 内存池使用过高：如果 Paged Pool 或 Nonpaged Pool 的使用量过高，表明系统内存可能过度使用，尤其是内存池泄漏可能导致系统不稳定。
  - Private Bytes 过高：表示进程在使用大量的非共享内存，这可能会导致内存压力过大，影响其他进程。

-- （5）监视SQL Server服务器内存总体使用情况的计数器

Total Server Memory 和 Target Server Memory

- Total Server Memory（KB） ：表示 SQL Server 当前为缓冲池（Buffer Pool）分配的内存总量。这个值并不是 SQL Server 使用的全部内存，而是缓冲池所占用的内存大小。
- Target Server Memory（KB） ：表示 SQL Server 希望使用的最大内存量，通常基于系统内存状况进行动态调整。

判断内存瓶颈：

- Total Server Memory < Target Server Memory：表示 SQL Server 尚未使用完系统为它分配的内存资源。在这种情况下，SQL Server 会继续分配内存以缓存数据和执行计划，从而使内存使用逐渐增加。如果此情况长期存在且 Total Server Memory 持续增长，说明 SQL Server 在内存资源充足时正在有效利用内存，不太可能是瓶颈。
- Total Server Memory ≥ Target Server Memory：当 SQL Server 使用的内存达到或超过系统为其配置的最大内存时，意味着它不再申请更多内存来缓存数据或执行计划。如果在这种情况下，系统内存压力加大，SQL Server 可能会开始清理缓存，减少内存使用，直到 Total Server Memory 与 Target Server Memory 保持一致。如果发生内存压力，SQL Server 可能会释放内存，这通常表明存在内存瓶颈。
- Target Server Memory 小于 Total Server Memory：这是内存瓶颈的关键指标，说明 SQL Server 在感知到内存压力时，会主动减少内存使用。此时，SQL Server 会尽量清理缓存，直到两者的内存使用量相等。频繁出现这种情况可能表明系统的物理内存已经不足，或者 SQL Server 的内存需求已经接近系统的物理内存限制。

内存分配相关计数器

这些计数器反映了 SQL Server 内存的具体分配情况，帮助判断是否有特定的内存分配瓶颈。

Optimizer Memory（KB） ：查询优化器使用的动态内存总量。如果该值过高，可能表示查询优化器在执行复杂查询时使用了大量内存，可能会导致内存不足。
SQL Cache Memory（KB） ：用于 SQL Server 动态查询缓存的内存。如果此值过高，表示 SQL Server 缓存了大量的执行计划，可能导致内存资源耗尽。
Lock Memory（KB） ：用于锁的内存。如果锁的内存过高，可能表明有大量的并发操作或锁竞争，导致内存占用过多。
Connection Memory（KB） ：用于维护连接的内存。过高的值可能表示有大量的连接占用内存，可能导致系统内存资源紧张。
Granted Workspace Memory（KB） ：用于执行哈希、排序、大容量复制和索引创建等操作的内存。如果该值过高，可能说明 SQL Server 正在进行大量的资源密集型操作，导致内存消耗过大。

Memory Grants Pending

Memory Grants Pending：表示当前有多少进程等待获得内存授权。这个计数器是判断内存瓶颈的一个关键指标。如果 Memory Grants Pending 的值大于 0，表示当前有一个或多个进程的内存申请因为系统内存压力而被延迟，这通常意味着存在内存瓶颈。

判断内存瓶颈：

如果 Memory Grants Pending 不为 0，说明当前有请求因内存不足而被延迟分配。这通常意味着系统的内存资源紧张，SQL Server 无法及时分配足够的内存给需要执行的任务，造成性能瓶颈。

综合判断：

要判断 SQL Server 是否存在内存瓶颈，您可以综合分析以下几点：

Total Server Memory 与 Target Server Memory：如果 Total Server Memory 长时间超过 Target Server Memory，或者 Target Server Memory 被调整到远低于 Total Server Memory，表明 SQL Server 正在因内存压力而缩减内存使用，可能存在内存瓶颈。
Memory Grants Pending：如果该计数器的值大于 0，表示有进程等待内存分配，严重时可能会导致执行任务的延迟或失败，是内存压力的直接表现。
内存分配计数器：查看 Optimizer Memory、SQL Cache Memory、Lock Memory、Connection Memory 和 Granted Workspace Memory 等计数器的值，判断是否有某些操作过于消耗内存，导致整体内存资源不足。
内存清理：如果 SQL Server 频繁进行内存清理（Total Server Memory < Target Server Memory），并且内存压力较大，则可能是由于系统内存不足或 SQL Server 对内存的需求过高。

-- （6）分析SQL Server内存用在了哪里

Buffer Cache Hit Ratio

- 定义：缓存命中率，表示缓冲区中找到页面的比例，而不是从磁盘读取数据页的次数。
- 阈值：理想情况下，该比率应保持在 99%以上。如果该值低于 95%，就可能存在内存不足的问题，SQL Server 需要频繁从磁盘读取数据，这会导致性能下降。
- 判断瓶颈：低缓存命中率通常意味着 SQL Server 内存不足，或者内存未能有效缓存所有常用数据。可以通过增加可用内存来提高缓存命中率，减少磁盘 I/O 操作。

Checkpoint Pages/sec

- 定义：每秒刷写到磁盘的脏页数（由检查点或其他操作触发）。
- 判断瓶颈：该计数器与内存压力关系不大，更多地与用户的操作（如大量的 INSERT、UPDATE、DELETE 操作）相关。如果操作频繁导致脏页积累，可能会导致较高的值。这个值更常用于诊断磁盘 I/O 问题。

Database Pages

- 定义：缓冲池中包含数据库内容的页数，即数据库缓存的大小。
- 判断瓶颈：如果数据库页数接近缓冲池的总大小，并且系统无法缓存更多数据，说明内存已经饱和，可能会影响查询性能。

Free Pages

- 定义：缓冲池中所有空闲且可用的页面总数。
- 判断瓶颈：
- 当该值较低时，SQL Server 会开始执行 Lazy Writes，释放内存给其他用户进程。若该值经常下降到很低，且不能恢复到稳定状态，说明 SQL Server 内存已经紧张。
- 如果该值保持在相对稳定的水平，通常表明内存管理良好，内存资源充足。

Lazy Writes/sec

- 定义：每秒写入缓冲区的脏页数。Lazy Writer 是一个系统进程，用于将脏页写回磁盘，并将其释放出来供其他用户使用。
- 判断瓶颈：当 Lazy Writes/sec 较高时，表明 SQL Server 正在频繁清理脏页，通常意味着内存压力较大。内存瓶颈时，Lazy Writer 会被频繁触发。

Page Life Expectancy (PLE)

- 定义：页面在缓冲池中停留的时间（单位：秒），如果该页不被引用，则它将被清除。
- 判断瓶颈：如果 Page Life Expectancy 值频繁波动，并且不能保持稳定，通常表明 SQL Server 存在内存压力。当 SQL Server 内存不足时，页面的生命周期会变短，Lazy Writer 会被频繁触发，导致该值下降。

Page Reads/sec

- 定义：每秒从磁盘读取的物理数据页数。反映了 SQL Server 从磁盘读取数据的频率。
- 判断瓶颈：当该值较高时，通常表明 SQL Server 缺少足够的内存缓存数据，需要频繁从磁盘读取数据页。过高的 Page Reads/sec 可能导致性能下降，建议通过增加缓存、优化查询或调整数据库设计来减少物理读取操作。

Page Writes/sec

- 定义：每秒从缓冲池写入磁盘的物理数据页数。这个计数器主要与用户对数据的修改量相关。
- 判断瓶颈：与内存的关系不大，更多的是与用户的写入操作（如 INSERT、UPDATE、DELETE）有关。它的值过高可能表示用户的操作导致了大量数据修改，进而增加了磁盘 I/O。

Stolen Pages

- 定义：缓冲池中用于非数据库页面（如执行计划缓存）的页数。也反映了 Buffer Pool 中用于 SQL Server 其他功能（如执行计划缓存、操作系统内部管理等）的内存。
- 判断瓶颈：较高的 Stolen Pages 值表明内存资源被 SQL Server 非数据库操作所占用。如果该值较高，可能会影响数据库的内存缓存，导致性能下降。

Target Pages 和 Total Pages

- Target Pages：缓冲池中理想的页面数目。目标页面数反映了 SQL Server 希望达到的内存缓存量。
- Total Pages：缓冲池中实际使用的页面数目（包括数据库页面、可用页面和 Stolen 页）。
- 判断瓶颈：当 Total Pages 远高于 Target Pages 时，表示 SQL Server 内存使用过多，超出了其预期的缓冲池大小。这通常是由于系统内存不足或内存压力过大。

综合判断：

- 缓存命中率（Buffer Cache Hit Ratio）低于 95% ，通常意味着内存不足，SQL Server 经常需要从磁盘读取数据。
- Lazy Writes/sec 高，说明 SQL Server 正在频繁清理内存中的脏页，可能是由于内存压力过大。
- Free Pages 值持续较低，意味着缓冲池中的空闲页数不足，SQL Server 必须开始执行 Lazy Writes。
- Page Life Expectancy 经常波动，特别是出现显著下降时，表明内存压力较大。
- Page Reads/sec 高，反映了 SQL Server 从磁盘读取数据的频率过高，可能存在内存瓶颈。
- Total Pages 超过 Target Pages，表明 SQL Server 内存资源超出了其理想配置，内存压力加大。

3. 内存动态性能视图

查看SQL Server内存分配

-- 查看内存分配
select
    type,
    sum(virtual_memory_reserved_kb) as [VM Reserved], --Memory Clerk的名称
    sum(virtual_memory_committed_kb) as [VM Committed], --内存Clerk Reserve的虚拟内存量
    sum(awe_allocated_kb) as [AWE Allocated], --内存Clerk使用地址窗口化扩展插件（AWE）分配的内存量
    sum(shared_memory_reserved_kb) as [SM Reserved], --内存Clerk保留的共享内存量
    sum(shared_memory_committed_kb) as [SM Committed] --内存Clerk提交的共享内存量
    --,sum(multi_pages_kb) as [MultiPage Allocator], --通过Stolen分配的单页内存量（KB
    --sum(single_pages_kb) as [SinlgePage Allocator] --分配的多页内存量（KB
from
    sys.dm_os_memory_clerks
group by type
order by 2 desc

查看内存中的页面都有哪些表构成

-- 内存中的页面都有哪些表构成？
declare @name nvarchar(100)
declare @cmd nvarchar(1000)
declare dbnames cursor for
select name from master.dbo.sysdatabases
open dbnames
fetch next from dbnames into @name
while @@fetch_status = 0
begin
set  @cmd  =  'select  b.database_id,  db=db_name(b.database_id),p.object_id,p.index_id,
buffer_count=count(＊) from ' + @name + '.sys.allocation_units a, '
+ @name + '.sys.dm_os_buffer_descriptors b, ' + @name + '.sys.partitions p
where a.allocation_unit_id = b.allocation_unit_id
and a.container_id = p.hobt_id
and b.database_id = db_id(''' + @name + ''')
group by b.database_id,p.object_id, p.index_id
order by b.database_id, buffer_count desc'
exec (@cmd)
fetch next from dbnames into @name
end
close dbnames
deallocate dbnames
go

可执行两次比对SQL执行所需要的内存量

dbcc dropcleanbuffers
go
---- 在这里运行前面的内存查询脚本
Go
use adventureworks
go
select * from person.address
go
---- 在这里再次运行前面的内存查询脚本
Go

执行计划都缓存了什么？

-- 执行计划都缓存了些什么？哪些比较占内存,各种对象各占了多少内存
select objtype, sum(size_in_bytes) as sum_size_in_bytes, count(bucketid) as cache_counts from sys.dm_exec_cached_plans
group by objtype

缓存执行计划的对象

分析具体存储了哪些对象

-- 分析具体存储的对象
SELECT usecounts, refcounts, size_in_bytes, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY size_in_bytes DESC;
GO

4. 数据页面压力分析

数据页缓冲区存在压力时，会有如下表现特征：

SQL Server需要经常触发Lazy Writes，按访问的频度，把最近没有访问的数据页面回写到硬盘上的数据文件里，把最近没有使用的执行计划从内存里清除
SQL Server需要经常从数据文件里读数据页面，所以会有很多硬盘读
由于硬盘读取相对内存读取来讲，是件很慢的事，所以用户的语句会经常等待硬盘读写完成
由于执行计划会被经常清除，所以Buffer Pool里的Stolen内存部分应该不会很多
由于数据页会被经常清除，所以Page Life Expectancy不会很高，而且会经常下降

可以关注如下几个指标：

SQLServer:Buffer Manager - Lazy writes/sec
SQL Server:Buffer Manager - Page Life expectancy
SQL Server:Buffer Manager - Page reads/sec
SQL Server:Buffer Manager - Stolen pages

也可以检查动态性能视图

select * from sys.sysprocesses where lastwaittype like '%IO%';

压力有三部分：

外部压力
来自SQL Server自身database page使用需求的压力
来自Buffer Pool里的Stolen memory的压力
来自Multi-page（MemToLeave）的压力

第一种 - 外部压力特征：

SQLServer:Memory Manager - Total Server Memory有没有被压缩。
Memory:Available Mbytes有没有下降到一个比较低的值。
如果SQL Server没有使用AWE或Lock page in memory技术，Process上的内存计数器还是准的。可以看看Process:Private Bytes - sqlservr和Process:Working Set - sqlservr的值是不是也有了急剧的下降

解决方案：

加大服务器内存
调整SQL Server max server memory

第二种 - 内部压力特征：

SQLServer:Memory Manager - Total Server Memory一直维持在一个比较高的值，和SQLServer: Memory Manager - Target Server Memory相等。不会有Total Server Memory大于Target Server Memory的现象
其他共同特征

1. SQLServer:Buffer Manager - Lazy writes/sec：经常出现不为0
2. SQL Server:Buffer Manager - Page Life expectancy：经常有显著下降。
3. SQL Server:Buffer Manager - Page reads/sec：经常不为0。
4. SQL Server:Buffer Manager - Stolen pages：维持在一个比较低的水平，应该比Database page要小很多。
5. sys.sysprocesses动态管理视图中出现一些连接等待I/O完成的现象。

解决方案：

如果是一台32位的服务器，物理内存数大于4 GB，首先要确定AWE有没有成功开启，让SQL Server能够充分使用服务器上的内存。
如果SQL已经充分使用了服务器的内存，但是还不够，那可以考虑是不是给服务器再加一些物理内存。
如果scale up不容易，可以考虑scale out。一般SQL Server上都会放着多个数据库，每个数据库都要在内存里缓存数据页面。如果一台服务器的内存实在不够缓存多个数据库页面的需求，可以考虑将其中的一个或几个数据库移到新的服务器上。
跟踪SQL Server的运行，找到读取数据页最多的语句进行评估。如果这些语句天生就要读很多数据，那就要和应用开发人员商量，为什么每次要从SQL Server上读取这么多数据，是否有这个必要。如果语句只是要返回部分数据，但是因为表格上没有合适的索引，使得SQL Server选择了一个表扫描的执行计划，事实上很多数据是没必要读的，那就要优化数据库索引的设计，以优化语句的执行，减少内存使用。

第三种 - Stolen memory压力特征（比较罕见）：

查询sys.dm_os_memory_clerks这个系统管理视图的single_pages_kb字段。看是哪个clerk用掉了比较多的stolen内存

解决方案：

找到谁使用了那么多Stolen内存，以及原因

第四种 - 来自Multi-page（MemToLeave）的压力特征：

32位没有开AWE的SQL Server，地址空间本来就只有2 GB，又用-g参数大大扩展了MemToLeave的上限值
64位的SQL Server上，对Multi-page使用已经没有上限。SQL Server需要使用多少，就能够申请到多少。如果SQL Server调用了一些内存泄漏很厉害的第三方代码，64位上的内存虽然比较充裕，但也有被漏完的可能性

解决方案：

找到谁使用了MemToLeave内存

如何发现内存使用比较多的语句

-- 按照物理读的页面数排序，前50名。
SELECT TOP 50
qs.total_physical_reads,qs.execution_count,
qs.total_physical_reads /qs.execution_count as [Avg IO],
    SUBSTRING(qt.text,qs.statement_start_offset/2,
        (case when qs.statement_end_offset = -1
        then len(convert(nvarchar(max), qt.text)) * 2
        else qs.statement_end_offset end -qs.statement_start_offset)/2)
    as query_text,
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,
    qs.sql_handle,
    qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.total_physical_reads desc
-- 按照逻辑读的页面数排序，前50名。
SELECT TOP 50
qs.total_logical_reads,qs.execution_count,
        qs.total_logical_reads /qs.execution_count as [Avg IO],
    SUBSTRING(qt.text,qs.statement_start_offset/2,
        (case when qs.statement_end_offset = -1
        then len(convert(nvarchar(max), qt.text)) * 2
        else qs.statement_end_offset end -qs.statement_start_offset)/2)
    as query_text,
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,
    qs.sql_handle,
    qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.total_logical_reads desc

此方法有两个缺点：

这个视图里每一个语句记录的生存期与执行计划本身相关联。如果SQL Server有内存压力，把一部分执行计划从缓存中删除时，这些记录也会从该视图中删除。所以查询得到的结果不能保证其可靠性。可能有一些语句引起了很多页面读，但是当管理员运行语句的时候，它们的记录已经从内存里清除了。
视图里的是历史信息，从SQL Server启动就开始收集了。但是很多时候问题是在每天的某个特定时间段里发生的。（例如一家证券公司，每天早上开始交易的半个小时内，响应总是很慢。）从视图里看不出这些语句是什么时候执行的，什么时间段调用得比较频繁。所以可能找到的语句针对性不是很强。

5. Stolen Memory 缓存压力分析

Stolen内存的使用有些是有缓存的，例如执行计划、用户安全上下文等。所以如果SQL Server内存比较富裕，这部分的内存会像Database Pages一样，越缓存越多。另一些Stolen内存是没有缓存的，使用完毕以后，就会立刻被释放，供其他用户使用。像语义分析、优化，做排序，做Hash等，这些内存是用完就释放的。所以在32位的SQL Server上，虽然Stolen内存最多只能有不到2 GB，但是很少有Stolen内存不够用的情况。

如果SQL Server是64位，没有虚拟地址空间的限制，用户需要访问的数据量又比较小，物理内存十分富裕，Lazy Writer可能长时间不需要触发。这时候，SQL Server会将所有曾经生成的执行计划及相应的安全上下文都缓存起来。这部分Stolen内存可能会越积越多。最多会有多少呢？这和SQL Server的版本有关系。

版本	Target Memory 小于 8 GB	Target Memory 在 8 GB ~ 64 GB 之间	Target Memory 大于 64 GB
SQL Server 2000	最大 4 GB	N/A	N/A
SQL Server 2005 RTM & SP1	最多 75% 的 Target Memory	8 GB × 75% + (Target Memory - 8 GB) × 50%	8 GB × 75% + (64 GB - 8 GB) × 50% + (Target Memory - 64 GB) × 25%
SQL Server 2005 SP2 及以后	最多 75% 的 Target Memory	8 GB × 75% + (Target Memory - 8 GB) × 10%	8 GB × 75% + (64 GB - 8 GB) × 10% + (Target Memory - 64 GB) × 5%
SQL Server 2008 及以后版本	最多 75% 的 Target Memory	8 GB × 75% + (Target Memory - 8 GB) × 10%	8 GB × 75% + (64 GB - 8 GB) × 10% + (Target Memory - 64 GB) × 5%

SQL Server 2000：最多支持 4 GB 内存。
SQL Server 2005 RTM & SP1：内存分配根据 Target Memory 范围，低于 8 GB 时最多 75%，在 8 GB 到 64 GB 之间采用混合比例，超过 64 GB 的部分采用更低比例。
SQL Server 2005 SP2 及以后、SQL Server 2008 及以后：与 2005 RTM & SP1 类似，但对于 8 GB 到 64 GB 和超过 64 GB 的部分，分配比例显著降低。

像Database Pages一样，Stolen内存也面临着来自SQL Server进程外部的压力，以及SQL Server进程内部的压力。

外部压力

- 当Windows通知SQL Server要压缩内存的时候，整个Buffer Pool里的所有内存，包括Database Pages和Stolen Memory，都会面临着清理。这种压力，就是来自外部的压力。

内部压力

- 来自于Database Pages的挤压
- 来自于Stolen内存内部的一些始终未被清理的对象

表现特征与解决方法：

用户提交的请求因为缺少内存而不能完成，SQL Server返回错误信息（通常是错误701）
另一类是SQL Server内存空间申请出现了瓶颈，但是SQL Server可以通过压缩某些Clerk申请的内存数量，或者清理掉一些缓存来得到空余的内存，或者让用户等待一会，最终能完成用户提交的请求。这时，用户请求可以完成，不会报错，但是SQL Server的响应速度会受到影响

等待状态：

CMEMTHREAD（0x00B9）
SOS_RESERVEDMEMBLOCKLIST（0x007B）

解决方案：

避免使用这种带有大量参数，或者长“IN”子句的语句。
扩展MemToLeave的大小，或者使用64位的SQL Server，推迟SQL Server遇到瓶颈的时间。
定期运行DBCC FREEPROCCACHE语句，手工清除缓存的执行计划，缓解内存瓶颈。

等待状态：

RESOURCE_SEMAPHORE_QUERY_COMPILE（0x011A）

解决方案：

修改客户连接的行为，尽可能更多地使用存储过程，或者是使用参数化的T-SQL语句调用，减少语句编译量，增加执行计划的重用，避免大量连接同时申请内存做语句编译的现象
简化每次需要编译的语句的复杂度，降低编译需要的内存量。
当Stolen内存使用总量比较大的时候，也可以考虑定期运行DBCC FREEPROCCACHE语句，手工清除缓存的执行计划，保证Stolen内存一直处在一个比较富裕的状态。

6. Multi-Page缓存区压力分析

重新回顾概念：

SQL Server里内存分配按大小分只有两种。小于或等于8 KB的，SQL Server每次就给一个Page， 8 KB。这部分内存就是Buffer Pool。需要大于8 KB的，或者是加载在SQL Server进程内的第三方代码所申请的内存，就以大于8 KB的方式分配。在SQL Server 2005以后，把这部分内存叫作Multi-page allocation。
在32位的SQL Server上，Multi-page的数目是有限制的，所以也被称为MemToLeave。默认大小的计算公式是：

256 MB + 0.5 MB×(Max Threads number in sp_configure)

3. 在64位的SQL Server上，对Multi-page的数目不再做限制。而SQL Server的Max Server Memory设置，仅对Buffer Pool起作用。对这段内存也没有限制。差不多是需要多少，就可以使用多少。 4. Multi-page的用途主要有以下三个：

- 每个SQL进程的thread要用0.5 MB
- SQL Server自己申请的超过8 KB的Stolen内存
- SQL Server自己申请的超过8 KB的Stolen内存

SQL Server自己申请的超过8 KB的Stolen内存

造成的原因：

来自SQL Server内部

- 带有大量参数，或者长“IN”子句的语句。
- 有些连接把Network Packet Size设成的8 KB或更高，而这种连接的数目又几百上千。
- 客户端应用调用了一些复杂或数量巨大的XML功能。

来自非SQL Server自己的代码

- SQL CLR
- Linked Server
- 用sp_OACreate调用COM对象
- Extended Stored Procedures

7. 错误日志排查

Out of Memory（OOM）：701

Error: 701, Severity: 17, State: 123.
There is insufficient system memory to run this query.

2. 语句运行时没能及时申请到内存，最常见的是错误：8645

Error 8645: A time out occurred while waiting for memory resources to execute the query.
Re-run the query.

3. SQL Server无法创建新的线程供新连接使用：17189

Logon Error: 17189, Severity: 16, State: 1.
Logon SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login
or connection. Check the SQL Server error log and the Windows event logs for information
about possible related problems.

4. SQL Server被迫释放内存

2012-05-23 15:23:32.43 spid1s A significant part of sql server process memory has been
paged out. This may result in a performance degradation. Duration: 0 seconds. Working set
(KB): 1023400, committed (KB): 2143220, memory utilization: 50%.

5. MemToLeave用尽

2006-06-08 13:58:10.35 spid4601  WARNING:  Failed to reserve contiguous memory of
Size= 65536.

8. 2012 内存管理变化

在SQL 2005/2008的时候，8KB和大于8KB的内存分配是严格分开的，是由两个内存分配管理器来分别完成，所以会发生Buffer Pool这边有内存，而Multi-Page Allocator那边内存不够的情况。而SQL 2012的重大变化是所有的内存请求都将通过新的内存管理器来完成：

新的内存管理器支持分配所有的大小。Single page allocator和Multi-Page allocator都将退出历史的舞台。不论分配多大的内存大小，内存管理器都只是三种不同的内存页模型，普通页、锁定页（Locked Pages）和大内存页（Large Pages）。（大内存页只存在于64位版本中）
新的内存管理器已经为某些特定的负载做了额外的优化（如数据库关系型引擎和报表服务等）。
新的内存管理器是完全支持NUMA的。
虚拟地址空间的管理完全是动态的，特别是对32位的实例也是如此。
现在所有通过内存管理器分配出去的内存都受“max server memory”所控制。
32位实例不再支持AWE。

DMV的改变

DBCC MemoryStatus输出的改变

SQL Server 2008 R2 之前：Max Server Memory 仅限制了 Buffer Pool 和单页分配，不包括 Multi-page allocation 和 CLR allocation 等。
SQL Server 2012 及以后：引入了统一的 Any-size Page allocator，使得 Max Server Memory 能够控制更广泛的内存分配，包括 Multi-page allocations 和 SQL CLR 分配的内存。但 线程栈内存 和通过 VirtualAlloc 或 HeapAlloc 获取的内存 仍然不受控制。
-g 启动参数：主要用于调整 MemoryToLeave（MTL），在 SQL Server 2012 前控制大于 8KB 的内存请求分配，但 SQL Server 2012 后，由于内存管理器的改进，MTL 仅控制线程栈内存和一些通过系统调用分配的内存。

参考文献：

www.cnblogs.com/lulight/p/1…

《SQL Server 2012事实与管理实战指南》