We have just installed SQL Server 2012 and implemented AAG. It is a 2 node cluster on Windows 2012. The servers are VMs using VMWare 5.1. For some reason, I am seeing the memory usage max out at 15 gigs. The server has 16 GB of memory and I have set the max memory in SQL to be 12gb and the min memory server to be 8 gigs. Is this normal for SQL to max out on the memory like that? There are no other applications running on that server and at the time there is very low activity.
Asked
Active
Viewed 1.6k times
3
-
Where are you validating that 15 GB is in use? And are you sure your max server memory setting is in effect? It is not enough to just run `sp_configure`, you also must issue a `RECONFIGURE;` command. If you run `DBCC MEMORYSTATUS;`, how is the memory distributed? – Aaron Bertrand Aug 08 '13 at 17:39
-
I looked at the task manager and it said i was using 15.2/16 GB of memory. I did run the reconfigure statement when I set the MAX and MIN memory and when I checked the max server memory, the run value is 12582912 KB. What is it that I need to look for when I run dbcc memorystatus? – user2665372 Aug 08 '13 at 18:59
-
4Stop looking at task manager - it is a big fat liar. Look at actual performance counters or DBCC MEMORYSTATUS. – Aaron Bertrand Aug 08 '13 at 19:00
-
The Total Server Memory(KB) is 14772608 and Target Server Memory is 16776688. – user2665372 Aug 08 '13 at 19:04
1 Answers
4
SQL Server will be very memory 'hungry' and take what it can from the OS within the limits set in the properties. You haven't been totally clear as to whether your 'memory usage' of 15GB is for the server as a whole or for the sql server itself? I would be surprised if it was using 15GB after you have set the max memory to 12. If your SQL server is using 12 and the rest of your OS is using 3GB then this sounds very normal to me.
Determining what memory SQL server is using is not as simple as looking at the Windows Task Manager. As I mentioned, it will take what it can from the OS to avoid any overhead in claiming more memory later, even if it isn't using it all yet.
We have servers using up to 64GB of memory and when we tend to allocate SQL Server 4GB less than the server total, leaving that 4GB for Windows. I would then expect most servers to only show 1 or 2GB free memory at all times. This does make it more difficult to understand whether memory is a bottleneck within SQL Server, for that you will need to use the performance counters as user2665372 states. See this for further details: How much RAM is SQL Server actually using?
