監視記憶體使用量

  • 發行項

適用於：SQL Server

定期監視 SQL Server 執行個體，以確認記憶體使用量是在正常範圍內。

設定 SQL Server 的記憶體上限

根據預設，SQL Server 執行個體可能會隨著時間耗用伺服器中大部分可用的 Windows 作業系統記憶體。 取得記憶體之後，除非偵測到記憶體壓力，否則不會加以釋放。 這是預設行為，不代表 SQL Server 處理序中的記憶體流失。 使用 [最大伺服器記憶體] 選項，來限制允許 SQL Server 針對大部分用途取得的記憶體數量。 如需詳細資訊，請參閱記憶體管理架構指南 (部分機器翻譯)。

在 Linux 上的 SQL Server 中，使用 mssql-conf 工具與 memory.memorylimitmb 設定 (部分機器翻譯) 來設定記憶體限制 (部分機器翻譯)。

監視作業系統記憶體

若要監視記憶體不足的狀況，請使用下列 Windows 伺服器計數器。 許多作業系統記憶體計數器都可透過動態管理檢視 sys.dm_os_process_memory (部分機器翻譯) 與 sys.dm_os_sys_memory (部分機器翻譯) 進行查詢。

  • 記憶體：Available Bytes
    此計數器指出目前有多少記憶體位元組可供處理序使用。 可用位元組計數器的低值可指出作業系統記憶體的整體不足。 您可以使用 sys.dm_os_sys_memory.available_physical_memory_kb，透過 T-SQL 查詢此值。

  • 記憶體：Pages/sec
    此計數器指出由於硬體分頁錯誤而自磁碟取出，或由於分頁錯誤而寫入到磁碟，以釋出工作集內空間的分頁數。 Pages/sec 計數器數值過高可能代表過度分頁。

  • 記憶體：分頁錯誤/秒：此計數器會指出所有處理序 (包括系統處理序) 的分頁錯誤率。 低但非零的磁碟分頁率 (連同分頁錯誤) 是正常的，即使配備許多可用記憶體的電腦也是如此。 當 Microsoft Windows 虛擬記憶體管理員 (VMM) 修剪 SQL Server 與其他處理序的工作集大小時，會從那些處理序中取得分頁。 此 VMM 活動會造成分頁錯誤。

  • 處理序：分頁錯誤/秒：此計數器會指出適用於指定使用者處理序的分頁錯誤率。 監視處理序：分頁錯誤/秒，以判斷磁碟活動是否由 SQL Server 分頁所造成。 若要判斷 SQL Server 或其他處理序是否會導致過度分頁，請監視適用於 SQL Server 處理序執行個體的處理序：分頁錯誤/秒計數器。

如需解決過度分頁的詳細資訊，請參閱作業系統文件。

隔離 SQL Server 使用的記憶體

若要監視 SQL Server 記憶體使用量，請使用下列 SQL Server 物件計數器 (部分機器翻譯)。 許多 SQL Server 物件計數器都可透過動態管理檢視 sys.dm_os_performance_counters (部分機器翻譯) 或 sys.dm_os_process_memory (部分機器翻譯) 進行查詢。

根據預設，SQL Server 會根據可用的系統資源，動態管理其記憶體需求。 如果 SQL Server 需要更多記憶體，其會查詢作業系統，以判斷是否有可用的實體記憶體，並使用可用的記憶體。 如果 OS 的可用記憶體不足，SQL Server 就會將記憶體釋放回作業系統，直到記憶體不足的情況有所緩解，或是直到 SQL Server 達到 min server memory 限制為止。 不過，您可以使用 min server memory 與 max server memory 伺服器組態選項來覆寫此選項，動態使用記憶體。 如需詳細資訊，請參閱＜ 伺服器記憶體選項＞。

