Total memory and target memory

Question

Is it possible to release Total memory without restart sqlserver

I try

dbcc freesystemcache ('all') ...

dbcc freeproccache

dbcc dropcleanbuffers

but no use... any ideas ??

Answer 1

What do you mean by "release Total memory"? I think it more related to Windows OS....as it depends how much memory server has and MAX memory parameter for SQL Server

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

I mean the "Total Server Memory" , and we don't have MAX memory setting.

The "Total Server Memory" keeps on going and I want to release it without restart.

Answer 3

How much does the server have memory? As you probably know it is by design that SQL Server allocates all memory as it needs and releases it as OS requires.. Set MAX Memory param to 'prevent' from allocating all available memory 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Hi sakurai_db,

I mean the "Total Server Memory" , and we don't have MAX memory setting.

The "Total Server Memory" keeps on going and I want to release it without restart.

Setting the "max server memory" option (with sp_configure) should
release extra memory.

Note that this may impact performance of SQL Server. SQL Server is
designed to keep as much information in cache as possible, since it's
much faster to read data from cache than it is to read from disk. SQL
Server will release memory when the OS signals memory pressure, but
not otherwise.

If your server is running SQL Server and other applications as well,
it is indeed the best way to set the max server memory option to a
value that leaves space for the other applications. But for serious
performance, it's best to have a dedicated server running SQL Server,
and allow it to manage its own memory.

---

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

---

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Answer 5

If you set the MAX server memory configuration option to be a lower value SQL Server will adjust to adhere to this property without the need for a restart.

For example, if you have a 64GB server and have not set a value for MAX server memory, SQL Server will take as much memory as needed up to the server maximum. If you were to set the MAX server memory configuration to 50GB, SQL server will release memory over this amount without the need for a restart.

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

Answer 6

Actually I have some problem suddenly:

My server has 2GB

I have monitor the Total server memory vs target server memory, and total server memory is nearly the same as target server memory all day long.

We have a procedure need to run on the database which only takes few minutes originally.

But start on Monday, suddenly the procedure takes 30mins to finish.

don' t know how to tackle on it.~ any ideas?

Answer 7

I would say that the performance of a particular query is a separate question, to the original which relates to server memory configuration.

Create a new thread for the question.

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter