内存管理体系结构指南Memory Management Architecture Guide

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

Windows 虚拟内存管理器Windows Virtual Memory Manager

提交的地址空间区域由 Windows 虚拟内存管理器 (VMM) 映射到可用的物理内存。The committed regions of address space are mapped to the available physical memory by the Windows Virtual Memory Manager (VMM).

有关不同操作系统所支持的物理内存量的详细信息,请参阅介绍 Windows 版本的内存限制的 Windows 文档。For more information on the amount of physical memory supported by different operating systems, see the Windows documentation on Memory Limits for Windows Releases.

虚拟内存系统允许虚拟内存超过物理内存,这样虚拟内存与物理内存的比率可以大于 1:1。Virtual memory systems allow the over-commitment of physical memory, so that the ratio of virtual-to-physical memory can exceed 1:1. 因此,大型程序在计算机上运行时可以具有多种物理内存配置。As a result, larger programs can run on computers with a variety of physical memory configurations. 但是,使用比所有进程的平均组合工作集大得多的虚拟内存可能会导致性能降低。However, using significantly more virtual memory than the combined average working sets of all the processes can cause poor performance.

SQL Server 内存体系结构SQL Server Memory Architecture

SQL ServerSQL Server 将根据需要动态获取并释放内存。dynamically acquires and frees memory as required. 虽然该选项仍然存在且在有些环境下需要用到,但通常情况下管理员不必指定为 SQL ServerSQL Server分配多少内存。Typically, an administrator does not have to specify how much memory should be allocated to SQL ServerSQL Server, although the option still exists and is required in some environments.

所有数据库软件的主要设计目标之一是尽量减少磁盘 I/O,因为磁盘的读取和写入操作占用大量资源。One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. SQL ServerSQL Server 在内存中生成缓冲池,用于保存从数据库读取的页。builds a buffer pool in memory to hold pages read from the database. SQL ServerSQL Server 中的大量代码专门用于尽可能减少磁盘与缓冲池之间的物理读写次数。Much of the code in SQL ServerSQL Server is dedicated to minimizing the number of physical reads and writes between the disk and the buffer pool. SQL ServerSQL Server 设法在以下两个目标之间达到平衡:tries to reach a balance between two goals:

  • 防止缓冲池变得过大,从而导致整个系统内存不足。Keep the buffer pool from becoming so big that the entire system is low on memory.
  • 尽量增加缓冲池达的大小,以便尽量减少数据库文件的物理 I/O。Minimize physical I/O to the database files by maximizing the size of the buffer pool.

备注

在负载过重的系统中,某些在运行时需要大量内存的大型查询不能获取所需的最小内存量,并在等待内存资源时收到超时错误。In a heavily loaded system, some large queries that require a large amount of memory to run cannot get the minimum amount of requested memory and receive a time-out error while waiting for memory resources. 若要解决此问题,请增大 query wait 选项To resolve this, increase the query wait Option. 对于并行查询,请考虑减小 最大并行度选项For a parallel query, consider reducing the max degree of parallelism Option.

备注

在负载过重而内存不足的系统中,对于查询计划中带有合并联接、排序和位图的查询,如果无法获得位图所需的最小内存量,可以删除位图。In a heavily loaded system under memory pressure, queries with merge join, sort and bitmap in the query plan can drop the bitmap when the queries do not get the minimum required memory for the bitmap. 这会影响查询性能,并且如果排序过程无法容纳在内存中,就会增加 tempdb 数据库中工作表的使用量,从而导致 tempdb 增大。This can affect the query performance and if the sorting process can not fit in memory, it can increase the usage of worktables in tempdb database, causing tempdb to grow. 若要解决此问题,可添加物理内存或优化查询以使用其他更迅速的查询计划。To resolve this problem add physical memory or tune the queries to use a different and faster query plan.

SQL ServerSQL Server 提供最大内存量Providing the maximum amount of memory to SQL ServerSQL Server

通过使用 AWE 和“锁定内存中的页”权限,可为 SQL ServerSQL Server 数据库引擎提供下列内存量。By using AWE and the Locked Pages in Memory privilege, you can provide the following amounts of memory to the SQL ServerSQL Server Database Engine.

备注

下表包含一个 32 位版本的列,这些版本不再可用。The following table includes a column for 32-bit versions, which are no longer available.

内存策略Memory policy 32 位 132-bit 1 64 位64-bit
常规内存Conventional memory 所有 SQL ServerSQL Server 版本。All SQL ServerSQL Server editions. 最大处理虚拟地址空间限制:Up to process virtual address space limit:
- 2 GB- 2 GB
- 3 GB,带有 /3gb 引导参数 2- 3 GB with /3gb boot parameter 2
- 4 GB,在 WOW64 3- 4 GB on WOW64 3
所有 SQL ServerSQL Server 版本。All SQL ServerSQL Server editions. 最大处理虚拟地址空间限制:Up to process virtual address space limit:
- 7 TB,带有 IA64 体系结构( SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本中不支持 IA64)- 7 TB with IA64 architecture (IA64 not supported in SQL Server 2012 (11.x)SQL Server 2012 (11.x) and above)
- 操作系统支持的最大值,带有 x64 体系结构 4- Operating system maximum with x64 architecture 4
AWE 机制(允许 SQL ServerSQL Server 在 32 位平台上超过处理虚拟地址空间限制。)AWE mechanism (Allows SQL ServerSQL Server to go beyond the process virtual address space limit on 32-bit platform.) SQL ServerSQL Server Standard、Enterprise 和 Developer 版本:缓冲池支持访问最多 64 GB 的内存。Standard, Enterprise, and Developer editions: Buffer pool is capable of accessing up to 64 GB of memory. 不适用 5Not applicable 5
“锁定内存页”操作系统 (OS) 权限(允许锁定物理内存,防止 OS 对锁定的内存进行分页。)6Lock pages in memory operating system (OS) privilege (allows locking physical memory, preventing OS paging of the locked memory.) 6 SQL ServerSQL Server Standard、Enterprise 和 Developer 版本:SQL ServerSQL Server 进程使用 AWE 机制所需。Standard, Enterprise, and Developer editions: Required for SQL ServerSQL Server process to use AWE mechanism. 通过 AWE 机制分配的内存不能出页。Memory allocated through AWE mechanism cannot be paged out.
授予此权限但未启用 AWE 不会对服务器产生影响。Granting this privilege without enabling AWE has no effect on the server.
仅在必要时使用,即有迹象表明正在换出 sqlservr 进程时。在这种情况下,错误日志将报告错误 17890,类似于以下示例:A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.Only used when necessary, namely if there are signs that sqlservr process is being paged out. In this case, error 17890 will be reported in the Errorlog, resembling the following example: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

1 32 位版本不可用 SQL Server 2014 (12.x)SQL Server 2014 (12.x)作为开头。1 32-bit versions are not available starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x).
2 /3gb 是一个操作系统启动参数。2 /3gb is an operating system boot parameter.
3 WOW64 (Windows on Windows 64) 是 32 位 SQL ServerSQL Server 在 64 位操作系统上运行的一种模式。3 WOW64 (Windows on Windows 64) is a mode in which 32-bit SQL ServerSQL Server runs on a 64-bit operating system.
4 SQL ServerSQL Server Standard Edition 最大支持 128 GB。4 SQL ServerSQL Server Standard Edition supports up to 128 GB. SQL ServerSQL Server Enterprise Edition 支持操作系统最大值。Enterprise Edition supports the operating system maximum.
5 请注意,sp_configure awe enabled 选项存在于 64 位 SQL ServerSQL Server上,但将被忽略。5 Note that the sp_configure awe enabled option was present on 64-bit SQL ServerSQL Server, but it is ignored.
6 如果授予“锁定内存中的页”权限 (LPIM)(在支持 AWE 的 32 位系统上或单独在 64 位系统上),建议也要设置最大服务器内存。6 If lock pages in memory privilege (LPIM) is granted (either on 32-bit for AWE support or on 64-bit by itself), we recommend also setting max server memory. 有关 LPIM 的详细信息,请参阅“服务器内存”服务器配置选项For more information on LPIM, refer to Server Memory Server Configuration Options

