Sql-Server
資源池中的系統記憶體不足，無法執行此查詢
在過去的一年中，我們遇到了許多不同的查詢同時失敗並出現以下錯誤（或不同資源管理器組中的變化）的情況：資源池“預設”中的系統記憶體不足，無法執行它詢問。
最近，我們越來越頻繁地遇到它。關於導致問題的原因以及如何解決的任何想法？
@@version 返回：
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) 2015 年 10 月 20 日 15:36:27 版權所有 (c) Microsoft Corporation Enterprise Edition：Windows NT 6.1（內部版本 7601）上基於核心的許可（64 位） : 服務包 1)
全部同時發生的範例錯誤：
Error: 701, Severity: 17, State: 54. There is insufficient system memory in resource pool 'default' to run this query. Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool 'dm' to run this query. Error: 701, Severity: 17, State: 89. There is insufficient system memory in resource pool 'default' to run this query. Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool 'default' to run this query. Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool 'default' to run this query. Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool 'dm' to run this query. Error: 701, Severity: 17, State: 123.日誌中的記憶體值：
Buffer Pool Value ---------------------------------------- ---------- Database 8489253 Simulated 1367796 Target 9508783 Dirty 868368 In IO 1744 Latched 6720 Page Life Expectancy 12 Procedure Cache Value ---------------------------------------- ---------- TotalProcs 435 TotalPages 22156 InUsePages 9414 Global Memory Objects Pages ---------------------------------------- ---------- Resource 5696 Locks 497346 XDES 3161 DirtyPageTracking 32 SETLS 32 SubpDesc Allocators 68 SE SchemaManager 2481 SE Column Metadata Cache 6414 SE Column Metadata Cache Store 6 SQLCache 442 Replication 2 ServerGlobal 72 XP Global 2 SortTables 3 Query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 492067 Current Max 492067 Future Max 492067 Physical Max 16549102 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 25898 Current Max 25898 Future Max 25898 Remote Query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 8274551 Current Max 8274551 Query Memory Objects (default) Value ---------------------------------------- ---------- Grants 22 Waiting 2 Available 0 Current Max 14706463 Future Max 13657740 Physical Max 15954870 Next Request 482 Waiting For 723 Cost 6 Timeout 157 Wait Time 484 Small Query Memory Objects (default) Value ---------------------------------------- ---------- Grants 3 Waiting 0 Available 122428 Current Max 122880 Future Max 122880 Remote Query Memory Objects (default) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 7977435 Current Max 7977435 Query Memory Objects (fm) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 49206 Current Max 49206 Future Max 49206 Physical Max 1654911 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (fm) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 2589 Current Max 2589 Future Max 2589 Remote Query Memory Objects (fm) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 827455 Current Max 827455 Query Memory Objects (dm) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 477304 Current Max 477304 Future Max 477304 Physical Max 16052629 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (dm) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 25121 Current Max 25121 Future Max 25121 Remote Query Memory Objects (dm) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 8026314 Current Max 8026314 Query Memory Objects (J) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 477304 Current Max 477304 Future Max 477304 Physical Max 16052629 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (J) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 25121 Current Max 25121 Future Max 25121 Remote Query Memory Objects (J) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 8026314 Current Max 8026314 Query Memory Objects (c) Value ---------------------------------------- ---------- Grants 2 Waiting 0 Available 0 Current Max 303105 Future Max 269810 Physical Max 1643300 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (c) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 14200 Current Max 14200 Future Max 14200 Remote Query Memory Objects (c) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 821650 Current Max 821650 Query Memory Objects (d8) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 472388 Current Max 472388 Future Max 472388 Physical Max 15887138 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (d8) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 24862 Current Max 24862 Future Max 24862 Remote Query Memory Objects (d8) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 7943569 Current Max 7943569 Query Memory Objects (d4) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 472388 Current Max 472388 Future Max 472388 Physical Max 15887138 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (d4) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 24862 Current Max 24862 Future Max 24862 Remote Query Memory Objects (d4) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 7943569 Current Max 7943569 Query Memory Objects (b) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 49206 Current Max 49206 Future Max 49206 Physical Max 1654911 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (b) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 2589 Current Max 2589 Future Max 2589 Remote Query Memory Objects (b) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 827455 Current Max 827455 Optimization Queue (internal) Value ---------------------------------------- ---------- Overall Memory 144834560000 Target Memory 4526080000 Last Notification 0 Timeout 6 Early Termination Factor 5 Small Gateway (internal) Value ---------------------------------------- ---------- Configured Units 128 Available Units 128 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (internal) Value ---------------------------------------- ---------- Configured Units 32 Available Units 32 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 Big Gateway (internal) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Optimization Queue (default) Value ---------------------------------------- ---------- Overall Memory 140489523200 Target Memory 4390297600 Last Notification 0 Timeout 6 Early Termination Factor 5 Small Gateway (default) Value ---------------------------------------- ---------- Configured Units 128 Available Units 124 Acquires 4 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (default) Value ---------------------------------------- ---------- Configured Units 32 Available Units 32 Acquires 0 Waiters 0 Threshold Factor 12 Threshold 91464533 Big Gateway (default) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Optimization Queue (fm) Value ---------------------------------------- ---------- Overall Memory 14483456000 Target Memory 452608000 Last Notification 0 Timeout 6 Early Termination Factor 5 Small Gateway (fm) Value ---------------------------------------- ---------- Configured Units 128 Available Units 128 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (fm) Value ---------------------------------------- ---------- Configured Units 32 Available Units 32 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 Big Gateway (fm) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Optimization Queue (dm) Value ---------------------------------------- ---------- Overall Memory 140489523200 Target Memory 4390297600 Last Notification 0 Timeout 6 Early Termination Factor 5 Small Gateway (dm) Value ---------------------------------------- ---------- Configured Units 128 Available Units 128 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (dm) Value ---------------------------------------- ---------- Configured Units 32 Available Units 32 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 Big Gateway (dm) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Optimization Queue (J) Value ---------------------------------------- ---------- Overall Memory 140489523200 Target Memory 4390297600 Last Notification 0 Timeout 6 Early Termination Factor 5 Small Gateway (J) Value ---------------------------------------- ---------- Configured Units 128 Available Units 128 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (J) Value ---------------------------------------- ---------- Configured Units 32 Available Units 32 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1由於字元限制，此處為全文：http: //pastebin.com/WCAtRBdP
從您發布的輸出中我可以看到
MEMORYCLERK_SQLQERESERVATIONS 39874 (MB)價值是38G，非常大。為什麼 SQL Server 操作（排序和雜湊）需要這麼多記憶體
什麼是 SQLRESERVATIONS
在查詢執行期間為排序和散列操作分配的 SQL Server 記憶體。你真的認為 38 G 是排序和散列操作所需要的嗎？這就是問題。
查看您的資源管理器配置
CREATE WORKLOAD GROUP [fm] WITH(GROUP_MAX_REQUESTS=0, IMPORTANCE=HIGH, REQUEST_MAX_CPU_TIME_SEC=0, REQUEST_MAX_MEMORY_GRANT_PERCENT=100, REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0, MAX_DOP=8) USING [fm] GO看看REQUEST_MAX_MEMORY_GRANT_PERCENT=100現在這對我來說似乎是一個非常錯誤的配置。根據BOL 文件
REQUEST_MAX_MEMORY_GRANT_PERCENT = value 指定單個請求可以從池中獲取的最大記憶體量。此百分比與 MAX_MEMORY_PERCENT 指定的資源池大小有關
因此，這意味著當查詢在此工作負載上執行時，他們可以通過迫使其他人餓死記憶體來請求幾乎
ALL記憶體作為記憶體授權以在那裡執行。這肯定會導致OOM錯誤。此外，微軟所說的是
我們不建議將值設置為大於 70，因為如果其他並發查詢正在執行，伺服器可能無法留出足夠的空閒記憶體。這可能最終導致查詢超時錯誤 8645
你也可以分享的輸出
select total_request_count,blocked_task_count,max_request_grant_memory_kb,requested from sys.dm_resource_governor_workload_groups SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan FROM sys.dm_exec_query_memory_grants AS mg CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp ORDER BY 1 DESC OPTION (MAXDOP 1) USE master GO ;WITH cte AS ( SELECT RP.pool_id , RP.Name , RP.min_memory_percent , RP.max_memory_percent , CAST (RP.max_memory_kb / 1024. / 1024. AS NUMERIC(12, 2)) AS max_memory_gb , CAST (RP.used_memory_kb / 1024. / 1024. AS NUMERIC(12, 2)) AS used_memory_gb , CAST (RP.target_memory_kb / 1024. / 1024. AS NUMERIC(12,2)) AS target_memory_gb, CAST (SI.committed_target_kb / 1024. / 1024. AS NUMERIC(12, 2)) AS committed_target_kb FROM sys.dm_resource_governor_resource_pools RP CROSS JOIN sys.dm_os_sys_info SI ) SELECT c.pool_id , c.Name , c.min_memory_percent , c.max_memory_percent , c.max_memory_gb , c.used_memory_gb , c.target_memory_gb , CAST(c.committed_target_kb * CASE WHEN c.committed_target_kb <= 8 THEN 0.7 WHEN c.committed_target_kb < 16 THEN 0.75 WHEN c.committed_target_kb < 32 THEN 0.8 WHEN c.committed_target_kb <= 96 THEN 0.85 WHEN c.committed_target_kb > 96 THEN 0.9 END * c.max_memory_percent /100 AS NUMERIC(12,2)) AS [Max_for_InMemory_Objects_gb] FROM cte c編輯：
從您發布的輸出
total_request_count blocked_task_count max_request_grant_memory_kb -------------------- ------------------ --------------------------- 553 0 18000 1633564 0 19344744 0 0 0 89509 0 23448232 2073 0 8032 24999 0 4485384 861807 0 3149248 236419 0 32241240 293 0 32241240 1735195 0 1889544現在您可以看到您的工作組處理瞭如此多的請求，並看到他們請求的記憶體量從 19G 到 32G 不等。這可能是一個問題，請求大量記憶體授予的查詢可能會剝奪其他人的記憶體並註意記憶體工作組不會被共享。
你還可以看到
granted_memory_kb session_id -------------------- ---------- 19344744 210會話 ID 210 被授予 19G 記憶體。這是大錯特錯。如果 5-10 個這樣的查詢開始執行，只需考慮一個場景。
您還可以添加輸出嗎
select pool_id,cache_memory_kb,used_memory_kb,out_of_memory_count,used_memgrant_kb from sys.dm_resource_governor_resource_pools
我們正在使用 2012 - 列儲存索引的第一個實現。進一步的分析表明，當創建列儲存索引並且伺服器處於記憶體壓力下時，總是會發生崩潰。我們擺脫了這些列儲存索引，並且不再遇到錯誤。