Because SqlServer manages the amount of memory in the system, unless the system memory is insufficient (about 4 MB of memory remaining), SqlServer releases a little memory. Therefore, we often find that the system memory running SQL Server is always high. Guard God tutorial to sort out these memory is generally run
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. Guard tutorial to sort out these memory is generally run on SQL Server
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. Guard
ShenTutorial Arrangement
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.
Here, some netizens specially wrote a tool to release the memory when the SQL Server memory usage is too large, as shown below:
SQLserver memory release tool: Slam my download!
The following is a general idea of software implementation:
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 minimum and maximum memory guard
ShenTutorial Arrangement
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
*/