备注

旧版 SQL ServerSQL Server 可在 32 位操作系统上运行。Older versions of SQL ServerSQL Server could run on a 32-bit operating system. 在 32 位操作系统上访问超过 4 GB 的内存需要地址窗口化扩展插件 (AWE) 对内存进行管理。Accessing more than 4 gigabytes (GB) of memory on a 32-bit operating system required Address Windowing Extensions (AWE) to manage the memory. 如果 SQL ServerSQL Server 在 64 位操作系统上运行,则不需要。This is not necessary when SQL ServerSQL Server is running on 64-bit operation systems. 有关 AWE 的详细信息,请参阅 SQL Server 2008SQL Server 2008 文档中的进程地址空间管理大型数据库的内存For more information about AWE, see Process Address Space and Managing Memory for Large Databases in the SQL Server 2008SQL Server 2008 documentation.

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以来对内存管理的更改Changes to Memory Management starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)

SQL ServerSQL Server 的早期版本(SQL Server 2005 (9.x)SQL Server 2005 (9.x)SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2)中,使用五种不同的机制分配内存:In earlier versions of SQL ServerSQL Server ( SQL Server 2005 (9.x)SQL Server 2005 (9.x), SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2), memory allocation was done using five different mechanisms:

  • 单页分配器 (SPA),它仅包含 SQL ServerSQL Server 进程中小于或等于 8 KB 的内存分配。Single-Page Allocator (SPA), including only memory allocations that were less than, or equal to 8-KB in the SQL ServerSQL Server process. “max server memory (MB)”和“min server memory (MB)”这两个配置选项确定 SPA 消耗的物理内存的限制 。The max server memory (MB) and min server memory (MB) configuration options determined the limits of physical memory that the SPA consumed. 同时,缓冲池也是用于 SPA 的机制,并且还是最大的单页分配使用者。The Buffer Pool was simultaneously the mechanism for SPA, and the largest consumer of single-page allocations.
  • 多页分配器 (MPA) ,用于需要 8 KB 以上的内存分配。Multi-Page Allocator (MPA), for memory allocations that request more than 8-KB.
  • CLR 分配器,包含 CLR 初始化期间创建的 SQL CLR 堆及其全局分配。CLR Allocator, including the SQL CLR heaps and its global allocations that are created during CLR initialization.
  • 用于 SQL ServerSQL Server 进程中的 线程堆栈 的内存分配。Memory allocations for thread stacks in the SQL ServerSQL Server process.
  • 直接 Windows 分配 (DWA) ,用于直接向 windows 进行的内存分配请求。Direct Windows allocations (DWA), for memory allocation requests made directly to Windows. 包括由加载到 SQL ServerSQL Server 进程中的模块使用 Windows 堆和直接进行虚拟分配。These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL ServerSQL Server process. 此类内存分配请求的示例包括从扩展存储过程 DLL 分配、使用自动化过程(sp_OA 调用)创建的对象以及从链接服务器提供程序分配。Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,单页分配、多页分配和 CLR 分配全部合并到“任意大小”页分配器中,受到由“最大服务器内存(MB)”和“最小服务器内存(MB)”配置选项控制的内存限制 。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), Single-Page allocations, Multi-Page allocations and CLR allocations are all consolidated into a "Any size" Page Allocator, and it's included in memory limits that are controlled by max server memory (MB) and min server memory (MB) configuration options. 此更改使通过 SQL ServerSQL Server 内存管理器的所有内存要求能更准确地调整大小。This change provided a more accurate sizing ability for all memory requirements that go through the SQL ServerSQL Server memory manager.

重要

升级到 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2019 (15.x)SQL Server 2019 (15.x) 后,请仔细检查当前的“max server memory (MB)”和“min server memory (MB)”配置 。Carefully review your current max server memory (MB) and min server memory (MB) configurations after you upgrade to SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2019 (15.x)SQL Server 2019 (15.x). 这是因为从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,与早期版本相比,这些配置现在包括并用于更多内存分配。This is because starting in SQL Server 2012 (11.x)SQL Server 2012 (11.x), such configurations now include and account for more memory allocations compared to earlier versions. 这些更改适用于 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) 的 32 位和 64 位版本,以及 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2019 (15.x)SQL Server 2019 (15.x) 的 64 位版本。These changes apply to both 32-bit and 64-bit versions of SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x), and 64-bit versions of SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2019 (15.x)SQL Server 2019 (15.x).

下表指示特定类型的内存分配是否受“max server memory (MB)和“min server memory (MB)”配置选项控制 :The following table indicates whether a specific type of memory allocation is controlled by the max server memory (MB) and min server memory (MB) configuration options:

内存分配的类型Type of memory allocation SQL Server 2005 (9.x)SQL Server 2005 (9.x)SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2, SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2 SQL Server 2012 (11.x)SQL Server 2012 (11.x)Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)
单页分配Single-page allocations Yes 是,合并到“任意大小”页分配Yes, consolidated into "any size" page allocations
多页分配Multi-page allocations No 是,合并到“任意大小”页分配Yes, consolidated into "any size" page allocations
CLR 分配CLR allocations No Yes
线程堆栈内存Thread stacks memory No No
从 Windows 直接分配Direct allocations from Windows No No

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,SQL ServerSQL Server 可能会分配比 max server memory 设置中指定的值更多的内存。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), SQL ServerSQL Server might allocate more memory than the value specified in the max server memory setting. 当 Total Server Memory (KB) 值已达到 Total Server Memory (KB)(由 max server memory 指定)时,可能会出现这种情况。This behavior may occur when the Total Server Memory (KB) value has already reached the Target Server Memory (KB) setting (as specified by max server memory). 如果因内存碎片造成连续空闲内存不足,无法满足多页内存请求的需求(超过 8 KB),SQL ServerSQL Server 可以执行超额承诺使用量,而不是拒绝内存请求。If there is insufficient contiguous free memory to meet the demand of multi-page memory requests (more than 8 KB) because of memory fragmentation, SQL ServerSQL Server can perform over-commitment instead of rejecting the memory request.

只要执行此分配,资源监视器后台任务就会开始向所有内存消耗者发送信号,指示其释放已分配的内存,并尝试使 Target Server Memory (KB) 值低于 Target Server Memory (KB) 规范 。As soon as this allocation is performed, the Resource Monitor background task starts to signal all memory consumers to release the allocated memory, and tries to bring the Total Server Memory (KB) value below the Target Server Memory (KB) specification. 因此,SQL ServerSQL Server 内存使用情况可短暂超过 max server memory 设置。Therefore, SQL ServerSQL Server memory usage could briefly exceed the max server memory setting. 在这种情况下,Total Server Memory (KB) 性能计数器读取将超过 max server memory 和 Target Server Memory (KB) 设置 。In this situation, the Total Server Memory (KB) performance counter reading will exceed the max server memory and Target Server Memory (KB) settings.

在以下操作中通常会观察到此行为:This behavior is typically observed during the following operations:

  • 大型列存储索引查询。Large Columnstore index queries.
  • 列存储索引(重新)生成,该操作使用大量内存来执行哈希和排序操作。Columnstore index (re)builds, which use large volumes of memory to perform Hash and Sort operations.
  • 需要较大内存缓冲区的备份操作。Backup operations that require large memory buffers.
  • 需要存储较大输入参数的跟踪操作。Tracing operations that have to store large input parameters.

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以来对“memory_to_reserve”的更改Changes to "memory_to_reserve" starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)

