| Author |
Topic |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
 Posted - 2007-10-02 : 13:55:58
|
| I’m setting up a server for development that will have one instance of SQL Server 2000 Standard Edition and one of SQL Server 2005 Standard Edition installed.The server has a total of 3.5 GB of physical memory, so I was wondering about the best way to allocate the memory. Should I just let both instances have the default allocation and let them fight it out for memory as needed, or allocate a memory limit for each instance?CODO ERGO SUM |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 14:04:36
|
| Microsoft does not recommend using the defaults when you are using multiple instances. We had them on site and hadn't yet configured the memory settings on our cluster, so they wrote up a document saying that was one thing we needed to fix.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-10-02 : 14:15:33
|
quote:
Originally posted by tkizer
Microsoft does not recommend using the defaults when you are using multiple instances. We had them on site and hadn't yet configured the memory settings on our cluster, so they wrote up a document saying that was one thing we needed to fix.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
Have you seen any documents or links that explain how it should be done?CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 14:25:10
|
| The implentation would depend on the instance needs. You could start out by giving one half and the other half. You'd then need to monitor the SQL Server memory counters to determine if the instances are getting what they need.I'm unable to find the information that they provided to us in my email. It was quite a few months ago that they were here investigating our cluster issues.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-10-02 : 14:26:52
|
| I'd say it's more of a balancing act of memory for each instance. Myself, I'd start out with 1.25GB allocated to each, which in turn will consume about 3GB overall.Let them run for a bit and see which needs more memory and start re-tuning accordingly... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 16:18:25
|
| "see which needs more memory"How do you do that with 1x2000 and 1x2005?Isn't SQL2005 a it "shy" about how much memory it would like, grabbing as-little-as-possible, rather than as-much-as-possible?Kristen |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-10-02 : 16:30:49
|
| They'll both only still use whatever they're given to use. Version of SQL server doesn't really matter all that much. It's about figuring out which instance is going to want to use/need more memory.However, bearing in mind that the server has only 3.5GB of memory total, it won't take long for the database sizes to grow large enough for memory to be the bottleneck over cpu or i/o anyway. 3.5GB just _isnt_ that much memory any more.... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 16:32:17
|
quote:
Originally posted by Kristen
"see which needs more memory"How do you do that with 1x2000 and 1x2005?Isn't SQL2005 a it "shy" about how much memory it would like, grabbing as-little-as-possible, rather than as-much-as-possible?Kristen
You can view that information in Performance Monitor.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 16:59:46
|
| OK, I'm probably behind the ball here, but I read that "Target memory" doesn't quite cover it, in SQL 2005 ..... but I'm very happy to be properly educated on the matter |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 17:10:06
|
| Please post the article that you read.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 17:25:26
|
| I haven't noticed any discrepancies between Total and Target and I check these values often and on multiple instances. I'm with rmiao on that point.But the ultimate decider of whether or not SQL is starved for memory is the Buffer cache hit ratio.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 17:37:33
|
| Thanks, I'll put a higher ranking on "Buffer cache hit ratio" in future. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-02 : 22:35:58
|
From this article:http://msdn2.microsoft.com/en-us/library/ms178067.aspx"SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory."It does say that once the minimum is reached it will not drop below.
Future guru in the making. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-02 : 22:42:13
|
| Don't forget 'page life expectancy'. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-03 : 12:07:45
|
quote:
Originally posted by Zoroaster
From this article:http://msdn2.microsoft.com/en-us/library/ms178067.aspx"SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory."It does say that once the minimum is reached it will not drop below.
Future guru in the making.
I never set the min level, just the maximum. The maximum is what is important on a server with multiple instances. Min level is more for performance reasons of an instance.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-11 : 03:06:49
|
quote:
Originally posted by rmiao
Don't forget 'page life expectancy'.
But with higher page life expectancy, you get higher buffer cache hit ratio?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-11 : 09:41:46
|
| I also recommend to use fixed memory when you are using multiple instances. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-11 : 23:24:21
|
| >>But with higher page life expectancy, you get higher buffer cache hit ratio?Likely, but high buffer cache hit ratio doesn't always result higher page life expectancy. |
|
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-12 : 02:58:56
|
| My experience is that ms sql server will try and take as much memory as it can get relative to what is needed but if you have a db that is working a fair bit it will use as much memory as needed to do it's work as effective as it can. I belive the max for ms sql 2000 is ~4gig for the ram memory. What you mean by physical memory I asume is your ram memory. If sql runs out of ram it will start to use pagefiles on your harddrives, if this kicks in you should notice a drop in effectivity. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 03:14:51
|
| "I belive the max for ms sql 2000 is ~4gig for the ram memory"2GB max, 4GB for Enterprise version only."If sql runs out of ram it will start to use pagefiles on your harddrives"Not really, it will just store less of your precious data in the Cache, and more will have to be pulled from the database file on the disk.Kristen |
|
|
|
Next Page
|
Allocation of Memory by Instance