3

Following a previous post I've written, I've configured my server with the following parameters:

Instance Parameters Configured

I put 22497 as MAx Server Memory, disable AWE, since it runs in 64 bit environment and it's a production server.

During a reboot, it starts from the min configuration, and slowly increase memory consumption until almost midnight that reaches the max memory configuration.

Until then if I monitor memory consumption, the task manager reports that memory increases to 25 GB of RAM. Yesterday we removed AWE, reboot the instance, and it started slowly increasing memory Until around 17:00pm , it was using almost 25.2 GB of RAM:

State at 17:00pm

Today after a reboot, it is now using 11GB.

I now, that in a previous post said this memory consumption is "Normal Behaviour" of SQL Server, but why it suddenly use more of the max memory I set as maximum? UPDATE 2021-03-31:

As for Today March 31, 2021 08:09AM, my server is using 25.3 GB of RAM:

Current memory usage

Using Aleksey Script This are the numbers SQL Reports is using:

Numbers of Aleksey Script

Resource Monitor says SQL Server IS using all the RAM:

Resource Monitor today

Yesterday at 17:00 it was using 13 GB

enter image description here

And this is the behaviour of the server, That's why I'm asking if this is normal or not, what is making the server use 25.3 GB if it's limit should be 22.5 GB (rounded per my calculations). This is the issue.

I know Microsofts literature says a lot of stuff, but I haven't get a clue of why this behaviour, I've already check Stored Procedures, Indexes, Jobs, etc.. and I don't see what is making the SQL Server use more of the assigned maximum server memory.

I disabled AWE, since this instance is running in a Virtual Machine in Hyper V, 64 bit Environment, (Windows 2008 R2 64 Bit, Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) and I can't figure what's going on with this behaviour.

2

MAX SERVER MEMORY worked a little differently before SQL Server 2012:

Starting with SQL Server 2012, Multi-Page allocations and CLR allocations are also included in memory limits that are controlled by max server memory (MB) and min server memory (MB).

Memory configuration and sizing considerations in SQL Server 2012 and later versions

5
  • that is not what OP is asking – Shanky Mar 30 at 18:52
  • Perhaps, but if not I can't really figure out what OP is asking. – David Browne - Microsoft Mar 30 at 19:03
  • Well, the issue is that if Max Server Memory is configured to 22497 MB, why it reaches 25.5 GB of RAM Consumption instead of the established limiits. I starts as the min server memory parameters states, but surpass the max server memory. – Zero Mar 30 at 19:17
  • 1
    I think the point David is making is that in SQL Server 2008, there is memory consumed by SQL Server that is outside the buffer pool, and therefore not limited to the max-server-memory setting. – Hannah Vernon Mar 30 at 19:54
  • And that is the question I answered. Max Server Memory doesn't control CLR memory or multi-page allocations in SQL Server 2008. – David Browne - Microsoft Mar 30 at 19:55
0

When Total used memory reaches over 25 GB of RAM again, you can check how much SQL Server consuming RAM, using Windows Resource Monitor (Memory tab)

Windows Resource Monitor

It is also possible that some other application consumes your memory, Resource Monitor can help to confirm that (or otherwise prove SQL Server overblowns the limits)


Also, check how much memory SQL Server is currently using, try stored procedure "MemoryManagerInfo"

https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Memory%20-%20MemoryManagerInfo.sql

Deploy it to any user database and run. Post the output to the question
It will be similar to below picture:

MemoryManagerInfo

This SP will help to confirm or disprove that your SQL Server is consuming over 22497 MB
Look for "Target Server Memory" and "Total Server Memory" values

Update:

Based on screenshots you added today to the question - 1) Windows Task Manager (what you posted is not Resource Monitor) says your SQL Server is using 23,281,112 KB which is = 22.2 GB

Not 25.3 GB ! Other 3.1 GB are just other applications

If you worry about your server starving off memory, reduce Max Server Memory in SQL Server to a lower number (20 GB, 19 GB, 18 GB etc.)

Resource Monitor

enter image description here

14
  • Suggesting RM to check SQL Server memory is not correct, if SQL Server uses LPIM it will not show correct value. – Shanky Mar 30 at 18:54
  • 2
    Resource Monitor is not a good way to reference memory used by SQL Server versus the Max Server Memory (MB) setting. Max Server Memory only limits the buffer pool size in SQL Server 2008. There are numerous other places that SQL Server can consume memory outside of the max server memory "maximum." – Hannah Vernon Mar 30 at 19:56
  • LPIM is disabled in this server, also the AWE is disabled in its parameters. – Zero Mar 30 at 19:58
  • @Shanky "Task Manager" really shows incorrect memory for SQL Server with LPIM -- but "Resource Monitor" actually shows true memory consumption by SQL Server, even if LPIM is turned on. Brent Ozar wrote it in some of his blogs. If you downvoted, please remove downvote, I will be grateful – Aleksey Vitsko Mar 31 at 9:24
  • @MaxVernon it was good to learn about 2008 and max server memory affecting only the buffer pool (Database Cache Memory), thanks to you and David ;) RM may not be the best way to compare SQL total memory vs Max Server Memory for 2008, right, but RM still helps to narrow down that it is SQL Server (and not the other applications) consuming more than Author set for it in Max Server Memory. My answer includes stored procedure that helps to see which part of SQL Server memory overblowns the limits, so answer is valid and useful. Please remove downvote if you can, much appreciated! – Aleksey Vitsko Mar 31 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.