SQL Server Memory Usage - Task Manager

In this article we will see and learn SQL Server Memory usage and from performance perspective what amount of maximum SQL server memory should be allocated .

"SQLServr.Exe is consuming 80% of memory. Can you please help to reduce the memory utilization to less than 50% and check whats the problem with SQL ? "

If you are a SQL DBA, I guess you would have faced above question many times. So let me know your responses to such a question in the post below :).

Usually, SQL Server utilizes as much as RAM that is available on the server. This behavior is not a bad thing and has been specifically designed to be so. Initially, when a SQLServr.EXE started ( ie., when SQL Service is started ) , SQL Server consumes just a few MB of RAM. As the SQL Server starts getting utilized or when queries are getting fired and result sets are returned, the memory utilization starts increasing. When a query is executed, SQL Server retrieves data pages from the disk, loads them into memory ( RAM ) , performs the processing and returns the result set. As the data pages are getting loaded to the memory, the memory utilization increases. After the query execution completes, SQL Server doesn't release the memory by flushing data pages loaded. SQL Server retains them on the memory so that the next time when the same or similar query is executed, SQL Server need not fetch the data pages from the disk and instead directly fetch it from RAM, which would make the query execution much faster.

So a Production Server in use for few days or weeks ( depending upon its usage or RAM available), is expected to use most of the memory available in the server. On 64 BIT database server, SQL Server is free to use the entire memory available while on a 32 bit database server, SQL server can use only up to 50% of RAM if AWE is not enabled. Usually, the windows operating system consumes 1 GB of RAM at least. So normally on 64 BIT server, one can allocate, 75 to 80% of RAM to SQL Server, 1 GB for the operating system and the rest for other process on the server. Even if there are no other process on the server, it is better to have a few GB of RAM left so that there is no tussle for memory between SQL Server and Windows.

One can allocate memory to SQL Server from Management Studio. Right click on the registered server on the management studio, pick Memory tab. Set the amount of memory in KB you would like to allocate to SQL Server Process. Click OK and the maximum limit for memory utilization is set. The above operation doesn't require a service restart.

Coming back to question, the above details explain why SQLServr.exe on the task manager is having a high memory utilization and why it absolutely normal to have that. If SQL Server's memory allocation is reduced to below 75%, then it can hurt SQL Servers performance as queries will more frequently require to fetch data from disk instead of memory. So it is good to have 75 - 80% allocated to SQL Server and normal to have a 85 to 90% overall memory utilization on DB Server.

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to imjhachandan.com