February 18, 2012 at 9:17 am
How to find out that SQL server is facing performance issue due to insufficient memory. thanks in advance.
February 18, 2012 at 9:24 am
Use prformance counter like PLE ('Page life expectancy'), buffer cache hit ratio to find out the memory contegency. capture the data during various time of the day like peak and nopeak hours. the PLE should be more than 300 Seconds.
SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
February 18, 2012 at 9:46 am
Use the PageLifeExpectancy (PLE) counter to monitor memory issues. Buffer cache hit ratio is not a very good counter to look for when it comes to monitor issues related to memory, as it rarely changes over the time.
General recommendation of 300 sec for PLE is not valid for the servers having more than 4 GB memory. It is recommended value for the servers where SQL Server can access up to 2 GB memory only.
Say, If you have 32 GB of RAM then your PLE should be ~32*75 = 2400
February 18, 2012 at 10:40 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2012 at 12:11 pm
check this article on PLE.
http://www.sqlskills.com/BLOGS/PAUL/post/Page-Life-Expectancy-isnt-what-you-think.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply