One of our client reported that SQL Server is using almost 100% memory on the server. After analysis we found that what our client observed was actually designed behavior of SQL Server. When Microsoft SQL Server is started, it slowly starts using memory and after some time you may observe that whole memory is used by SQL Server Service. In Task Manager and Performance Monitor it will show SQL Service is using the whole memory. If you search on net, you will find very few articles which actually explain the cause. This behavior can be changed by setting Min Server Memory and Max Server Memory in SQL Server. Most of the time it is left to default value which leads to the observation that 100% memory is used by SQL Server.
To set the memory settings, login to SQL Server Management Studio—>Right click on the DB server—>Properties—>Click on Memory
I normally suggest to set Minimum Server Memory to 8192 MB and Maximum Server Memory to a limit depending upon the physical memory available on the server. Exclude atleast 3 GB memory for OS operations.
Having said that it does mean the SQL Server will not show memory usage problems but in that case we have to find the database which is using most memory and then find within Database the objects using most of cache and buffered space. Will write in detail in next post on how to investigate real memory issues.
For more details please refer to http://support.microsoft.com/kb/321363