SQL 2005 64 bit Consuming RAM Rapidly (Please Help)
-
I am running SQL 2005 Enterprise 64bit on a Windows 2008 Enterprise server 4 proc's 32core with 32 gigs of RAM. I just migrated my databases to this new server from a 32bit Windows 2003 system. If I go to task manager and the performance tab the Memory is being consumed very rapidly. From 8am to 8pm it will consume 18gigs of RAM. I have AWE disabled in SSMS and I have lock pages in memory set to the account that I am running the SQL Service under. I need to find a way to lock SQL down to only using 20gigs and not allow it to page to the disk or the processors. I have done research and I have found conflicting information. I know using 64 bit AWE, POE, and /3G does not apply but I found an article that says if the locked pages in memory has the account tied to it then the AWE will work on a 64bit system.
The only way for me to release the memory is to restart the SQL Service as everyone knows.
Please help
Any suggestions will be helpfull at this point in time
-
AWE settings have no affect on 64 bit isntallations.
by default, SQL will consume 100% of the memory, or up to 100% of the maximum you set.
What can be a little misleading is the default setting is for more memory than any machine I've ever heard of:
change that setting on your sever to be the maximum you want SQL to use.
for example, if this was a dedicated machine for only SQL, set it for , oh 29 to leave 3 gig for the operating system.
if you are using it for a virtualized instance, each instance would be like 1 or 2 gig max, for example.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
jason.nodarse (2/21/2012)
I need to find a way to lock SQL down to only using 20gigsYou can set the max server memory to 20 GB for SQL by executing the following:
EXEC sp_configure 'max server memory (MB)', '20480'GO
RECONFIGURE WITH OVERRIDE
GO
-
Can you please provide me the procedure to do this?
-
Lowell and Jeremy have both explained how to do it, one using the GUI, one using T-SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
When I do a sp_configure it does not list out the max in memory Can someone give me the command so I can see if I already have it locked down to something. The previous system had it locked down to 18gigs and I used doubletake to sync so I believe this system may be locked down seeing those settings were replicated to this system. I know that the server already exceeded the 18gigs so I am not sure.
-
JeremyE (2/21/2012)
You can set the max server memory to 20 GB for SQL by executing the following:EXEC sp_configure 'max server memory (MB)', '20480'GO
RECONFIGURE WITH OVERRIDE
GO
You don't need Override. Override is when you want to set a configuration option to an invalid value or a non-recommended value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
jason.nodarse (2/21/2012)
When I do a sp_configure it does not list out the max in memory Can someone give me the command so I can see if I already have it locked down to something.Check on the server properties page that Lowell has shown in his post. If the max memory is configured, that screen will show the configured value, if not it'll show the 2TB that is default (as in Lowell's post)
The previous system had it locked down to 18gigs and I used doubletake to sync so I believe this system may be locked down seeing those settings were replicated to this system. I know that the server already exceeded the 18gigs so I am not sure.
Unless doubletake copied over the master database, the config settings would not have come across.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
jason.nodarse (2/21/2012)
When I do a sp_configure it does not list out the max in memory Can someone give me the command so I can see if I already have it locked down to something. The previous system had it locked down to 18gigs and I used doubletake to sync so I believe this system may be locked down seeing those settings were replicated to this system. I know that the server already exceeded the 18gigs so I am not sure.Here is the full code:
sp_configure 'show advanced options', 1GO
RECONFIGURE
GO
EXEC sp_configure 'max server memory (MB)', '20480'
GO
RECONFIGURE
-
Yes it did copy over the master. I know on a 64 bit system enabling AWE is worthless so I unchecked that after the server was brought online and I set the Service account for the lock pages in memory and that was the only tweaks I made but this server is just consuming the ram at a rapid pace.
-
That's normal, expected, documented behaviour. SQL will take as much memory as it is allowed to, up to max server memory (plus a small amount of non-buffer memory)
p.s. If locked pages is enabled, task manager is useless for monitoring SQL Server's memory usage. It will show a completely wrong figure. Use target and total server memory in perfmon.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Other than locking the pages in memory and setting the max amount of ram consumption in SQL are there any other memory tweaks that anyone is aware of for a 64 bit SQL 2005 system?
-
I'm a little worried that you fundamentally don't know about max memory in SQL. As said above SQL will use ALL the memory it wants if you don't expressly limit it. This is normal so the concern of it growing rapidly is unfounded.
You seem to have a pretty big box, I'm curious why you want to limit it to 20GB? What else is running on that machine? If nothing I'd probably set it to between 28 and 30GB no need to waste memory..
CEWII
-
Elliot, My previous server was a Windows 2003 enterprise server 32 bit with 32 gigs of RAM and it would take a full 5 days for this server to consume 13 gigs of RAM running the same databases and jobs. This server is only a DB server and nothing else is running on it. Now that I have migrated to a Windows 2008 server 64 bit and SQL 2005 64bit this new server is consuming 16 gigs in one day and I have recycled the service already seeing I do not want the system to run out of Physical memory. When I looked at the Physical memory that was available there was 700mb left and the server was consuming 21gigs of RAM and it was running for a day in a half prior to me restarting the SQL Service to flush the memory.
I used Double Take to sync the data from the 32 bit system to this 64 bit system and this carried over the Memory settings that I had configured seeing I mirrored the MASTER over so all the settings carried. After bringing the 64 bit server online I went and set the lock pages in memory to the SQL Server Service account and I went into SSMS and unchecked use AWE seeing it is not going to work running on a 64 bit system from what I have read. When I do a sp_configure I see that the setting has carried over to limit SQL to only use 18gigs but what I observed was that it exceeded the 18gigs that I allowed and was paging to the DISK as designed. My concern is that the server will run out of Physical memory at some point and cause an outage seeing it is consuming the same amount of RAM in one day as my previous 32 bit server did in one week.
I can't risk this server causing any outages so that is why I made the choice to recycle the service to recover the memory but I do not want to be doing this on a daily basis.
-
SQL will not page to disk when locked pages is enabled. Locked pages means DO NOT swap memory into the page file.
Are you absolutely, 100% sure that it's SQL paging not some other app?
How are you monitoring the paging?
Don't use Task Manager to monitor SQL's memory when locked pages is enabled, it gives a wrong figure. In fact, don't use Task Manager at all, half the figures show are misleading unless you understand exactly how they are computed
Are you seeing low available memory? (perfmon, not task manager)? (rough figure for low is < 300MB) If not, stop worrying and stop restarting SQL that just makes it do more work to repopulate all of those caches.
If SQL is using 18GB and there's 32GB on the server, what's using the rest? The OS does not need 14GB of memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply