I have a production MSSQL 2012 Standard SP4 (11.0.7493) database that periodically (non-deterministically, but anywhere from every few days to every few weeks) becomes unresponsive to queries made by clients using SQL Server Native Client 11. Underlying OS is Server 2012 R2, patched up to date as of January 2021. When the DB becomes unresponsive to queries, the quickest intervention to get clients happy again is to move the role onto the other server in the cluster, after which it works well again for a while. Presumably it is either the action of the instance restarting, or starting fresh without cache that gets things moving again.
The unresponsiveness does not correlate with high CPU load; however, it does seem to correlate with the SQL server process RAM utilization having to expanded to over 70GB. Each server in the cluster has 256GB RAM available, so it I don't believe it is raw memory pressure. The unresponsiveness also does not seem to correlate with backups or any other jobs or external processes, and I haven't seen any long running queries line up either.
Cluster is built on HP servers with dual Xeon E5-2670 v3, 2.30Ghz, HT and Virt enabled. 24 physical/48 logical cores total. 256MB RAM. Cluster storage (including the DB volume) is MSA 2040, direct attached via SAS. I know it's an old setup - we'll be upgrading soon enough.
Here's the max memory setting and some other numbers:
SELECT * FROM sys.configurations WHERE name = 'Max Server Memory (MB)';
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
---------------- ----------------------- -------- ------- ---------- ------------ ----------------------------------- ---------- -----------
1544 max server memory (MB) 245630 128 2147483647 245630 Maximum size of server memory (MB) 1 1
SELECT total_physical_memory_kb, available_physical_memory_kb,
total_page_file_kb, available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory OPTION (RECOMPILE);
total_physical_memory_kb available_physical_memory_kb total_page_file_kb available_page_file_kb system_memory_state_desc
------------------------ ---------------------------- -------------------- ---------------------- -------------------------------------
268302452 233856628 280590452 244473252 Available physical memory is high
SELECT
physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
page_fault_count AS sql_page_fault_count,
memory_utilization_percentage AS sql_memory_utilization_percentage,
process_physical_memory_low AS sql_process_physical_memory_low,
process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;
sql_physical_memory_in_use_MB sql_large_page_allocations_MB sql_locked_page_allocations_MB sql_VAS_reserved_MB sql_VAS_committed_MB sql_VAS_available_MB sql_page_fault_count sql_memory_utilization_percentage sql_process_physical_memory_low sql_process_virtual_memory_low
----------------------------- ----------------------------- ------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------------------- ------------------------------- ------------------------------
23650 0 22135 264724 48003 133953003 559475 97 0 0
Page faults seem high, and that number grows about 5/sec (320/min). DB has been in production since 2012. Any advice would be greatly appreciated!
