疑難排解 SQL Server 中記憶體授與所造成的效能緩慢或記憶體不足問題

Summary

記憶體授與是指 SQL Server 在查詢執行時分配給排序、雜湊、批量複製及索引建立作業的記憶體。 當多個查詢同時請求大量記憶體授與時,其他查詢會等待記憶體(RESOURCE_SEMAPHORE 等待),導致效能緩慢及記憶體條件不足。

本文說明什麼是記憶體授與,以及如何在伺服器層級辨識記憶體授與等候。 同時也說明如何找到使用或等待記憶體授與的特定查詢,以及如何透過調整查詢、使用查詢提示、設定 Resource Governor 和利用記憶體授與意見反應來減少大型授與。

什麼是記憶體授權?

記憶體授與,也稱為查詢執行 (QE) 保留、查詢執行記憶體、工作區記憶體和記憶體保留區,描述查詢執行時的記憶體使用量。 SQL Server 會在查詢執行期間配置此記憶體,以供下列一或多個用途使用:

  • 排序作業
  • 雜湊作業
  • 大量複製作業(不是常見問題)
  • 索引建立,包括插入欄位儲存索引,因為執行時會使用雜湊字典/資料表來建立索引(這不是常見問題)

查詢在其生命週期內如何使用記憶體

在其執行期間,查詢可能會根據需要執行的任務,向不同的記憶體配置器或記憶體事務員請求記憶體。 當查詢最初被解析並編譯時,會消耗編譯記憶體。 編譯查詢之後,即會釋放該記憶體,而產生的查詢計劃會儲存在計劃快取記憶體中。 計劃快取後,查詢便可供執行。

如果查詢執行任何排序操作、雜湊匹配操作(JOIN 或 aggregates)或插入欄位儲存索引,則會使用查詢執行配置器的記憶體。 一開始,查詢會要求該次執行所需的記憶體。 若記憶體被授予,查詢會使用全部或部分記憶體來進行排序結果或雜湊桶。 在查詢執行期間配置的這個記憶體稱為記憶體授權。

查詢執行操作完成後,記憶體授與會被釋放回 SQL Server,供其他工作使用。 因此,記憶體授權配置本質上是暫時性的,但仍可以持續很長的時間。 例如,若查詢對記憶體中非常大的列集執行排序操作,排序可能需要數秒或數分鐘,且所分配的記憶體會被使用至查詢的整個週期。

具有記憶體授與的查詢範例

以下是使用執行記憶體及其查詢計劃的查詢範例,其中顯示所授與的記憶體:

SELECT * 
FROM sys.messages
ORDER BY message_id

此查詢會選取超過 300,000 個資料列集,並加以排序。 排序作業會引發記憶體配置要求。 如果你在 SQL Server Management Studio(SSMS)執行此查詢,可以查看其查詢計畫。 當您選取查詢計劃最 SELECT 左邊的運算子時,您可以檢視查詢的記憶體授與資訊(按 F4 以顯示 屬性):

含有記憶體授與和查詢計畫的查詢螢幕擷取畫面。

此外,如果您在查詢計劃的空白處按下滑鼠右鍵,您可以選擇顯示執行計劃 XML...,並找出顯示相同記憶體授與資訊的 XML 元素。

 <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />

查詢可能需要特定數量的執行記憶體(DesiredMemory),而且通常會要求該數量(RequestedMemory)。 在執行階段,SQL Server 會視可用記憶體量而定,授與全部或部分所要求的記憶體(GrantedMemory)。 最後,查詢可能會使用比一開始要求的記憶體更多或更少的記憶體(MaxUsedMemory)。 如果查詢優化器已過度估計所需的記憶體數量,則會使用小於要求的大小。 但是,該記憶體被浪費了,因為原本可供另一個請求使用。

另一方面,如果優化器低估了所需的記憶體容量,多餘的列可能會溢出到磁碟,以便在執行時完成工作。 SQL Server 並非配置比最初要求的大小更多的記憶體,而是將額外的資料列寫入磁碟,並將其當作暫存工作區使用。 欲了解更多關於工作檔案與工作資料表的資訊,請參閱 記憶體授與考量。

術語

讓我們來看看你可能會遇到的關於這種耗用記憶體的項目的不同術語。 同樣地,這些術語描述的概念都與相同的記憶體分配有關。

  • 查詢執行記憶體(QE 記憶體): 此詞彙強調排序或雜湊記憶體是在查詢執行期間使用的。 通常,QE 記憶體是查詢生命週期內的最大記憶體使用者。

  • 查詢執行(QE)預留或記憶體預留: 當查詢需要記憶體來執行排序或雜湊作業時,會發出記憶體預留要求。 該保留請求是根據預估資料筆數在編譯時期進行計算的。 稍後,當查詢執行時,SQL Server 會根據記憶體可用性部分或完全滿足該要求。 最終,查詢可能會使用分配記憶體的一定百分比。 有一位名為 MEMORYCLERK_SQLQERESERVATIONS 的記憶體 clerk(記憶體會計師)負責追蹤這些記憶體分配(可以參考 DBCC MEMORYSTATUS 或 sys.dm_os_memory_clerks)。

  • 記憶體授權:當 SQL Server 將所要求的記憶體授與正在執行的查詢時,就表示已發生記憶體授權。 有幾個效能計數器會使用「grant」一詞。這些計數器 Memory Grants Outstanding 和 Memory Grants Pending 會顯示已滿足或等待中的記憶體 grant 計數。 它們不會考慮記憶體授權大小。 單靠一個查詢就可能已耗用 4 GB 的記憶體來執行排序,但這不會反映在這兩個計數器中。

  • 工作區記憶體 是描述相同記憶體的另一個術語。 你通常會在 Perfmon 計數器 Granted Workspace Memory (KB)中看到這個術語,它反映了目前用於排序、雜湊、批量複製和索引建立操作的整體記憶體量,以 KB 表示。 Maximum Workspace Memory (KB)另一個計數器則表示任何可能需要執行雜湊、排序、批量複製及索引建立操作的請求所能使用的最大工作區記憶體容量。 工作區記憶體一詞在這兩個計數器之外很少見到。

大型QE記憶體使用量的效能影響

在大部分情況下,當執行緒在 SQL Server 內要求配置記憶體以完成作業且記憶體無法使用時,要求就會失敗,並發生記憶體不足錯誤。 不過,有幾個例外狀況案例,線程不會失敗,但會等到記憶體可用為止。 其中一個案例是記憶體配置,另一個案例是查詢編譯記憶體。

SQL Server 會使用稱為 號誌 的線程同步處理對象,追蹤已授與多少記憶體以供查詢執行。 如果 SQL Server 用完預先定義的 QE 工作區,而不是因為記憶體不足錯誤而讓查詢失敗,而會導致查詢等候。 鑑於允許工作區內存佔用相當大比例的整體 SQL Server 記憶體,在此空間中等候記憶體有嚴重的效能影響。

等待發生於大量並行查詢要求執行記憶體並同時耗盡 QE 記憶體池時,或當少數並行查詢各自要求非常大的記憶體配置量時。 無論哪種情況,隨之而來的效能問題都可能引發以下症狀:

  • 緩衝區快取中的資料和索引頁面可能已清出,以便為大型記憶體授權要求提供空間。 這表示來自查詢請求的頁面讀取必須從磁碟提供(這會是明顯較慢的操作)。
  • 其他記憶體配置的請求可能會因記憶體不足錯誤而失敗,因為資源被排序、雜湊或索引建立操作佔用。
  • 需要執行記憶體資源的請求正在等候資源可供使用,而且需要很長的時間才能完成。 換句話說,對終端用戶來說,這些查詢速度很慢。

因此,如果您在 Perfmon、動態管理檢視(DMV)或 DBCC MEMORYSTATUS 中觀察到查詢執行記憶體等候,則必須採取動作來解決此問題,特別是當問題經常發生時。 想了解如何操作,請參閱 《如何減少排序與雜湊運算的記憶體授與》。

如何辨識記憶體授與等待

從伺服器層開始確認查詢是否正在等待查詢執行(QE)記憶體。 接著深入分析需要調整或重寫的個別查詢。 選擇最適合你的方法。

以下方法顯示伺服器層級的總體記憶體授權使用情況:

以下方法用以識別使用或等待記憶體授與的個別查詢:

如何在伺服器層級監控記憶體授與使用情況

請使用以下方法查看整個 SQL Server 實例的總體記憶體授與使用量與等待情況。 有些方法可能在你的環境中無法取得。 例如,Azure SQL Database 不提供 Perfmon。

資源信號 DMV sys.dm_exec_query_resource_semaphores

sys.dm_exec_query_resource_semaphores動態管理檢視(DMV)將查詢保留記憶體依資源池(內部、預設及使用者建立)及resource_semaphore依工作負載群組(常規及小型查詢請求)加以細分。 一個有用的查詢可能是:

SELECT 
  pool_id
  ,total_memory_kb
  ,available_memory_kb
  ,granted_memory_kb
  ,used_memory_kb
  ,grantee_count, waiter_count 
  ,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs

下列範例輸出顯示,大約 900 MB 的查詢執行記憶體已由 22 個要求使用,還有 3 個正在等候。 此記憶體使用發生在預設池(pool_id = 2)及一般查詢信號量(resource_semaphore_id = 0)。

pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1       30880           30880               0                 0              0             0            0
1       5120            5120                0                 0              0             0            1
2       907104          0                   907104            898656         22            3            0
2       40960           40960               0                 0              0             0            1

(4 rows affected)

效能監視器計數項目

你也可以透過 SQL Server 記憶體管理器物件 效能監視器(Perfmon)計數器找到類似資訊。 你可以觀察目前已授與的要求(Memory Grants Outstanding)、等待中的授與要求(Memory Grants Pending),以及記憶體授與所使用的記憶體量(Granted Workspace Memory (KB))。

以下截圖顯示,擱置中的授與為 18 筆,待處理的授與為 2 筆,且已授與的工作區記憶體為 828,288 KB。 Memory Grants Pending 計數器的值非零表示記憶體已耗盡。

顯示記憶體授權未完成、待處理及已授與的工作區記憶體之 效能監視器 螢幕擷取畫面。

請使用以下計數器:

  • SQLServer,記憶體管理員:最大工作區記憶體(KB)
  • SQLServer,記憶體管理員:記憶體授予未完成
  • SQLServer,記憶體管理員:記憶體授權擱置中
  • SQLServer,記憶體管理器:已授與的工作區記憶體(KB)

DBCC 記憶體狀態

另一個您可以查看查詢保留記憶體詳細資料的地方是 DBCC MEMORYSTATUS(查詢記憶體物件區段)。 您可以查看使用者查詢的 Query Memory Objects (default) 輸出。 例如,如果您已使用名為 PoolAdmin 的資源集區來啟用 Resource Governor,您可以同時查看 Query Memory Objects (default) 和 Query Memory Objects (PoolAdmin)。

以下範例顯示系統輸出,其中18個請求擁有用於查詢執行的記憶體,2個請求正在等待記憶體。 可用計數為零,代表工作區記憶體已無餘量。 這個條件說明兩個等待中的請求。 此欄位 Wait Time 顯示自請求加入等待佇列以來的經過時間(毫秒)。 欲了解更多關於這些計數器的資訊,請參見 查詢記憶體物件。

