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