| Author |
Topic |
|
label
Posting Yak Master
197 Posts |
 Posted - 2003-06-12 : 08:41:45
|
| We've been having issues with our instance of SQL Server 2000 and how much memory it uses of late which has led me to ask the following questions: 1. How much memory should an instance of SQL server be using. Right now, if we let ours go unconstrained, it gets well into the 1.5 gig range using memory. 2. I constrained it's maximum useage to about 1 gig but when I do that, instead of releasing memory and reassiging it to new processes, it locks up the server once it reaches that ceiling. 3. What's the best way to configure the memory and processor useage of a sql server?I have about 6 or 7 Databases on the server. One of them get's a fair amount of useage, the others are much less frequent. I'm on the latest service pack and as far as I know have applied all patches that should be and I'm still watching the memory creep up till it kills our server. It's almost like SQL has a memory leak and can't release old memory because I can't believe that we have so much traffic at any given time to take up anywhere near a 1 gig worth of memory. Any help is much appreciated! |
|
|
JCamburn
Starting Member
31 Posts |
Posted - 2003-06-12 : 10:10:27
|
| From SQL BOL:Allowing SQL Server to use memory dynamically is the recommended configuration; however, you can set the memory options manually and override SQL Server's ability to use memory dynamically. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting from the total physical memory the memory required for Windows NT 4.0 or Windows 2000 and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This is the maximum amount of memory you can assign to SQL Server.Note If you have installed and are running the Full-Text Search support (Microsoft Search service, also known as MSSearch), then you must set the max server memory option manually to leave enough memory for the MSSearch service to run. The max server memory setting must be adjusted in conjunction with the Windows NT 4.0 virtual memory size such that the virtual memory remaining for Full-Text Search is 1.5 times the physical memory (excluding the virtual memory requirements of the other services on the computer). Configure the SQL Server max server memory option so that there is sufficient virtual memory left to satisfy this Full-Text Search memory requirement. Total virtual memory - (SQL Server maximum virtual memory + virtual memory requirements of other services) >= 1.5 times the physical memory.Also see PRB: There May Not Be Enough Virtual Memory with Large Number of Databases at:[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;316749[/url] |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-12 : 12:33:04
|
| Why do you think that SQL Server using 1.5GB of memory is a problem? What if SQL Server just needs that much and no other process is asking for memory? That's how SQL Server was designed to behave. If SQL Server needs the memory, then it will consume it. It does not release it unless another process is requesting AND SQL Server no longer needs it. I am not aware of SQL Server 2000 having any memory leaks. That was a problem with SQL Server 7.0 when it was below a certain service pack level. You just have to understand that the behavior that you are seeing is normal. As long as the other instances of SQL Server or any other software package does not need the memory, then don't put a cap on the memory for this instance.Tara |
|
|
|
label
Posting Yak Master
197 Posts |
Posted - 2003-06-12 : 15:04:08
|
quote:
Why do you think that SQL Server using 1.5GB of memory is a problem?
Mostly because it causes our box to freeze up because they've tried to shove way to much on one server. quote:
What if SQL Server just needs that much and no other process is asking for memory? That's how SQL Server was designed to behave. If SQL Server needs the memory, then it will consume it. It does not release it unless another process is requesting AND SQL Server no longer needs it.
Fair enough, that's why I asked here. While I'm somewhat competent writing stored procs and desiging databases, I know very little about server configuration and settings. quote:
I am not aware of SQL Server 2000 having any memory leaks.
I thought there was one before you applied SP3....could be thinking of something else though. quote:
You just have to understand that the behavior that you are seeing is normal. As long as the other instances of SQL Server or any other software package does not need the memory, then don't put a cap on the memory for this instance.
Unfortuantely I have Cold Fusion server, .NET and lots of websites running classic ASP pages, not to mention Webtrends and several other reporting programs all stuffed onto one box, all demanding memory and disk space.Edited by - label on 06/12/2003 15:05:58 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-12 : 15:12:53
|
| With all of that stuff on your server, it's no wonder it freezes. You need to either increase the amount of RAM (how much RAM do you have now?) or move SQL Server to a dedicated server.Tara |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-06-12 : 15:14:28
|
quote:
Unfortuantely I have Cold Fusion server, .NET and lots of websites running classic ASP pages, not to mention Webtrends and several other reporting programs all stuffed onto one box, all demanding memory and disk space.
You have all that stuff, and SQL server installed on one box? -ec |
|
|
|
label
Posting Yak Master
197 Posts |
Posted - 2003-06-12 : 15:38:11
|
quote:
You have all that stuff, and SQL server installed on one box?
Yup. I have dual pentium III processors and 4 gigs of memory and everything is supposed to fit on that one box. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-06-12 : 15:56:45
|
| Some questions:1. Are you running Win2k Advanced or Datacenter server?2. Are you running SQL 2K enterprise or standard? 3. Are you using the /3gb or /PAE switches in the boot.ini? 4. Are you running SQL in AWE mode?You should check technet for articles about configuring win2k for more than 2GB memory. Do searches on AWE, /PAE and /3GB, this will bring up tons of articles about configuring a server for > 2GB RAM.Start here:http://support.microsoft.com/default.aspx?scid=kb;en-us;274750http://support.microsoft.com/default.aspx?scid=kb;EN-US;283037http://support.microsoft.com/default.aspx?scid=kb;EN-US;291988If you aren't using these settings (Item 3 and 4 above), you might be paging pretty heavily. You should use perfmon to determine this. I would start by checking out the pages at www.sql-server-performance.com for the counters to check.Start here:http://www.sql-server-performance.com/performance_monitor_tips.asphttp://www.sql-server-performance.com/performance_monitor_counters_memory.asp-ec |
|
|
|
label
Posting Yak Master
197 Posts |
Posted - 2003-06-12 : 17:37:10
|
quote:
Some questions:1. Are you running Win2k Advanced or Datacenter server?
Win2k Advancedquote:
2. Are you running SQL 2K enterprise or standard?
Standardquote:
3. Are you using the /3gb or /PAE switches in the boot.ini?
Not a clue what you're talking about.  quote:
4. Are you running SQL in AWE mode?
See above: Not a clue. I didn't set this server up, nor do I have much experience with managing servers. I'm really a .NET programmer whose had to wear a SQL developer and DBA hat at this company so I'm pretty limited in my knowledge about Server stuff. quote:
You should check technet for articles about configuring win2k for more than 2GB memory. Do searches on AWE, /PAE and /3GB, this will bring up tons of articles about configuring a server for > 2GB RAM.
I can do that. quote:
[b]Start here:http://support.microsoft.com/default.aspx?scid=kb;en-us;274750http://support.microsoft.com/default.aspx?scid=kb;EN-US;283037http://support.microsoft.com/default.aspx?scid=kb;EN-US;291988If you aren't using these settings (Item 3 and 4 above), you might be paging pretty heavily. You should use perfmon to determine this. I would start by checking out the pages at www.sql-server-performance.com for the counters to check.Start here:http://www.sql-server-performance.com/performance_monitor_tips.asphttp://www.sql-server-performance.com/performance_monitor_counters_memory.asp
Looks like I've got some reading to do.  Thanks for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-12 : 17:51:26
|
| If you haven't made the changes to the boot.ini file, then your server isn't even using the 4GB of RAM that you have. It is only using 2GB. The links that ec provided will guide you through what needs to be done to have your server support more than 2GB of RAM.Tara |
|
|
|
|
SQL Server Memory Issues - Help!