Query Memory Objects (default)                                           Value
------------------------------------------------------------------------ -----------
Grants                                                                   18
Waiting                                                                  2
Available                                                                0
Current Max                                                              103536
Future Max                                                               97527
Physical Max                                                             139137
Next Request                                                             5752
Waiting For                                                              8628
Cost                                                                     16
Timeout                                                                  401
Wait Time                                                                2750

(11 rows affected)

Small Query Memory Objects (default)                                     Value
------------------------------------------------------------------------ -----------
Grants                                                                   0
Waiting                                                                  0
Available                                                                5133
Current Max                                                              5133
Future Max                                                               5133

DBCC MEMORYSTATUS 同時顯示負責追蹤查詢執行記憶體的記憶體 clerk 資訊。 下列輸出顯示配置給查詢執行 (QE) 保留的頁面超過 800 MB。

MEMORYCLERK_SQLQERESERVATIONS (node 0)                                   KB
------------------------------------------------------------------------ -----------
VM Reserved                                                              0
VM Committed                                                             0
Locked Pages Allocated                                                   0
SM Reserved                                                              0
SM Committed                                                             0
Pages Allocated                                                          824640

記憶體管理員 DMV sys.dm_os_memory_clerks

如果你需要表格式的結果集而非區 DBCC MEMORYSTATUS 段式輸出,請使用 sys.dm_os_memory_clerks 來取得類似資訊。 尋找 MEMORYCLERK_SQLQERESERVATIONS 記憶體管理員。 查詢記憶體物件在這個 DMV 中無法使用。

SELECT type, memory_node_id, pages_kb 
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'

以下是範例輸出:

type                                            memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS                   0              824640
MEMORYCLERK_SQLQERESERVATIONS                   64             0

使用 Extended Events 識別記憶體授與 (XEvents)

多個 擴充事件(XEvents) 提供您可以在追蹤中擷取的記憶體授與資訊:

  • sqlserver.additional_memory_grant:當查詢在執行過程中嘗試取得更多記憶體授權時發生。 若未能取得這項額外記憶體授與,可能會導致查詢變慢。
  • sqlserver.query_memory_grant_blocking:當查詢在等待記憶體授權期間阻擋其他查詢時發生。
  • sqlserver.query_memory_grant_info_sampling:在隨機抽樣的查詢結束時發生,並提供記憶體授與資訊(例如可用於遙測)。
  • sqlserver.query_memory_grant_resource_semaphores:會每五分鐘在每個資源管理員資源集區發生一次。
  • sqlserver.query_memory_grant_usage:在查詢處理結束時發生,適用於記憶體授權超過 5 MB 的查詢。 它幫助你理解記憶體授與的不準確度。
  • sqlserver.query_memory_grants:每次有記憶體授權的查詢,每五分鐘發生一次。

記憶體授權意見反應擴充事件

關於查詢處理記憶體授權回饋功能的資訊,請參見 記憶體授權回饋。

下列延伸事件與記憶體授與回饋相關:

  • sqlserver.memory_grant_feedback_loop_disabled:當記憶體授權回饋迴圈被停用時發生。
  • sqlserver.memory_grant_updated_by_feedback:當記憶體授權透過回饋更新時發生。

與記憶體授與相關的查詢執行警告

以下擴展事件會在查詢等待或記憶體耗盡時報告警告:

  • sqlserver.execution_warning:當 T-SQL 陳述式或預存程序等待取得記憶體授與超過一秒,或第一次嘗試取得記憶體時失敗時,就會發生此事件。 將此事件與識別等待的事件結合使用,以排除影響效能的爭用問題。
  • sqlserver.hash_spill_details:當雜湊處理結束時,記憶體不足以處理雜湊連接的建置輸入時會發生。 將此事件與任何 query_pre_execution_showplan 或 query_post_execution_showplan 事件搭配使用,以判斷產生的計劃中的哪個作業造成哈希溢出。
  • sqlserver.hash_warning:當記憶體不足以處理雜湊連接的建置輸入時發生。 當建置輸入被分割時,此條件會導致雜湊遞迴,或當建置輸入的分割超過最大遞迴層級時,發生雜湊中止。 將此事件與任何 query_pre_execution_showplan 或 query_post_execution_showplan 事件搭配使用,以判斷產生的計劃中的哪個作業造成哈希警告。
  • sqlserver.sort_warning:當執行中的查詢的排序操作無法放入記憶體時發生。 這個事件不會針對索引建立所造成的排序作業產生,而只適用於查詢中的排序作業。 (例如,Select 陳述式中的 Order By。)使用此事件來識別因排序作業而執行緩慢的查詢,特別是當 warning_type = 2 時,表示需要對資料進行多次傳遞才能完成排序。

包含記憶體授與資訊的計畫產生事件

下列產生擴充事件的查詢計畫預設會包含 granted_memory_kb 和 ideal_memory_kb 欄位:

  • sqlserver.query_plan_profile
  • sqlserver.query_post_execution_plan_profile
  • sqlserver.query_post_execution_showplan
  • sqlserver.query_pre_execution_showplan

資料行存放區索引建置

Extended Events 也涵蓋欄位儲存索引建置期間所使用的執行記憶體。 以下活動可參加:

  • sqlserver.column_store_index_build_low_memory:儲存引擎偵測到記憶體不足,並減少了列群組大小。 這裡有幾個值得注意的欄位。
  • sqlserver.column_store_index_build_memory_trace:追蹤索引建置期間的記憶體使用情況。
  • sqlserver.column_store_index_build_memory_usage_scale_down:儲存引擎縮減。
  • sqlserver.column_store_index_memory_estimation:顯示 COLUMNSTORE 列群組建置期間的記憶體估計結果。

如何辨識使用或等待記憶體授與的查詢

在個別請求層級,你可以找到兩種查詢:一種是消耗大量查詢執行記憶體的查詢,另一種是等待該記憶體的查詢。 請使用以下 DMV 與 查詢存放區 來識別查詢及請求執行記憶體的大小。

等待查詢的記憶體需求可能較低,這時你可以將注意力放在其他地方。 如果他們要求超大記憶體容量,也可能是罪魁禍首。

常見的是某個特定的查詢是肇因,但許多實例仍在運行。 獲得記憶體授與的實例會讓同一查詢的其他實例等待授與。

使用 sys.dm_exec_query_memory_grants 識別特定查詢

若要查看個別請求及其請求與獲授與的記憶體大小,請查詢 sys.dm_exec_query_memory_grants 動態管理檢視(DMV)。 此 DMV 會顯示目前執行查詢的相關信息,而非歷程記錄資訊。

下列語句會從 DMV 取得數據,並也會擷取查詢文字和查詢計劃:

SELECT 
  session_id
  ,requested_memory_kb
  ,granted_memory_kb
  ,used_memory_kb
  ,queue_id
  ,wait_order
  ,wait_time_ms
  ,is_next_candidate
  ,pool_id
  ,text
  ,query_plan
FROM sys.dm_exec_query_memory_grants
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
  CROSS APPLY sys.dm_exec_query_plan(plan_handle)

以下是查詢在 QE 記憶體使用中期間的縮寫範例輸出。 大部分的查詢都會獲得記憶體配置,如 granted_memory_kb 和 used_memory_kb 所示,且兩者皆為非 NULL 的數值。 其要求未獲核准的查詢正在等候執行記憶體,以及該 granted_memory_kb = NULL。 此外,它們也會放在等候佇列中,queue_id = 6。 他們的 wait_time_ms 顯示約需等待 37 秒。 工作階段 72 是下一個會獲得授權的,如 wait_order = 1 所示,而工作階段 74 則排在其後,wait_order = 2。

session_id requested_memory_kb  granted_memory_kb    used_memory_kb       queue_id wait_order  wait_time_ms         is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
83         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
84         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
74         41232                NULL                 NULL                 6        2           37438                0                 2      
78         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
81         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
71         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
75         41232                NULL                 NULL                 6        0           37438                1                 2      
82         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
76         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
79         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
85         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
70         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
55         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
59         41232                NULL                 NULL                 6        3           37438                0                 2      
62         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
54         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
77         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
52         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
72         41232                NULL                 NULL                 6        1           37438                0                 2      
69         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
73         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
66         41232                NULL                 NULL                 6        4           37438                0                 2      
68         41232                41232                40848                NULL     NULL        NULL                 NULL              2      
63         41232                41232                40848                NULL     NULL        NULL                 NULL              2      

使用 sys.dm_exec_requests 識別特定查詢

等待類型RESOURCE_SEMAPHORE表示查詢正在等待記憶體授與。 你可以在 sys.dm_exec_requests 中觀察到個別請求的等候類型。 此 DMV 是識別哪些查詢因記憶體授與不足而成為受害者的最佳起點。

您也可以在 SQL Server 層級將 RESOURCE_SEMAPHORE 等待視為 sys.dm_os_wait_stats 中的匯總資料點來觀察。 當查詢記憶體請求無法獲得核准時,會顯示此等待類型,因為其他同時進行的查詢已佔用記憶體。 大量等待中的請求和較長的等待時間表示使用執行記憶體或較大的記憶體請求大小的並行查詢數量過多。

附註

記憶體授權的等候時間有限。 過度等候之後(例如超過 20 分鐘),查詢會逾時,且 SQL Server 會引發錯誤 8645:「等候記憶體資源執行查詢時發生逾時。 重新執行那個查詢。你可以透過查看 sys.dm_exec_query_memory_grants 中的 timeout_sec,看到在伺服器層級設定的逾時值。 逾時值可能在不同 SQL Server 版本間略有差異。

透過使用 sys.dm_exec_requests,你可以查看哪些查詢被賦予記憶體以及該授與的記憶體大小。 此外,您可以藉由尋找 RESOURCE_SEMAPHORE 等候類型,來識別目前正在等候記憶體授與的查詢。 以下是一個查詢語句,顯示已授與和等候的請求:

SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests 
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0 
       OR wait_type = 'RESOURCE_SEMAPHORE'

範例輸出顯示兩個要求已獲授與記憶體,另有 24 個要求正在等待授與。 granted_query_memory 欄會顯示以 8 KB 頁面為單位的大小。 例如,值為 34,709 表示配置的記憶體為 34,709 * 8 KB = 277,672 KB。

session_id wait_type               wait_time   granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
66         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
67         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
68         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
69         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
70         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
71         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
72         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
73         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
74         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
75         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
76         ASYNC_NETWORK_IO        11          34709                select * from sys.messages order by message_id option (maxdop 1)
77         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
78         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
79         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
80         RESOURCE_SEMAPHORE      161435      0                    select * from sys.messages order by message_id option (maxdop 1)
81         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
82         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
83         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
84         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
85         ASYNC_NETWORK_IO        14          34709                select * from sys.messages order by message_id option (maxdop 1)
86         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
87         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
88         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)
89         RESOURCE_SEMAPHORE      161439      0                    select * from sys.messages order by message_id option (maxdop 1)

使用sys.dm_exec_query_stats識別特定查詢

如果記憶體授權問題目前還沒發生,但你想找出造成問題的查詢,可以查看 sys.dm_exec_query_stats 的歷史查詢資料。 數據的存留期會系結至每個查詢的查詢計劃。 從計畫快取中移除計畫時,會從這個檢視中移除對應的資料列。 DMV 會將統計資料保留在記憶體中,但在 SQL Server 重新啟動後,或因記憶體壓力導致計畫快取釋放後,這些統計資料不會被保留。

即使有此限制,這些資訊仍具價值,尤其對於彙整查詢統計資料而言。 有人最近可能報告查詢獲得大量記憶體配置,但當你看伺服器工作負載時,問題已經消失。 在這種情況下,sys.dm_exec_query_stats 可以提供其他車輛管理所無法提供的見解。

以下範例查詢將找出佔用執行記憶體最多的前 20 個語句。 即使查詢結構相同,輸出也會顯示個別語句。 例如,SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 與 SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 是不同的資料列(只有篩選述詞的值會有所不同)。 查詢會取得前 20 個授與大小上限大於 5 MB 的陳述式。

SELECT TOP 20
  SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2) + 1) AS statement_text  
  ,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
  ,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
  ,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
  ,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
  ,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
  ,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count)  /1024.0) AS avg_grant_used_mb
  ,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count)  /1024.0) AS avg_ideal_grant_mb
  ,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
  ,execution_count
FROM sys.dm_exec_query_stats QS
  CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC

藉由查看 所 query_hash匯總的查詢,即可取得更強大的深入解析。 此範例說明如何自查詢計劃首次快取以來,尋找查詢語句在其所有實例中的平均、最大和最小授與大小。

SELECT TOP 20
  MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2) + 1)) AS sample_statement_text  
  ,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
  ,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
  ,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
  ,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
  ,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
  ,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
  ,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count))  /1024.0) AS avg_ideal_grant_mb
  ,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
  ,SUM(execution_count) AS execution_count
  ,query_hash
FROM sys.dm_exec_query_stats QS
  CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)

數據 Sample_Statement_Text 欄會顯示符合查詢雜湊的查詢結構範例,但應該讀取而不考慮語句中的特定值。 例如,如果語句包含 WHERE Id = 5,您可以使用其更泛型的形式來讀取它: WHERE Id = @any_value。

以下是查詢的簡略範例輸出,其中只顯示選取的數據行:

