I have 32 GB RAM, So how to configure SQL server memory and how to monitor which will be using high memory and how to resolve it. Please help me with anyone.
Assuming it is a dedicated machine for SQL Server, you should leave about four to five gigs for the OS and configure the SQL server max memory to the rest. So in your instance, configure it to use a max of 27,000 MB. There are more details and step by step instructions here.
SQLOS will grab the memory it needs, up to the maximum you have configured it for. SQL OS does its own memory management, so once it acquires memory from the Windows OS, it will not release the memory back to the OS. So you may see that SQL Server is consuming a large amount of memory even when it is idle. This is quite normal and to be expected.
I suggest leaving yourself some RAM, if possible, in case a SQL emergency comes up later. In other words, don't immediately all possible RAM to SQL at the start. What do you do then if a situation comes up where SQL is having RAM issues? You'd have no reserve RAM to give SQL then.
Start by allocating say 22G or 24G for SQL. Review memory usage in SQL. If SQL indicates that it's short of memory, allocate more of it to SQL.
If you need to, add memory to the server to give SQL more RAM. RAM is the cheapest, easiest way to get better performance from SQL Server (or most any other db for that matter).
1 Like