April 1, 2010 at 2:40 pm
Hi,
We have 4 SQL server 2005 ee x64 with SP3 instances on Single server with 16 GB physical RAM. We set the Max memory to 3 GB for each instance & Min memory is set to default and left 4 GB for OS. Lock pages in Memory has enabled.
Question:
1. Do we need to required to set the Min memory setting, if we have multiple instances?
2.Three of the instances using Max of allocated memory. i.e 3 GB but one of the instance using just 1 GB out of Max memory 3 GB. So can I change the Max meory setting for this instance to 1 GB and make Max Memory setting for other instances to 4GB?
3. Generally , SQL Server instance takes all the Max memory allocated to it right? In my case, 3 instances take all the Max memory allocated But one instance takes only 1 GB out of 3 GB Max memory? Is that means that particular instance NOT required 3 GB memory? 1 GB is sufficient for that & that's why it is NOT taking more than that 1 GB even if 3 GB memory is available?
Thanks
April 2, 2010 at 3:34 pm
Mani-584606 (4/1/2010)1. Do we need to required to set the Min memory setting, if we have multiple instances?
It is not required.
2.Three of the instances using Max of allocated memory. i.e 3 GB but one of the instance using just 1 GB out of Max memory 3 GB. So can I change the Max meory setting for this instance to 1 GB and make Max Memory setting for other instances to 4GB?
Yes - you can change the max memory to allocate more or less memory to each instance.
3. Generally , SQL Server instance takes all the Max memory allocated to it right? In my case, 3 instances take all the Max memory allocated But one instance takes only 1 GB out of 3 GB Max memory? Is that means that particular instance NOT required 3 GB memory? 1 GB is sufficient for that & that's why it is NOT taking more than that 1 GB even if 3 GB memory is available?
Thanks
That is correct - if the instance does not need all of the memory allocated - it won't ever use it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 2, 2010 at 5:24 pm
By design SQL Server 2005 takes all the memory allocated to it's buffer pool(Max Memory setting) whether it requires or not.
Considering above statement, Why one of the 4 instances is NOT taking all of the 3 GB memory allocated to it's buffer pool(Max Memory setting is 3 GB)?
I appreciate if could explain little more detail
Thanks
April 2, 2010 at 5:59 pm
SQL Server will take all of the memory up to the max memory setting. It does not guarantee that it will take all of it.
If that instance does not need all of that memory, it won't take it. In some cases, it's because the database is smaller than the available max memory for the instance. In other cases, it's because the system is not accessing enough data to fill the buffer pool.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 2, 2010 at 6:44 pm
Thank you Jeffrey!!
April 2, 2010 at 9:10 pm
Just a quick note to say I concur with Jeffrey. I think he's explained it well.
If you ran a bunch of large queries on that one instance, likely the buffer pool would fill up and it would use more RAM.
April 3, 2010 at 3:12 am
Jeffrey Williams-493691 (4/2/2010)
SQL Server will take all of the memory up to the max memory setting. It does not guarantee that it will take all of it.
I would just add a couple of bits of detail to your excellent post:
1. The max server memory setting only restricts the size of the buffer pool. SQL Server (the process) can use more memory than specified, since memory is allocated outside of the buffer pool for various purposes, including thread stacks, single allocations over 8KB, and so on and so on.
2. The buffer pool does not just contain cached data and log pages. The buffer pool is used for pretty much all single-page allocations, and a big consumer is often the procedure cache. A server can therefore use much more buffer pool than just the size of the database.
3. SQL Server can be configured to attempt to allocate max server memory at start up. There are at least two methods for this: (1) Trace flag 836 - x86 with AWE only; (2) On Enterprise Edition with at least 8GB physical RAM, and 'lock pages in memory' granted to the SQL Server process account, trace flag 834 can be used.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply