SQL Query and RAM issues

Question

I have an sql query which I am trying to run on a server which has 8GB RAM. If I restart the server, it starts up and the memory usage is about 1.2GB.

If I then execute the query, by the time the query has finished, the RAM usage goes to about 4GB and seems to stay there, even over night.

If I then execute the query again (the next day), the RAM usage goes up to about 7GB and stays there even when the query has finished.

If I then try to execute the query again after waiting 24 hours, the RAM usage is still at 7GB, but this time, the query starts returning out of memory errors.

My question is, how to I clear the memory usage when the query has finished running? Ideally, it would be good if the sql script itself could clear the RAM usage when it finished it's main job.

---

The server version is:

It's Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64).

---

The error message is:

System.Data.SqlClient.SqlException (0x80131904): There is insufficient memory available in the buffer pool.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at Project1.Form1.intenseProcess3() in c:\Users\oshirowanen\Documents\Visual Studio 2013\Projects\Project1\Form1.cs:line 117
ClientConnectionId:33f515db-0086-4f88-a8fd-e7779d92d030
Error Number:802,State:20,Class:17 SqlException caught.

Answer 1

You should set your max server memory to leave at least a gig or two available to the OS and any other software on your server. SQL will cache data and only release it when approaching it's limits.

Use max server memory to prevent the SQL Server buffer pool from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. SQL Server does not immediately allocate the memory specified in max server memory on startup. Memory usage is increased as needed by SQL Server until reaching the value specified in max server memory. SQL Server cannot exceed this memory usage unless the value of max server memory is raised.

https://msdn.microsoft.com/en-us/library/ms178067%28v=sql.105%29.aspx

You can change it with Management Studio by right clicking on the server in the object viewer and selecting properties. Then change the Maximum Server Memory value: