記憶體管理架構指南Memory Management Architecture Guide

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel 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 ServerTypically, 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. 若要解決這個問題,請增加 查詢等候選項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 Database Engine 提供下列記憶體數量。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.

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
- 使用 /3gb 開機參數時為 3 GB 2- 3 GB with /3gb boot parameter 2
- 在 WOW64 上為 4 GB 3- 4 GB on WOW64 3
所有的 SQL ServerSQL Server 版本。All SQL ServerSQL Server editions. 處理序虛擬位址空間的最大限制:Up to process virtual address space limit:
- 使用 IA64 架構時為 7 TB (IA64 不支援 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 和以上版本)- 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: ##%.

1SQL Server 2014 (12.x)SQL Server 2014 (12.x)開始不提供 32 位元版本。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. 如需詳細資訊,請瀏覽 MSDN Library。For more information, visit the MSDN Library.
3 WOW64 (Windows 64 上的 Windows) 是 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 如果(在具有 AWE 支援的 32 位元上或是本身的 64 位元上) 授與鎖定記憶體中的分頁權限 (LPIM),我們建議另外設定最大伺服器記憶體。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 的記憶體,會需要 Address Windowing Extensions (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. [最大伺服器記憶體 (MB)] 與 [最小伺服器記憶體 (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) ,適用於要求超過 8KB 的記憶體配置。Multi-Page Allocator (MPA), for memory allocations that request more than 8-KB.
  • CLR 配置器,包括 SQL CLR 堆積,及其在 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. 這些包括使用 Windows 堆積,以及載入至 SQL ServerSQL Server 處理之模組所做的直接虛擬配置。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 2019SQL Server 2019之後,仔細檢閱目前的 [最大伺服器記憶體 (MB)] 與 [最小伺服器記憶體 (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 2019SQL Server 2019. 這是因為從 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. 這些變更適用 32 位元及 64 位元版本的 SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x),以及 64 位元版本的 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2019SQL Server 2019These 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 2019SQL Server 2019.

下表指出特定類型的記憶體配置是否受 [最大伺服器記憶體 (MB)] 與 [最小伺服器記憶體 (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 配置的記憶體可能會超過最大伺服器記憶體設定中所指定的值。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. 當 [總伺服器記憶體 (KB)] 值已經超過 [目標伺服器記憶體 (KB)] 設定 (由最大伺服器記憶體指定) 時,即可能出現此行為。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.

執行配置之後,資源監視器背景工作會開始對所有記憶體取用者要求釋放配置的記憶體,並會嘗試將 [總伺服器記憶體 (KB)] 值降至 [目標伺服器記憶體 (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 的記憶體使用量可能會短時間超過伺服器記憶體設定的上限。Therefore, SQL ServerSQL Server memory usage could briefly exceed the max server memory setting. 在此情況下,[總伺服器記憶體 (KB)] 效能計數器會讀取超過最大伺服器記憶體及 [目標伺服器記憶體 (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 Database EngineSQL Server Database Engine 的預設記憶體管理行為是以不造成系統發生記憶體短缺為前提,盡可能取得所需的記憶體。The default memory management behavior of the SQL Server Database EngineSQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. SQL Server Database EngineSQL Server Database Engine 會使用 Microsoft Windows 的「記憶體通知 API」來達成此目的。The SQL Server Database EngineSQL 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 會將記憶體釋出給 OS。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 中找到的記憶體 Clerk 皆是)。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 CLR1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks).

1SQL Server 2012 (11.x)SQL Server 2012 (11.x) 開始,CLR 記憶體由 max_server_memory 配置進行管理。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 stacks1, CLR2, 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) 開始,CLR 記憶體由 max_server_memory 配置進行管理。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 執行個體會持續取得實體記憶體,直到達到它的最大伺服器記憶體配置目標,或直到 OS 指出已無可用記憶體;當擁有的記憶體多於最小伺服器記憶體設定,且 OS 指出可用記憶體短缺時,它就會釋出記憶體。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.

最小與最大伺服器記憶體的作用Effects of min and max server memory

min server memorymax 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 Database EngineSQL Server Database Engine 的工作負載增加時,它會一再取得支援工作負載所需的記憶體。As the SQL Server Database EngineSQL 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 Database EngineSQL Server Database Engine 取得的記憶體數量完全是依據執行個體的工作負載而定。The amount of memory acquired by the SQL Server Database EngineSQL 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.

如果為最小伺服器記憶體和最大伺服器記憶體指定相同的值,則一旦配置給 SQL Server Database EngineSQL Server Database Engine 的記憶體達到該值,SQL Server Database EngineSQL 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 Database EngineSQL Server Database Engine reaches that value, the SQL Server Database EngineSQL 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. [最小伺服器記憶體] 與 [最大伺服器記憶體] 選項以 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,由 network packet size 組態選項所控制。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 中之工作檔案的支援,而溢出的排序作業則受到工作資料表的支援。A hash operation that spills is supported by a Workfile in TempDB, while a sort operation that spills is supported by a Worktable.

排序作業期間發生的溢出稱為排序警告A spill that occurs during a Sort operation is known as a 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).

雜湊作業期間發生的溢出稱為雜湊警告A 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,且切換到其他計畫以處理其他的分割資料。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 是 Database Engine 的核心特性。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. 緩衝區管理是達成這種效率的重要元件。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 資料行,以分別傳回保留為記憶體目標的頁數和緩衝區快取中目前認可的頁數。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 啟動時與緩衝區快取取得其記憶體目標時當中的間隔稱為「擴置」(Ramp-up)。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 選項,否則所有的 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 來完成,可讓資料轉入或轉出非連續的記憶體區域。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. 一般以最接近讀寫頭目前位置的要求為優先處理的技術即稱為「電梯式尋軌」(Elevator Seeking)。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 (例如非常活躍的延遲寫入器背景執行緒)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 Database EngineSQL Server Database Engine 設定較低的記憶體使用量上限時,回應外部記憶體壓力。Responding to the external memory pressure, when the SQL Server Database EngineSQL 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 Database EngineSQL Server Database Engine 會實作專門用來偵測及處理記憶體壓力的架構,以作為其動態記憶體管理的一部分。The SQL Server Database EngineSQL Server Database Engine implements a framework dedicated to detecting and handling memory pressure, as part of its dynamic memory management. 此架構包含稱為資源監視器的背景工作。This framework includes the backgroud 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_HIGHRESOURCE_MEMPHYSICAL_LOWRESOURCE_MEMPHYSICAL_STEADYRESOURCE_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.
  • 記憶 Broker 信號緩衝區,其中包含每個 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.

記憶 Broker 會監視每個元件的記憶體使用量需求,然後根據所收集的資訊,計算每個元件的最佳記憶體值。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 資源集區會有一組 Broker。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. 如需記憶 Broker 的詳細資訊,請參閱 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 加以變更。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 Options (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 Options (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 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.

另請參閱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