在早期版本的 SQL Server(SQL Server 2005 (9.x)SQL Server 2005 (9.x)SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2)中,SQL ServerSQL Server 内存管理器保留了一部分进程虚拟地址空间 (VAS),供多页分配器 (MPA)、CLR 分配器、用于 SQL Server 进程中的线程堆栈的内存分配以及直接 Windows 分配 (DWA) 使用 。In earlier versions of SQL Server ( SQL Server 2005 (9.x)SQL Server 2005 (9.x), SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2), the SQL ServerSQL Server memory manager set aside a part of the process virtual address space (VAS) for use by the Multi-Page Allocator (MPA), CLR Allocator, memory allocations for thread stacks in the SQL Server process, and Direct Windows allocations (DWA). 这一部分虚拟地址空间也称为“Mem-To-Leave”或“非缓冲池”区域。This part of the virtual address space is also known as "Mem-To-Leave" or "non-Buffer Pool" region.

为这些分配保留的虚拟地址空间是由 memory_to_reserve 配置选项确定的。The virtual address space that is reserved for these allocations is determined by the memory_to_reserve configuration option. SQL ServerSQL Server 使用的默认值是 256 MB。The default value that SQL ServerSQL Server uses is 256 MB. 要替代该默认值,请使用 SQL ServerSQL Server -g 启动参数。To override the default value, use the SQL ServerSQL Server -g startup parameter. 有关 -g 启动参数的信息,请参阅介绍数据库引擎服务启动选项的文档页。Refer to the documentation page on Database Engine Service Startup Options for information on the -g startup parameter.

因为从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,新的“任何大小”页分配器也处理大于8 KB 的分配,所以 memory_to_reserve 值不包括多页分配。Because starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), the new "any size" page allocator also handles allocations greater than 8 KB, the memory_to_reserve value does not include the multi-page allocations. 除了此更改以外,其他设置都与此配置选项相同。Except for this change, everything else remains the same with this configuration option.

下表指示特定类型的内存分配是否属于 SQL ServerSQL Server 进程虚拟地址空间的 memory_to_reserve 区域:The following table indicates whether a specific type of memory allocation falls into the memory_to_reserve region of the virtual address space for the SQL ServerSQL Server process:

内存分配的类型Type of memory allocation SQL Server 2005 (9.x)SQL Server 2005 (9.x)SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2, SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2 SQL Server 2012 (11.x)SQL Server 2012 (11.x)Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x)
单页分配Single-page allocations No 否,合并到“任意大小”页分配No, consolidated into "any size" page allocations
多页分配Multi-page allocations Yes 否,合并到“任意大小”页分配No, consolidated into "any size" page allocations
CLR 分配CLR allocations Yes Yes
线程堆栈内存Thread stacks memory Yes Yes
从 Windows 直接分配Direct allocations from Windows Yes Yes

动态内存管理Dynamic Memory Management

SQL Server 数据库引擎SQL Server Database Engine的默认内存管理行为是获取尽可能多的内存而不会造成系统内存短缺。The default memory management behavior of the SQL Server 数据库引擎SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. SQL Server 数据库引擎SQL Server Database Engine通过使用 Microsoft Windows 中的内存通知 API 来实现这一点。The SQL Server 数据库引擎SQL Server Database Engine does this by using the Memory Notification APIs in Microsoft Windows.

SQL ServerSQL Server 动态使用内存时,它会定期查询系统以确定可用内存量。When SQL ServerSQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. 保持此可用内存可避免操作系统 (OS) 进行分页。Maintaining this free memory prevents the operating system (OS) from paging. 如果可用内存较少, SQL ServerSQL Server 将会释放内存以供操作系统使用。If less memory is free, SQL ServerSQL Server releases memory to the OS. 如果有更多的内存可用, SQL ServerSQL Server 可能会分配更多的内存。If more memory is free, SQL ServerSQL Server may allocate more memory. SQL ServerSQL Server 仅在其工作负荷需要更多内存时才增加内存;空闲的服务器不会增加其虚拟地址空间的大小。adds memory only when its workload requires more memory; a server at rest does not increase the size of its virtual address space.

Max server memory 控制 SQL ServerSQL Server 内存分配、编译内存、所有缓存(包括缓冲池)、查询执行内存授予锁管理器内存和 CLR1 内存(实质上是 sys.dm_os_memory_clerks 中存在的所有内存分配器) 。Max server memory controls the SQL ServerSQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR 1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks).

1SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,在 max_server_memory 分配下管理 CLR 内存。1 CLR memory is managed under max_server_memory allocations starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x).

以下查询返回有关当前分配内存的信息:The following query returns information about currently allocated memory:

SELECT 
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage, 
    process_physical_memory_low AS sql_process_physical_memory_low, 
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;  

线程堆栈1、CLR2、扩展过程 .dll 文件、分布式查询引用的 OLE DB 提供程序以及Transact-SQLTransact-SQL 语句中引用的自动化对象的内存以及由非 SQL ServerSQL Server DLL 分配的任何内存均不受最大服务器内存控制。Memory for thread stacks 1, CLR 2, extended procedure .dll files, the OLE DB providers referenced by distributed queries, automation objects referenced in Transact-SQLTransact-SQL statements, and any memory allocated by a non SQL ServerSQL Server DLL are not controlled by max server memory.

1 有关为当前主机中给定数量的关联 CPU 计算得出的默认工作线程数的信息,请参阅介绍如何“配置最大工作线程数”服务器配置选项的文档页。面。1 Refer to the documentation page on how to Configure the max worker threads Server Configuration Option, for information on the calculated default worker threads for a given number of affinitized CPUs in the current host. SQL ServerSQL Server 堆栈大小如下所示:stack sizes are as follows:

SQL Server 体系结构SQL Server Architecture OS 体系结构OS Architecture 堆栈大小Stack Size
x86(32 位)x86 (32-bit) x86(32 位)x86 (32-bit) 512 KB512 KB
x86(32 位)x86 (32-bit) x64 (64 位)x64 (64-bit) 768 KB768 KB
x64 (64 位)x64 (64-bit) x64 (64 位)x64 (64-bit) 2048 KB2048 KB
IA64 (Itanium)IA64 (Itanium) IA64 (Itanium)IA64 (Itanium) 4096 KB4096 KB

2SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,在 max_server_memory 分配下管理 CLR 内存。2 CLR memory is managed under max_server_memory allocations starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x).

SQL ServerSQL Server 使用内存通知 API QueryMemoryResourceNotification 来确定 SQL ServerSQL Server 内存管理器何时可以分配内存和释放内存。uses the memory notification API QueryMemoryResourceNotification to determine when the SQL ServerSQL Server Memory Manager may allocate memory and release memory.

启动 SQL ServerSQL Server 时,它将基于多个参数(例如系统的物理内存量、服务器线程数和各个引导参数)计算缓冲池的虚拟地址空间的大小。When SQL ServerSQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. SQL ServerSQL Server 将为缓冲池保留计算得到的进程虚拟地址空间量,但它仅为当前负荷获取(提交)所需的物理内存量。reserves the computed amount of its process virtual address space for the buffer pool, but it acquires (commits) only the required amount of physical memory for the current load.

然后实例将继续获取支持工作负荷所需的内存。The instance then continues to acquire memory as needed to support the workload. 随着用户连接和运行查询的逐步增多, SQL ServerSQL Server 将按需获取更多的物理内存。As more users connect and run queries, SQL ServerSQL Server acquires the additional physical memory on demand. SQL ServerSQL Server 实例将继续获取物理内存,直到达到自己的最大服务器内存分配目标或操作系统指示不再有可用剩余内存;如果该实例获取的内存超过最小服务器内存设置,并且操作系统指示可用内存短缺,将释放内存。A SQL ServerSQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or the OS indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and the OS indicates that there is a shortage of free memory.

随着在运行 SQL ServerSQL Server实例的计算机上启动其他应用程序,这些应用程序将会占用内存,从而使可用物理内存量降到 SQL ServerSQL Server 目标以下。As other applications are started on a computer running an instance of SQL ServerSQL Server, they consume memory and the amount of free physical memory drops below the SQL ServerSQL Server target. SQL ServerSQL Server 实例将调整其内存使用量。The instance of SQL ServerSQL Server adjusts its memory consumption. 如果另一个应用程序已停止,并且可用内存增多, SQL ServerSQL Server 的实例会增加其内存分配的大小。If another application is stopped and more memory becomes available, the instance of SQL ServerSQL Server increases the size of its memory allocation. SQL ServerSQL Server 每秒可释放和获取几 MB 的内存,从而根据内存分配变化快速做出调整。can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.

min server memory 和 max server memory 的影响Effects of min and max server memory

“min server memory”和“max server memory”配置选项建立 SQL ServerSQL Server 数据库引擎的缓冲池和其他缓存使用的内存量的上限和下限 。The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool and other caches of the SQL ServerSQL Server Database Engine. 缓冲池并不立即获取最小服务器内存中指定的内存量。The buffer pool does not immediately acquire the amount of memory specified in min server memory. 缓冲池启动时只使用初始化所需的内存。The buffer pool starts with only the memory required to initialize. 随着SQL Server 数据库引擎SQL Server Database Engine工作负荷的增加,它将继续获取支持工作负荷所需的内存。As the SQL Server 数据库引擎SQL Server Database Engine workload increases, it keeps acquiring the memory required to support the workload. 在达到最小服务器内存中指定的内存量之前,缓冲池不会释放它获取的任何内存。The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory. 达到最小服务器内存后,缓冲池将使用标准算法,根据需要来获取和释放内存。Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. 唯一的区别是缓冲池从不将内存分配降到最小服务器内存所指定的水平下,也从不获取超过最大服务器内存所指定水平的内存。The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory.

备注

作为进程的SQL ServerSQL Server 将获取超过最大服务器内存选项所指定的内存。SQL ServerSQL Server as a process acquires more memory than specified by max server memory option. 内部和外部组件都可以分配缓冲池以外的内存,这将占用额外内存,但是分配给缓冲池的内存通常仍表示 SQL ServerSQL Server 占用的内存的最大部分。Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually still represents the largest portion of memory consumed by SQL ServerSQL Server.

SQL Server 数据库引擎SQL Server Database Engine获取的内存量完全取决于放置在实例上的工作负荷。The amount of memory acquired by the SQL Server 数据库引擎SQL Server Database Engine is entirely dependent on the workload placed on the instance. 不处理很多请求的 SQL ServerSQL Server 实例可能永远不会达到最小服务器内存。A SQL ServerSQL Server instance that is not processing many requests may never reach min server memory.

如果为 min server memory 和 max server memory 指定相同的值,则一旦分配给 SQL Server 数据库引擎SQL Server Database Engine 的内存达到该值,SQL Server 数据库引擎SQL Server Database Engine 将停止为缓冲池动态释放和获取内存。If the same value is specified for both min server memory and max server memory, then once the memory allocated to the SQL Server 数据库引擎SQL Server Database Engine reaches that value, the SQL Server 数据库引擎SQL Server Database Engine stops dynamically freeing and acquiring memory for the buffer pool.

如果在运行 SQL ServerSQL Server 实例的计算机上频繁启动或停止其他应用程序,启动这些应用程序所需的时间可能会因 SQL ServerSQL Server 实例分配和释放内存而延长。If an instance of SQL ServerSQL Server is running on a computer where other applications are frequently stopped or started, the allocation and deallocation of memory by the instance of SQL ServerSQL Server may slow the startup times of other applications. 另外,如果 SQL ServerSQL Server 是几个在一台计算机上运行的服务器应用程序中的一个,系统管理员可能需要控制分配给 SQL ServerSQL Server的内存量。Also, if SQL ServerSQL Server is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to SQL ServerSQL Server. 在这些情况下,可以使用最小服务器内存和最大服务器内存选项控制 SQL ServerSQL Server 可使用的内存量。In these cases, you can use the min server memory and max server memory options to control how much memory SQL ServerSQL Server can use. “min server memory”和“max server memory”选项均以 MB 为单位指定 。The min server memory and max server memory options are specified in megabytes. 有关详细信息,请参阅 服务器内存配置选项For more information, see Server Memory Configuration Options.

SQL Server 对象规范使用的内存Memory used by SQL Server objects specifications

以下列表介绍了 SQL ServerSQL Server中不同对象所用内存量的近似值。The following list describes the approximate amount of memory used by different objects in SQL ServerSQL Server. 列出的数值为估计值,根据环境以及创建对象的方式可能有所不同:The amounts listed are estimates and can vary depending on the environment and how objects are created:

  • 锁(由锁管理器维护):每个所有者 64 字节 + 32 字节Lock (as maintained by the Lock Manager): 64 bytes + 32 bytes per owner
  • 用户连接:约为 (3 * network_packet_size + 94 kb)User connection: Approximately (3 * network_packet_size + 94 kb)

网络数据包大小是表格数据模式 (TDS) 数据包的大小,该数据包用于应用程序和 SQL ServerSQL Server 数据库引擎之间的通信。The network packet size is the size of the tabular data scheme (TDS) packets that are used to communicate between applications and the SQL ServerSQL Server Database Engine. 默认的数据包大小为 4 KB,由“网络数据包大小”配置选项控制。The default packet size is 4 KB, and is controlled by the network packet size configuration option.

启用多个活动的结果集 (MARS) 时,用户连接约为 (3 + 3 *num_logical_connections)* network_packet_size + 94 KBWhen multiple active result sets (MARS) are enabled, the user connection is approximately (3 + 3 * num_logical_connections) * network_packet_size + 94 KB

min memory per query 的影响Effects of min memory per query

min memory per query 配置选项设定将为执行查询分配的最小内存量 (KB)。The min memory per query configuration option establishes the minimum amount of memory (in kilobytes) that will be allocated for the execution of a query. 这也称为最小内存授予。This is also known as the minimum memory grant. 所有查询必须等待,直到:可确保提供请求的最小内存,可以启动执行,或者已超过查询等待服务器配置选项中指定的值。All queries must wait until the minimum memory requested can be secured, before execution can start, or until the value specified in the query wait server configuration option is exceeded. 此方案中累积的等待类型是 RESOURCE_SEMAPHORE。The wait type that is accumulated in this scenario is RESOURCE_SEMAPHORE.

重要

不要将 min memory per query 服务器配置选项设置过高,尤其是在非常繁忙的系统上,因为这样做可能导致:Do not set the min memory per query server configuration option too high, especially on very busy systems, because doing so could lead to:

  • 内存资源竞争加剧。Increased competition for memory resources.
  • 即使所需的内存在运行时低于此配置,也会通过增加每次单个查询的内存量来降低并发。Decreased concurrency by increasing the amount of memory for every single query, even if the required memory at runtime is lower that this configuration.

有关使用此配置的建议,请参阅配置 min memory per query 服务器配置选项For recommendations on using this configuration, see Configure the min memory per query Server Configuration Option.

内存授予注意事项Memory grant considerations

对于“行模式执行”,任何情况下都不得超过初始内存授予。For row mode execution, the initial memory grant cannot be exceeded under any condition. 如果执行哈希或排序操作需要的内存多于初始授予,这些内存将溢出到磁盘 。If more memory than the initial grant is needed to execute hash or sort operations, then these will spill to disk. 溢出的哈希操作由 TempDB 中的 Workfile 支持,而溢出的排序操作由工作表支持。A hash operation that spills is supported by a Workfile in TempDB, while a sort operation that spills is supported by a Worktable.

排序操作期间发生的溢出称为 Sort WarningA spill that occurs during a Sort operation is known as a Sort Warning. Sort Warning 指示排序操作的内存不足。Sort warnings indicate that sort operations do not fit into memory. 这不包括涉及创建索引的排序操作,只包括查询内的排序操作(例如 SELECT 语句中使用的 ORDER BY 子句)。This does not include sort operations involving the creation of indexes, only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).

哈希操作期间发生的溢出称为 Hash WarningA spill that occurs during a hash operation is known as a Hash Warning. 在哈希操作过程中发生哈希递归或哈希终止(哈希释放)时,会出现此情况。These occur when a hash recursion or cessation of hashing (hash bailout) has occurred during a hashing operation.

  • 当生成输入无法装入可用内存时,会发生哈希递归,这将导致输入分割成单独处理的多个分区。Hash recursion occurs when the build input does not fit into available memory, resulting in the split of input into multiple partitions that are processed separately. 如果这些分区中任何一个仍然大于可用内存,则该分区再拆分成子分区分别进行处理。If any of these partitions still do not fit into available memory, it is split into sub-partitions, which are also processed separately. 此拆分过程将一直持续到每个分区都小于可用内存,或达到最大递归级数。This splitting process continues until each partition fits into available memory or until the maximum recursion level is reached.
  • 当哈希操作达到其最大递归级数并转换到替换计划以处理剩余的分区数据时发生哈希释放。Hash bailout occurs when a hashing operation reaches its maximum recursion level and shifts to an alternate plan to process the remaining partitioned data. 这些事件可导致服务器性能降低。These events can cause reduced performance in your server.

对于“批模式执行”,默认情况下,初始内存授予可以动态增加到特定内部阈值。For batch mode execution, the initial memory grant can dynamically increase up to a certain internal threshold by default. 此动态内存授予机制旨在允许在批模式下运行哈希或排序操作的内存驻留执行 。This dynamic memory grant mechanism is designed to allow memory-resident execution of hash or sort operations running in batch mode. 如果这些操作仍然内存不足,则将溢出到磁盘。If these operations still do not fit into memory, then these will spill to disk.

有关执行模式的详细信息,请参阅查询处理体系结构指南For more information on execution modes, see the Query Processing Architecture Guide.

缓冲区管理Buffer management

SQL ServerSQL Server 数据库的主要用途是存储和检索数据,因此,大量磁盘 I/O 是该数据库引擎的一个核心特点。The primary purpose of a SQL ServerSQL Server database is to store and retrieve data, so intensive disk I/O is a core characteristic of the Database Engine. 此外,完成磁盘 I/O 操作要消耗许多资源并且耗时较长,所以 SQL ServerSQL Server 侧重于提高 I/O 效率。And because disk I/O operations can consume many resources and take a relatively long time to finish, SQL ServerSQL Server focuses on making I/O highly efficient. 缓冲区管理是实现高效 I/O 操作的关键环节。Buffer management is a key component in achieving this efficiency. 缓冲区管理组件由下列两种机制组成:用于访问及更新数据库页的缓冲区管理器和用于减少数据库文件 I/O 的缓冲区高速缓存(又称为“缓冲池”) 。The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer cache (also called the buffer pool), to reduce database file I/O.

缓冲区管理的工作原理How buffer management works

一个缓冲区就是一个 8 KB 大小的内存页,其大小与一个数据页或索引页相当。A buffer is an 8 KB page in memory, the same size as a data or index page. 因此,缓冲区缓存被划分为多个 8 KB 页。Thus, the buffer cache is divided into 8 KB pages. 缓冲区管理器负责将数据页或索引页从数据库磁盘文件读入缓冲区高速缓存中,并将修改后的页写回磁盘。The buffer manager manages the functions for reading data or index pages from the database disk files into the buffer cache and writing modified pages back to disk. 缓冲区缓存中会保留一页,直到缓冲区管理器需要该缓冲区读入更多数据。A page remains in the buffer cache until the buffer manager needs the buffer area to read in more data. 数据只有在被修改后才重新写入磁盘。Data is written back to disk only if it is modified. 在将缓冲区缓存中的数据写回磁盘之前,可对其进行多次修改。Data in the buffer cache can be modified multiple times before being written back to disk. 有关详细信息,请参阅 读取页写入页For more information, see Reading Pages and Writing Pages.

SQL ServerSQL Server 启动时,它将基于大量参数(例如系统的物理内存量、配置的最大服务器线程数和各个启动参数)计算缓冲区缓存的虚拟地址空间的大小。When SQL ServerSQL Server starts, it computes the size of virtual address space for the buffer cache based on a number of parameters such as the amount of physical memory on the system, the configured number of maximum server threads, and various startup parameters. SQL ServerSQL Server 将为缓冲区缓存保留此计算得到的进程虚拟地址空间量(称为“内存目标”),但它仅为当前负荷获取(提交)所需的物理内存量。reserves this computed amount of its process virtual address space (called the memory target) for the buffer cache, but it acquires (commits) only the required amount of physical memory for the current load. 可查询 sys.dm_os_sys_info 目录视图中的 bpool_commit_targetbpool_committed columns 列,分别返回保留为内存目标的页数以及缓冲区缓存中当前提交的页数。You can query the bpool_commit_target and bpool_committed columns in the sys.dm_os_sys_info catalog view to return the number of pages reserved as the memory target and the number of pages currently committed in the buffer cache, respectively.

SQL ServerSQL Server 启动与缓冲区缓存获得其内存目标之间的间隔称为“增长期”。The interval between SQL ServerSQL Server startup and when the buffer cache obtains its memory target is called ramp-up. 在此期间,读取请求将根据需要填充缓冲区。During this time, read requests fill the buffers as needed. 例如,一个 8 KB 的页面读取请求填充一个缓冲区页。For example, a single 8 KB page read request fills a single buffer page. 也就是说,增长期取决于客户端请求的数量和类型。This means the ramp-up depends on the number and type of client requests. 通过将单页读取请求转换为对齐的八页请求加快增长(组成一个盘区)。Ramp-up is expedited by transforming single page read requests into aligned eight page requests (making up one extent). 这样可更快地完成增长,尤其是那些内存容量很大的机器。This allows the ramp-up to finish much faster, especially on machines with a lot of memory. 有关页和盘区的详细信息,请参阅页和盘区体系结构指南For more information about pages and extents, refer to Pages and Extents Architecture Guide.

因为缓冲区管理器将多数内存用于 SQL ServerSQL Server 进程,所以它会与内存管理器协作以使其他组件能使用其缓冲区。Because the buffer manager uses most of the memory in the SQL ServerSQL Server process, it cooperates with the memory manager to allow other components to use its buffers. 缓冲区管理器主要与下列组件交互:The buffer manager interacts primarily with the following components:

  • 资源管理器。此交互用于控制内存的整体使用情况以及 32 位平台中的地址空间使用情况。Resource manager to control overall memory usage and, in 32-bit platforms, to control address space usage.
  • 数据库管理器和 SQL ServerSQL Server 操作系统 (SQLOS)。此交互用于低级文件 I/O 操作。Database manager and the SQL ServerSQL Server Operating System (SQLOS) for low-level file I/O operations.
  • 日志管理器。此交互用于预写日志记录。Log manager for write-ahead logging.

支持的功能Supported Features

缓冲区管理器支持以下功能:The buffer manager supports the following features:

  • 缓冲区管理器可识别非一致性内存访问 (NUMA)。The buffer manager is non-uniform memory access (NUMA) aware. 缓冲区高速缓存页将跨硬件 NUMA 节点进行分布,它允许线程访问分配到本地 NUMA 节点上的缓冲区页,而不是从外部内存访问。Buffer cache pages are distributed across hardware NUMA nodes, which allows a thread to access a buffer page that is allocated on the local NUMA node rather than from foreign memory.

  • 缓冲区管理器支持热添加内存,用户无需重新启动服务器即可添加物理内存。The buffer manager supports Hot Add Memory, which allows users to add physical memory without restarting the server.

  • 缓冲区管理器在 64 位平台上支持大型页。The buffer manager supports large pages on 64-bit platforms. 页大小因 Windows 版本不同而异。The page size is specific to the version of Windows.

    备注

    SQL Server 2012 (11.x)SQL Server 2012 (11.x) 之前,启用 SQL ServerSQL Server 中的大型页面需要跟踪标志 834Prior to SQL Server 2012 (11.x)SQL Server 2012 (11.x), enabling large pages in SQL ServerSQL Server requires trace flag 834.

  • 缓冲区管理器提供通过动态管理视图显示的其他诊断信息。The buffer manager provides additional diagnostics that are exposed through dynamic management views. 您可以使用这些视图来监视 SQL ServerSQL Server特有的各种操作系统资源。You can use these views to monitor a variety of operating system resources that are specific to SQL ServerSQL Server. 例如,可以使用 sys.dm_os_buffer_descriptors 视图来监视缓冲区缓存中的页。For example, you can use the sys.dm_os_buffer_descriptors view to monitor the pages in the buffer cache.

磁盘 I/ODisk I/O

缓冲区管理器仅对数据库执行读写操作。The buffer manager only performs reads and writes to the database. 其他文件和数据库操作(如打开、关闭、扩展和收缩)则由数据库管理器和文件管理器组件执行。Other file and database operations such as open, close, extend, and shrink are performed by the database manager and file manager components.

缓冲区管理器的磁盘 I/O 操作具有以下特点:Disk I/O operations by the buffer manager have the following characteristics:

  • 所有 I/O 操作均异步执行。这样,在后台进行 I/O 操作的同时,即可调用线程继续处理。All I/Os are performed asynchronously, which allows the calling thread to continue processing while the I/O operation takes place in the background.
  • 所有 I/O 操作均在调用线程中发出,除非 affinity I/O 选项处于使用状态。All I/Os are issued in the calling threads unless the affinity I/O option is in use. Affinity I/O mask 选项将 SQL ServerSQL Server 磁盘 I/O 绑定到指定的 CPU 子集。The affinity I/O mask option binds SQL ServerSQL Server disk I/O to a specified subset of CPUs. 在高端 SQL ServerSQL Server 联机事务处理 (OLTP) 环境中,此扩展可以提高 SQL ServerSQL Server 线程执行 I/O 的性能。In high-end SQL ServerSQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL ServerSQL Server threads issuing I/Os.
  • 可通过散播-聚集 I/O 实现多页 I/O,散播-聚集 I/O 允许数据传入或传出非连续内存区域。Multiple page I/Os are accomplished with scatter-gather I/O, which allows data to be transferred into or out of noncontiguous areas of memory. 这意味着 SQL ServerSQL Server 可以快速填充或刷新缓冲区高速缓存,同时避免多个物理 I/O 请求。This means that SQL ServerSQL Server can quickly fill or flush the buffer cache while avoiding multiple physical I/O requests.

长时 I/O 请求Long I/O requests

缓冲区管理器报告任何经过 15 秒或更长时间仍未完成的 I/O 请求。The buffer manager reports on any I/O request that has been outstanding for at least 15 seconds. 这可以帮助系统管理员区分 SQL ServerSQL Server 问题和 I/O 子系统问题。This helps the system administrator distinguish between SQL ServerSQL Server problems and I/O subsystem problems. 将报告错误消息 833 并且该消息在 SQL ServerSQL Server 错误日志中显示如下:Error message 833 is reported and appears in the SQL ServerSQL Server error log as follows:

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

长时 I/O 可以是读或写,不过当前消息并未指明。A long I/O may be either a read or a write; it is not currently indicated in the message. 长时 I/O 消息是警告而不是错误。Long-I/O messages are warnings, not errors. 它们并不表示 SQL ServerSQL Server 存在问题,而是基础 I/O 系统存在问题。They do not indicate problems with SQL ServerSQL Server but with the underlying I/O system. 报告这些消息是为了帮助系统管理员更快地找到 SQL ServerSQL Server 响应缓慢的原因,并找出 SQL ServerSQL Server无法控制的问题。The messages are reported to help the system administrator find the cause of poor SQL ServerSQL Server response times more quickly, and to distinguish problems that are outside the control of SQL ServerSQL Server. 因此,不需要为它们执行任何操作,但系统管理员应调查 I/O 请求耗时很长的原因以及耗时是否合理。As such, they do not require any action, but the system administrator should investigate why the I/O request took so long, and whether the time is justifiable.

长时 I/O 请求的原因Causes of Long-I/O Requests

长时 I/O 消息可能指示 I/O 永久阻塞并且永远无法完成(称为“I/O 丢失”),或者只是它尚未完成。A long-I/O message may indicate that an I/O is permanently blocked and will never complete (known as lost I/O), or merely that it just has not completed yet. 虽然 I/O 丢失往往会导致闩锁超时,但无法根据消息确定是哪种情况。It is not possible to tell from the message which scenario is the case, although a lost I/O will often lead to a latch timeout.

长时 I/O 往往指示磁盘子系统的 SQL ServerSQL Server 工作负荷过于密集。Long I/Os often indicate a SQL ServerSQL Server workload that is too intense for the disk subsystem. 以下情况可能会指示磁盘子系统不足:An inadequate disk subsystem may be indicated when:

  • SQL ServerSQL Server 工作负荷很大时,错误日志中出现多个长时 I/O 消息。Multiple long I/O messages appear in the error log during a heavy SQL ServerSQL Server workload.
  • Perfmon 计数器显示磁盘长时间滞后、磁盘队列长或无磁盘空闲时间。Perfmon counters show long disk latencies, long disk queues, or no disk idle time.

长时 I/O 还可能因以下原因所致:I/O 路径中的某个组件(如驱动程序、控制器或固件)不断延迟为早期 I/O 请求提供服务,而为距离当前磁头位置较近的新请求提供服务。Long I/Os may also be caused by a component in the I/O path (for example, a driver, controller, or firmware) continually postponing servicing an old I/O request in favor of servicing newer requests that are closer to the current position of the disk head. 这种对距离读/写磁头当前位置最近的请求进行优先处理的常见技术称为“电梯式查找”。The common technique of processing requests in priority based upon which ones are closest to the current position of the read/write head is known as "elevator seeking." 它可能很难与 Windows 系统监视器 (PERFMON.EXE) 工具配合使用,因为多数 I/O 是立即获得服务的。This may be difficult to corroborate with the Windows System Monitor (PERFMON.EXE) tool because most I/Os are being serviced promptly. 执行大量连续 I/O 的工作负荷可能会使长时 I/O 请求情况更严重,如备份和还原、表扫描、排序、创建索引、大容量加载以及清零文件。Long I/O requests can be aggravated by workloads that perform large amounts of sequential I/O, such as backup and restore, table scans, sorting, creating indexes, bulk loads, and zeroing out files.

单独出现的长时 I/O 如果与上述情况无关,则可能是由硬件或驱动程序问题所致。Isolated long I/Os that do not appear related to any of the previous conditions may be caused by a hardware or driver problem. 系统事件日志可能会包含有助于进行问题诊断的相关事件。The system event log may contain a related event that helps to diagnose the problem.

内存压力检测Memory pressure detection

内存压力是由内存不足造成的一种情况,可导致:Memory pressure is a condition resulting from memory shortage, and can result in:

  • 额外的 I/O(如非常活跃的 lazy writer 后台线程)Extra I/Os (such as very active lazy writer background thread)
  • 重新编译比升高Higher recompile ratio
  • 运行查询延长(如果存在内存授予等待)Longer running queries (if memory grant waits exist)
  • 额外的 CPU 周期Extra CPU cycles

这种情况可能由外部或内部原因引发。This situation can be triggered by external or internal causes. 外部原因包括:External causes include:

  • 可用物理内存 (RAM) 不足。Available physical memory (RAM) is low. 这会导致系统调整当前正在运行的进程的工作集,并可能导致整体速度下降。This causes the system to trim working sets of currently running processes, which may result in overall slowdown. SQL ServerSQL Server 可能减少缓冲池的提交目标,并更频繁地开始调整内部缓存。may reduce the commit target of the buffer pool and start trimming internal caches more often.
  • 总体可用系统内存(包括系统页面文件)不足。Overall available system memory (which includes the system page file) is low. 这可能导致系统无法分配内存,因为无法对当前已分配的内存进行页面换出。This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory. 内部原因包括:Internal causes include:
  • SQL Server 数据库引擎SQL Server Database Engine 设置较低内存使用情况上限时响应外部内存压力。Responding to the external memory pressure, when the SQL Server 数据库引擎SQL Server Database Engine sets lower memory usage caps.
  • 通过减少 max server memory 配置手动降低内存设置。Memory settings were manually lowered by reducing the max server memory configuration.
  • 多个缓存之间的内部组件的内存分配发生变化。Changes in memory distribution of internal components between the several caches.

SQL Server 数据库引擎SQL Server Database Engine 实现了一个专门用于检测和处理内存压力的框架,包含在动态内存管理中。The SQL Server 数据库引擎SQL Server Database Engine implements a framework dedicated to detecting and handling memory pressure, as part of its dynamic memory management. 此框架包含名为“资源监视器”的后台任务。This framework includes the background task called Resource Monitor. “资源监视器”任务可监视外部和内部内存指示器的状态。The Resource Monitor task monitors the state of external and internal memory indicators. 一旦某个指示器改变状态,它会计算相应的通知并进行广播。Once one of these indicators changes status, it calculates the corresponding notification and it broadcasts it. 这些通知是每个引擎组件中的内部消息,存储在环形缓冲区中。These notifications are internal messages from each of the engine components, and stored in ring buffers.

两个环形缓冲区保留与动态内存管理相关的信息:Two ring buffers hold information relevant to dynamic memory management:

  • “资源监视器”环形缓冲区,用于跟踪“资源监视器”活动,例如是否有内存压力信号。The Resource Monitor ring buffer, which tracks Resource Monitor activity like was memory pressure signaled or not. 此环形缓冲区包含关于 RESOURCE_MEMPHYSICAL_HIGH、RESOURCE_MEMPHYSICAL_LOW、RESOURCE_MEMPHYSICAL_STEADY 或 RESOURCE_MEMVIRTUAL_LOW 的当前状况的状态信息 。This ring buffer has status information depending on the current condition of RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY, or RESOURCE_MEMVIRTUAL_LOW.
  • “内存代理”环形缓冲区,其中包含每个 Resource Governor 资源池的内存通知记录。The Memory Broker ring buffer, which contains records of memory notifications for each Resource Governor resource pool. 检测到内部内存压力时,将为分配内存的组件启用内存不足通知,以触发用于平衡缓存之间的内存的操作。As internal memory pressure is detected, low memory notification is turned on for components that allocate memory, to trigger actions meant to balance the memory between caches.

内存代理监视每个组件对内存的需求消耗,然后根据收集的信息计算所有这些组件的最佳内存值。Memory brokers monitor the demand consumption of memory by each component and then based on the information collected, it calculates and optimal value of memory for each of these components. 每个 Resource Governor 资源池都有一组代理。There is a set of brokers for each Resource Governor resource pool. 它们随后可将此信息广播给每个组件,使组件根据需要增加或减少用量。This information is then broadcast to each of the components, which grow or shrink their usage as required. 有关内存代理的详细信息,请参阅 sys.dm_os_memory_brokersFor more information about memory brokers, see sys.dm_os_memory_brokers.

错误检测Error Detection

数据库页可使用下面两种可选机制之一来保证页在从写入磁盘到再次读取期间的完整性:残缺页保护和校验和保护。Database pages can use one of two optional mechanisms that help insure the integrity of the page from the time it is written to disk until it is read again: torn page protection and checksum protection. 这些机制允许采用独立方法验证数据存储以及诸如控制器、驱动程序、电缆等硬件组件甚至操作系统的正确性。These mechanisms allow an independent method of verifying the correctness of not only the data storage, but hardware components such as controllers, drivers, cables, and even the operating system. 在即将把页写入磁盘之前将向页添加保护,并在从磁盘读取页后对它进行验证。The protection is added to the page just before writing it to disk, and verified after it is read from disk.

SQL ServerSQL Server 将对因校验和、页撕裂或其他 I/O 错误而失败的任何读取都重试四次。will retry any read that fails with a checksum, torn page, or other I/O error four times. 如果在其中一次重试中读取成功,则会向错误日志中写入一条消息,且触发读取的命令将继续执行。If the read is successful in any one of the retry attempts, a message will be written to the error log and the command that triggered the read will continue. 如果重试失败,则该命令失败,且显示错误消息 824。If the retry attempts fail, the command will fail with error message 824.

页保护类型是包含此页的数据库的属性之一。The kind of page protection used is an attribute of the database containing the page. 校验和保护是在 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 和更高版本中创建的数据库的默认保护。Checksum protection is the default protection for databases created in SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later. 页保护机制是在创建数据库时指定的,并且可以使用 ALTER DATABASE SET 进行更改。The page protection mechanism is specified at database creation time, and may be altered by using ALTER DATABASE SET. 可通过查询 sys.databases 目录视图中的 page_verify_option 列或 DATABASEPROPERTYEX 函数的 IsTornPageDetectionEnabled 属性来确定当前的页保护设置 。You can determine the current page protection setting by querying the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.

备注

如果页保护设置发生变化,新设置不会立即影响到整个数据库。If the page protection setting is changed, the new setting does not immediately affect the entire database. 相反,每当下一次写入时,这些页才会采用数据库的当前保护级别。Instead, pages adopt the current protection level of the database whenever they are written next. 这意味着数据库可能包含带有不同保护的页。This means that the database may be composed of pages with different kinds of protection.

残缺页保护Torn Page Protection

SQL ServerSQL Server 2000 中引入的“残缺页保护”其实是一种对电源故障导致的页损坏进行检测的方法。Torn page protection, introduced in SQL ServerSQL Server 2000, is primarily a way of detecting page corruptions due to power failures. 例如,意外电源故障可能导致只有部分页写入磁盘。For example, an unexpected power failure may leave only part of a page written to disk. 使用残缺页保护时,在将 8 KB 的数据库页写入磁盘时,该页的每个 512 字节的扇区都有一个特定的 2 位签名模式存储在数据库的页头中。When torn page protection is used, a specific 2-bit signature pattern for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk. 从磁盘中读取页时,页头中存储的残缺位将与实际的页扇区信息进行比较。When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. 每次进行写操作时,这个签名模式在二进制数 01 和 10 之间交替,这样始终可以确定是否只有部分扇区写到磁盘:如果稍后读取页时发现某个位的状态不正确,则说明该页没有被正确写入并会因此检测到残缺页。The signature pattern alternates between binary 01 and 10 with every write, so it is always possible to tell when only a portion of the sectors made it to disk: if a bit is in the wrong state when the page is later read, the page was written incorrectly and a torn page is detected. 残缺页检测使用的资源最少,但是它无法检测到磁盘硬件故障导致的所有错误。Torn page detection uses minimal resources; however, it does not detect all errors caused by disk hardware failures. 有关设置残缺页保护的信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For information on setting torn page detection, see ALTER DATABASE SET Options (Transact-SQL).

