April 13, 2011 at 3:53 am
I have a windows cluster SOL 2005,SP3 server with 64GB RAM and lot of SAN's.
Users are keeping on requesting for new databases as there is enough space on the server but they are not worried about the memory on the server. And we are having performance issues on the server how will I convince them about adding more memory on the server?
April 13, 2011 at 4:23 am
Hi,
you should set up a performance monitoring.
You can start, checking the Buffer Cache Hit Ratio over time.
With the following statement you can get the current performance counter value of the Buffer Cache Hit Ratio:
-- Buffer cache hit ratio
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio') a
CROSS JOIN
(SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base') b
If this value falls below 95 percent you should add memory. To monitor this you can implement an sql server agent alert with email notification.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 13, 2011 at 4:55 am
Thanks a lot Patrick
April 13, 2011 at 5:40 am
I would also suggest you monitor the counter : Page Life Expectency. This also gives you an indication of memory pressure.
April 13, 2011 at 5:43 am
I was just trying to type Page Life Expectancy but Craig beat me to it. It's one of the best indicators for memory pressure.
Also, you should monitor the wait stats for the server to see what users are waiting on. Memory isn't the only thing to worry about. There's also the CPU and I/O that can get you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 13, 2011 at 5:49 am
That's true, this would be also an indicator. Thanks Craig.
You can take the following statement:
selectcntr_value as [PageLifeExpectancyInSeconds]
fromsys.dm_os_performance_counters
whereobject_name like '%:Buffer Manager%'
and counter_name like 'Page life expectancy%'
If the value falls below 300 (seconds) it would help to increase the memory.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 13, 2011 at 6:19 am
Patrick_Fiedler (4/13/2011)
That's true, this would be also an indicator. Thanks Craig.You can take the following statement:
selectcntr_value as [PageLifeExpectancyInSeconds]fromsys.dm_os_performance_counters
whereobject_name like '%:Buffer Manager%'
and counter_name like 'Page life expectancy%'
If the value falls below 300 (seconds) it would help to increase the memory.
I'm saying this despite what's written in my book...
Be careful about using a hard number with page life expectancy. 300 isn't a magic number. The thing to look for on this is volatility. If it's going up & down a lot, then you have issues, or if it's consistently very low, then you have issues, but it could be 150 seconds and fairly flat and not be an indication of a problem. It really depends on the system and everything else that's going on with it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 13, 2011 at 6:24 am
Thanks Grant. We can't learn enough ... 😉
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 14, 2011 at 6:02 am
Thank you all
Cheers
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply