SQL Server Memory Issue

Every one running SQL Server has always had the same issue of memory. Whatever the extra memory is given to windows, it is all eaten by the SQL Server alone. No matter what the process is going on, the memory takes much resources for all related to SQL Server. How can we control the memory issue. Well there are some possibilities from SQL Server that we can restrict the server to occupy the server memory.

--To check for the existing memory usage
use master
EXEC sp_configure 'max server memory (MB)'

By default the server won't display the advance options to enable this service. So follow the steps below to view the memory usage.

--To display advance option
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

After this you can run the below query to see the usage.

use master
EXEC sp_configure 'max server memory (MB)'

Then use this to change the server memory.

--To change the maximum server memory
USE master
EXEC sp_configure 'max server memory (MB)', 6144
RECONFIGURE WITH OVERRIDE

Then again you can check the usage using the query which we have see earlier. Once you are happy then you need to switch off the advanced options. And for this use the following.

--not to display advance option
USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Give a try!