SQL Server memory Release

Https://www.cnblogs.com/luluping/archive/2010/11/26/1888364.html

Because of the amount of SQL Server Management policy for system memory, unless the system memory is not enough (about the remaining memory is about 4M),
SQL Server will not release a little memory. So many times, we find that the system memory running SQL Server is often high.
These memory are typically used as caches when SQL Server runs, such as when you run a SELECT statement,
Then SQL Server loads the related data pages (the data for SQL Server operations in pages) into memory.
The next time you request this page's data again, you do not have to read the disk, greatly improving the speed. This type of cache is called a data cache.
There are other types of caches, such as when a stored procedure is executed, SQL Server needs to compile and run, and the compiled results are cached.
The next time you don't need to compile again. If these caches are no longer needed, then we can call the following DBCC management commands to clean up these caches:

DBCC Freeproccache
DBCC Freesessioncache
DBCC Freesystemcache (' all ')
DBCC dropcleanbuffers

These commands are used to purge stored procedure-related caches, session caches, system caches, and all caches

However, it is important to note that although these commands will clear out the existing cache and make room for the new cache,
However, SQL Server does not release the memory that is already occupied. Unfortunately, the SQL Server
There is no command to allow us to release unused memory. So we can only adjust by dynamic
The physical memory settings available to SQL Server to force it to free memory.

We can also perform dynamic control via SQL Server Management Enterprise Manager.
After you connect to Enterprise Manager, open the properties panel for the instance of SQL Server,
Find memory settings and change the maximum server memory usage




--Memory usage
SELECT *From Sys.dm_os_performance_counters
WHERE Counter_nameIn (' Target Server Memory (KB) ', ' Total Server memory (KB) ')

--Memory state
DBCC Memorystatus






--View min max memory
SELECT
Cfg.nameAs [Name],
cfg.configuration_idAs [Number],
Cfg.minimumAs [Minimum],
Cfg.maximumAs [Maximum],
Cfg.is_dynamicAs [Dynamic],
cfg.is_advancedAs [advanced],
Cfg.valueAs [ConfigValue],
Cfg.value_in_useAs [Runvalue],
Cfg.descriptionAs [Description]
from 
Sys.configurations as cfg  
 
--set minimum maximum memory  
 
 
 
sp_ configure  ' show advanced options ',  1 
 
Go  
sp_configure  ' min server memory ",  0 
reconfigure 
go 
 
sp_configure  ' max server memory ',  2147483647 
reconfigure 
go 
 
sp_configure  ' max server  memory ',  256 
reconfigure 
go 
sp_configure   ' show advanced options ',  0 

CREATE proc [dbo].reclaimmemory--Force free memory


As

Begin

DBCC Freeproccache
DBCC Freesessioncache
DBCC Freesystemcache (' all ')
DBCC dropcleanbuffers

exec sp_configure ' max server memory ', 256
EXEC (' RECONFIGURE ')

WAITFOR DELAY ' 00:00:05 '

EXEC sp_configure ' max server memory ', 2147483647
EXEC (' RECONFIGURE ')
GO

End

--Using the example
/*

Reclaimmemory

*/

SQL Server memory Release