December 14, 2001 at 6:02 am
Hi
I know there are no hard and fast rules to how memory a server should have - but how does one determine the RAM requirements for SQL when it uses all avail. memory less 5 MB?
The machine is dual 550MHZ 1GB RAM and approx 200-250 connections simultaneously. Occasionaly it is extremely slow, then navigating Enterprise Manager takes over 10secs to open a +, it comes right again - but I usually need to restart the services
Thanks for any suggestions!
December 14, 2001 at 6:18 am
Looking at the used memory is a little misleading, because SQl will cache the compiled sps and table data until it uses up the available memory, so in your case if your databases (tables, indexes and compiled sps) are > 1GB, SQl will use up all your RAM and this is normal. If SQL is suffering from lack of RAM, the counters I look at are Buffer Cache Hit Ration (ratio of cache hits to cache lookups) Memory Pages/sec and % Processor time. In a sense hopefully these counters show that more RAM is needed, because this is the easiest solution. If not, using the profiler and perf mon together might show what's causing the performance to slow down -long running sps, incorrect indexes, backups, shrinking dbs etc etc.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 14, 2001 at 8:31 am
I agree with Paul, however these won't help you detrmine how much more.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 14, 2001 at 10:27 am
Great advice above. Look for sp recompiling. Profiler will assist with a lot of whats going into your server, and per mon will let you know what your processor is doing. One user with a large load, or request can cause you processor to spike 100%. in these cases it comes down to investiagtion work with the two utilities guiding you in the right direction. -jg
-JG
December 17, 2001 at 11:23 pm
Thanks for the advice.
What do you make of this: I updated the antivirus defs on the sql server and after that the machine was so slow that people couldn't make connections. The processor % was very low so thats not the problem. Will reducing the max available memory to sql help by allowing more memory for other applications to run?
December 18, 2001 at 7:09 am
Anti-Virus on a server running SQL Server. Not here. Maybe I am a bit picky but, too much background munching by the AV software and really no benefit. I allow no shares on the SQL Server to the client community and the data itself should not get any virus so, the door remains closed to virus. The only potential threat would be through the mail system and we have AV on that and the mailbox we use for SQL is hidden so, no one can send to it.
So, if you don't have shares and you feel your mail is adequately protected, I would not have it on the box. I would love to hear others opinions on this but maybe that should be another thread.
Hope this helps. (Probably spark a big debate)
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply