1

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?

4
  • Can you reconfirm version and edition of your SQL Server please by adding output of select @@version in the question. Plus please let me know on your windows 10 machine, apart from SQL Server, what other applications are running ? It could be possible that some app running on windows 10 is excessively using memory and thus creating memory pressure forcing OS to trim SQL Server memory by paging it – Shanky Oct 28 '20 at 3:46
  • My version is Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19041: ) (Hypervisor). When OS memory utilization is over 95% SQL server is out of game for about 3 mins. Based on the message in Event Log it's wiping 200MB only, should be matter of seconds. – Jan Zahradník Oct 29 '20 at 14:55
  • Your dev system on SSD too? If so, how fast can you write to it, meaning physically? – Gerard H. Pille Oct 29 '20 at 15:10
  • CrystalDiskMark says 3GB/s seq, 350MB/s random, 100k IOPS. Should persist whole 16GB RAM in less than 10 secs. – Jan Zahradník Oct 29 '20 at 20:04
-2

To me this sounds like a clear case of using Lock Pages in Memory and setting a reasonable max and min server memory.

3
  • But we first need to confirm that the edition is either Enterprise/Dev or Standard. AFAIK express edition would not have LPIM feature. – Shanky Oct 28 '20 at 10:25
  • Ah, indeed. The OP states it is Express, but a double check is in place. I never tried LPIM with Express, but I trust you are correct when you say it isn't available. :-) – Tibor Karaszi Oct 28 '20 at 11:00
  • 1
    Confirmed, SQL 2019 Express doesn't support LPIM – Jan Zahradník Oct 29 '20 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.