August 18, 2009 at 10:00 am
Hi Guru's,
Hope you can help.
We have a new 64-bit Win 2003 SP2 server, with SQL Server 2005 SP3 installed.
Running for a few weeks and we are performing some test migrations.
Tets maintenance plans were created last week and the integrity check on one db ran on Sat 15th 10:00am. This completed successfully at 10:05am.
Using our Idera Diagnostics Manager I have noticed today that the SQL Memory Used is over 75% constantly. I have gone back through the history viewer and it was recorded at 10:06am on Sat 15th that Memory Jumped from 40% to 75%, at that time Disk Queue length for G (data drive) also spiked to 623.
I have checked logs, event viewing and nothing showing up apart from the above dbcc command that completed as stated. At 10:06 when this state change was repoted there were no active or user sessions on the server at all.
Disk queue length dropped back to normal by 10:12 the next snapshot from Idera, but memory is still at 75%.
Not sure what has caused this? any idea anyone?
We are migrating our Prod dbs over next week and need to know what has caused this.
Thanks
Oraculum
August 18, 2009 at 1:20 pm
SQL Server will allocate memory up to the max memory setting and keep that memory. It does not release the memory. What you are seeing is normal behavior.
On x64 hardware - you want to make sure you set max memory and, if you are running Enterprise Edition of SQL Server you need to set lock pages in memory policy for the service account running SQL Server (Group Policy Editor).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 19, 2009 at 2:08 am
Hi, you say this is normal behaviour but no other server holds this memory as 'used'. All our servers have min and max memory set and lock pages in memory setting.
All other servers may spike on SQL Server Memory Used %, but they always release and it drops back below the defined threshold for the alert system.
There are no users or activity on this new server as its being setup for migration next week.
I am trying to find out why at 10:06 the memory jumped to 75% and is still showing 75% used, no change. This compared to all our other servers is not normal behaviour at all...
The allocation of memory is fine i see this on all servers, but keeping this memory as 'used' is not surley..?
We are rebooting anyway today, so I guess i will have to see if this happens again..
Oraculum
August 19, 2009 at 7:56 am
SQL Server will only release memory back to the OS when the OS needs that memory. On dedicated SQL Server machines - that will never occur. If you have a max memory defined, SQL Server will not take more than the max memory setting for the buffer pool - but does take more than that, just not a lot more.
I have never seen SQL Server ever release memory once it has allocated it, and I wouldn't want it to - because databases perform much better with a lot of memory.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 19, 2009 at 8:22 am
Exactly.
Your other SQL Instances are having memory stolen from them by the OS which is under memory pressure.
My favorite SQL Server memory article of all time says that
SQL Server 2005 does respond to memory pressure both when lock pages in memory is enabled and disabled
It makes no difference whether it's 32 or 64 bit, either
However, the official docs and Slava Oks are at odds with this.
Locking pages in memory is not required on 64-bit operating systems.
(from http://technet.microsoft.com/en-us/library/ms190730%28SQL.90%29.aspx)
I did indicate that on 64 bit platform we recommend to enable SQL Server to use locked pages in memory
(from http://blogs.msdn.com/slavao/archive/2005/11/15/493019.aspx)
I use lock pages in memory on both x64 and ia64.
Craig Outcalt
August 19, 2009 at 10:35 am
Still think i'm being misunderstood, the memory allocated is 24GB this is fine and as expected as its the Max Server Memory...
The problem is that 75% of that 'allocated' memory is constantly being used i.e. 18GB is in use now by sql server, even though nothing is running. If you look at perfmon logs the sql memory in use never drops on this server..
Server 32GB
SQL Allocated 24GB
SQL In Use 18GB ??
OS is not using more than a couple ever, and it has 8GB to itself anyhow...
Oraculum
August 19, 2009 at 10:49 am
oraculum (8/19/2009)
Still think i'm being misunderstood, the memory allocated is 24GB this is fine and as expected as its the Max Server Memory...The problem is that 75% of that 'allocated' memory is constantly being used i.e. 18GB is in use now by sql server, even though nothing is running. If you look at perfmon logs the sql memory in use never drops on this server..
Server 32GB
SQL Allocated 24GB
SQL In Use 18GB ??
OS is not using more than a couple ever, and it has 8GB to itself anyhow...
Nope, you are not being mis-understood. That is normal behavior for SQL Server - and the 18GB of usage is for the buffer pool. SQL Server reads the data from the database into memory - and accesses that data from memory. The more memory that is available, the more SQL Server will allocate for the buffer pool and load.
The fact that you have set the max memory for the server to 24GB - and SQL Server is only using 18GB at this point tells me that you might not need more than 18GB of memory for this instance of SQL Server.
Again, that is normal behavior and expected for SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2009 at 11:06 am
Jeffrey,
On x64 hardware, Microsoft says that the "Lock pages in memory" right is not necessary. From this Technet article:
http://technet.microsoft.com/en-us/library/ms190730.aspx
"Locking pages in memory is not required on 64-bit operating systems."
What makes you recommend it on x64?
August 20, 2009 at 11:22 am
Jeffrey Williams (8/19/2009)
SQL Server will only release memory back to the OS when the OS needs that memory. On dedicated SQL Server machines - that will never occur. If you have a max memory defined, SQL Server will not take more than the max memory setting for the buffer pool - but does take more than that, just not a lot more.I have never seen SQL Server ever release memory once it has allocated it, and I wouldn't want it to - because databases perform much better with a lot of memory.
Several incorrect statements there. It can occur on a SQL Server only box that the OS asks for memory back. Also SQL Server does give memory back, and you DO want it to or else your server could suffer incredibly bad performance or even literrally stop execution if the OS has a need for RAM that cannot be served.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 20, 2009 at 11:30 am
jcosta (8/20/2009)
Jeffrey,On x64 hardware, Microsoft says that the "Lock pages in memory" right is not necessary. From this Technet article:
http://technet.microsoft.com/en-us/library/ms190730.aspx
"Locking pages in memory is not required on 64-bit operating systems."
What makes you recommend it on x64?
Read my post above. Microsoft itself can't make up its mind on this.
Lock Pages also only prevents other apps (like another SQL Server Instance) from taking memory.
The OS can take whatever it wants.
Craig Outcalt
August 20, 2009 at 12:44 pm
TheSQLGuru (8/20/2009)Several incorrect statements there. It can occur on a SQL Server only box that the OS asks for memory back. Also SQL Server does give memory back, and you DO want it to or else your server could suffer incredibly bad performance or even literrally stop execution if the OS has a need for RAM that cannot be served.
Not sure where you think I was incorrect - I stated clearly that SQL Server will release memory back to the OS when requested. However, I have also seen on multiple occasions where an x64 system running SQL Server was starved of memory and:
suffer incredibly bad performance or even literrally stop execution if the OS has a need for RAM that cannot be served
specifically because SQL Server took all of the memory on that system and would not release it back to the OS when the OS needed it.
On a properly configured system, where you have defined the max memory to something less than the max available to the OS - SQL Server will never release the memory it allocates back to the OS. Mostly, this is because the OS does not need the memory, and that is because the system was configured to make sure the OS always has enough memory available. For example, on a 64bit machine with a single instance of SQL Server 2005 and 64GB of memory - setting the max memory to no more than 56GB of memory is what I would recommend to start. That leaves the OS 8GB of memory to manage the system and should never have to request that SQL Server give up any of the memory it has allocated.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2009 at 1:40 pm
There are lots of reasons for OS memory starvation outside of the max server setting.
MPA + connection memory + worker thread + VAS.... all of this memory is allocated outside the bpool.
Sometimes SQLOS can't release the bpool pages fast enough to to stop the implosion when things go wrong in these "external areas". This is because the memory clerks are starved and can't release the bpool memory that's causing the starvation.
It's usually around this time that the OS starts paging to disk and people start paging YOU!
best to leave lots for the Windows OS and SQLOS when sizing your max memory setting...
at any rate, the OP's sql server is fine... the other systems need more memory if SQLOS is paging out the bpool of one or more instances. Esp. if "lock pages in memory" is set.
~BOT.
Craig Outcalt
August 20, 2009 at 3:02 pm
the Technet article is incorrect (it wouldnt be the first time would it 😉 )
see the following Microsoft KB
http://support.microsoft.com/kb/918483
i always use lock pages on 64 bit systems, its even more important if you start seeing the message "A significant part of sql server process memory has been paged out........"
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 14, 2012 at 7:46 am
If you don't want to distribute so much memory on SQL Server usage, just lower the Max memory. You can also issue commands to clear buffer and procedure cache in server quiet time. (recommend you do so at maintenance window)
Jason
http://dbace.us
😛
November 14, 2012 at 1:30 pm
jswong05 (11/14/2012)
If you don't want to distribute so much memory on SQL Server usage, just lower the Max memory. You can also issue commands to clear buffer and procedure cache in server quiet time. (recommend you do so at maintenance window)
This is a 3 yr old thread!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply