December 19, 2008 at 10:01 am
We wondering whats the best approach to this situation?
We have both instances running (SQL2005&2000) on:
Windows 2003 server
4gig of ram
Dual Xeon Processors
Raid 1 mirror
Raid 5 (4 scsi 146gig 10k)
Page File
Applications installed:
HR suite
Accounting suite
Anti Virus
Backup Software
Looking at our system resources.... sql2000 & sql2005 are both taking up about 1.5 gigs of ram each.... leaving a gig left for other processes in which in total we have about 500 megs of resources left after all the other applications are running. Both of our 3rd party applications are running real slow... one is a java app that runs off the server thru IIS(installed on the server) and the other is a per seat licensed app on each client machine... I'm thinking we need to pump in more RAM into the box and investigate where our resources are going. Others in the office declare it's a page file problem (our current Page file is set to 6gigs which is the 1.5x minimum recommendation) due to we have limited space on the volume it's on and that the software vendor did an incorrect install. So we rebuilding this server with a similar hardware configuration to see if anything changes.
One of the issues we had before installing SQL 2005 and the other 3rd party software was that the java app and sql2000 ran real slow already with 2gigs of RAM. Any thoughts?
December 19, 2008 at 10:18 am
You did not note if this was a 32bit or 64bit machine.
Unless your applications are AWE-aware, adding memory is only going to help SQL - and it will only help SQL a little.
I'll wait to find out if the server is 32bit or 64bit before posting anything else.
December 19, 2008 at 10:40 am
32bit... and no we do not have awe enabled.... we are running 2k standard and 2005 standard
December 22, 2008 at 4:25 am
Without AWE enabled, adding memory will do nothing.
My first suggestion would be to move the SQL servers to another machine and make it a 64 bit machine. You are probably over-taxing this server.
It's pretty easy to determine if there are memory bottlenecks using PerfMon on a 32 bit machine with AWE disabled. I recommend you search around google for performance monitoring SQL servers for some tips.
December 22, 2008 at 4:49 am
Hi,
Windows will not use all 4GB RAM, untill you enable the \3G or \PAE.
In your case you can.
>> Enable the \3GB or \PAE in OS http://www.microsoft.com/whdc/system/platform/server/PAE/PAEdrv.mspx
If you using Windows Server 2003 Standard edition then it support only upto 4GB RAM (3GB after \3GB and 4GB after \PAE otherwise 2GB)
>> Restrict the SQL server memory usage to 1GB (or max 1.5GB) each for both instances, if you use Server 2k3 standard edition. That way you can make available the atleast 1GB for other processes. (user sp_configure for setting memory restriction)
>> If are using Server 2K3 ent. edi or Data center then you can add more RAM.
Best Regards
Nitin
Regards,
Nitin
December 22, 2008 at 7:31 am
64bit would be nice but that would mean new hardware and software which is not in our budget for 2009.
We are running 2003 r1 enterprise. So thats a definite plus if we have to load in more memory. One question though is should we use set affinity and set priority so that we are splitting the load between both processors? Currently it looks like we are only pointing to one processor due to that we have processor licenses for our sql databases. Overall thanks for the help, I'll try all your suggestions.
December 22, 2008 at 7:38 am
Again, the only way you can use any additional memory would be to enable AWE. This may help alleviate the problem, but it is pretty likely that the 3rd party applications are not AWE aware so SQL is the only thing that can really benefit much from additional memory. SQL can only use AWE memory for a limited amount of things, so it will still have to use physical memory. You also cannot specify to use AWE vs. physical memory, you can only set overall memory limitations and SQL will swap out physical, AWE, and virtual memory where appropriate. SQL 2005 is reasonably good at this, but SQL 2000 is not very good at it.
As far as the per-processor licensing. If you have purchased a single processor license, SQL will not restrict the number of processors it uses. If you install a single processor license on a 2 processor server, it will use both processors - you will simply be in violation of the license agreement. If you have a multi-processor server that you are running SQL on, you need to purchase the appropriate number of per-processor licenses or use server/CAL licensing.
December 22, 2008 at 8:42 am
Right we are aware of that... the question is it legal to point one sql server to run only one of the processor and point the other sql server to run only on the other processor? I know there is a difference between dual/quad/8 core (still 1 processor) verse two single core processors in which it is a gray area right now.
We did enable DEP on the server... because we have sp2 running.... do we still have to put in the /PAE
December 22, 2008 at 8:49 am
The answer is no, you cannot use processor affinity to try to avoid the licensing cost. You have to physically remove the processor from the server.
I am not sure what you mean by this:
I know there is a difference between dual/quad/8 core (still 1 processor) verse two single core processors in which it is a gray area right now.
MS per-processor licensing is by socket, so an 8 core processor is a single processor license.
December 22, 2008 at 8:53 am
You may be able to get away with disabling a processor in the server BIOS rather than removing it...
MS allows for a virtualization layer to allocate resources. If you created two virtual servers on this machine and allocated one processor to each server, you could use 2 single-processor licenses. Of course, their support of SQL on 3rd party virtualization software is "sketchy".
December 22, 2008 at 8:56 am
ok... then maybe we'll then move all the other 3rd party app processes to use the other processor.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply