Dynamically Managing Memory Between Multiple Instances
When multiple instances of SQL Server are running on the same computer, each instance independently uses the standard dynamic memory management algorithm. There is no need for the instances to communicate with each other to cooperatively manage memory. When all but 4 MB to 10 MB of the memory on a computer is allocated, the amount of memory allocated to each specific instance of the database engine is driven by the relative workload of each instance. The instances with higher workloads acquire more memory, while instances processing lighter workloads acquire less memory. Regardless of the number of instances of SQL Server on a computer, the algorithm ensures:
- The overall amount of allocated memory remains under the level that would generate Windows NT® or Windows® 2000 page I/Os.
- The computer memory is efficiently distributed between the instances of SQL Server based on their relative workloads.
- The memory allocations are dynamic and can immediately adjust to changes in the workloads of individual instances of SQL Server.
The interactions can be illustrated on a computer running two instances, but the same principles apply when several instances are running on the same computer. Consider a computer with 512MB of physical memory running two instances named Instance1 and Instance2.
When both instances are first started, they typically acquire 8 MB to 12 MB of memory. As users connect to the instances, each instance acquires enough memory to satisfy its current workload.
Once the amount of memory reaches the point where only 4 MB to 10 MB is free, the instances begin competing with each other for memory. Assume that Instance1 has a long page life expectancy and a free memory target of 10 MB, Instance2 has a short page life expectancy with a free memory target of 4 MB. Assume 506 MB of memory have been allocated, leaving only 6 MB free. Because 6 MB free memory is below the 10 MB target of Instance1, Instance1 begins freeing memory. Instance2 keeps acquiring memory because the amount of free memory is over its target of 4 MB. It does not matter how much memory either instance actually has. What is important is that the current buffer pool of Instance2 is small relative to the demand for its data pages, while the buffer pool of Instance1 is large relative to the demand for its data pages. So long as this is true, Instance1 will have a free memory target of 10 MB and Instance2 will have a free memory target of 4 MB, driving Instance1 to free memory that is taken up by Instance2.
As Instance1 frees memory, it reduces the size of its buffer cache. Eventually, Instance1 reaches a point where the reduced size of the buffer cache starts decreasing the page life expectancy of the instance. As this happens, Instance1 starts lowering its free memory target from 10 MB. At the same time, Instance2 is using the memory it has acquired from Instance1 to increase the size of the Instance2 buffer cache. This increases the page life expectancy of Instance2, and so Instance2 begins raising its free memory target from 4 MB. At some point, Instance1 will have transferred enough memory to Instance2 that both instances have the same free memory target. As soon as the amount of free memory reaches the level that is now the target of both instances, Instance1 stops freeing memory, Instance2 stops acquiring memory, and the system reaches a state of equilibrium.
The state of equilibrium lasts only as long as the relative workload of both instances remains constant. As soon as the workload on one or the other of the instances changes, either increases or decreases, the instance will change its free memory target. The instance with the higher free memory target will then start freeing memory and the instance with the lower free memory target will start acquiring memory until a new equilibrium is reached.
The same mechanism operates with more than two instances on a computer. All of the instances will keep freeing or acquiring memory until all of them reach the same free memory target. Once the amount free memory on the computer reaches the common target, the instances are in equilibrium.