SQL Server Performance Forum – Threads Archive
Memory on Win2K Std w/2 Instances
With two instances of SQL Server 2000 running on a Windows 2000 Server (not Advanced) with 4GB RAM, will each automatically use up to 2GB? ThanksNot unless you limit both to 2 GB. Otherwise the one that needs most memory will take more memory.
Of course Windows 2000 Server limits an instance to 2GB, anyway. I currently have two large databases in one instance, limited (by OS) to 2GB, on a server with 4GB RAM. The server also handles a ton of batch processing, and memory stats are bad (buffer cache hit ratio ~60%, high paging, deadlocks from lock escalation, etc.). My strategy is to move the batch processing to another server, create a dedicated database server, and optimize exclusively for database performance. I don’t want to leave 2 GB RAM unused, much less make performance worse. But you’re saying that two instances on a 4GB Windows 2000 Server box could each simultaneously grab something close to the 2GB max, right? They aren’t collectively limited to 2GB by Windows 2000 Server? Thanks
SQL Server Enterprise Edition can use 4GB on Windows 2000 Server.
SQL Server Standard Edition can use 2GB on Windows 2000 Server. So it depends on which edition you use. I assume each instance is treated seperate but I have never tested it. So with two instances of SQL Server Enterpise Edition on a Windows 2000 server (Standard) you could probably set one to use 3.0GB and the other to 0.5GB and leave 0.5GB for the OS for example.
I’m working with standard edition. I’d like to set the busier of the two to 2GB and the smaller to somewhere between 1.2 and 1.5GB. If anyone has tried this and can say for sure whether or not it works, please advise! Thanks
If it’s standard edition none of the instances will be able to utilize the RAM above 2GB. They would have to share the memory below 2GB. At least that’s how I understand it.
each process in Windows has a 4GB virtual address space, the OS reserves 2GB of this for itself (unless other options are set to change this) processes are not aware of which bits of RAM they can/can’t use, they only know about the area of the virtual address space that they can use so each SQL Server instance has it’s own 4GB address space, 2GB of which it can use itself. SQL Server is written such that it tries to avoid the OS paging so it will give memory back when the amount of physical RAM free reaches a threshold about 40MB from memory. anyway, each SQL process acts independently and can use up to 2GB of RAM. So if you set one to max out at 1.2 GB then the other SQL instance will still try to use up to 2GB Cheers
Twan
]]>