Server memory usage

EXEC sp_configure 'show advanced options', 1 -- must be in an advanced mode

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'max server memory (MB)', 512 --make the actual change 

RECONFIGURE WITH OVERRIDE 

--The RECONFIGURE WITH OVERRIDE is necessary in order for the change to take effect immediately

GO
RECONFIGURE;
GO

-- Set ‘show advanced options’ back to default:

EXEC sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

GO

-- check the current max memory usage

EXEC sp_configure 'max server memory (MB)'

While memory setting is recommended to remember that there are other processes that should run on your computer and operating system itself must memory. Recommended to leave 1-2GB to the operating system.

http://msdn.microsoft.com/en-us/library/ms178067.aspx

http://blog.colinmackay.net/archive/2008/07/20/2996.aspx

http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/