Sql-server – There is insufficient system memory in resource pool to run this query

memoryresource-governorsql serversql-server-2012

Over the past year we have been suffering a situation where many different queries at the same time will fail with the following error (or a variation in a different resource Governor group): There is insufficient system memory in resource pool 'default' to run this query.

Recently we have encountered it with more and more frequency. Any ideas on what is causing the issue and how to resolve?

@@version returns:

Microsoft SQL Server 2012 (SP3) (KB3072779) – 11.0.6020.0 (X64)
Oct 20 2015 15:36:27
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Sample errors which all occur at the exact same time:

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.

Memory values from the log:

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

Full text here due to character limit: http://pastebin.com/WCAtRBdP

Best Answer

From the output you posted I can see

MEMORYCLERK_SQLQERESERVATIONS       39874 (MB)

The value is 38G which is huge. Why is SQL Server operations(sortand hash) requesting so much memory

What is SQLRESERVATIONS

SQL Server memory allocated during query execution for Sort and Hash operations. Do you really think 38 G is what the sort and hash operations require. This is the problem.

Looking at your resource governor configuration

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

Just have a look at REQUEST_MAX_MEMORY_GRANT_PERCENT=100 now this seems a very wrong configuration to me. According to BOL document

REQUEST_MAX_MEMORY_GRANT_PERCENT = value Specifies the maximum amount of memory that a single request can take from the pool. This percentage is relative to the resource pool size specified by MAX_MEMORY_PERCENT

So this means when queries run on this workload they can request almost ALL memory as memory grant for the execution there by forcing others to starve for memory. And this is may definitely cause OOM error.

Further more what Microsoft says is

We do not recommend setting value greater than 70 because the server may be unable to set aside enough free memory if other concurrent queries are running. This may eventually lead to query time-out error 8645

Can you also share the output of 

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

EDIT:

From the output you posted

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

Now you can see there are so many requests which your workgroup handles and see the amount of memory they requested all ranges from 19G to 32 G. This can be a problem the query requesting huge memory grants can deprive others from memory and also note the memory for workgroup wont be shared.

You can also see 

granted_memory_kb    session_id
-------------------- ----------
19344744             210

Session ID 210 was granted 19G of memory. This is blunder. just consider a scenario if 5-10 such queries start running.

Can you also add output of

select pool_id,cache_memory_kb,used_memory_kb,out_of_memory_count,used_memgrant_kb from sys.dm_resource_governor_resource_pools
Related Question