January 28, 2002 at 10:44 am
we have two sql server instances on sigle server. One instace is sql7 another is sql2000. Server has 4processors of 500 MHz. 1GB Ram. Is it required to set manual memory configurations for two instances or shall i leave for dynamic memory.
Thanks
January 28, 2002 at 10:46 am
Haven't really worked with multiple instances, but I'd be inclined to let SQL manage memory.
Unless you ahve a good reason not to.
Steve Jones
January 28, 2002 at 1:23 pm
Microsoft's support for multiple instances with going with dynamic memory is supposedly up to par. I've not had issues with it running SQL 7 Default and SQL 2K Named on my workstation. With respect to a production environment, however, the only time we've had multiple instances on the same box is in a forced failover of an Active/Active cluster, and the time there wasn't sufficient to draw any conclusions.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 28, 2002 at 10:33 pm
In a multi-instance environment, each instance yields memory resources only to the operating system, and not to other instances of SQL Server. So, consider switching to "fixed memory" size when you have a production server that you can assign a high percentage of memory (75% for example), and a development server where you can safely allocate a lower percentage of memory(10%, for example).
Ideally, you should baseline the system to understand how your instances are using memory before you switch to "fixed memory" allocation.
I've run different configurations on 8-way, 2-node clusters on Datacenter with multiple instances and fixed memory allocation worked fine. Mostly, I just left the settings on "Dynamically allocate memory."
cabby2583
Always Learn!
January 29, 2002 at 7:17 am
While it is true that the multiple instances won't talk to each other directly and yield directly to each other, the way SQL Server dynamically handles memory with regards to other applications needing memory (to include multiple instances) will allow the servers to reach an equilibrium point based on usage, etc, when consistent load states are reached. So the instances will yield memory to each other just as they would to another application. Here's more from Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_0fub.asp
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 29, 2002 at 10:55 am
I think the consensus is to let SLQ manage the memory. If one server is not getting what it needs, I'd add memory before messing with the settings.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply