February 3, 2016 at 3:21 am
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: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:
full text here due to character limit: http://pastebin.com/WCAtRBdP
Buffer Pool Value
---------------------------------------- ----------
Database8489253
Simulated 1367796
Target 9508783
Dirty868368
In IO 1744
Latched6720
Page Life Expectancy 12
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 435
TotalPages22156
InUsePages 9414
Global Memory Objects Pages
---------------------------------------- ----------
Resource 5696
Locks497346
XDES 3161
DirtyPageTracking32
SETLS32
SubpDesc Allocators 68
SE SchemaManager 2481
SE Column Metadata Cache 6414
SE Column Metadata Cache Store6
SQLCache442
Replication 2
ServerGlobal 72
XP Global 2
SortTables3
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available492067
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
---------------------------------------- ----------
Grants0
Waiting 0
Available 25898
Current Max 25898
Future Max25898
Remote Query Memory Objects (internal)Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 8274551
Current Max 8274551
Query Memory Objects (default)Value
---------------------------------------- ----------
Grants 22
Waiting 2
Available 0
Current Max14706463
Future Max 13657740
Physical Max 15954870
Next Request482
Waiting For 723
Cost 6
Timeout 157
Wait Time 484
Small Query Memory Objects (default) Value
---------------------------------------- ----------
Grants3
Waiting 0
Available122428
Current Max 122880
Future Max 122880
Remote Query Memory Objects (default) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 7977435
Current Max 7977435
Query Memory Objects (fm) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 49206
Current Max 49206
Future Max49206
Physical Max1654911
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Small Query Memory Objects (fm) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 2589
Current Max2589
Future Max 2589
Remote Query Memory Objects (fm) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available827455
Current Max 827455
Query Memory Objects (dm)Value
---------------------------------------- ----------
Grants0
Waiting 0
Available477304
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
---------------------------------------- ----------
Grants0
Waiting 0
Available 25121
Current Max 25121
Future Max25121
Remote Query Memory Objects (dm) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 8026314
Current Max 8026314
Query Memory Objects (J) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available477304
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
---------------------------------------- ----------
Grants0
Waiting 0
Available 25121
Current Max 25121
Future Max25121
Remote Query Memory Objects (J) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 8026314
Current Max 8026314
Query Memory Objects (c) Value
---------------------------------------- ----------
Grants2
Waiting 0
Available 0
Current Max 303105
Future Max 269810
Physical Max1643300
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Small Query Memory Objects (c) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 14200
Current Max 14200
Future Max14200
Remote Query Memory Objects (c) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available821650
Current Max 821650
Query Memory Objects (d8) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available472388
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
---------------------------------------- ----------
Grants0
Waiting 0
Available 24862
Current Max 24862
Future Max24862
Remote Query Memory Objects (d8)Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 7943569
Current Max 7943569
Query Memory Objects (d4) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available472388
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
---------------------------------------- ----------
Grants0
Waiting 0
Available 24862
Current Max 24862
Future Max24862
Remote Query Memory Objects (d4)Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 7943569
Current Max 7943569
Query Memory Objects (b)Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 49206
Current Max 49206
Future Max49206
Physical Max1654911
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Small Query Memory Objects (b) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available 2589
Current Max2589
Future Max 2589
Remote Query Memory Objects (b) Value
---------------------------------------- ----------
Grants0
Waiting 0
Available827455
Current Max 827455
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 144834560000
Target Memory4526080000
Last Notification 0
Timeout 6
Early Termination Factor 5
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold380000
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 Memory4390297600
Last Notification 0
Timeout 6
Early Termination Factor 5
Small Gateway (default) Value
---------------------------------------- ----------
Configured Units128
Available Units 124
Acquires 4
Waiters 0
Threshold Factor 380000
Threshold380000
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 Units128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold380000
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 Memory4390297600
Last Notification 0
Timeout 6
Early Termination Factor 5
Small Gateway (dm) Value
---------------------------------------- ----------
Configured Units128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold380000
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 Memory4390297600
Last Notification 0
Timeout 6
Early Termination Factor 5
Small Gateway (J) Value
---------------------------------------- ----------
Configured Units128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold380000
Medium Gateway (J)Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 12
Threshold-1
Big Gateway (J) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold-1
Optimization Queue (c) Value
---------------------------------------- ----------
Overall Memory 14483456000
Target Memory 452608000
Last Notification 0
Timeout 6
Early Termination Factor 5
Small Gateway (c)Value
---------------------------------------- ----------
Configured Units128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold380000
Medium Gateway (c) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 12
Threshold-1
Big Gateway (c) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold-1
Optimization Queue (d8) Value
---------------------------------------- ----------
Overall Memory 139041177600
Target Memory4345036800
Last Notification 0
Timeout 6
Early Termination Factor 5
Small Gateway (d8) Value
---------------------------------------- ----------
Configured Units128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold380000
Medium Gateway (d8) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 12
Threshold-1
Big Gateway (d8)Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold-1
Optimization Queue (d4) Value
---------------------------------------- ----------
Overall Memory 139041177600
Target Memory4345036800
Last Notification 0
Timeout 6
Early Termination Factor 5
Small Gateway (d4) Value
---------------------------------------- ----------
Configured Units128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold380000
Medium Gateway (d4) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 12
Threshold-1
Big Gateway (d4)Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold-1
Optimization Queue (b) Value
---------------------------------------- ----------
Overall Memory 14483456000
Target Memory 452608000
Last Notification 0
Timeout 6
Early Termination Factor 5
Small Gateway (b) Value
---------------------------------------- ----------
Configured Units128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold380000
Medium Gateway (b) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 12
Threshold-1
Big Gateway (b) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold-1
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 15010020
Reserved Limit 19084520
Memory Pool (internal)Pages
---------------------------------------- ----------
Allocations 2085790
Predicted 2085885
Private Target0
Private Limit 0
Total Target 21760000
Total Limit21760000
OOM Count 0
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 23082
Rate-84
Target Allocations 552500
Future Allocations0
Overall17680000
Last Notification 0
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 26087
Rate 95
Target Allocations 552500
Future Allocations0
Overall17680000
Last Notification 0
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 552500
Future Allocations0
Overall17680000
Last Notification 0
MEMORYBROKER_FOR_COMMITTED (internal) Pages
---------------------------------------- ----------
Allocations 2036619
Rate -2
Target Allocations 2035570
Future Allocations0
Overall17680000
Last Notification 2
MEMORYBROKER_FOR_XTP (internal) Pages
---------------------------------------- ----------
Allocations 2
Rate 0
Target Allocations 552500
Future Allocations0
Overall17680000
Last Notification 0
Memory Pool (default) Pages
---------------------------------------- ----------
Allocations15240311
Predicted 15243396
Private Target 217600
Private Limit217600
Total Target 21107200
Total Limit21107200
OOM Count 0
MEMORYBROKER_FOR_CACHE (default) Pages
---------------------------------------- ----------
Allocations 500697
Rate 3085
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
MEMORYBROKER_FOR_STEAL (default) Pages
---------------------------------------- ----------
Allocations 32699
Rate -1297
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
MEMORYBROKER_FOR_RESERVE (default)Pages
---------------------------------------- ----------
Allocations14706915
Rate-330935
Target Allocations 14699342
Future Allocations 15818502
Overall17149600
Last Notification 2
MEMORYBROKER_FOR_XTP (default)Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
Memory Pool (fm)Pages
---------------------------------------- ----------
Allocations 0
Predicted 0
Private Target0
Private Limit 0
Total Target2176000
Total Limit 2176000
OOM Count 0
MEMORYBROKER_FOR_CACHE (fm) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
MEMORYBROKER_FOR_STEAL (fm) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
MEMORYBROKER_FOR_RESERVE (fm) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
MEMORYBROKER_FOR_XTP (fm) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
Memory Pool (dm) Pages
---------------------------------------- ----------
Allocations4710
Predicted 4710
Private Target 217600
Private Limit217600
Total Target 21107200
Total Limit21107200
OOM Count 0
MEMORYBROKER_FOR_CACHE (dm) Pages
---------------------------------------- ----------
Allocations4293
Rate 0
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
MEMORYBROKER_FOR_STEAL (dm) Pages
---------------------------------------- ----------
Allocations 417
Rate -6
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
MEMORYBROKER_FOR_RESERVE (dm) Pages
---------------------------------------- ----------
Allocations 0
Rate-399771
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
MEMORYBROKER_FOR_XTP (dm)Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
Memory Pool (J) Pages
---------------------------------------- ----------
Allocations1038
Predicted 1038
Private Target 217600
Private Limit217600
Total Target 21107200
Total Limit21107200
OOM Count 0
MEMORYBROKER_FOR_CACHE (J)Pages
---------------------------------------- ----------
Allocations 706
Rate 0
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
MEMORYBROKER_FOR_STEAL (J)Pages
---------------------------------------- ----------
Allocations 332
Rate 0
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
MEMORYBROKER_FOR_RESERVE (J) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
MEMORYBROKER_FOR_XTP (J) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 535925
Future Allocations0
Overall17149600
Last Notification 0
Memory Pool (c) Pages
---------------------------------------- ----------
Allocations 313594
Predicted314022
Private Target 217600
Private Limit217600
Total Target2176000
Total Limit 2176000
OOM Count 0
MEMORYBROKER_FOR_CACHE (c) Pages
---------------------------------------- ----------
Allocations2864
Rate -1
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
MEMORYBROKER_FOR_STEAL (c) Pages
---------------------------------------- ----------
Allocations7625
Rate428
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
MEMORYBROKER_FOR_RESERVE (c) Pages
---------------------------------------- ----------
Allocations 303105
Rate -843
Target Allocations 302948
Future Allocations0
Overall 1768000
Last Notification 2
MEMORYBROKER_FOR_XTP (c) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
Memory Pool (d8)Pages
---------------------------------------- ----------
Allocations 33171
Predicted 33171
Private Target0
Private Limit 0
Total Target 20889600
Total Limit20889600
OOM Count 0
MEMORYBROKER_FOR_CACHE (d8) Pages
---------------------------------------- ----------
Allocations 24407
Rate 0
Target Allocations 530400
Future Allocations0
Overall16972800
Last Notification 0
MEMORYBROKER_FOR_STEAL (d8) Pages
---------------------------------------- ----------
Allocations8764
Rate 0
Target Allocations 530400
Future Allocations0
Overall16972800
Last Notification 0
MEMORYBROKER_FOR_RESERVE (d8) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 530400
Future Allocations0
Overall16972800
Last Notification 0
MEMORYBROKER_FOR_XTP (d8) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 530400
Future Allocations0
Overall16972800
Last Notification 0
Memory Pool (d4)Pages
---------------------------------------- ----------
Allocations2578
Predicted 2578
Private Target0
Private Limit 0
Total Target 20889600
Total Limit20889600
OOM Count 0
MEMORYBROKER_FOR_CACHE (d4) Pages
---------------------------------------- ----------
Allocations1990
Rate 0
Target Allocations 530400
Future Allocations0
Overall16972800
Last Notification 0
MEMORYBROKER_FOR_STEAL (d4) Pages
---------------------------------------- ----------
Allocations 588
Rate 0
Target Allocations 530400
Future Allocations0
Overall16972800
Last Notification 0
MEMORYBROKER_FOR_RESERVE (d4) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 530400
Future Allocations0
Overall16972800
Last Notification 0
MEMORYBROKER_FOR_XTP (d4) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 530400
Future Allocations0
Overall16972800
Last Notification 0
Memory Pool (b) Pages
---------------------------------------- ----------
Allocations4308
Predicted 4308
Private Target0
Private Limit 0
Total Target2176000
Total Limit 2176000
OOM Count 0
MEMORYBROKER_FOR_CACHE (b) Pages
---------------------------------------- ----------
Allocations3077
Rate 0
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
MEMORYBROKER_FOR_STEAL (b) Pages
---------------------------------------- ----------
Allocations1231
Rate 0
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
MEMORYBROKER_FOR_RESERVE (b)Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
MEMORYBROKER_FOR_XTP (b)Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations55250
Future Allocations0
Overall 1768000
Last Notification 0
Memory Broker Clerk (Column store object pool) Pages
---------------------------------------- ----------
Total 37329
Simulated 4384
Simulation Benefit0
Internal Benefit 0
External Benefit 0.0000003365
Value Of Memory 0.0000010280
Periodic Freed 15
Internal Freed161497121
Memory Broker Clerk (Buffer Pool) Pages
---------------------------------------- ----------
Total 8490108
Simulated 1367779
Simulation Benefit 0.0000010280
Internal Benefit 0
External Benefit 0.0000003365
Value Of Memory 0.0000003365
Periodic Freed0
Internal Freed0
February 4, 2016 at 3:57 am
how much memory does the server have?
how much granted to sql server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 4, 2016 at 7:57 am
server: 224GB, sql server: 170GB
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply