November 28, 2007 at 5:56 pm
How could i see, if my SQL Server is experiencing memory pressure. My current production server details are as follows:
SQL Server 2000 Enterprise Edition.
OS: Windows 2003
AWE is enable and the server is runing with 16 GB of memory. I have assigned 8 GB of Fixed Memory to SQL Server and over the period, i see Total server memory and Target memory Counters to be same. My take was, the sql server is under memory pressure, but came acoss with some articles and i believe, it may not be the case here.
I was wondering, how could i make sure, the assigned 8GB of memory is sufficient to SQL and there's no memory pressure on sql server? Anyway to do this?
Appreciated any suggestions.
Thanks,
Cali
November 28, 2007 at 6:38 pm
There's a few things in performance monitor you can check. The most obvious is Memory: Pages/sec. If this is high, then generally more memory is required (I've seen recommendation that this should not be sustained over 20).
SQLServer Memory Manager: Target Server Memory & Total Server Memory is another indicator. Target is what SQL Server wants to use, Total is what it currently has. This should be Target <= Total.
SQLServer Buffer Manager: Buffer Cache Hit Ratio - if this is not in the high 90s, it could indicate a lack of memory.
SQLServer Buffer Manager: Page Life Expectancy - this should be > 300s (so I have read).
There are a few others but these should give you a fair idea of how things are going.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
November 28, 2007 at 7:00 pm
Thanks for the answer.
To clarify on SQLServer Memory Manager: Target Server Memory & Total Server Memory. Well on a AWE Enabled systems, the total and target will be same and i have seen that in my case. Additionally the memory pages sec counter is less than 20 in my environment.
Buffer manager cache hit ratio is greater than > 90%.
But isnt there a precise way to tell, the server is under memory pressure? I did attend a SQL 2005 optimization class and the Microsoft instructor who i talked to said, will get back to me with the answer and unfortunately he never did. sent him 2 emails and no answer.
but thanks for the answer. i will look around and if i find anything, i shall post here.
November 28, 2007 at 7:22 pm
I haven't come across any magic bullet. Everything I have read says things along the lines of 'look at performance counters x, y, z and compare with values a, b, c' with the usual caveats of being aware that each system will be different, so what's appropriate for one may not be appropriate for another.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
November 28, 2007 at 7:51 pm
Hi,
I want just add my contribution.
Buffer cache hit ratio : should be maintened in the neighborhood of 90 %. Threshold>=90%
It's buffer cache hit ratio/buffer cache hit ratio base
Select
s1.counter_name as [Counter Name]
,s1.instance_name as [Instance Name]
,convert(decimal(5,2),(convert(real,s1.cntr_value)/convert(real,case when s2.cntr_value=0 then 1 else s2.cntr_value end))*100) as [Value]
From
sys.dm_os_performance_counters s1 Join sys.dm_os_performance_counterss2 On s1.instance_name=s2.instance_name
Where
(s1.counter_name = 'buffer cache hit ratio' And s1.cntr_value!=0) And (s2.counter_name = 'buffer cache hit ratio base' And s2.cntr_value!=0)
Page Life Expectancy : How many times SQL Server estimates a page will remain in the buffer cache. Threshold>=300s
Select *
From
sys.dm_os_performance_counters
Where
counter_name = 'Page life expectancy'
AND object_name = 'SQLServer:Buffer Manager';
If Target Server Memory(KB)< Total Server Memory (KB) there is a memory pressure
If Total Server Memory (KB)> Physical memory ( where physical memory is Convert(Bigint,(select PropertyInternalValue from #xp_msver))*1024 ) there is a memory pressure
There is a memory pressure ratio I use all the time. Threshold when total memory is approaching the target memory there is a memory pressure
select
total_mem_mb = x.cntr_value / 1024,
target_mem_mb = y.cntr_value / 1024 ,
pressure_ratio = convert(decimal(18,2),100 * (convert(decimal(16,2),x.cntr_value) / y.cntr_value))
from
sys.dm_os_performance_counters x,
sys.dm_os_performance_counters y
where
x.object_name = 'SQLServer:Memory Manager' and
y.object_name = x.object_name and
y.counter_name like 'Target Server Memory%' and
x.counter_name like 'Total Server Memory%'
Another way to check to see if your SQL Server has enough physical RAM is to check the Memory Object: Available Bytes counter. This value should be greater than 5MB.
Check the following links, it worth
http://www.rampant-books.com/t_hpsdba_62_buffer_cache_performance.htm
http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/1/[/url]
http://www.sqlmag.com/Article/ArticleID/43117/sql_server_43117.html
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/analyzingmemoryrequirementsforsqlserver/2498/[/url]
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p2.aspx
Regards,
Ahmed
November 29, 2007 at 11:51 am
Thanks Ahmed for the detail answer.
But speaking about Total and Target memory, well in my case, i have seen Total and Target memory counter to be same. again i have AWE Enabled and i have read couple of articles, where it said, both values could be same on a AWE enabled system. But buffer cache is greater > 90 and memory pages are less than 10.
But if i have to go with Total and Target server memory counter, that means, my server is under memory pressure. But if i see the buffer cache and memory pages, then i dont see any memory pressure. what do we do in this case?
Thanks,
November 29, 2007 at 12:26 pm
Hi California,
http://www.sql-server-performance.com/tips/performance_monitor_memory_counter_p1.aspx
If, over time, the SQLServer:Memory Manager: Total Server Memory (KB) counter is less than the SQLServer:Memory Manager: Target Server Memory (KB) counter, then this means that SQL Server has enough memory to run efficiently. On the other hand, if the SQLServer:Memory Manager: Total Server Memory (KB) counter is more or equal than the SQLServer:Memory Manager: Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure and could use access to more physical memory. [7.0, 2000] Updated 5-25-2005
But I found this memory pressure ratio in a book talking about performance http://www.rampant-books.com/book_2005_2_sql_server_dba.htm
http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!339.entry
-- Detect external and internal memory pressure
SELECT *
FROM sys.dm_os_memory_cache_clock_hands
WHERE rounds_count > 0
AND removed_all_rounds_count > 0
The clock_hand column tells you whether the clock algorithm is responding to internal (other SQL Server caches) or external memory pressure. If the values for the rounds_count and removed_all_rounds_count columns are increasing, this is a good sign of memory pressure.
Good luck
November 29, 2007 at 1:05 pm
Hi California,
Check the following links:
https://blogs.msdn.com/slavao/archive/2005/11/15/493066.aspx
Regards,
Ahmed
November 29, 2007 at 4:12 pm
I think also using AWE will mean that the memory setting configured will be allocated to SQL so total memory will be whatever you set it to 6GB i think you mentioned
Gethyn Elliswww.gethynellis.com
November 29, 2007 at 4:59 pm
Thanks for all your postings. I have looked into the provided info and it's very knowledgable.
Thanks again,
Cali
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply