January 10, 2018 at 5:46 pm
After adding 16GB of memory to one of our VMWare virtual servers late last year I increased the Max Server Memory setting by the same amount and found that around 2 hours later the server became totally unresponsive to the point where I could no longer RDP to it or connect using SSMS. In the end the server had to be force rebooted and once it was back up I reduced the Max Mem setting by 7GB and it has been stable ever since.
As there is generally 10GB free RAM on the server (out of a total of 48GB) I increased Max Mem by 4GB last night and the same issue happened again a couple of hours later. This time, although most SQL commands I tried just complained of no buffer pool resources, I was able to recover it by running DBCC FREESYSTEMCACHE('ALL') in an existing connection then quickly change the Max Mem setting back. I have been capturing various metrics every minute on this server and the problem seemed to occur shortly after the memory utilisation reaches the new Max Mem setting even though there was still 6GB RAM free on the server.
I actually had the same issue on this server before the RAM upgrade when I increased Max Mem by just 500MB and have seen it on a different server running SQL 2012 Enterprise.
This server runs SQL 2016 Enterprise (with CU3 for SP1) and the first memory dump from the log from last night is below. Has anyone else experienced this or can suggest why this might be happening as I would quite like to make use of the supposedly spare RAM?
2018-01-10 23:41:10.55 spid204
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 6595866624
Available Virtual Memory 140601412993024
Available Paging File 57299546112
Working Set 584704000
Percent of Committed Memory in WS 100
Page Faults 213379333
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
2018-01-10 23:41:10.55 spid204
Memory Manager KB
---------------------------------------- ----------
VM Reserved 132206156
VM Committed 727328
Locked Pages Allocated 38569352
Large Pages Allocated 233472
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 39936000
Current Committed 39296680
Pages Allocated 37380048
Pages Reserved 3744
Pages Free 34664
Pages In Use 8072880
Page Alloc Potential 29866320
NUMA Growth Phase 0
Last OOM Factor 2
Last OS Error 0
2018-01-10 23:41:10.55 spid204
Memory node Id = 0 KB
---------------------------------------- ----------
VM Reserved 132206092
VM Committed 727308
Locked Pages Allocated 38569352
Pages Allocated 37380048
Pages Free 34664
Target Committed 39935976
Current Committed 39296664
Foreign Committed 0
Away Committed 0
Taken Away Committed 0
2018-01-10 23:41:10.55 spid204
Memory node Id = 64 KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 14832
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 39012864
VM Committed 65536
Locked Pages Allocated 809704
SM Reserved 0
SM Committed 0
Pages Allocated 31193560
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 6416
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3096
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_QUERYDISKSTORE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 11128
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_QUERYDISKSTORE_HASHMAP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 127624
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 416
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- ----------
VM Reserved 234944
VM Committed 0
Locked Pages Allocated 234944
SM Reserved 0
SM Committed 0
Pages Allocated 109936
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 12200
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- ----------
VM Reserved 6338924
VM Committed 201240
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 23384
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLCLRASSEMBLY (node 0) KB
---------------------------------------- ----------
VM Reserved 5144
VM Committed 5144
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1320
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 952
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SNI (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SNI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 968
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 304
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 632
2018-01-10 23:41:10.55 spid204
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3744
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 13056
VM Committed 13056
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_SQLTRACE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16536
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_XTP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2160
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_CSILOBCOMPRESSION (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1216
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 150536
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_SOSNODE (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2976
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 153512
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_SOSOS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 224
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_SOSMEMMANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 383080
VM Committed 273000
Locked Pages Allocated 109992
SM Reserved 0
SM Committed 0
Pages Allocated 0
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 96
SM Committed 96
Pages Allocated 0
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 200
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_FSAGENT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_FILETABLE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 142200
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_SPATIAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 96
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_SQLLOGPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 10464
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_LWC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2018-01-10 23:41:10.56 spid204
MEMORYCLERK_FSCHUNKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48
2018-01-10 23:41:10.56 spid204
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 485832
2018-01-10 23:41:10.56 spid204
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3963072
2018-01-10 23:41:10.56 spid204
CACHESTORE_PHDR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 232848
2018-01-10 23:41:10.56 spid204
CACHESTORE_XPROC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
2018-01-10 23:41:10.56 spid204
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 744
2018-01-10 23:41:10.56 spid204
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2018-01-10 23:41:10.56 spid204
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2018-01-10 23:41:10.56 spid204
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.56 spid204
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.56 spid204
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.56 spid204
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.56 spid204
CACHESTORE_STACKFRAMES (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.56 spid204
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2018-01-10 23:41:10.56 spid204
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 120
2018-01-10 23:41:10.56 spid204
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.57 spid204
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.57 spid204
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.57 spid204
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.57 spid204
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48
2018-01-10 23:41:10.57 spid204
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2018-01-10 23:41:10.57 spid204
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2018-01-10 23:41:10.57 spid204
CACHESTORE_CLRPROC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 312
2018-01-10 23:41:10.57 spid204
CACHESTORE_CLRUDTINFO (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 72
2018-01-10 23:41:10.57 spid204
CACHESTORE_SEHOBTCOLUMNATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4616
2018-01-10 23:41:10.57 spid204
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 19856
2018-01-10 23:41:10.57 spid204
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48
2018-01-10 23:41:10.57 spid204
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 88
2018-01-10 23:41:10.57 spid204
CACHESTORE_SEARCHPROPERTYLIST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2018-01-10 23:41:10.57 spid204
CACHESTORE_COLUMNSTOREOBJECTPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2018-01-10 23:41:10.57 spid204
CACHESTORE_QDSCONTEXTSETTINGS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 576
2018-01-10 23:41:10.57 spid204
CACHESTORE_QDSRUNTIMESTATS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3984
2018-01-10 23:41:10.57 spid204
CACHESTORE_XML_SELECTIVE_DG (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.57 spid204
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 188816
2018-01-10 23:41:10.57 spid204
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 91064
2018-01-10 23:41:10.57 spid204
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 242288
2018-01-10 23:41:10.57 spid204
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 21032
2018-01-10 23:41:10.57 spid204
USERSTORE_SXC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3232
2018-01-10 23:41:10.57 spid204
USERSTORE_SXC (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2018-01-10 23:41:10.57 spid204
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3240
2018-01-10 23:41:10.57 spid204
USERSTORE_QDSSTMT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 736
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 216
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 14952
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_SNI_PACKET (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 15008
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48640
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 131072
VM Committed 131072
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 207848
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_LOCK_MANAGER (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 131072
VM Committed 131072
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 207872
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2018-01-10 23:41:10.57 spid204
OBJECTSTORE_XACT_CACHE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 23264
2018-01-10 23:41:10.67 spid204
Buffer Pool Value
---------------------------------------- ----------
Database 3899106
Simulated 539368
Target 98254848
Dirty 43258
In IO 0
Latched 1
Page Life Expectancy 20912
2018-01-10 23:41:10.69 spid204
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 74112
TotalPages 585235
InUsePages 2935
2018-01-10 23:41:10.69 spid204
Global Memory Objects Pages
---------------------------------------- ----------
Resource 867
Locks 25984
XDES 748
DirtyPageTracking 4322
SETLS 4
SubpDesc Allocators 4
SE SchemaManager 8560
SE Column Metadata Cache 15041
SE Column Metadata Cache Store 169
SQLCache 7604
Replication 359
ServerGlobal 61
XP Global 2
SortTables 3
2018-01-10 23:41:10.69 spid204
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 2569949
Current Max 2569949
Future Max 2569949
Physical Max 3685769
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2018-01-10 23:41:10.69 spid204
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 65536
Current Max 65536
Future Max 65536
2018-01-10 23:41:10.69 spid204
Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 2
Waiting 0
Available 3433331
Current Max 3433799
Future Max 3433799
Physical Max 3685769
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2018-01-10 23:41:10.69 spid204
Small Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 65536
Current Max 65536
Future Max 65536
2018-01-10 23:41:10.69 spid204
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 32779468800
Target Memory 23269064704
Last Notification 1
Timeout 6
Early Termination Factor 5
2018-01-10 23:41:10.69 spid204
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
2018-01-10 23:41:10.69 spid204
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
2018-01-10 23:41:10.69 spid204
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2018-01-10 23:41:10.69 spid204
Optimization Queue (default) Value
---------------------------------------- ----------
Overall Memory 32779468800
Target Memory 23411580928
Last Notification 1
Timeout 6
Early Termination Factor 5
2018-01-10 23:41:10.69 spid204
Small Gateway (default) Value
---------------------------------------- ----------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
2018-01-10 23:41:10.69 spid204
Medium Gateway (default) Value
---------------------------------------- ----------
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
2018-01-10 23:41:10.69 spid204
Big Gateway (default) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2018-01-10 23:41:10.69 spid204
Memory Pool Manager KB
---------------------------------------- ----------
Reserved Current 3744
Reserved Limit 29863416
2018-01-10 23:41:10.69 spid204
Memory Pool (internal) KB
---------------------------------------- ----------
Allocations 2270584
Predicted 2270592
Private Target 0
Private Limit 0
Total Target 37939200
Total Limit 37939200
OOM Count 0
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_CACHE (internal) KB
---------------------------------------- ----------
Allocations 152296
Rate 0
Target Allocations 22641824
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_STEAL (internal) KB
---------------------------------------- ----------
Allocations 234160
Rate 8
Target Allocations 22723696
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_RESERVE (internal) KB
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 22489528
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_COMMITTED (internal) KB
---------------------------------------- ----------
Allocations 1881968
Rate -16
Target Allocations 24371496
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_HASHED_DATA_PAGES (internal) KB
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 22489528
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_XTP (internal) KB
---------------------------------------- ----------
Allocations 2160
Rate 0
Target Allocations 22491688
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
Memory Pool (default) KB
---------------------------------------- ----------
Allocations 5807920
Predicted 13179080
Private Target 0
Private Limit 0
Total Target 37939200
Total Limit 37939200
OOM Count 0
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_CACHE (default) KB
---------------------------------------- ----------
Allocations 5433984
Rate 216
Target Allocations 27923728
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_STEAL (default) KB
---------------------------------------- ----------
Allocations 371216
Rate 2128
Target Allocations 22862872
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_RESERVE (default) KB
---------------------------------------- ----------
Allocations 2720
Rate 0
Target Allocations 29861064
Future Allocations 7371536
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_HASHED_DATA_PAGES (default) KB
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 22489528
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
MEMORYBROKER_FOR_XTP (default) KB
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 22489528
Future Allocations 0
Overall 32011200
Last Notification 1
2018-01-10 23:41:10.69 spid204
Memory Broker Clerk (Buffer Pool) Pages
---------------------------------------- ----------
Total 3899106
Simulated 539368
Simulation Benefit 0
Internal Benefit 0
External Benefit 0
Value Of Memory 0
Periodic Freed 0
Internal Freed 0
January 10, 2018 at 5:56 pm
OS, SQL versions, SQL server min and max memory setting, From Task Manager - Performance - Memory, paste screenshot.
Also, please post SQL Server Errorlog.
I have this snippet handy from Aaron Bertrand's blog. It shows your buffer pool status. Please post the result then wait for pros to chip in.
-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';;WITH src AS
( SELECT database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id )
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
GO
January 10, 2018 at 8:27 pm
Thanks very much for your response.
OS is Windows 2012 R2,
SQL as mentioned earlier is SQL 2016 Enterprise with SP1 and CU3.
Min Max currently set to 0 and 35,000 (server has 49152 of RAM) but I had been trying 39000 before it fell over.
Errorlog and Memory graph attached.
The server is stable since I reduced the Max Mem but the top 20 sanitised results from Aaron's query are:
database_id db_buffer_pages db_buffer_MB db_buffer_percent
40 663838 5186 18.793
2 599904 4686 16.983
20 559360 4370 15.836
38 421446 3292 11.931
10 387970 3031 10.984
28 266971 2085 7.558
15 193464 1511 5.477
34 167398 1307 4.739
42 54851 428 1.553
30 49434 386 1.399
4 26572 207 0.752
45 19277 150 0.546
5 10606 82 0.300
13 10428 81 0.295
44 9564 74 0.271
47 8132 63 0.230
32 7049 55 0.200
8 7120 55 0.202
29 6869 53 0.194
14 5769 45 0.163
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply