question

DevendraSahu-0306 avatar image
0 Votes"
DevendraSahu-0306 asked Yufeishao-msft answered

SQL server memory issue

I am using SQL Server 2016 SE. When I used Update statistics Maintenance plan , the SQL server memory go on increasing & it will never come down. Maintenance plan successfully completed but it not come down memory . Can anybody suggest how to release memory from sql server.

Thanks in advance.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog converted comment to answer

You don't want to release memory from SQL Server, period.

SQL Server is designed to grab as much memory as it can. It reads the data into the buffer cache, as reading from cache is faster than reading from disk. Once it has read data into cache, what would be the point with throwing that memory away, as long as it is not needed for anything else?

If there other processes running on the machine and there is memory pressure, SQL Server will yield memory, don't worry.

Although, sometimes SQL Server may not yield fast enough, and if other processes are suffering, you can set "max server memory" to limit how much memory SQL Server can consume. For instance, if you are running SQL Server on your laptop, where there are lot of other things going, you may want to run

EXEC sp_configure 'max server memory', 4000
RECONFIGURE


to cap SQL Server at 4GB of memory. But don't do this on a production server which is dedicated to run SQL Server.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i already Configure memory on sql server, physical memory is 154 GB and we assign to sql is 124 GB
after successful run Update statistics Maintenance plan 121 GB use.
no any other process running on sql server

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered OlafHelper-2800 commented

So everything is perfectly normal then. Just roll over to the other side and fall back into sleep.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes i have configure cluster
when i failover and failback than memory come down 40 to 50 GB

but it way not come down automatically and why when failover and failback after that memory come down.

0 Votes 0 ·

When you fail over the SQL Server process from one machine to another, you are restarting the process, and all allocated memory is lost.

A collorarry of this is that restarting SQL Server unnecessarily means that you are taking a performance hit. Until the cache has been filled up again, you will doing extra disk reads

I repeat: what you see is perfectly normal and expected and nothing you should worry over.

0 Votes 0 ·

i don't link weekly basis failover and failback

my main concern is why automatically not relies memory

0 Votes 0 ·

my main concern is why automatically not relies memory

Because it would be a bad thing to do. The purpose of a cache is to have the data quickly available.

Thus, as I've told you a couple of times, you should not be concerned. It is all by design.

1 Vote 1 ·

my main concern is why automatically not relies memory

Why should SQL Server do? That's the way it works. It allocates memory for buffer pool (=cache) and hold it for it's work.

1 Vote 1 ·
Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

Hi @DevendraSahu-0306


It is quite normal for SQL Server to utilize memory allocated to it which often seems like it is using high memory. SQL Server always assumes it is the primary application running, it will always take all the available memory and it will only release it for the operating system, so you can set ‘max server memory’

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.