SQL Server out of memory resolution, and the knowledge points involved

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