August 27, 2009 at 2:06 am
Hi all
we have a server running two SQL 2005 instances (Standard edition). At the time of installation noone bothered to configure the min/max memory settings. Now, of course, it seems one of the instances takes a significant amount of memory leaving the other instance unresponsive at times.
We would like to set minimum/maximum values for the instances. How can I determine adequate values so that we do not run into performance problems?
Any thoughts much appreciated!
Thierry
August 27, 2009 at 2:55 am
thierry.vandurme (8/27/2009)
Hi allwe have a server running two SQL 2005 instances (Standard edition). At the time of installation noone bothered to configure the min/max memory settings. Now, of course, it seems one of the instances takes a significant amount of memory leaving the other instance unresponsive at times.
We would like to set minimum/maximum values for the instances. How can I determine adequate values so that we do not run into performance problems?
Any thoughts much appreciated!
Thierry
How much memory do you have on the server. is awe enabled perhaps ?. normally the threshold is about 75% of the total memory if it is a dedicated sql box. you can throttle the usage of memory but it depends on the usage between the instances. normally the memory settings need constant adjustment until you get an idea of what is going on.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
August 27, 2009 at 4:30 am
total memory is 8Gb. SQL instance 1 is using about 7.5Gb of it. I suppose if we add memory it would even grow so can I assume the server is short of memory then?
I would like to know what SQL is using the 7.5Gb for (a memory breakdown let's say).
August 27, 2009 at 4:49 am
thierry.vandurme (8/27/2009)
total memory is 8Gb. SQL instance 1 is using about 7.5Gb of it. I suppose if we add memory it would even grow so can I assume the server is short of memory then?I would like to know what SQL is using the 7.5Gb for (a memory breakdown let's say).
You dont say if AWE is enabled, to allow the use of more than 3GB of memory.
5734MB would be 70% of total server memory. if both servers are resource intensive and both critical, then you can set the max memory to 3072MB on both instances and leave about 2GB for the Operating system. without a baseline for either instance. I would set the memory to what I have suggested and monitor performance.
SQL Server will grab hold of all server memory if you allow it. and only release it if the operating system needs it. Better to set the max memory at the very least and restrict what it can use, initially.
using perfmon or other monitoring tools, you will be able to find out which one of the instances is more memory intensive and is paging to disk, and then you can readjust the maximum memory for that instance.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
August 27, 2009 at 4:53 am
Hi, is this a 32 bit or 64 bit server?
August 27, 2009 at 5:03 am
Silverfox (8/27/2009)
thierry.vandurme (8/27/2009)
total memory is 8Gb. SQL instance 1 is using about 7.5Gb of it. I suppose if we add memory it would even grow so can I assume the server is short of memory then?I would like to know what SQL is using the 7.5Gb for (a memory breakdown let's say).
You dont say if AWE is enabled, to allow the use of more than 3GB of memory.
5734MB would be 70% of total server memory. if both servers are resource intensive and both critical, then you can set the max memory to 3072MB on both instances and leave about 2GB for the Operating system. without a baseline for either instance. I would set the memory to what I have suggested and monitor performance.
SQL Server will grab hold of all server memory if you allow it. and only release it if the operating system needs it. Better to set the max memory at the very least and restrict what it can use, initially.
using perfmon or other monitoring tools, you will be able to find out which one of the instances is more memory intensive and is paging to disk, and then you can readjust the maximum memory for that instance.
He said that one instance is using 7.5Gb which means that either AWE is enabled or its a 64 bit system..
Regards,
[font="Verdana"]Sqlfrenzy[/font]
August 27, 2009 at 5:30 am
Yup appreciate that, but thought it important to establish which is the case.
August 27, 2009 at 7:04 am
Hi guys
thx so far for the repsonse!
Indeed, I forgot to answer the AWE question. AWE is not enabled but it's a 64bit instance.
So, I guess I'll just restrict the memory and see what happens to the performance? That's kind of what I wanted to avoid. I wanted to be more on the safe side and make a guess based on DMVs or perfmon or...
I was asked to predict the effect on performance... .
August 27, 2009 at 7:17 am
So what will be your planned carve up?
If you have 8GB to play with how about starting off with 3GB for each instance and then 2GB for the OS?
August 27, 2009 at 7:46 am
Hmm, I think I'll start with 5Gb for the first instance (now consuming over 7Gb) and 1.5Gb for the second instance.
Why...? 🙂
1. I suspect the instance that's now consuming 7Gb to be infected with poorly written queries etc. (haven't yet checked this out). Our developers need to be made aware that they MUST learn to put some effort in this instead of relying on Infrastructure to just add memory.
2. I have some historical figures for the second instance indicating that the memory usage has not exceeded 1.2Gb. So I thought 1.5Gb to leave some room.
That makes 6.5 SQL and 1.5 OS. I think I only need to worry about the first instance. I'll be implementing this in the coming month.
Thx for your thoughts on this!
Thierry
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply