There is insufficient system memory in resource pool to run this query

Question

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

What are your memory setting (min, max, server RAM, used by sql server, used by Windows, ...) — Julien Vavasseur, Commented Feb 3, 2016 at 10:43
Can you post the output of select type, sum(multi_pages_kb) from sys.dm_os_memory_clerks where multi_pages_kb != 0 group by type — Tom V, Commented Feb 3, 2016 at 10:45
Msg 207, Level 16, State 1, Line 1 Invalid column name 'multi_pages_kb'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'multi_pages_kb'. — James Lester, Commented Feb 3, 2016 at 10:57
memory setting (MIN 0 , max 170000, server RAM 224, used by sql SERVER 11468984KB (AWE - 166519756 KB), used by Windows 229341MB) — James Lester, Commented Feb 3, 2016 at 10:59
Can you add the output of queries, in your question, which I have posted in This Link. Also please let us know how resource governor is configured to me it seems more of resource governor issue. — Shanky, Commented Feb 3, 2016 at 11:03

Shanky · Accepted Answer · 2016-02-05 05:02:18Z

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

The value is 38G which is huge. Why is SQL Server operations(sor tand hash) requesting so much memory - Lots of overnight processing happening on large tables Do you really think 38 G is what the sort and hash operations required? - given the size of the datasets that we have use, this is not a surprising value — James Lester, Commented Feb 4, 2016 at 14:55
Just have a look at REQUEST_MAX_MEMORY_GRANT_PERCENT=100 now this seems a very wrong configuration to me. - This is a very specific group that is only utilized maybe once a month or less and all other jobs are stopped when this group is in use. — James Lester, Commented Feb 4, 2016 at 14:55
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. - this is the intention, it should use 100% of the memory as no other code will run when this is being used. Would it cause an OOM error? I would have expected an resource semaphore wait if something else were to run — James Lester, Commented Feb 4, 2016 at 14:56
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 - wasnt aware of this, but we havent received any 8645 time out error, probably because we dont allow other code to run when this group is in use Can you also share the output of xxx - pastebin.com/b4MEXYa8 or dropbox.com/s/7jctho1db8h1u36/shanky1.txt?dl=0 — James Lester, Commented Feb 4, 2016 at 14:56
Its helped in that I have focused on resource governor as the issue. After discussion we are considering a phased reconfiguration starting with removing the minimum memory percentage value. We havent had a reoccurance however until we go 2 weeks ago without a reoccurrence I wont be starting to feel happy. — James Lester, Commented Feb 9, 2016 at 12:51

James Lester · Accepted Answer · 2016-12-13 16:24:07Z

2

we are using 2012 - the first implementation of columnstore indexes. Further analysis showed that the crashes always happened when a columnstore index was being created and the server was under memory pressure. We got rid of these columnstore indexes and we are no longer experiencing the errors.

answered Dec 13, 2016 at 16:24

James Lester

1891 gold badge1 silver badge6 bronze badges

Add a comment |

Stack Exchange Network

There is insufficient system memory in resource pool to run this query

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2012
memory
resource-governor
or ask your own question.

Linked

Hot Network Questions

There is insufficient system memory in resource pool to run this query

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2012memoryresource-governor or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2012
memory
resource-governor
or ask your own question.