MS SQL Server cache cleanup and memory release

SQL Server system memory management in the absence of configured memory maximums, many times we find that the system memory running SQL Server is often high. This is because his strategy for memory usage is how much idle memory to occupy, until memory usage takes into account the system peak (reserved memory based on the system default reserved use, at least 4M), will clear some cache free up a small amount of memory to make room for the new cache.

  These memory is typically used as a cache when SQL Server runs, such as when you run a SELECT statement, execute a stored procedure, call a function,  1. Data caching: Executing a query statement, SQL Server loads the relevant data page (the data for SQL Server operations is in pages) into memory, and the next time you request the data for this page, you do not have to read the disk, greatly increasing the speed.  2. Executing a command cache: When executing a stored procedure, a custom function, SQL Server needs to be binary compiled and then run, and the compiled result will be cached again without having to compile again when the call is made.   After we execute the corresponding query statement, or stored procedure, if we do not need these caches, I can purge it, and the DBCC ADMIN command cache is cleared as follows: [SQL]   --Clear the stored procedure cache      DBCC Freeproccache    www.2cto.com    --Note: Easy to remember keyword Freeproccache can be disassembled to free (give up, clear) PROC (shorthand for Stored procedure keywords), cache   [sql]  --clear Session cache     DBCC Freesessioncache      --Note: Free (give up, clear) session (session) cache (cache)    [sql]  --Clear System cache      DBCC Freesystemcache (' all ')    --Note: free  syste mcache  [sql]  --Clear all caches      DBCC dropcleanbuffers    --Note: DROP clean buffers    Although we have cleared the cache, However, SQL does not release the appropriate memory. It just frees up new space for the script to execute later. SQL Server   does not provide any commands that allow us to release unused memory. So we onlyYou can force it to free memory by dynamically adjusting the physical memory settings available to SQL Server. The principle of operation is to adjust the memory configuration size. Manual operation Method: 1. Open SQL Server Management (Enterprise Manager); 2. Open the Properties panel for the instance of SQL Server; 3. Locate the memory settings and change the maximum server memory usage.   Use script action:  [sql]  --Force free memory  create procedure [dbo]. Clearmemory    as    www.2cto.com  begin  --Clear All caches  dbcc dropcleanbuffers  -- Open Advanced Configuration  exec sp_configure ' show advance options ', 1  --set maximum memory value, clear existing cache space  exec sp_configure ' max server mem Ory ',  exec (' RECONFIGURE ')  --set wait time  waitfor DELAY ' 00:00:01 '  --reset maximum memory value  exec  SP _configure ' max server memory ', 4096  exec (' RECONFIGURE ')  --off Advanced Configuration  exec sp_configure ' show advance opti Ons ', 0  go    Below are some script statements that provide memory viewing capabilities: [SQL]   --memory usage           Www.2cto.com  select * from Sys.dm_os_performance_counters   where counter_name in (' Target Server Memory (KB) ', ' Total Server memory (KB) ')    [sql]  --Ram State &nbsP; DBCC memorystatus    [sql]  --view min max memory   select configuration_id as id,name as name , minimum as configuration minimum, maximum as maximum,   is_dynamic as dynamic value, is_advanced as priority, Value_in_use as run value,    Description as description from sys.configurations 

MS SQL Server cache cleanup and memory release