2

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.

Memory Counters Past 7 Days

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
Improve this question
11
  • Nothing else was implemented during this maintenance on March 31st, just the Windows Server and SQL Server patches? No changes to the instance settings, applications, workloads running against SQL Server, VM host / guest settings (if it's a VM), etc?
    – Josh Darnell
    Apr 3, 2019 at 15:45
  • 1
    Some big table or index was loaded into memory before the reboot and hasn't been read into memory again since? Buffer pool is just a reflection of how much of your data has been requested - it won't get used until that data has been read. How have you directly tied buffer pool size to performance issues? What other metrics are you using for that correlation? Just because the performance issues started after these three reboots doesn't necessarily mean that your observation about buffer pool size has any correlation to performance observations.
    – Aaron Bertrand
    Apr 3, 2019 at 15:55
  • Those were the only changes I saw in the Windows event log. I don't know what might have changed at the VM level, but I will ask. User workload would start the same but get heavier during April 2 and April 3 during financial period close. Instance settings stayed the same as far as I can see.
    – Paul Williams
    Apr 3, 2019 at 15:56
  • @AaronBertrand The server's buffer pool page life span dropped from normal looking numbers prior to the reboot to a fairly flat line around 5 seconds. Yes, 5! I've never seen a server get that low. Disk latency from a SQL Server perspective is worse after the reboot. My strong hunch is that if we had that extra ~200 GB back in the buffer cache, I/O would be much reduced, and performance would go back to normal. I have been searching for expensive queries and found one that needs a fix, but it does not run frequently.
    – Paul Williams
    Apr 3, 2019 at 16:01
  • But again, PLE is just a number. How are you confirming that "because PLE is low, customer is complaining about performance"? Where are you tracking I/O and pinning the problem on that? Your graph doesn't show any I/O counters, so are you just guessing? And if a different I/O profile does turn out to be the issue, I suspect you're down to a config issue somewhere. SQL Server doesn't expose knobs for how much buffer pool to use, but if max server memory is capped some way due to hardware or config, it's not going to use as much as you'd like.
    – Aaron Bertrand
    Apr 3, 2019 at 16:03

0

Reset to default

Browse other questions tagged or ask your own question.