SQLServer 2000 Memory usage
-
hello,
Can anybody tell me if SQLServer 2000 Entrepise Version deals with more than 2GB of Memory or does it only takes up to 2GB of memory and the remaining isn't used?
-
Yes SQL 2000 Enterprise edition deals with more than 2 GB, however, you also need to look at the memory availability for the OS.
Assuming you're on advanced server or above you'll want to be investigating the /3GB and /PAE switches for boot.ini (can't find the link right now cause i'm on someone elses PC).
Hope this helped, if not feel free to ask again.
-
i was told by some guy in microsoft that the usage of the /PAE must follow some rules and that doing it with the /3G parameter can do more wrong than right.
Can you explain me how does the /PAE parameter affects the SQLServer?
I'm already using the /3G and what is happening is that my 2 Xeon processors sometimes go to the top but my physical memory usage stay below 2GB.
Any ideas?
-
The /PAE switch is only required when you have more than 4GB of system RAM. If you have between 4GB and 16GB of system RAM, you should use both the /3GB and /PAE switches (although the /3GB switch isn't required, it will give SQL an extra GB of RAM). You must also enable "awe on" in SQL Server, and set the "max server memory" option to
If you have 4GB or less, you should just use the /3GB switch. SQL will manage it's memory usage up to a total of 3GB.
-Dan -
Couldnt have put it better myself (but thats probably the wines fault
) -
umm .. that's not always the case with /3gb switch. I've found that with the 3gb switch enabled there can sometimes be problems with xml operations. In this instance only using the /PAE switch with 4Gb ram and turning on awe, and setting memory works better. In general terms I tend to avoid the 3gb switch - if you don't do xml stuff then it seems not to be a problem
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks for that Colin, it's a handy thing to know
We're working on 64-bit Itanium2 servers here so I don't need to use boot.ini switches but i'm sure it's going to come up sometime in QA or development.
-
When you use the /3gb switch it restricts the Windows kernel address space to only 1GB and this is why you can experience more performance problems with this option set.
The kernel address space is used for all kind of things such as process tracking, caching etc. so the more processes you are running the more work (mainly I/O) the OS has to do due to it having less space to cache and buffer things. I suspect this is why it is more noticable when doing XML work because of the overhead that this sort of thing creates. All in all I would only use the /3gb switch if the server was a) dedicated to SQL b) had only a single instance and c) didn't use any of the 'advanced' features such as XML.
But, as always, the best method is to try it out in your enviroment and see what gives...
Malc
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply