September 25, 2008 at 1:15 am
Hi Guys
Our production server specs are:
Quad Xeon 3 Ghz (Hyperthreading, 4 processors reflect in the task manager.)
6 gigs of ram
Windows 2003 server
SQL 2005 Standard edition
Now, the operating system only sees 4 of the 6 gigs of ram. Of those 4 gigs SQL has commited 2.7 gigs. SQL standard can take as much RAM thats available on the server box.
If i up the amount of ram availale to SQL, will it:
A.) take the additional RAM from the memory available to the operating system and knock the server over?
B.) take the additional RAM from the 2 Gig spare pool of memory that exists on the server?
C.) is using the AWE option a good way to go?
Regards.
September 25, 2008 at 5:19 am
I am assuming this is a 32 bit server.
Enabling AWE at the OS and SQL Server level is probably what you will want to do for now. Depending on what else is on the server, there is also a -3gb switch for the OS that will allocate 3gb of memory for actual use, 1gb essentially gets dedicated to the OS for memory map lookups and some other operations (usually it is 2gb). You usually only use the 3gb switch if the server is 100% dedicated to a single application (like SQL Server).
AWE memory is not as good as regular memory. So, when SQL accesses AWE memory, it goes through a memory map to find the physical location (because 32 bits can only address 4gb). So, when SQL uses the physical memory it will be faster. The AWE memory can only be used for certain operations - if I am remembering correctly, it will use it for data cache, but it cannot use it for plan cache. So, it is not anywhere near as good a boost as being able to access physical memory.
This is all pretty complicated, so search google a bit for more detail. The real bottom line is that you need to start thinking about migrating to a 64 bit OS. This will make a huge difference if you are getting memory pressure problems with your current configuration. Honestly, the migration is pretty painless unless you have a great deal of dependancy on SSIS running on the same server.
September 25, 2008 at 6:07 am
Thanx for the reply.
This is a 32 bit server. The business has set a budget for a server upgrade and the 64 bit idea has been tossed around a little. You mentioned that migrating SSIS packages from 32 to 64 with dependancies on the same server might cause some pain. What problems did you find?
September 25, 2008 at 6:42 am
SSIS can be a bit tricky. Visual Studio is 32 bit only, so if you do any testing or development on the server itself, you will be doing so in a 32 bit environment, but when you run a package outside of VS, it will run in the 64 bit environment.
The biggest issue is drivers. The MS driver for oracle is 32 bit only - so you have to use the native oracle driver. MS does not have a Jet driver for 64 bit - so if you have exports to Excel you have to run in 32 bit mode or work around the unavailable driver. It's almost a 64 bit world, but not everything has caught up. On the other hand, running the SSIS packages from a second (32 bit) server it a pretty good option if you can afford the licensing, or you can even run SSIS packages from the 32 bit runtime on a 64 bit machine to get around some of the problems.
It requires some testing, but it is really the only area in which the switch to 64 bit is complex. Everything else (in my experience) just worked.
At this point, it is hard to find a reason not to move to 64 bit for a MS SQL server.
September 25, 2008 at 8:50 am
If you were to Consolodate 2 servers together, say a datawarehousing machine and a production machine into one server installation, and you run 64 bit SQL. Can you designate (Via SQL) which database may use which CPU? So if you running something intensive in the back office it doesnt affect the production side of the business.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply