HI experts.
This Windows server has 8 GB of RAM
SQL (2016 SP3) is allocated a MAX of 4 GB
Task Manager shows 92 % Memory usage
Navigating in SSMS is very slow
IN Resource Manager --> Memory:
sqlservr.exe is using 400 MB and SQLAgent is using 50 MB
If I total everything in the Commit Column, it doesn't come close to 8 GB.
What else is using memory to bring the total usage up to 92 percent?
(This instance does not have SSAS nor SSRS)
Thanks
Windows cannot accurately report on SQL Server's memory usage.
For a quick check on total memory used, run this query in SQL Server:
SELECT
physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
locked_page_allocations_kb/1024 AS [SQL Server Locked Pages Allocation (MB)],
large_page_allocations_kb/1024 AS [SQL Server Large Pages Allocation (MB)],
page_fault_count, memory_utilization_percentage, available_commit_limit_kb,
process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK);
Thank you, @ScottPletcher
The query returns:
Other than physical memory being low, what do you make of these numbers? Is the page fault count too high?
Thanks very much
I once had a problem where two identical VMs on Windows Server 2016 would just slowly lose memory until they needed to be restarted. These boxes weren't even doing anything, since we noticed the memory loss before deploying anything to them. We removed a VMware driver from one and it seemed to fix the problem, but that same action didn't fix the problem on the other server, so to be safe we just nuked both of those VMs and created new ones. We spent hours troubleshooting them before that and never learned what was really causing the problem. All this is just to say sometimes you just have to give up on that box and start over.
Good info, @SqlHippo. In my case, this VM was just rebooted yesterday. I'll discuss your ideas with the VM guys. Thanks
I'll also say, unless this is SQL Server Express Edition, you're really suffocating SQL Server by not allowing it access to more RAM. But sometimes you don't have much choice.
How large are the dbs involved? How often do you read new data and in what volume? The cheapest performance gain for SQL Server, by far, is more RAM.
SQL max memory doesn't account for absolutely all of SQL's memory usage, although in later versions it's much closer to true than in earlier versions of SQL. And it's a one-time cost. Since you have SQL 2016, any edition of that can use more than 8GB.
@ScottPletcher and @SqlHippo : 2 GB db size
tables are indexed well with little fragmentation.
STD Edition, Server has 8 GB with SQL Max set to 4 GB. No apps on server. Just navigating around in SSMS is painful vey slow. No blocking.
I may allocate a max of 6 GB to see if we see any improvement at all.
Oh, wow... that's not a very big database... but even at that size, I'd want that server to have 16GB, with 8GB allocated to SQL Server, leaving the other 8GB for the OS and apparently users to log in and run SSMS on it. It seems strange that SSMS behaves so poorly against a local database, but that may very well be due to SSMS, SQL Server, and the OS all competing for the same memory.
We have slow SQL Server VMs sometimes after Windows Update has been applied and the VM is Pending Reboot. Look at the following and if it returns true restart the VM.
Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending"
Thanks @Ifor . The VM was restarted yesterday am. And it was still very slow. Mysteriously, today it is performing much better. I cannot explain the improvement.
Sometimes it can take 2 or 3 restarts to get rid of the Pending Reboot.
@SqlHippo The last reboot was 2 days ago and there are no reboots pending. The sudden improvement in performance is a complete mystery. No changes have bene made to server or SQL bu I will continue to monitor....
Good luck. 
BTW, the memory leak we had was in the non-paged pool. It kept growing and growing until the server was starved of memory. Here's what we were reading to try to troubleshoot it at the time, but I can't offer any guidance beyond what you'll find in these docs.
RAMMap - Sysinternals | Microsoft Learn
Use PoolMon to Find a Kernel-Mode Memory Leak - Windows drivers | Microsoft Learn
