February 2, 2006 at 11:51 am
Hi,
We have an OLTP + some reporting on the system with 600+ connections, 30 gb database running on a 4 CPU and 4.2gb memory, out of which 3.5 gb is allocated to SQL Server (using fixed memory option). SQL Server is on its own dedicated machine.
Our clients are complaining about performance. Disk utilization is around 50%.Memory util always says 90% since I've set it to use 'fixed' memory.
1) How do I tell if the sql server needs more memory ?
2) How do I tell if there are large table scans happening using SQL Profiler ?
TIA
MO
February 2, 2006 at 12:43 pm
have a look at
http://www.sql-server-performance.com/articles_audit.asp
1)when to add memory: monitor Buffer Cache Hit Ratio
In OLTP applications, this ratio should exceed 90%, and ideally be over 99%. If your buffer cache hit ratio is lower than 90%, you need to go out and buy more RAM today. If the ratio is between 90% and 99%, then you should seriously consider purchasing more RAM
2)http://www.sql-server-performance.com/sql_server_performance_audit10.asp
They are great articles to start from
February 3, 2006 at 8:35 am
Hello,
I would look at the Page Life Expectancy counter. If it stays above 300, then you don't need more memory. If it drops below 300, then you do.
And that buffer cache hit ratio thing. (they are related)
ymmv
jg
February 6, 2006 at 12:18 pm
Hi
Where would I find the "Page Life Expectancy Counter" to set ?
TIA
Mo
February 6, 2006 at 12:26 pm
Hello,
In the list of counters, there should be a SQLSERVERNAME:Buffer Manager object. The PLE counter is in there.
Jeff
February 6, 2006 at 12:27 pm
And I do mean "In the list of performance objects". :o)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply