First observed few months ago at end-user's laptop (rather slow i3 CPU, 4GB RAM, SSD) with SQL 2014 Express. The problem persists with SQL 2019, even with clean Windows 10 1903 installation.
The database has 250MB so pretty small even for Express Edition. Application mostly works smooth, but there are time to time very looong pauses, while simple query which is usually finished within 2 secs timeouts after 5 mins! It's client machine, there are no other queries nor databases.
Recently the same problem start occurring on my dev machine too. I can confirm the memory pressure in Windows event log:
A significant part of sql server process memory has been paged out. This may result in a performance degradation.
Duration: 7204 seconds. Working set (KB): 203376, committed (KB): 577992, memory utilization: 35%%.
Based on this MSDN article about paging I should turn on Locked pages. There is a caveat that the hungry SQL Server can eat all free memory, so user can't work at all. Are my worries relevant?
What struggles me most is that it start happening quite recently (approx. 2 months ago) while there is no significant change at my workload as well as 16GB RAM. Second, why freeing few hundreds MBs is taking several minutes with NVMe drive (and in my case also quite performant CPU).
Are there some recent changes how Windows 10 / SQL server handles out of memory states?
Best Answer
To me this sounds like a clear case of using Lock Pages in Memory and setting a reasonable max and min server memory.