1

I am using SQL Server 2014 Developer edition.

SQL Server is running on server and it's occupied around 60 GB memory while execution and after completion of execution it's not releasing it.

Please suggest on this.

enter image description here

I want to reduce it to normal.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dhaval Lakhani
  • 29
  • 5
  • 1
    SQL Server likes memory and will use as much as is available for it to use - this is by design and very much to your benefit in terms or how it performs. Google for *configure sql server max memory* if you have a compelling reason to impose an upper limit. – Alex K. Jul 17 '17 at 13:16
  • 2
    SQL Server is a memory hoarder. Once it gets it's hands on some free memory it will not let it go. It's not a bug, it's a feature. – Zohar Peled Jul 17 '17 at 13:17
  • Thanks Alex, but it will bound me to use that much only(which I will set as max). I want to use as much memory as I have on server when execution is running. but when execution get complete, (ideal state of sql), it should release it. – Dhaval Lakhani Jul 17 '17 at 13:19
  • @zohar peled, Is there any way we can release it? – Dhaval Lakhani Jul 17 '17 at 13:19
  • Short of shutting down SQL Server and restarting it - no, there's no way to release that memory. SQL Server grabs it and then uses it as it sees fit - and it will keep memory it's once allocated, and re-use it for other things. That's just the way it works - get used to it. – marc_s Jul 17 '17 at 13:20
  • 1
    If you set a server maximum below what it's using now and then issue `DBCC DROPCLEANBUFFERS`, the memory will be released -- if you're lucky. But tweaking the maximum is not something you can or should do on every query (let alone `DBCC DROPCLEANBUFFERS`!) If you need that memory for something other than SQL Server, your software is running on the *wrong* machine. Move it to somewhere else. – Jeroen Mostert Jul 17 '17 at 13:21
  • 1
    Is there a reason you want to release it? By keeping data in memory rather than disk subsequent queries have a chance of being significantly faster. On a dedicated SQL Server machine you would simply leave it alone to do its own thing. – Alex K. Jul 17 '17 at 13:21
  • @AlexK.my query is one time in a day. – Dhaval Lakhani Jul 17 '17 at 13:23
  • If you only need to use the server once a day you could start/stop the service on demand I suppose. – Alex K. Jul 17 '17 at 13:24
  • It's not a best solution, as I have terabytes database, it will kill me if some times database not get up properly. :) – Dhaval Lakhani Jul 17 '17 at 13:27
  • Use a dedicated machine to run SQL Server on. Keep nothing else on that machine. Note that it can be a virtual machine. – Zohar Peled Jul 17 '17 at 13:36

1 Answers1

0

Why you might want to limit SQL Server memory

It's common for SQL server to have several instances on a machine which is not a dedicated SQL server machine, but where SQL Server embedded or LocalDB is installed as part of an operating system component or as part of an application.

In these circumstances it is appropriate to allocate memory to each instance so they don't tread on each other's toes.

In addition, some applications make heavy use both of a database as well as memory and processor intensive application processing. Where there is a lot of transfer to and from the database it can make sense to locate the DB on the same machine as the application to reduce the IO cost, as they can then use a shared memory connection. In this case, again, you will have to allocate how much memory SQL server is allowed to use.

Development machines are a perfect example of this - typically you will have an installation of SQL Server development edition, and in addition to that any database projects will spin up an instance of SQL Server LocalDB.

How to do it

For your Dev machine you want to keep this value relatively low. I’d suggest 512 MB, but if you feel that’s too low 1024 MB shouldn’t be a problem

To reduce SQL Server memory usage you can run the following SQL:

EXEC sys.sp_configure N'show advanced options', N'1' ;
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'max server memory (MB)', N'512';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0'; 
RECONFIGURE WITH OVERRIDE;

Reducing usage of the LocalDB instances is similar, you simply have to connect to each LocalDB instance and use the same commands.

For example, to set all LocalDB instances to use at most 256MB:

$MaxServerMemory = 256
@( & sqllocaldb info ) | %{
    "Reconfiguring (LocalDB)\$_"
    & sqlcmd.exe -E -S "(LocalDB)\$_" -Q "EXEC sys.sp_configure N'show advanced options', N'1' ;RECONFIGURE WITH OVERRIDE;EXEC sys.sp_configure N'max server memory (MB)', N'$MaxServerMemory';RECONFIGURE WITH OVERRIDE;EXEC sys.sp_configure N'show advanced options', N'0'; RECONFIGURE WITH OVERRIDE;" *>&1
""
}
Ben
  • 34,935
  • 6
  • 74
  • 113