SQL Server memory Release

Note: I am using the sqlserver2000 test, many commands are not used

Because of the amount of SQL Server's administrative policy for system memory, unless the system is running out of memory (about approximately 4M of remaining memory),

SQL Server frees up a little memory. So in many cases, we find that system memory running SQL Server is often high.

This memory is typically used by SQL Server runtime as a cache, such as when you run a SELECT statement,

Then SQL Server loads the relevant data pages (the data for the SQL Server operation is in pages) into memory.

The next time you request this page's data again, there is no need to read the disk, greatly increasing the speed. This type of cache is called the data cache.

There are other types of caching, such as when executing a stored procedure, SQL Server needs to compile and run, and the results will be cached.

The next time you don't have 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 caching, session caching, system caching, and all cache

However, it should be noted that these commands will erase the existing cache and make room for the new cache.

However, SQL Server does not release memory that is already occupied. Unfortunately, SQL Server

does not provide any command to allow us to release the unused memory. So we can only adjust by dynamic

The physical memory settings that SQL Server can use to force it to free memory.

We can also use SQL Server Management Enterprise Manager for dynamic control.

Open the Properties panel of the SQL Server instance after you connect to Enterprise Manager.

Locate the 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 status

DBCC Memorystatus

--View minimum maximum 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 ', 256

EXEC (' reconfigure ')

WAITFOR DELAY ' 00:00:05 '

EXEC sp_configure ' max server memory ', 2147483647

EXEC (' reconfigure ')

Go

End

--Using the example

/*

Reclaimmemory

*/