Releases memory occupied by SQL Server

since SQL server manages the system memory, SQL Server releases a small amount of memory unless the system memory is insufficient (about 4 MB. Therefore, we often find that the system memory for running SQL Server is always high.

these memories are generally used as Cache during SQL server running. For example, if you run a SELECT statement, then, SQL Server loads the relevant data pages (data operated by SQL Server is in the unit of pages) to the memory. The next time you request data on this page, the disk does not need to be read, greatly improving the speed. This type of cache is called data cache. There are other types of cache. For example, when executing a stored procedure, SQL Server needs to compile and run the program first, and the compiled results will also be cached. You do not need to compile the program again next time. 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. For more information, see msdn.

However, it should be noted that although these commands will clear the existing cache and leave a place for the new cache, SQL server will not release the occupied memory. However, SQL server does not provide any commands to allow us to release unused memory. Therefore, we can only force SQL Server to release memory by dynamically adjusting the available physical memory settings.

 use master 
 -- open the advanced settings configuration exec sp_configure 'show advanced options ', 1 
 reconfigure with override 
 -- first set the physical memory ceiling to 1g exec sp_configure 'max server memory (MB )', 1024 
 reconfigure with override 
 -- restore the original upper limit exec sp_configure 'max server memory (MB )', 5120 
 reconfigure with override 
 -- restore the default exec sp_configure 'show advanced options', 0 
 reconfigure with override 

We can also use the SQL Server Management Enterprise Manager for dynamic control. After connecting to the Enterprise Manager, open the properties Panel of the SQL server instance, find the memory settings, and change the maximum service