August 28, 2005 at 6:36 pm
HI All
any thoughts would be great
i've got a dell PE6600 quad xeon processors /1 gb ram
this server only runs sql/analysis server / reporting server.
i have noticed in the task manager that the sqlserver.exe has run away with all the ram and a cant see where i can alter this so my server doesnt crash or have a big slow up.
does anyone know or has experienced this and can give me some quality advice
cheers
Todd
August 29, 2005 at 6:42 am
By design, when in 'dynamic memory mode', SQL Server will grab memory when it needs it up to a limit leaving 'enough for the o/s' (sry don't have the exact numerics offhand). Also by design, SQL Server will not release this memory unless some other app needs it. So, in a sense, this is normal behaviour you're seeing.
This can become a problem if SQL Server isn't alone on the server. The other option is then to cap the memory, and that way setting the limit for how much memory SQL Server can use at any time. You configure the memory settings in either EM -> (right click)server -> properties -> Memory tab or by using sp_configure. To cap memory at a hard max-limit, you set the min and max memory to the same values.
BOL also have more info on SQL Server memory managment.
/Kenneth
August 29, 2005 at 9:59 am
Sounds to me like you have a memory bottle neck. You might want to consider adding more memory and then setting a cap like Kenneth suggests. You should genereally leave 512MB for the OS and let SQL have the rest, if it is a dedicated SQL Server Box.
Run some perf checks to see if you have a memory bottleneck. Check your buffer cache hit ratio - if that is constantly below 90%, you prob have a memory issue. Also check other Memory counters through Perf monitor.
August 29, 2005 at 11:16 am
In my opinion, any production SQL server, unless it's used for tiny DBs, should have at least 2 GB of RAM. SQL works best when you give it as much RAM as you can afford. The Standard edition can use a max of 2 GB, though, so throwing more than that on a server will work best if you have other applications besides the OS that can use the additional RAM. (IIS would be one of those apps commonly run on SQL server.)
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
August 29, 2005 at 2:01 pm
you stated: "this server only runs sql/analysis server / reporting server"
Well 4 Gb of RAM would seem enough but here's what I think may be happening:
Operating system uses what it needs - up to 2 Gb
SQL Server is taking it's max 2 Gb
Analysis Server is using what it can as well - again 2 Gb max
Reporting Server uses a littl memory too (I am not sure how much) but it can 'grab' like everyone else too !
Reporting Services needs IIS and it can grab up to 2 Gb of RAM !
A quick bit of addition says if every major process was RAM hungry you'd need 10 Gb of RAM !!!
With everbody trying to grab the most memory they can you are just plain using it up. Granted everything in the example is theoretical maximums but it seems like your feeling a good part of it by running 'default' memory installation parameters.
If I were in your situation I'd segregate the Reporting Services and IIS to another box.
This allows SQL Server to again be a dedicated machine. Then you could give
SQL Server 2 Gb, Analysis Server 1 Gb and that would leav the OS 1 Gb (I feel that 512Mb is way too low in some situations).
If another server is not available then pare things down a bit and give IIS 512 Mb and Reporting Services 512 Mb.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 29, 2005 at 3:23 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply