September 10, 2009 at 9:45 am
Hi. I've just upgraded from 2000 to 2008 on a test machine. On our production site, we run SQL Server 2000 consuming an average of 1.4G of memory. On the machine we just upgraded to, where there's no traffic running SQL Server 2008, it's consuming 4GB. All that has been ran after a fresh installation and migration of the database, are SQL jobs that backup databases. Right now there's no queries running or jobs.
Any thoughts why 2008 consumes more memory when there's queries running on it?
September 10, 2009 at 12:14 pm
why? because sql love memory 😉
you can use server properties to limit memory usage:
SSMS -> object explorer -> Right click on the server -> Properties -> Memory page
there are:
minimum server memory (in mb)
maximum server memory (in mb)
use it if you wish.
you can do same task using:
sp_configure 'max server memory (MB)',
go
reconfigure
September 10, 2009 at 12:15 pm
bounce SQL server and see if it is still consuming that much. If so then check the actual min and max memory configuration settings.
The probability of survival is inversely proportional to the angle of arrival.
September 10, 2009 at 1:29 pm
Thanks for the reply guys! Much appreciated.
I've reset the server and get the same thing after a while. SQL Server 2000 had the max at 4GB. I've set the max for 2008 at 4GB and its already consuming 2GB without any traffic on it. It just seems that 2008 is naturally consuming more.
September 10, 2009 at 1:59 pm
sql grab as much as it can . later if it need to relase it to other resources it will but in profmon under memory manager check to see target and total server memory. it will help to indentify any bottle neck of resoucres
:crazy: :alien:
Umar Iqbal
September 10, 2009 at 2:24 pm
Umar is correct check the target and total memory then add page life expectancy.....should be around 5000 sec.
This the amount of time a page stays in memory, if you are seeing number that are lowering then that then try and reset max and min memory. If you still not able to hit that number then add more RAM.
Assuming that you are not running anything on the server besides SQL you need at least a gig for the OS but more likely about 2. this will only leave 2 gig for SQL not much.
I am assuming that this is a small DB, if it is say 6 gig then I would add 4 gigs of ram leave 2 gigs for the OS and 6 for the DB then your entire DB will be memory most of the time..
good luck
September 11, 2009 at 9:44 am
Thanks guys! I ran the checks and everything seems fine. Is it safe to conclude that SQL Server 2008 will try to consume more RAM than 2000 if given the chance? (I saw this because I don't see 2000 taking up as much memory.)
September 11, 2009 at 10:52 am
Yes, you can probably assume that. SQL Server 2000 Standard Edition could not use more than 2GB of memory, regardles of how much was available on the server.
SQL Server 2008 (All Editions) can use as much memory that is available on the system. So, yes - SQL Server 2008 will use more memory.
It gets even worse if you are working on x64 hardware - because now SQL Server will use that memory without having to enable anything else and will also use it all unless you explicitly set a max memory for the instance.
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
September 11, 2009 at 11:22 am
Thanks for the response! Strange that I have SQL Server Enteprise Edition, which should be able to access 4GB.
September 11, 2009 at 11:49 am
Jess (9/11/2009)
Thanks for the response! Strange that I have SQL Server Enteprise Edition, which should be able to access 4GB.
Well, that depends - if you are running on x86 hardware, by default the max memory available to applications is 2GB and even Enterprise Edition won't take any more than that (in fact - it's more like 1.7GB). To use more than that on x86 you need to enable the /3GB switch for systems with no more than 4GB of memory, and PAE in the boot.ini and AWE in SQL Server for systems with more than 4GB of memory.
On x64 hardware - SQL Server will use it all, if allowed.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply