Sqlserver memory release

Note: I am using sqlserver2000 for testing. Many Commands cannot be used.

Because SQL Server manages the amount of memory in the system, unless the system memory is insufficient (about 4 MB of memory remaining ),

SQL Server releases a little memory. Therefore, we often find that the system memory for running SQL Server is always high.

These memories are generally used as cache when SQL Server is running. For example, if you run a select statement,

Then, SQL Server loads the relevant data pages (the data operated by SQL Server is in the unit of pages) to the memory,

If you request data on this page again next time, you do not need to read the disk, greatly improving the speed. This type of cache is called data cache.

There are also some other types of cache, such as when executing the stored procedure, the SQL Server needs to compile and run, and the compiled results will also be cached,

The next compilation is unnecessary. If these caches are no longer needed, we can call the following DBCC management commands to clear these caches:

DBCC FREEPROCCACHE

DBCC FREESESSIONCACHE

Dbcc freesystemcache ('all ')

DBCC DROPCLEANBUFFERS

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

However, although these commands will clear the existing cache, they will make a place for the new cache,

However, SQL server does not release the occupied memory. However, SQL Server

No command is provided to allow us to release unused memory. Therefore, we can only adjust it dynamically.

SQL Server can use physical memory settings to force it to release memory.

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

Connect to the Enterprise Manager and open the properties Panel of the SQL Server instance,

Find 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 and 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 the minimum and maximum memory size.

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

Bytes -----------------------------------------------------------------------------------------------

CREATE proc [dbo]. reclaimmemory -- force release of 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

-- Example

/*

Reclaimmemory

*/