SQL Server memory Release

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_name in (' Target Server Memory (KB) ', ' Total Server memory (KB) ')

--Memory state

DBCC Memorystatus

--View min max memory

SELECT

Cfg.name as [name],

CFG.CONFIGURATION_ID as [number],

Cfg.minimum as [minimum],

Cfg.maximum as [maximum],

Cfg.is_dynamic as [dynamic],

Cfg.is_advanced as [advanced],

Cfg.value as [ConfigValue],

Cfg.value_in_use as [Runvalue],

Cfg.description as [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 ',
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