針對 SQL Server 記憶體授與所造成的效能變慢或記憶體不足問題進行疑難解答
什麼是記憶體授與？
記憶體授與，也稱為查詢執行 (QE) 保留、查詢執行記憶體、工作區內存和記憶體保留，描述查詢運行時間的記憶體使用量。 SQL Server 會在查詢執行期間配置此記憶體，以用於下列一或多個用途：
- 排序作業
- 哈希作業
- 大量複製作業 (不是常見的問題)
- 索引建立，包括插入 COLUMNSTORE 索引，因為哈希字典/數據表會在運行時間用於建立索引 (不是常見的問題)
若要提供一些內容，查詢可能會在其存留期內要求來自不同記憶體配置器或 Clerk 的記憶體，視其需要執行的動作而定。 例如，一開始剖析和編譯查詢時，它會取用編譯記憶體。 編譯查詢之後，就會釋放該記憶體，而產生的查詢計劃會儲存在計劃快取記憶體中。 一旦快取計劃之後，查詢就可以開始執行。 如果查詢執行任何排序作業、哈希比對作業 (JOIN 或匯總) ，或插入 COLUMNSTORE 索引，則會使用查詢執行配置器中的記憶體。 一開始，查詢會要求該執行記憶體，之後如果授與此記憶體，查詢會使用記憶體的全部或部分來排序結果或哈希貯體。 在查詢執行期間配置的這個記憶體就是所謂的記憶體授與。 如您所想，查詢執行作業完成後，記憶體授與會釋放回 SQL Server 以用於其他工作。 因此，記憶體授與配置在本質上是暫時性的，但仍可以持續很長的時間。 例如，如果查詢執行在記憶體中非常大的數據列集上執行排序作業，排序可能需要數秒或幾分鐘的時間，而且授與的記憶體會用於查詢的存留期。
具有記憶體授與的查詢範例
以下是使用執行記憶體及其查詢計劃來顯示授與的查詢範例：
SELECT *
FROM sys.messages
ORDER BY message_id
此查詢會選取超過 300,000 個數據列集，並加以排序。 排序作業會引發記憶體授與要求。 如果您在 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 會根據記憶體可用性，部分或完全授與該要求。 最後，查詢可能會使用已授與記憶體的百分比。 有一個記憶體 clerk (名為 'MEMORYCLERK_SQLQERESERVATIONS' 的記憶體) ，可追蹤這些記憶體配置， (查看 DBCC MEMORYSTATUS 或 sys.dm_os_memory_clerks) 。
記憶體授與：當 SQL Server 將要求的記憶體授與執行中的查詢時，即表示已發生記憶體授與。 有幾個性能計數器使用「授與」一詞。這些計數器
Memory Grants Outstanding和Memory Grants Pending會顯示已滿足或等待的記憶體授與計數。 它們不會考慮記憶體授與大小。 例如，單一查詢可能已耗用 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保留的等候。 挑選最適合您在伺服器層級查看較大圖片的圖片。 其中有些工具可能無法供您 (例如，Perfmon 無法在 Azure SQL Database) 中使用。 識別問題之後，您必須在個別查詢層級向下切入，以查看哪些查詢需要微調或重寫。
在伺服器層級，使用下列方法：
- 資源號誌 DMV sys.dm_exec_query_resource_semaphores 如需詳細資訊，請 參閱 sys.dm_exec_query_resource_semaphores。
- 效能監視器 計數器 如需詳細資訊，請參閱 SQL Server Memory Manager 物件。
- DBCC MEMORYSTATUS 如需詳細資訊，請參閱 DBCC MEMORYSTATUS。
- 記憶體 clerks DMV sys.dm_os_memory_clerks 如需詳細資訊， 請參閱 sys.dm_os_memory_clerks。
- 使用 XEvents (擴充事件來識別記憶體授與) 如需詳細資訊，請 參閱 XEvents) (擴充事件 。
在個別查詢層級，使用下列方法：
- 使用sys.dm_exec_query_memory_grants識別特定查詢：目前正在執行查詢。 如需詳細資訊， 請參閱 sys.dm_exec_query_memory_grants。
- 使用sys.dm_exec_requests識別特定查詢：目前正在執行查詢。 如需詳細資訊， 請參閱 sys.dm_exec_requests。
- 使用sys.dm_exec_query_stats識別特定查詢：查詢的歷程記錄統計數據。 如需詳細資訊， 請參閱 sys.dm_exec_query_stats。
- 使用 查詢存放區 (QDS) 搭配 sys.query_store_runtime_stats：使用 QDS 查詢的歷程記錄統計數據來識別特定查詢。 如需詳細資訊，請 參閱 sys.query_store_runtime_stats。
匯總記憶體使用量統計數據
資源號誌 DMV 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
下列範例輸出顯示 22 個要求使用大約 900 MB 的查詢執行記憶體，還有 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)
效能監視器 計數器
類似的資訊可透過 效能監視器 計數器取得，您可以在其中觀察目前授與的要求 (Memory Grants Outstanding) 、等候中的授與要求 (Memory Grants Pending) ，以及記憶體授與所使用的記憶體數量 (Granted Workspace Memory (KB)) 。 在下圖中，未付的授與為 18，暫止的授與為 2，而授與的工作區內存為 828,288 KB。 Memory Grants Pending具有非零值的 Perfmon 計數器表示記憶體已耗盡。
如需詳細資訊，請參閱 SQL Server Memory Manager 物件。
- SQLServer，記憶體管理員：工作區內存 (KB)
- SQLServer，記憶體管理員：記憶體授與未完成
- SQLServer，記憶體管理員：記憶體授與暫止
- SQLServer，記憶體管理員：授與工作區內存 (KB)
DBCC MEMORYSTATUS
另一個您可以查看查詢保留記憶體詳細數據的地方是 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
記憶體 Clerks DMV sys.dm_os_memory_clerks
如果您需要更多表格式結果集，不同於以區段為基礎的 DBCC MEMORYSTATUS，則您可以使用 sys.dm_os_memory_clerks 來取得類似的資訊。 尋找記憶體 Clerk 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
使用 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：發生於執行中查詢的排序作業無法放入記憶體時。 此事件不會針對索引建立所造成的排序作業產生，而只會針對查詢中的排序作業產生。 (例如，
Order BySelectstatement.) 使用此事件來識別因為排序作業而執行緩慢的查詢，特別是當 = 2，表示需要多次通過數據才能排序時warning_type。
規劃產生包含記憶體授與資訊的事件
根據預設，產生擴充事件的下列查詢計劃包含 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
數據行存放區索引建立
其中一個透過 XEvents 涵蓋的區域是在資料行存放區建置期間使用的執行記憶體。 這是可用的事件清單：
- 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 數據列群組建置期間的記憶體估計結果。
識別特定查詢
查看個別要求層級時，您可能會發現兩種查詢。 耗用大量查詢執行記憶體的查詢，以及正在等候相同記憶體的查詢。 第二個群組可能包含記憶體授與需求適中的要求，若是如此，您可能會將注意力放在其他位置。 但如果他們要求龐大的記憶體大小，也可能成為其中一個原因。 如果您發現情況如此，請將焦點放在它們上。 通常會發現其中一個特定查詢是命脈，但會繁衍其許多實例。 這些取得記憶體授與的實例會導致相同查詢的其他實例等候授與。 無論特定情況為何，您最終都必須識別查詢和所要求執行記憶體的大小。
使用 sys.dm_exec_query_memory_grants 識別特定查詢
若要檢視個別要求及其要求和已授與的記憶體大小，您可以查詢 sys.dm_exec_query_memory_grants 動態管理檢視。 此 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_kbused_memory_kb且為非 NULL 數值。 未取得要求授與查詢正在等候執行記憶體 和 granted_memory_kb = NULL。 此外，它們會放在 =6 的 queue_id 等候佇列中。 其 wait_time_ms 表示等候約37秒。 會話 72 是下一行，以取得 = 1 所指出的 wait_order 授與，而會話 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 識別特定查詢
SQL Server 中有一種等候類型，表示查詢正在等候記憶體授與 RESOURCE_SEMAPHORE。 您可以在 中觀察到個別要求的 sys.dm_exec_requests 這種等候類型。 後者 DMV 是識別哪些查詢是授與記憶體不足之犧牲者的最佳起點。 您也可以在sys.dm_os_wait_stats中觀察 RESOURCE_SEMAPHORESQL Server 層級的匯總數據點等候。 當因為其他並行查詢已用盡記憶體而無法授與查詢記憶體要求時，就會顯示此等候類型。 等候要求的計數很高，等候時間很長，表示使用執行記憶體或大型記憶體要求大小的並行查詢數目過多。
注意事項
記憶體授與的等候時間有限。 例如，在過度等候 (之後，) 超過 20 分鐘，SQL Server 逾時查詢並引發錯誤 8645：「等候記憶體資源執行查詢時發生逾時。 重新執行查詢。」在 中查看 ，您可能會看到在伺服器層級設定的timeout_secsys.dm_exec_query_memory_grants逾時值。 逾時值在 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'
範例輸出顯示已授與兩個要求的記憶體，而另外二十個要求正在等候授與。 數據行 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 可以提供其他 DVM 無法使用的深入解析。 以下是範例查詢，可協助您找出耗用最多執行記憶體的前 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 數據列，只有篩選述詞值會因) 而異。 查詢會取得授與大小上限大於5 MB的前20個語句。
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 也有大小限制和保留原則。 如需詳細資訊，請參閱管理 查詢存放區 的最佳做法中的設定最佳 查詢存放區 擷取模式和在 查詢存放區 中保留最相關的數據章節。
使用此查詢來識別您的資料庫是否已啟用 查詢存放區：
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1在您想要調查的特定資料庫內容中執行下列診斷查詢：
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 |
範例輸出清楚說明 = 60 所 session_id 提交的查詢如何成功取得所要求的 9 MB 記憶體授與，但只需要 7 MB 才能成功啟動查詢執行。 最後，查詢只使用了從伺服器收到的 9 MB 中的 1 MB。 輸出也會顯示工作階段 75 和 86 正在等候記憶體授與，因此為 RESOURCE_SEMAPHOREwait_type。 其等候時間已超過 1,300 秒， (21 分鐘) ，而其 granted_memory_mb 為 NULL。
此診斷查詢是一個範例，因此您可以隨意修改它，以符合您需求的任何方式。 此查詢的版本也用於 Microsoft SQL Server 支援的診斷工具中。
診斷工具
Microsoft SQL Server 技術支持人員會使用診斷工具來收集記錄檔，並更有效率地針對問題進行疑難解答。 SQL LogScout 和 Pssdiag Configuration Manager ( 與 SQLDiag) 收集先前所述 DMV 的輸出，以及可協助您診斷記憶體授與問題的 效能監視器 計數器。
如果您使用 LightPerf、GeneralPerf 或 DetailedPerf 案例執行 SQL LogScout，此工具會收集必要的記錄。 然後，您可以手動檢查 YourServer_PerfStats.out，並尋找 -- dm_exec_query_resource_semaphores -- 和 -- dm_exec_query_memory_grants -- 輸出。 或者，您可以使用 SQL Nexus 將來自 SQL LogScout 或 PSSDIAG 的輸出匯入 SQL Server 資料庫，而不是手動檢查。 SQL Nexus 會建立兩個數據表 和 tbl_dm_exec_query_resource_semaphorestbl_dm_exec_query_memory_grants，其中包含診斷記憶體授與所需的資訊。 SQL LogScout 和 PSSDIAG 也會以 的形式收集 Perfmon 記錄。BLG 檔案，可用來檢閱 效能監視器 計數器一節中所述的性能計數器。
為什麼記憶體授與對開發人員或 DBA 很重要
根據 Microsoft 支援體驗，記憶體授與問題通常會是一些最常見的記憶體相關問題。 應用程式通常會執行看似簡單的查詢，最後可能會因為大量排序或哈希作業而導致 SQL Server 效能問題。 這類查詢不僅會耗用大量的 SQL Server 記憶體，還會導致其他查詢等候記憶體變成可用，因而造成效能瓶頸。
您可以使用此處概述的工具 (DMV、Perfmon 計數器和實際查詢計劃) ，以識別哪些查詢是大型授與取用者。 然後您可以微調或重寫這些查詢，以解析或減少工作區內存使用量。
開發人員可以如何處理排序和哈希作業
一旦您識別出耗用大量查詢保留記憶體的特定查詢，您可以採取步驟，藉由重新設計這些查詢來減少記憶體授與。
造成查詢中排序和哈希作業的原因
第一個步驟是了解查詢中的哪些作業可能會導致記憶體授與。
查詢使用 SORT 運算子的原因：
ORDER BY (T-SQL) 會導致數據列在串流處理為最終結果之前先排序。
GROUP BY (T-SQL) 可能會在分組之前於查詢計劃中引入排序運算元，如果基礎索引不存在，則會排序群組數據行。
DISTINCT (T-SQL) 的行為類似
GROUP BY。 若要識別不同的數據列，會排序中繼結果，然後移除重複專案。 如果數據因為已排序的索引搜尋或掃描而尚未排序，則優化工具會使用Sort此運算元之前的運算符。當查詢優化器選取 合併聯 結運算符時，需要排序這兩個聯結的輸入。 如果其中一個數據表的聯結數據行上沒有叢集索引可用，SQL Server 可能會觸發排序。
查詢使用 HASH 查詢計劃運算子的原因：
這份清單並不詳盡，但包含哈希作業最常見的原因。 分析查詢計劃 以識別哈希比對作業。
JOIN (T-SQL) ：聯結資料表時，SQL Server 可以選擇三個實體運算子、
Nested LoopMerge Join和Hash Join。 如果 SQL Server 最後選擇哈希聯結，則需要QE記憶體，才能儲存和處理中繼結果。 一般而言，缺少良好的索引可能會導致這個資源成本最高的聯結運算符 。Hash Join若 要檢查查詢計劃 以識別Hash Match，請參閱 邏輯和實體運算符參考。DISTINCT (T-SQL) ：
Hash Aggregate運算子可用來消除數據列集中的重複專案。 若要在查詢計劃中尋找Hash Match(Aggregate) ，請參閱 邏輯和實體運算符參考。UNION (T-SQL) ：這類似於
DISTINCT。Hash Aggregate可用來移除這個運算子的重複專案。SUM/AVG/MAX/MIN (T-SQL) ：任何匯總作業都可能以
Hash Aggregate執行。 若要在查詢計劃中尋找Hash Match(Aggregate) ，請參閱 邏輯和實體運算符參考。
了解這些常見原因可協助您盡可能消除即將 SQL Server 的大型記憶體授與要求。
減少排序和哈希作業或授與大小的方法
- 將 統計數據 保持在最新狀態。 此基本步驟可改善許多層級查詢的效能，可確保查詢優化器在選取查詢計劃時擁有最精確的資訊。 SQL Server 會根據統計數據決定要要求其記憶體授與的大小。 過期的統計數據可能會導致授與要求的過度估計或過大，因而導致不必要的高授與要求或將結果分別溢出至磁碟。 請確定已在資料庫中啟用 自動更新統計數據 ，並/或使用 UPDATE STATISTICS 或 sp_updatestats保持靜態更新。
- 減少來自數據表的數據列數目。 如果您使用更嚴格的 WHERE 篩選或 JOIN 並減少數據列數目，則查詢計劃中的後續排序會排序或匯總較小的結果集。 較小的中繼結果集需要較少的工作集記憶體。 這是一般規則，開發人員不僅可以遵循此規則來儲存工作集記憶體，還可以減少 CPU 和 I/O， (此步驟不一定) 。 如果已備妥妥善撰寫且有資源效率的查詢，則已符合此指導方針。
- 在聯結數據行上建立索引，以協助合併聯結。 查詢計劃中的中繼作業會受到基礎表上的索引影響。 例如，如果數據表在聯結數據行上沒有索引，而且發現合併聯結是最符合成本效益的聯結運算符，則必須先排序該數據表中的所有數據列，才能執行聯結。 相反地，如果數據行上存在索引，則可以排除排序作業。
- 建立索引以協助避免哈希作業。 基本查詢微調通常會從檢查您的查詢是否有適當的索引來協助它們減少讀取，並在可能的情況下將大型排序或哈希作業最小化或消除。 通常會選取哈希聯結來處理大型、未排序和非索引的輸入。 建立索引可能會變更此優化工具策略，並加速數據擷取。 如需建立索引的協助，請參閱 Database Engine Tuning Advisor 和使用遺漏的索引建議微調非叢集索引。
- 針對使用的匯總查詢，請視需要使用
GROUP BYCOLUMNSTORE 索引。 處理非常大型數據列集且通常會執行「群組依據」匯總的分析查詢可能需要大型記憶體區塊，才能完成工作。 如果無法提供已排序結果的索引，則查詢計劃中會自動導入排序。 一種非常大的結果可能會導致耗費資源的記憶體授與。 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設定時，Resource Governor 實際限制的記憶體。 一旦您識別出造成大量記憶體授與的查詢，您就可以限制會話或應用程式所使用的記憶體。 值得一提的是，default工作負載群組可讓查詢擷取最多 25% 可在 SQL Server 實例上授與的記憶體。 如需詳細資訊，請參閱 Resource Governor 資源集區和 CREATE WORKLOAD GROUP。
自適性查詢處理和記憶體授與意見反應
SQL Server 2017 引進了記憶體授與意見反應功能。 它可讓查詢執行引擎根據先前的歷程記錄，調整提供給查詢的授與。 目標是盡可能減少授與的大小，或在需要更多記憶體時增加授與大小。 這項功能已以三波發行：
- SQL Server 2017 中的批次模式記憶體授與意見反應
- SQL Server 2019 中的數據列模式記憶體授與意見反應
- 使用 2022 年 SQL Server 中的 查詢存放區 和百分位數授與在磁碟上的記憶體授與意見反應
如需詳細資訊，請參閱 記憶體授與意見反應。 記憶體授與功能可能會在運行時間減少查詢的記憶體授與大小，因而減少因大型授與要求而產生的問題。 此功能已就緒，特別是在 SQL Server 2019 和更新版本上，其中有可用的數據列模式調適型處理，您甚至可能不會注意到任何來自查詢執行的記憶體問題。 不過，如果您已備妥此功能，預設 () ，但仍會看到大量的QE記憶體耗用量，請套用先前討論的步驟來重寫查詢。
增加 SQL Server 或OS記憶體
在您採取步驟來減少查詢不必要的記憶體授與之後，如果您仍然遇到相關的低記憶體問題，工作負載可能需要更多記憶體。 因此，如果系統上有足夠的物理記憶體可以這麼做，請考慮使用 max server memory 設定來增加 SQL Server的記憶體。 請遵循有關保留大約 25% 的記憶體以供作業系統和其他需求使用的建議。 如需詳細資訊，請參閱 伺服器記憶體組態選項。 如果系統上沒有足夠的記憶體可用，請考慮新增實體 RAM，或者如果它是虛擬機，請增加 VM 的專用 RAM。
記憶體授與內部
若要深入了解查詢執行記憶體的一些內部資訊，請參閱 瞭解 SQL Server 記憶體授與 部落格文章。
如何建立具有大量記憶體授與使用量的效能案例
最後，下列範例說明如何模擬大量耗用查詢執行記憶體，以及引入等候的 RESOURCE_SEMAPHORE查詢。 您可以這麼做來瞭解如何使用本文中所述的診斷工具和技術。
警告
請勿在生產系統上使用此功能。 提供此模擬可協助您瞭解概念，並協助您進一步瞭解。
在測試伺服器上，安裝 RML 公用程式和 SQL Server。
使用 SQL Server Management Studio 之類的用戶端應用程式，將 SQL Server 的最大伺服器記憶體設定降到 1,500 MB：
EXEC sp_configure 'max server memory', 1500 RECONFIGURE開啟命令提示字元，並將目錄變更為 RML 公用程式資料夾：
cd C:\Program Files\Microsoft Corporation\RMLUtils使用 ostress.exe 針對您的測試 SQL Server 產生多個同時要求。 此範例會同時使用 30 個工作階段，但您可以變更該值：
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30使用先前所述的診斷工具來識別記憶體授與問題。
處理大型記憶體授與方式的摘要
- 重寫查詢。
- 更新統計數據並定期更新統計數據。
- 為識別的查詢或查詢建立適當的索引。 索引可能會減少處理的大量數據列，因而變更
JOIN演算法並減少授與的大小，或完全消除它們。 OPTION使用 (min_grant_percent = XX，max_grant_percent= XX) 提示。- 使用 Resource Governor。
- SQL Server 2017 和 2019 使用自適性查詢處理，允許記憶體授與意見反應機制在運行時間動態調整記憶體授與大小。 此功能一開始可能會防止記憶體授與問題。
- 增加 SQL Server 或OS記憶體。
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應