Memory configuration for two instances of SQL Server
-
I have a log shipping server that has two instances of SQL. One is for a production system, the other is for log shipping. In the next few week, the server needs to host a production database on the nameded instance.
My question is how to best configura the memory setting for two instances of SQL Server. The server is running advanced server 2000 and has 8 GB RAM. Right now, it is set for 5 GB AWE memory for the default instance of SQL Server and the named instance is running with dynamic memory configuration.
-
First your SysAdmin should have place /PAE switch in the boot.ini file. This switch allows the OS to recognize memory past 4 GB. Then setting your Max memory or the AWE setting will grab all the memory except 128 MB for the OS. With AWE turn on the second instance will try using 5 GB for a total of 10 GB. 2 GB short which will cause the server to use page file (pages/sec – Perfmon-memory). The OS needs a min. of 1 GB just for managing AWE not counting typical OS tasks. AWE is typical use for reducing I/O and getting past the 2.1 GB application limit. I do not know in your case what you are trying to accomplish but here is my suggestion. Use Perfmon and AWE counters to adjust as needed.
3 GB for each instance
1 GB for OS task
1 GB for managing AWE (OS)
-
"With AWE turn on the second instance will try using 5 GB for a total of 10 GB. 2 GB short which will cause the server to use page file "
This is what i understand. Windows distributes 4 gb of virtual address space into 2gb for applications and 2 gb for kernel. If enough memory is not available, it will page out memory and assign accordingly. When you enable the PAE switch, the additional memory is available as an extension to the existing memory. this memory cannot be shared and is dedicated to the application. say if you have 4 gb of memory on your machine and and you install two instances, below is how the memory would be allocated.
inst1 : max upto 2 gb of virtual address space.
inst2 : max upto 2 gb of virtual address space
kernel : max upto 2 gb of virtual address space.
Windows will manage the above on its own by paging.
Ref : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
You can have the /3 gb switch to let application use 3 gb of virtual address space and only 1 gig to the kernel.
If you enable pae switch and then awe from your application(SQL Server) the additional memory will not be shared and will be dedicated to a specific instance. so the max. memory you can allocate from the pae pool is only 4 gb(if you have 8 gb of memory) and this memory never gets paged out. the paging is only seen for the virtual address space.
I think it will depend on what actions you would be performing in both instances and allocate memory accordingly.
-
OK. The goal is to equally share the memory resources accross both instances.
We all agree that for this 8 GB box with two SQL instances:
In boot.ini: have the /PAE switch
and if the memory is divide as:
3 GB for each inst1
3 GB for each inst2
1 GB for OS task
1 GB for managing AWE (OS)
8 GB total.
Next question:
And BTW, this is I am a little confused.
What is the memory setting inside SQL Server?
In the SQL Serve Properties dialog, memory page tab.
The setting is now listed as: Use a fixed memory size (MB): 4096
The memory range on the fixed memory slider, 16 MB .......7423 MB
So, it looks like for the 3 GB for each instance design,
that this setting for both instances would be set to: 3072
Please review.
-
exec dbo.sp_configure 'min server memory',3072
exec dbo.sp_configure 'max server memory',3072
exec dbo.sp_configure 'awe enabled',1
reconfigure with override
go
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
After the step in QA, reconfigure with override
Will the SQL Server instances need to be cycled to pickup the configuration changes
?If boot.ini had the /PAE setting, then it looks like the server will not require a cycle. Or, will it?
-
W2003 should have the PAE enabled by default, however you might need to check with your platform support/ see how much memory is reported in sysperfinfo or the sql error log.
You may want to restart your sql service to be absolutely sure. Check in sysperfinfo table to see how much memory each instance is using.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply