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?

Improve this question
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
    Commented Oct 28, 2020 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
    Commented Oct 29, 2020 at 14:55
  • Your dev system on SSD too? If so, how fast can you write to it, meaning physically?
    – Gerard H. Pille
    Commented Oct 29, 2020 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
    Commented Oct 29, 2020 at 20:04

1 Answer 1

Reset to default
-2

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

Improve this answer
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
    Commented Oct 28, 2020 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
    Commented Oct 28, 2020 at 11:00
  • 1
    Confirmed, SQL 2019 Express doesn't support LPIM
    – Jan Zahradník
    Commented Oct 29, 2020 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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