Dear i am facing a Problem that whenever we execute complex queries with million of records
memory allocated to SQL Server got full as a result system got hanged
i only want to free the memory used by query
i will execute it after executing every query
Thanks in Advance
I think you want to look at the SQL Server Min Server Memory setting. Once the SQL Server engine has a hold on memory it does not automatically free it back to the wild. It will. however, recognize a demand for memory and allow itself to release unused memory but only to the level defined in Min Server Memory setting. If your Max Server Memory is set to the same value as the Min Server Memory then it won't ever release the unused memory. Set the Min value lower.
Also, since you are having issues with the system hanging, the Max Server Memory setting may be too high. The OS needs to have access to enough memory itself. Either your Max Server Memory is too large per se or you have other applications that share the server. SQL Server performs best when it is on its own dedicated server. If possible, migrate any other applications off to another server.
If the system is running out of memory - then you need to define a max memory setting that leaves enough memory available for the OS and other processes outside SQL Server.
This is especially important to set if you have locked pages in memory set - which will be set if you use local system or your service account is a local administrator.