若要監視 SQL Server 所使用的記憶體數量，請檢查下列效能計數器：

  • SQL Server：記憶體管理員：Total Server Memory (KB)
    此計數器表示 SQL Server 記憶體管理員目前已認可到 SQL Server 的作業系統記憶體數量。 此數量預期會隨著實際活動的需要而增長，並且將在 SQL Server 啟動之後成長。 使用 sys.dm_os_sys_info (部分機器翻譯) 動態管理檢視來查詢此計數器，並觀察 committed_kb 資料行。

  • SQL Server：記憶體管理員：目標伺服器記憶體 (KB)
    此計數器會指出根據最近的工作負載，SQL Server 可能取用的理想記憶體數量。 在一段時間的一般作業之後，與總伺服器記憶體進行比較，以判斷 SQL Server 是否配置所需的記憶體數量。 一般作業之後，總伺服器記憶體和目標伺服器記憶體應該類似。 如果總伺服器記憶體明顯低於目標伺服器記憶體，SQL Server 執行個體可能會遭遇記憶體壓力。 在啟動 SQL Server 之後的一段期間，總伺服器記憶體預期會低於目標伺服器記憶體，因為總伺服器記憶體會成長。 使用 sys.dm_os_sys_info (部分機器翻譯) 動態管理檢視來查詢此計數器，並觀察 committed_target_kb 資料行。 如需設定記憶體的詳細資訊和最佳做法，請參閱伺服器記憶體組態選項 (部分機器翻譯)。

  • 處理序：Working Set
    此計數器會根據作業系統，指出目前處理序正在使用的實體記憶體數量。 觀察此計數器的 sqlservr.exe 執行個體。 使用 sys.dm_os_process_memory 動態管理檢視來查詢此計數器，並觀察 physical_memory_in_use_kb 資料行。

  • 處理序：私用位元組
    此計數器會指出處理序已要求自己用於作業系統的記憶體數量。 觀察此計數器的 sqlservr.exe 執行個體。 因為此計數器包含 sqlservr.exe 要求的所有記憶體配置，包括不受 [最大伺服器記憶體選項] 限制的記憶體配置，所以，此計數器可以報告大於 [最大伺服器記憶體選項] 的值。

  • SQL Server：緩衝區管理員：Database Pages
    此計數器會指出具有資料庫內容之緩衝集區中的分頁數。 不包含 SQL Server 處理序中的其他非緩衝集區記憶體。 使用 sys.dm_os_performance_counters (部分機器翻譯) 動態管理檢視來查詢此計數器。

  • SQL Server：緩衝區管理員：Buffer Cache Hit Ratio
    此計數器專屬於 SQL Server。 需要 90 或更高的比率。 值大於 90 表示有超過 90% 的資料要求已從記憶體中的資料快取得到滿足，而不需從磁碟讀取。 如需 SQL Server 緩衝區管理員的詳細資訊，請參閱 SQL Server 緩衝區管理員物件 (部分機器翻譯)。 使用 sys.dm_os_performance_counters (部分機器翻譯) 動態管理檢視來查詢此計數器。

  • SQL Server：緩衝區管理員：頁面的預期壽命
    此計數器會測量最舊分頁保留在緩衝集區中的秒數。 對於使用 NUMA 結構的系統，這是所有 NUMA 節點上的平均。 更高且不斷成長的值最好。 意外出現的谷值表示有大量資料進出緩衝集區，表示工作負載無法從已經存在於記憶體中的資料充分受益。 每個 NUMA 節點都有自己的緩衝集區節點。 在具有多個 NUMA 節點的伺服器上，使用 SQL Server：緩衝節點：頁面的預期壽命，來檢視每個緩衝集區節點分頁的預期壽命。 使用 sys.dm_os_performance_counters (部分機器翻譯) 動態管理檢視來查詢此計數器。

範例

決定目前的記憶體配置

下列查詢會傳回目前配置記憶體的相關資訊。

SELECT
(total_physical_memory_kb/1024) AS Total_OS_Memory_MB,
(available_physical_memory_kb/1024)  AS Available_OS_Memory_MB
FROM sys.dm_os_sys_memory;

SELECT  
(physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_by_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory;

判斷目前的 SQL Server 記憶體使用率

下列查詢會傳回目前 SQL Server 記憶體使用率的相關資訊。

SELECT
sqlserver_start_time,
(committed_kb/1024) AS Total_Server_Memory_MB,
(committed_target_kb/1024)  AS Target_Server_Memory_MB
FROM sys.dm_os_sys_info;

判斷頁面的預期壽命

下列查詢會使用 sys.dm_os_performance_counters 來觀察 SQL Server 執行個體在整體緩衝區管理員層級與在每個 NUMA 節點層級上目前頁面的預期壽命值。

SELECT
CASE instance_name WHEN '' THEN 'Overall' ELSE instance_name END AS NUMA_Node, cntr_value AS PLE_s
FROM sys.dm_os_performance_counters    
WHERE counter_name = 'Page life expectancy';

相關內容