Preferred, thanks to blogger Shield-hand for an article (MS SQL Server cache cleanup and memory release ). The writing is very meticulous and concise. I am not here to write blog, but to use their own thinking of many posts or official website information to do a consolidation.
Before explaining the cause of the problem, I put out the solution first. Then we will analyze the deep-seated reasons in detail.
MS SQL Server cache cleanup and memory release this article has given a very perfect solution, I will not repeat it. One problem to note is that scripting is best done in the following form:
Use [IVMS8100]GO/** * * * object:storedprocedure [dbo]. [Clearmemory] Script date:04/13/2010 14:28:39 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGO/***start***/----Force free memoryCREATE PROCEDURE [dbo].[clearmemory] asBEGIN--Clear All cachesDBCCdropcleanbuffers--Turn on Advanced ConfigurationEXECsp_configure'Show advanced Options',1----Set maximum memory value, clear existing cache spaceEXECsp_configure'max server memory', theEXEC('RECONFIGURE')--set the wait timeWAITFORDELAY'00:00:01'--Reset Maximum Memory valueEXECsp_configure'max server memory',4096EXEC('RECONFIGURE')--Turn off Advanced configurationEXECsp_configure'Show advanced Options',0END/***end***/
Reference documents:
MS SQL Server cache cleanup and memory release
How do I release the memory that SQL Server 2005 occupies?
Windows 2003 server Performance Monitor (RPM)
SQL Server Stored Procedures
stored procedure authoring experience and optimization measures
SQL Server Reading notes: Memory