We are hosting a SQL Server 2014 SP2 CU16 in a Windows Server 2016 64-bit environment for a customer. Performance has been fine for a long while.
On March 31 at midnight, the database server rebooted 3 times. After the reboot, the buffer cache has been roughly half the size it was previously. The customer is reporting continual performance issues.
The machine has 464 GB of RAM. SQL Server maximum memory is 414 GB.
Prior to the reboot, SQL Server was on SQL 2014 SP2 CU12. The buffer cache was consistently about 366 GB. It would drop down during nightly maintenance and return to normal afterwards.
After Windows updates applied SQL 2014 SP2 CU16, the buffer cache was about 206 GB. An admin attempted to drop the SQL max memory down and back up again and also restart the SQL Server process to regain the prior buffer cache, but the cache memory dropped to 196 GB.
The workload is the same before and after the reboot. The users do the same procedures every week, and the largest tables in the database are guaranteed to be queried frequently each day.
Why would SQL Server not be using as much memory for the buffer cache after a reboot? I don't know of a way to control the buffer usage. Given the same workload, I would expect the same amount of buffer cache usage. I suspect that the lack of buffer cache is the root cause of this issue.
The following updates were installed on the morning of Sunday, March 31:
KB4485447 - 2019-02 Service Stack Update for Windows Server 2016 for x64-based Systems KB4482967 - SQL Server 2014 SP2 CU16
SQL memory counters for the past 7 days are below. Note the light blue line which is database cache memory. The cache memory is half of what it was, and there is a very large amount of free memory that is not being used for cache. The old pattern extends as far back as we have recorded: March 1.
Legend:
light blue line: Database Cache Memory
thin purple line on top: Target Server Memory
black line: Total Server (same as Target Server)
light green line: Maximum Workspace Memory
orange line at bottom: Stolen Server Memory
I looked through the SQL 2014 SP and CU data on the Microsoft Support site, but I did not see anything that looks like it would be related.
-- EDIT --
Looking at memory clerks, there are 2 nodes. Node 1 has 199 GB of memory allocated and 377 GB virtual memory reserved. Node 2 has 17 GB allocated and 0 virtual memory reserved.
select * from sys.dm_os_process_memory
column value
================================== ===============
physical_memory_in_use_kb 423,440,372
large_page_allocations_kb 1,136,640
total_virtual_address_space_kb 137,438,953,344
virtual_address_space_reserved_kb 976,658,048
virtual_address_space_committed_kb 426,252,192
virtual_address_space_available_kb 136,462,295,296
page_fault_count 19,891,928
memory_utilization_percentage 99
available_commit_limit_kb 51,376,768
process_physical_memory_low 0
process_virtual_memory_low 0
There appears to be 180 GB of free memory that is not being allocated to buffer cache:
select * from sys.dm_os_performance_counters where object_name = 'SQLServer:Memory Manager'
column value
============================= ===========
Connection Memory (KB) 2,744
Database Cache Memory (KB) 210,193,912
Free Memory (KB) 180,068,432
Granted Workspace Memory (KB) 1,056
Lock Memory (KB) 456,656
Maximum Workspace Memory (KB) 318,573,000
Optimizer Memory (KB) 4,048
Reserved Server Memory (KB) 1,056
SQL Cache Memory (KB) 54,688
Stolen Server Memory (KB) 33,511,968
Log Pool Memory (KB) 16,968
Target Server Memory (KB) 423,936,008
Total Server Memory (KB) 423,774,312
-- EDIT 2019-04-05 --
We have opened a Microsoft support case. The representative asked us to run dbcc memorystatus
. He noticed there are 2 NUMA nodes, but the nodes are out of balance.
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 975,781,588
VM Committed 6,016,224
Locked Pages Allocated 205,951,768
Pages Allocated 200,936,584
Pages Free 216
Target Committed 211,967,992
Current Committed 211,967,992
Foreign Committed 55,000
Away Committed 325,944
Taken Away Committed 0
(10 row(s) affected)
Memory node Id = 1 KB
---------------------------------------- -----------
VM Reserved 74,128
VM Committed 35,732
Locked Pages Allocated 210,464,000
Pages Allocated 26,244,712
Pages Free 178,942,456
Target Committed 211,967,992
Current Committed 210,499,736
Foreign Committed 24,523,444
Away Committed 0
Taken Away Committed 325,944