sample_statement_text                      max_grant_mb  avg_grant_mb  max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count 
-----------------------------------------  ------------  ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select     de.ObjectName,de.CounterName,d  282.45        282.45        6.50              6.50              282.45             282.45                        1               
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch  33.86         8.55          7.80              1.97              8.55               42.74                         5               
insert into #tmpCounterDateTime (CounterD  32.45         32.45         3.11              3.11              32.45              32.45                         1               
select db_id() dbid, db_name() dbname, *   20.80         1.30          5.75              0.36              1.30               20.80                         16              
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch  20.55         5.19          5.13              1.28              5.19               25.93                         5               
SELECT xmlplan FROM (SELECT ROW_NUMBER()   19.69         1.97          1.09              0.11              1.97               19.69                         10              
if ( select max(cast(countervalue as floa  16.39         8.20          0.77              0.38              8.20               16.39                         2               
SELECT udf.name AS [Name], udf.object_id   11.36         5.08          1.66              0.83              5.08               20.33                         4               
select --*                     Database_I  10.94         5.47          1.98              0.99              5.47               10.94                         2               
IF (select max(cast(dat.countervalue as f  8.00          1.00          0.00              0.00              0.53               8.00                          8               
insert into #tmpCounterDateTime (CounterD  5.72          2.86          1.98              0.99              2.86               5.72                          2               
INSERT INTO #tmp (CounterDateTime, Counte  5.39          1.08          1.64              0.33              1.08               6.47                          6               

使用 查詢存放區 (QDS) 搭配sys.query_store_runtime_stats來識別特定查詢

如果你啟用 查詢存放區,就可以使用其持久的歷史統計資料。 與來自 sys.dm_exec_query_stats 的資料不同,這些統計資料能在 SQL Server 重啟或記憶體壓力下存活,因為它們被儲存在資料庫中。

查詢存放區(QDS)有大小限制和保留政策。 如需詳細資訊,請參閱 管理 查詢存放區 的最佳做法中的 設定最佳 查詢存放區 擷取模式 和 在 查詢存放區 中保留最相關的資料 章節。

  1. 使用此查詢確認您的資料庫是否已啟用查詢存放區:

    SELECT name, is_query_store_on 
    FROM sys.databases
    WHERE is_query_store_on = 1
    
  2. 在您想要調查的特定資料庫內容中執行下列診斷查詢:

    SELECT
       MAX(qtxt.query_sql_text) AS sample_sql_text
       ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb
       ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb
       ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb
       ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb
       ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb
       ,SUM(count_executions) AS count_query_executions
    FROM sys.query_store_runtime_stats rts
    JOIN sys.query_store_plan p
      ON p.plan_id = rts.plan_id
    JOIN sys.query_store_query q
      ON p.query_id = q.query_id
    LEFT OUTER JOIN sys.query_store_query_text qtxt
      ON q.query_text_id = qtxt.query_text_id
    GROUP BY q.query_hash
    HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB
    ORDER BY SUM(avg_query_max_used_memory) DESC
    OPTION (MAX_GRANT_PERCENT = 5)
    

    此處的原則與 sys.dm_exec_query_stats 相同;您會看到陳述式的彙總統計資料。 不過,有一個差別是,使用 QDS 時,你只會看到這個資料庫範圍內的查詢,而不是整個 SQL Server。 你可能需要知道某個記憶體授與要求是在哪個資料庫中執行的。 否則,請在多個資料庫中執行此診斷查詢,直到您找到大量的記憶體授與為止。

    以下是精簡的範例輸出:

    sample_sql_text                           avg_mem_grant_used_mb  min_mem_grant_used_mb  max_mem_grant_used_mb  stdev_mem_grant_used_mb  last_mem_grant_used_mb  count_query_executions
    ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ----------------------
    SELECT   qtxt.query_sql_text  ,CONVERT(D  550.16                 550.00                 550.00                 0.00                     550.00                  1
    SELECT   qtxt.query_sql_text  ,rts.avg_q  61.00                  36.00                  65.00                  10.87                    51.00                   14
    SELECT   qtxt.query_sql_text  ,q.*  ,rts  25.46                  25.00                  25.00                  0.00                     25.00                   2
    insert into #tmpStats select 5 'Database  13.69                  13.00                  13.00                  0.03                     13.00                   16
    SELECT   q.*  ,rts                        11.93                 11.00                  12.00                  0.23                     12.00                   2
    SELECT *  ,rts.avg_query_max_used_memory  9.70                   9.00                   9.00                   0.00                     9.00                    1
    SELECT   qtxt.query_sql_text  ,rts.avg_q  9.32                   9.00                   9.00                   0.00                     9.00                    1
    select db_id() dbid, db_name() dbname, *  7.33                   7.00                   7.00                   0.00                     7.00                    9
    SELECT q.*  ,rts.avg_query_max_used_memo  6.65                   6.00                   6.00                   0.00                     6.00                    1
    (@_msparam_0 nvarchar(4000),@_msparam_1   5.17                   4.00                   5.00                   0.68                     4.00                    2
    

自訂診斷查詢

以下是一個查詢,結合了多個檢視的資料,包括先前列出的三個檢視。 除了 sys.dm_exec_query_resource_semaphores 所提供的伺服器層級統計數據之外,它也會透過 sys.dm_exec_requests 和 sys.dm_exec_query_memory_grants 提供對會話及其授權更全面的檢視。

附註

此查詢每個會話回傳兩列,因為它使用 sys.dm_exec_query_resource_semaphores。 你會得到一列給一般資源信號量,另一列給小查詢資源信號量。

SELECT    CONVERT (varchar(30), GETDATE(), 121) as runtime
         , r.session_id
         , r.wait_time
         , r.wait_type
         , mg.request_time 
         , mg.grant_time 
         , mg.requested_memory_kb
          / 1024 requested_memory_mb 
         , mg.granted_memory_kb
          / 1024 AS granted_memory_mb 
         , mg.required_memory_kb
          / 1024 AS required_memory_mb 
         , max_used_memory_kb
          / 1024 AS max_used_memory_mb
         , rs.pool_id as resource_pool_id
         , mg.query_cost 
         , mg.timeout_sec 
         , mg.resource_semaphore_id 
         , mg.wait_time_ms AS memory_grant_wait_time_ms 
         , CASE mg.is_next_candidate 
           WHEN 1 THEN 'Yes'
           WHEN 0 THEN 'No'
           ELSE 'Memory has been granted'
         END AS 'Next Candidate for Memory Grant'
         , r.command
         , ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
         , rs.target_memory_kb
          / 1024 AS server_target_grant_memory_mb 
         , rs.max_target_memory_kb
          / 1024 AS server_max_target_grant_memory_mb 
         , rs.total_memory_kb
          / 1024 AS server_total_resource_semaphore_memory_mb 
         , rs.available_memory_kb
          / 1024 AS server_available_memory_for_grants_mb 
         , rs.granted_memory_kb
          / 1024 AS server_total_granted_memory_mb 
         , rs.used_memory_kb
          / 1024 AS server_used_granted_memory_mb 
         , rs.grantee_count AS successful_grantee_count 
         , rs.waiter_count AS grant_waiters_count 
         , rs.timeout_error_count 
         , rs.forced_grant_count 
         , mg.dop 
         , r.blocking_session_id
         , r.cpu_time
         , r.total_elapsed_time
         , r.reads
         , r.writes
         , r.logical_reads
         , r.row_count
         , s.login_time
         , d.name
         , s.login_name
         , s.host_name
         , s.nt_domain
         , s.nt_user_name
         , s.status
         , c.client_net_address
         , s.program_name
         , s.client_interface_name
         , s.last_request_start_time
         , s.last_request_end_time
         , c.connect_time
         , c.last_read
         , c.last_write
         , qp.query_plan
FROM     sys.dm_exec_requests r
         INNER JOIN sys.dm_exec_connections c
           ON r.connection_id = c.connection_id
         INNER JOIN sys.dm_exec_sessions s
           ON c.session_id = s.session_id
         INNER JOIN sys.databases d
           ON r.database_id = d.database_id
         INNER JOIN sys.dm_exec_query_memory_grants mg
           ON s.session_id = mg.session_id
         INNER JOIN sys.dm_exec_query_resource_semaphores rs
           ON mg.resource_semaphore_id = rs.resource_semaphore_id
         CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
         CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )

附註

這個診斷查詢利用 LOOP JOIN 提示來避免查詢本身的記憶體授與,且不使用 ORDER BY 子句。 如果診斷查詢等待授與,就失去了診斷記憶體授與的初衷。 這個 LOOP JOIN 提示可能會讓診斷查詢變慢,但取得診斷結果更重要。

以下是此診斷查詢中只有選取數據行的簡略範例輸出。

session_id wait_time wait_type requested_memory_mb granted_memory_mb required_memory_mb max_used_memory_mb resource_pool_id
60 0 NULL 9 9 7 1 1
60 0 NULL 9 9 7 1 2
75 1310085 RESOURCE_SEMAPHORE 40 NULL 0 NULL 1
75 1310085 RESOURCE_SEMAPHORE 40 NULL 0 NULL 2
86 1310129 RESOURCE_SEMAPHORE 40 NULL 0 NULL 1
86 1310129 RESOURCE_SEMAPHORE 40 NULL 0 NULL 2

範例輸出清楚顯示,由 session_id = 60 提交的查詢成功獲得了它所請求的 9 MB 記憶體,但啟動查詢執行只需 7 MB。 最後,查詢只使用了它從伺服器收到的 9 MB 中的 1 MB。 輸出也顯示工作階段 75 和 86 正在等待記憶體授與,這就是 wait_type 為何 RESOURCE_SEMAPHORE 的原因。 他們的等待時間已經超過 1,300 秒(21 分鐘),且 granted_memory_mb 為 NULL。

此診斷查詢是一個範例,因此您可以隨意以任何符合您需求的方式加以修改。

記憶體授與問題的診斷工具

要診斷記憶體授權問題,請使用 SQL LogScout 從先前描述的 DMV 與 效能監視器 計數器收集輸出。

在 LightPerf、 GeneralPerf 或 DetailedPerf 情境中執行 SQL LogScout,以收集必要的日誌。 在 YourServer_PerfStats.out 檔案中尋找 -- dm_exec_query_resource_semaphores -- 和 -- dm_exec_query_memory_grants -- 輸出。

或者,您也可以使用 SQL Nexus 將 SQL LogScout 輸出匯入 SQL Server 資料庫。 SQL Nexus 會建立兩個數據表,tbl_dm_exec_query_resource_semaphores 以及 tbl_dm_exec_query_memory_grants,其中包含診斷記憶體授權所需的資訊。

SQL LogScout 也會以 .blg 檔案的形式收集 效能監視器 日誌。 使用這些檔案檢視 效能監視器 計數器章節中所述的計數器。

為什麼記憶體授予對開發者和資料庫管理員很重要

記憶體授與問題往往是最常見的記憶體相關問題之一。 應用程式經常執行看似簡單的查詢,卻因龐大的排序或雜湊操作而導致 SQL Server 效能問題。 這類查詢不僅會消耗大量 SQL Server 記憶體,還會導致其他查詢必須等待記憶體可用,造成效能瓶頸。

透過使用本文所述工具(DMV、Perfmon 計數器及實際查詢計畫),您可以辨識哪些查詢會消耗大量記憶體授與。 然後,您可以微調或重寫這些查詢,以解決或減少工作區內存使用量。

如何減少排序與雜湊操作帶來的記憶體授與

當你識別出消耗大量查詢保留記憶體的特定查詢後,可以透過重新設計這些查詢來減少記憶體授與。

造成查詢中的排序和哈希作業的原因

第一步是了解查詢中哪些操作可能導致記憶體授與。

查詢使用 SORT 運算子的原因

以下 T-SQL 元素可能會在查詢計畫中產生 Sort 運算子:

  • ORDER BY(T-SQL) 會讓列先被排序,然後作為最終結果串流。

  • 若沒有底層索引來排序被群組欄位,則 GROUP BY(T-SQL)可能會在查詢計畫中引入排序運算子,以對群組欄位進行排序。

  • DISTINCT (T-SQL) 的行為類似於 GROUP BY。 若要識別相異的資料列,會排序中繼結果,然後移除重複專案。 如果資料尚未因已排序的索引搜尋或掃描而排序,優化器會在此運算子之前使用 Sort 運算子。

  • 當查詢優化器選取此運算子時,合併聯結 運算子會要求兩個要聯結的輸入都已排序。 如果某個資料表的 join 欄位沒有叢集索引,SQL Server 可能會觸發排序。

查詢使用雜湊值查詢計畫運算子的原因

這份清單並不詳盡,但包含最常見的 Hash 作業原因。 分析查詢計畫 以識別雜湊值匹配操作。 以下 T-SQL 元素可能會產生 Hash Match 運算子:

了解這些常見原因可協助您盡可能消除對 SQL Server 提出的大型記憶體授與要求。

減少排序和雜湊作業或記憶體授與大小的方法

以下技術可減少排序與雜湊操作,或減少記憶體配置的大小:

  • 讓 統計數據 保持在最新狀態。 這個可改善許多層級查詢效能的基本步驟,可確保查詢優化器在選取查詢計劃時具有最精確的資訊。 SQL Server 會根據統計資料決定要求的記憶體配置大小。 過時的統計數據可能會導致過度估計或低估授權要求,因而分別導致不必要的高授權要求,或將結果寫入磁碟。 請務必在資料庫啟用自動更新統計資料並/或使用 UPDATE STATISTICS 或 sp_updatestats 隨時更新統計資料。
  • 減少來自數據表的數據列數目。 如果您使用更嚴格的 WHERE 篩選條件或 JOIN 並減少資料列數目,則查詢計劃中的後續排序可對較小的結果集進行排序或彙總。 較小的中繼結果集需要較少的工作集記憶體。 這是一項一般規則,開發人員可遵循這項通則,不僅能節省工作集記憶體,還能減少 CPU 和 I/O(此步驟不一定可行)。 如果查詢已妥善撰寫且節省資源,則已符合此指導方針。
  • 在聯結資料行上建立索引,以協助合併聯結。 查詢計劃中的中繼作業會受到基礎表上的索引所影響。 例如,如果資料表在聯結資料行上沒有索引,而且發現合併聯結是最具成本效益的聯結運算符,該資料表中的所有資料列都必須在執行聯結之前排序。 如果改為在該欄位上建立索引,則可以省略排序作業。
  • 建立索引有助於避免雜湊作業。 一般而言,基本查詢調校會從檢查您的查詢是否有適當的索引來協助它們減少讀取,並盡可能減少或消除大型排序或哈希作業。 哈希聯結通常用於處理大型、未排序和非索引的輸入。 建立索引可能會改變這種優化策略,並加快資料檢索速度。 如需建立索引的協助,請參閱 Database Engine Tuning Advisor 和 使用遺漏索引建議調整非叢集索引。
  • 對於使用 GROUP BY 的聚合查詢,適當時使用欄式儲存索引。 處理非常大型列集且通常執行「分組」聚合的分析查詢,可能需要大量記憶體區塊才能完成工作。 如果沒有可提供已排序結果的索引,則查詢計劃中會自動引入排序。 如果結果非常大,可能會導致昂貴的記憶體配置。
  • 如果您不需要它,請移除 ORDER BY。 在將結果串流處理至以自己的方式排序結果的應用程式,或允許使用者修改檢視的數據順序時,您不需要在 SQL Server 端執行排序。 只要依照伺服器產生數據的順序將數據串流至應用程式,讓使用者自行排序。 Power BI 或 Reporting Services 之類的報表應用程式是這類應用程式的範例,可讓使用者排序其數據。
  • 請考慮在 T-SQL 查詢中存在聯結時,謹慎使用 LOOP JOIN 提示。 此技術可能避免使用記憶體配置的雜湊或合併連接。 不過,這個選項僅建議作為最後手段,因為強制聯結可能會導致查詢速度明顯變慢。 對你的工作負載進行壓力測試,以確認已啟用此選項。 在某些情況下,巢狀迴圈聯結甚至可能不是選項。 在這種情況下,SQL Server 可能會因錯誤 MSSQLSERVER_8622「查詢處理器因本查詢中定義的提示而無法產生查詢計畫。」而失敗。

使用記憶體授與查詢提示控制授與大小

從 SQL Server 2012 SP3 開始,你可以使用查詢提示來控制每次查詢的記憶體配置大小。 以下是如何使用此提示的範例:

SELECT Column1,  Column2
FROM Table1 
ORDER BY Column1 
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )

我們建議您在這裡使用保守的值,特別是在預期您的查詢會有許多執行個體同時執行的情況下。 請務必對工作負載進行壓力測試,以符合生產環境,並判斷要使用的值。

如需詳細資訊,請參閱 MAX_GRANT_PERCENT和MIN_GRANT_PERCENT。

使用 Resource Governor 限制記憶體授與

QE 記憶體是資源管理員在使用 MIN_MEMORY_PERCENT和MAX_MEMORY_PERCENT 設定時實際限制的記憶體。 識別導致大量記憶體授權量的查詢之後,您可以限制工作階段或應用程式所使用的記憶體。

default工作負載群組允許查詢佔用在 SQL Server 執行個體上可授與的記憶體最多 25%。 如需詳細資訊,請參閱 Resource Governor 資源集區 和 CREATE WORKLOAD GROUP。

使用自適應查詢處理與記憶體授權回饋

SQL Server 2017 引進了記憶體授與回饋功能。 它可讓查詢執行引擎根據先前的歷程記錄調整提供給查詢的授權。 目標是盡可能減少記憶體授與的大小,或在需要更多記憶體時增加其大小。 此功能分為三波發布:

  1. SQL Server 2017 中的批次模式記憶體授與意見反應
  2. SQL Server 2019 中的資料列模式記憶體授與回饋
  3. 在 SQL Server 2022 中使用查詢存放區和百分位數授與的記憶體授與意見反應磁碟持續性

如需詳細資訊,請參閱記憶體授權意見反應。 此功能可減少執行時查詢的記憶體授與大小,從而減少大量記憶體授與要求所帶來的問題。

當你使用此功能時,尤其是在可使用列模式自適應處理的 SQL Server 2019 及更新版本中,你可能不會注意到查詢執行所導致的任何記憶體問題。 不過,如果該功能開啟(預設),且仍看到大量 QE 記憶體消耗,請依照前述步驟重寫查詢。

增加 SQL Server 或 OS 記憶體

在你採取措施減少查詢時不必要的記憶體分配後,如果你仍遇到相關的低記憶體問題,工作負載很可能需要更多記憶體。 因此,如果系統上有足夠的實體記憶體,建議使用設定max server memory來增加 SQL Server 的記憶體。 請遵循有關保留約 25% 記憶體給 OS 和其他需求的建議。 如需詳細資訊,請參閱 伺服器記憶體組態選項。 如果系統記憶體不足,可以考慮加裝實體 RAM。 如果是虛擬機,請增加您虛擬機的專用 RAM。

記憶體授與的內部運作方式

欲了解更多查詢執行記憶體內部資訊,請參閱 「了解 SQL Server 記憶體授與」 部落格文章。

如何建立高記憶體授與使用量的效能案例

以下範例展示了如何模擬大量查詢執行記憶體的消耗,並使查詢在 RESOURCE_SEMAPHORE 上等待。 利用此模擬學習如何使用本文所述的診斷工具與技術。

警告

請勿在生產系統上使用。 此模擬可協助您瞭解概念,並協助您更好地學習此概念。

  1. 在測試伺服器上,安裝 RML 公用程式 和 SQL Server。

  2. 使用像 SQL Server Management Studio 這類客戶端應用程式,將 SQL Server 的最大伺服器記憶體設定降低到 1,500 MB。

    EXEC sp_configure 'max server memory', 1500
    RECONFIGURE
    
  3. 打開命令提示字元,將目錄切換到 RML 工具資料夾。

    cd C:\Program Files\Microsoft Corporation\RMLUtils   
    
  4. 使用 ostress.exe 對測試 SQL Server 發送多個同時的要求。 這個例子使用 30 個同時工作階段,但你可以更改這個數值。

    ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30
    
  5. 使用先前所述的診斷工具來識別記憶體配置問題。

快速參考:減少大量記憶體配置的方法

以下清單總結了本文中描述的減少大量記憶體授與的選項:

  • 重寫查詢。
  • 更新統計數據,並定期更新統計數據。
  • 為識別的查詢或查詢建立適當的索引。 索引可能會減少大量處理的列數,從而改變 JOIN 演算法並縮小記憶體授與的大小,甚至完全消除它們。
  • 使用OPTION提示(min_grant_percent = XX,max_grant_percent = XX)。
  • 使用 資源管理員。
  • 請依賴 SQL Server 2017 及更新版本的記憶體授與意見反應。 此機制會根據先前執行調整記憶體配置大小,可能從一開始就避免記憶體配置問題。 在 SQL Server 2022 及更新版本中,它會在 查詢存放區 持續保留回饋,並使用百分位數授與。
  • 增加 SQL Server 或 OS 記憶體。