疑難排解 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 不會配置比最初要求的大小更多的記憶體，而是將額外的數據列推送至磁碟，並將其當做暫存工作區使用。 如需詳細資訊，請參閱記憶體授與考慮中的 Workfiles 和 Worktable。
詞彙
讓我們檢閱您對於此記憶體取用者可能遇到的不同詞彙。 同樣地，所有這些描述與相同記憶體配置相關的概念。
查詢執行記憶體（QE 記憶體）： 這個詞彙可用來醒目提示在查詢執行期間使用排序或哈希記憶體的事實。 通常QE記憶體是查詢存回期間記憶體的最大取用者。
查詢執行 （QE） 保留或記憶體保留： 當查詢需要記憶體進行排序或哈希作業時，它會對記憶體提出保留要求。 該保留要求是根據估計基數在編譯時期計算的。 稍後，當查詢執行時，SQL Server 會根據記憶體可用性授與部分或完全要求。 最後，查詢可能會使用授與記憶體的百分比。 有一個名為 『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保留的等候。 挑選最適合您在伺服器層級查看較大圖片的畫面。 其中某些工具可能無法使用（例如，Azure SQL 資料庫 中無法使用 Perfmon）。 識別問題之後，您必須在個別查詢層級向下切入，以查看哪些查詢需要微調或重寫。
在伺服器層級，使用下列方法：
- 資源信號 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 來取得類似的資訊。 尋找 MEMORYCLERK_SQLQERESERVATIONS 記憶體 Clerk。 不過，此 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：每個資源管理員資源集區間隔 5 分鐘。
- 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 By語句中的Select。使用此事件來識別因為排序作業而執行緩慢的查詢，特別是當 = 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_kb ，且 used_memory_kb 為非NULL數值。 未取得要求的查詢正在等候執行記憶體，以及 granted_memory_kb = NULL。 此外，它們也會放在具有 a 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 識別特定查詢
SQL Server 中有一種 等候類型 ，表示查詢正在等候記憶體授與 RESOURCE_SEMAPHORE。 您可能會在 中 sys.dm_exec_requests 觀察到此等候類型，以取得個別要求。 後者 DMV 是找出哪些查詢是授與記憶體不足的受害者的最佳起點。 您也可以將等候觀察RESOURCE_SEMAPHOREsys.dm_os_wait_stats為 SQL Server 層級的匯總數據點。 當無法授與查詢記憶體要求時，此等候類型會顯示，因為其他並行查詢已用完記憶體。 大量等候要求和長時間等候時間表示使用執行記憶體或大型記憶體要求大小過多的並行查詢。
注意
記憶體授與的等候時間有限。 過度等候之後（例如超過 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'
範例輸出顯示已授與兩個要求記憶體，另有兩十個要求正在等待授與。 數據 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 （只有篩選述詞值會有所不同）。 查詢會取得前 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 也有大小限制和保留原則。 如需詳細資訊，請參閱<設定最佳 查詢存放區 擷取模式>和<管理 查詢存放區 最佳做法>查詢存放區 一節中保留最相關的數據。
識別您的資料庫是否已使用此查詢啟用 查詢存放區：
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_query_memory_grants提供更徹底的會話及其授sys.dm_exec_requests與。
注意
此查詢會針對每個會話傳回兩個數據列，因為會使用 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_SEMAPHORE wait_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_semaphores 以及 tbl_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 Loop、Merge 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（此步驟不一定可行）。 如果已就緒妥善撰寫且具有資源效率的查詢，則已符合此指導方針。
- 在聯結數據行上建立索引，以協助合併聯結。 查詢計劃中的中繼作業會受到基礎表上的索引所影響。 例如，如果數據表在聯結數據行上沒有索引，而且發現合併聯結是最具成本效益的聯結運算符，該數據表中的所有數據列都必須在執行聯結之前排序。 如果索引存在於數據行上，則可以排除排序作業。
- 建立索引以協助避免哈希作業。 一般而言，基本查詢微調會從檢查您的查詢是否有適當的索引來協助它們減少讀取，並盡可能減少或消除大型排序或哈希作業。 哈希聯結通常會選取來處理大型、未排序和非索引的輸入。 建立索引可能會變更此優化工具策略，並加速數據擷取。 如需建立索引的協助，請參閱 資料庫引擎 Tuning Advisor 和 Tune 非叢集索引，並提供遺漏索引建議。
- 適用於使用
GROUP BY的匯總查詢時，請使用 COLUMNSTORE 索引。 處理非常大型數據列集且通常會執行「分組依據」匯總的分析查詢，可能需要大量的記憶體區塊才能完成工作。 如果索引無法提供已排序的結果，則查詢計劃中會自動引入排序。 一種非常大的結果可能會導致昂貴的記憶體授與。 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。
資源管理員
QE 記憶體是資源管理員在使用MIN_MEMORY_PERCENT和MAX_MEMORY_PERCENT設定時實際限制的記憶體。 識別導致大型記憶體授與的查詢之後，您可以限制會話或應用程式所使用的記憶體。 值得一提的是， default 工作負載群組可讓查詢佔用最多 25% 的記憶體，才能在 SQL Server 實例上授與。 如需詳細資訊，請參閱 資源管理員資源集 區和 CREATE WORKLOAD GROUP。
調適型查詢處理和記憶體授與意見反應
SQL Server 2017 引進了記憶體授與意見反應功能。 它可讓查詢執行引擎根據先前的歷程記錄調整提供給查詢的授與。 目標是盡可能減少授與的大小，或在需要更多記憶體時增加授與大小。 這項功能已在三波中發行：
- SQL Server 2017 中的批次模式記憶體授與意見反應
- SQL Server 2019 中的數據列模式記憶體授與意見反應
- 在 SQL Server 2022 中使用 查詢存放區 和百分位數授與，在磁碟上授與意見反應
如需詳細資訊，請參閱記憶體授與意見反應。 記憶體授與功能可能會降低運行時間查詢的記憶體授與大小，因而減少來自大型授與要求的問題。 這項功能已就緒，特別是在 SQL Server 2019 和更新版本上，其中數據列模式調適型處理可供使用，您甚至可能不會注意到來自查詢執行的任何記憶體問題。 不過，如果您已就緒這項功能（預設為開啟），但仍會看到大型QE記憶體耗用量，請套用先前討論的步驟來重寫查詢。
增加 SQL Server 或 OS 記憶體
在您採取步驟來減少查詢不必要的記憶體授與之後，如果您仍然遇到相關的記憶體不足問題，工作負載可能需要更多記憶體。 因此，如果系統上有足夠的物理記憶體可執行此動作，請考慮使用 max server memory 設定來增加 SQL Server 的記憶體。 請遵循有關保留約 25% 記憶體給 OS 和其他需求的建議。 如需詳細資訊，請參閱 伺服器記憶體組態選項。 如果系統上沒有足夠的記憶體可用，請考慮新增實體 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） 提示。- 使用 資源管理員。
- SQL Server 2017 和 2019 會使用調適型查詢處理，讓記憶體授與意見反應機制在運行時間動態調整記憶體授與大小。 此功能可能會防止第一次發生記憶體授與問題。
- 增加 SQL Server 或 OS 記憶體。