Score:2

Free up memory in Windows Server 2019 for SQL Server instance?

kr flag

We have Windows Server 2019 Standard (x64) with 64GB memory.

The screenshot below shows the memory usage at 96% with the SQL Server Windows NT running and using 344.5 MB.

enter image description here

This is the Task Manager after stopping the service SQL Server Windows NT. Memory usage went down to 8%.

enter image description here

I start service SQL Server Windows NT again. The Task manager is very low at 9% but Sql server is using more memory now than when the memory usage was at 96%. enter image description here

Two questions:

  1. What's the issue with the memory?
  2. How can I lower the memory usage without having to restart the sql server instance?
cn flag
`it seems to me that sql server is using up little memory because everything else is being consumed by Windows.` 100% it is not Windows.
Score:8
cn flag

You're running SQL Server on this machine, so it's doing what SQL Server does - it reserves (almost) all the memory available on the OS, so that when it gets big queries or other operations, it doesn't have to allocate RAM, it already has it.

You have a few options.

  1. Accept it. If you're only running SQL Server on this machine, then it's operating as intended. Leave it alone.
  2. Cap it. If you're required to run other workloads on this server, you can limit the amount of RAM that SQL will reserve.

I would absolutely not do option 2 unless SQL is never using the RAM it soaks up. You will only know this if you're measuring OS-level SQL performance counters and SQL-level dynamic management views.

Big picture, what problem are you actually trying to solve? "Freeing up memory" in this scenario is a pointless task unless you know you're freeing it up for something else to use. Are you having SQL performance problems, and if so, how have you determined that you're bottlenecked by RAM instead of, for example, bad index design or unoptimized queries?

What I'm leading you to is use proper monitoring to diagnose performance problems. You need to know a lot about SQL and a decent amount about Windows to do this. If you're not a DBA, hire one or get one in on a contract, or work with your software vendor if this is SQL for a purchased product. If it's something your company wrote itself without a DBA, go get one.

https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver15

By default, a SQL Server instance may over time consume most of the available Windows operating system memory in the server. Once the memory is acquired, it will not be released unless memory pressure is detected. This is by design and does not indicate a memory leak in the SQL Server process.

/Edit based on your edit:

Although I'm not a system admin, it seems to me that sql server is using up little memory because everything else is being consumed by Windows.

You're almost definitely wrong here. As I mention above, SQL server will allocate almost all of the system RAM over time. The amount of RAM in use by the sqlserver.exe process is NOT telling you how much RAM that SQL has actually allocated. The DMVs and perfcounters will tell you that.

I use SSMS to work with the databases, and I notice firsthand how slow everything is. Even simple things like opening the tables pane to see all my tables take a while and sometimes times out.

Don't run that on the SQL server, run it from your workstation.

rbhat avatar
kr flag
I want to reduce the memory of Windows services so that sql server consumes more memory. It shouldn't be topping out at 97% all the time.
mfinni avatar
cn flag
No, you really don't. The Windows services that come with the OS are running; leave them alone.
rbhat avatar
kr flag
You're saying that it's normal for the task manager memory column header to always be red?
mfinni avatar
cn flag
Read the article that I linked, or simply read the relevant paragraph that I quoted in my answer.
rbhat avatar
kr flag
I've updated the question with screenshots of the task manager after stopping the sql server service and starting it again. Why is the memory now at 9% and sql server using more memory now than at 96%?
rbhat avatar
kr flag
FYI I run SSMS from my workstation.
Score:8
ng flag

You are missing an important detail here: SQL Server is shown by the Task Manager as only using 344.5 MB of RAM, but it has actually reserved much more, almost all of it, for its own use. Task Manager will not show this. The numbers in Task Manager don't actually add up to 97% of the system memory, because reserved memory is not shown there.

Your assumption that almost all of your memory is used up by something else and SQL Server is thus unable to use more than 344.5 MB is just plain wrong. It doesn't work like this. The Task Manager can be quite misleading, have a look at the actual performance counters.


Update to try explaining it better.

SQL Server by design caches data in memory, and over time it will use up almost all of the available memory in the system; but this memory is not shown (or at least not all of it) in the Task Manager, because SQL Server tries to play nice and tells the OS "I want this much memory, but it's only for caching, I can survive without it, so feel free to reclaim it if you need". This translates to the Task Manager showing a lot less memory used by SQL Server than what it has actually reserved.

This is by no means a problem; it's done on purpose and by design. The "problem" you are seeing doesn't exist, only what you see in the Task Manager is misleading.

If you stop SQL Server, it will of course release all of its allocated memory, both that which is shown in the Task Manager and that (much more) which is not shown. When you start it up again, it will use very little memory: the usage will grow again over time as SQL Server loads data in memory and caches it. But most of this memory, again, will not be shown in the Task Manager, because that specific tool is not able to see it.

I'll also add that if SQL Server actually needed memory, it would allocate it in a different way and then the Task Manager would show it; you would see 60 GB of memory used by SQL Server if it actually required that much memory, instead of only using it for caching.

rbhat avatar
kr flag
I've updated the question with screenshots of the task manager after stopping the sql server service and starting it again. Why is the memory now at 9% and sql server using more memory now than at 96%?
Massimo avatar
ng flag
As I said, SQL Server *reserves memory when it runs*. This reserved memory is not shown in the Task Manager, but it's used by SQL Server. If you restart it, all reserved memory is released and memory usage drops; it will grow again over time and the Task Manager will keep *not* showing it.
Massimo avatar
ng flag
What you did actually demonstrates that the process using up all your memory was *exactly* SQL Server, otherwise restarting it would have had no effect on memory usage.
in flag
As a best practice, how much memory should be left for the OS?
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.