校验和保护Checksum Protection

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 中引入的“校验和保护”提供了更强大的数据完整性检查。Checksum protection, introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x), provides stronger data integrity checking. 此方法将对写入每一页中的数据进行校验和计算并将其值存储在页头中。A checksum is calculated for the data in each page that is written, and stored in the page header. 每次从磁盘读取存储了校验和的页时,数据库引擎将重新计算页中数据的校验和。如果新的校验和不同于已存储的校验和,则引发错误 824。Whenever a page with a stored checksum is read from disk, the database engine recalculates the checksum for the data in the page and raises error 824 if the new checksum is different from the stored checksum. 校验和保护比残缺页保护能捕获到更多的错误,因为它受到页中每个字节的影响,但它对资源的消耗较多。Checksum protection can catch more errors than torn page protection because it is affected by every byte of the page, however, it is moderately resource-intensive. 启用校验和后,当缓冲区管理器从磁盘读取页时均可以检测到因电源故障以及硬件或固件故障导致的错误。When checksum is enabled, errors caused by power failures and flawed hardware or firmware can be detected any time the buffer manager reads a page from disk. 有关设置校验和的信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For information on setting checksum, see ALTER DATABASE SET Options (Transact-SQL).

重要

在用户数据库或系统数据库升级到 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 或更高版本后,将保留 PAGE_VERIFY 值(NONE 或 TORN_PAGE_DETECTION)。When a user or system database is upgraded to SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. 强烈建议使用 CHECKSUM。We highly recommend that you use CHECKSUM. TORN_PAGE_DETECTION 可能使用较少资源,但提供的 CHECKSUM 保护最少。TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the CHECKSUM protection.

了解非一致性内存访问Understanding Non-uniform Memory Access

SQL ServerSQL Server 能识别非一致性内存访问 (NUMA),无需特殊配置便可在 NUMA 硬件上顺利地执行。is non-uniform memory access (NUMA) aware, and performs well on NUMA hardware without special configuration. 随着处理器时钟速度的提高和处理器数量的增加,使用这种额外处理能力所需的内存滞后时间越来越难以减少。As clock speed and the number of processors increase, it becomes increasingly difficult to reduce the memory latency required to use this additional processing power. 为了避开这一问题,硬件供应商提供了大型的 L3 缓存,但这只是一种有限的解决方案。To circumvent this, hardware vendors provide large L3 caches, but this is only a limited solution. NUMA 体系结构为此问题提供了可缩放的解决方案。NUMA architecture provides a scalable solution to this problem. SQL ServerSQL Server 已设计为利用基于 NUMA 的计算机而无需更改任何应用程序。has been designed to take advantage of NUMA-based computers without requiring any application changes. 有关详细信息,请参阅操作说明:将 SQL Server 配置为使用软件 NUMAFor more information, see How to: Configure SQL Server to Use Soft-NUMA.

内存对象的动态分区Dynamic partition of memory objects

SQL ServerSQL Server 中的堆分配器称为内存对象,数据库引擎Database Engine 可通过它们从堆中分配内存。Heap allocators, called memory objects in SQL ServerSQL Server, allow the 数据库引擎Database Engine to allocate memory from the heap. 可以使用 sys.dm_os_memory_objects DMV 跟踪它们。These can be tracked using the sys.dm_os_memory_objects DMV. CMemThread 是一个线程安全内存对象类型,它允许从多个线程并发分配内存。CMemThread is a thread-safe memory object type that allows concurrent memory allocations from multiple threads. 为实现正确的跟踪,CMemThread 对象依赖于同步构造(互斥体),以确保同一时间只有一个线程更新重要信息。For correct tracking, CMemThread objects rely on synchronization constructs (a mutex) to ensure only a single thread is updating critical pieces of information at a time.

备注

在整个 数据库引擎Database Engine 代码库中,CMemThread 对象类型用于许多不同的分配,并可按节点或 CPU 进行全局分区。The CMemThread object type is utilized throughout the 数据库引擎Database Engine code base for many different allocations, and can be partitioned globally, by node or by CPU.

但是,如果很多线程以高度并发的方式从同一内存对象进行分配,则使用互斥体可能会导致争用。However, the use of mutexes can lead to contention if many threads are allocating from the same memory object in a highly concurrent fashion. 因此,SQL ServerSQL Server 具有分区内存对象 (PMO) 的概念,每个分区由单个 CMemThread 对象表示。Therefore, SQL ServerSQL Server has the concept of partitioned memory objects (PMO) and each partition is represented by a single CMemThread object. 内存对象的分区是静态定义的,创建后不可更改。The partitioning of a memory object is statically defined and cannot be changed after creation. 由于内存分配模式因硬件和内存使用情况等方面迥然相异,因此不可能提前设定出完美的分区模式。As memory allocation patterns vary widely based on aspects like hardware and memory usage, it is impossible to come up with the perfect partitioning pattern upfront. 在绝大多数情况下,使用单个分区就足够了,但在某些情况下,这可能会导致争用,只能通过高度分区的内存对象阻止这种争用。In the vast majority of cases, using a single partition will suffice, but in some scenarios this may lead to contention which can be prevented only with a highly partitioned memory object. 对每个内存对象进行分区是不理想的,因为分区增多可能导致其他方面的效率低下并增加内存碎片。It is not desirable to partition each memory object as more partitions may result in other inefficiencies and increase memory fragmentation.

备注

SQL Server 2016 (13.x)SQL Server 2016 (13.x)之前,可以使用跟踪标志 8048 将基于节点的 PMO 强制变为基于 CPU 的 PMO。Before SQL Server 2016 (13.x)SQL Server 2016 (13.x), trace flag 8048 could be used to force a node-based PMO to become a CPU-based PMO. SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,此行为是动态的,由引擎控制。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x), this behavior is dynamic and controlled by the engine.

SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,数据库引擎Database Engine 可以动态检测特定 CMemThread 对象上的争用,并将对象提升为基于每个节点或每个 CPU 的实现。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x), the 数据库引擎Database Engine can dynamically detect contention on a specific CMemThread object and promote the object to a per-node or a per-CPU based implementation.  升级后,PMO 会保持升级状态,直到重新启动 SQL ServerSQL Server 进程。Once promoted, the PMO remains promoted until the SQL ServerSQL Server process is restarted. sys.dm_os_wait_stats DMV 中 CMEMTHREAD 等待数过多可指示 CMemThread 争用,可通过观察以下 sys.dm_os_memory_objects DMV 列来发现它:contention_factor partition_type exclusive_allocations_countwaiting_tasks_countCMemThread contention can be detected by the presence of high CMEMTHREAD waits in the sys.dm_os_wait_stats DMV, and by observing the sys.dm_os_memory_objects DMV columns contention_factor, partition_type, exclusive_allocations_count, and waiting_tasks_count.

另请参阅See Also

“服务器内存”服务器配置选项 Server Memory Server Configuration Options
读取页 Reading Pages
写入页 Writing Pages
如何:将 SQL Server 配置为使用软件 NUMA How to: Configure SQL Server to Use Soft-NUMA
使用内存优化表的要求 Requirements for Using Memory-Optimized Tables
使用内存优化表来解决内存不足问题Resolve Out Of Memory Issues Using Memory-Optimized Tables