Enormous Page Life Expectancy (PLE)?
-
I've been running perfmon and analysing the results on a production Windows 2008 R2 server running SQL Server 2008 R2 and I have a consistently high PLE. And when I say high, I mean gigantic.
The server has 24GB of RAM, so a comfortable value is apparently 24 / 4 * 300 = 1800 seconds or 30 minutes
Over the last 12 hours (7pm last night to 2pm today), perfmon has reported this:
Average 3,643,293 Median 3,637,530 Min 3,633,329 Max 3,672,127 Std Deviation 11,952 Just ran this (at 3pm):
[SELECT [object_name], [counter_name], [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'And I get this:
Which is (roughly) 3.6 million seconds or 60,000 minutes or 1,000 hours or nearly 30 days!A month?????
Is this too high? Is there such a thing as PLE being too high?
-
Max server memory is set in SQL Server to 16GB.
Available MBytes over the same period was:
Gulfstream Portal Tool Average 4,559 Median 4,567 Min 4,061 Max 4,628 Std Deviation 49 -
Why is that a problem?
And no, 'a comfortable value is apparently 24 / 4 * 300 = 1800 seconds or 30 minutes', is not a comfortable level, that's a way of calculating the point below which suggests memory pressure, or at least too much churn.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Thursday, August 17, 2017 10:05 AM
Well, I didn't think so, but when I Googled on "page expectancy level high" (and they said the same as you Gail), the biggest "high" example I found was 70,000 not 3,600,600!
Does this mean the infrastructure team will be saying I should drop the amount of RAM allocated to this server in VMware as it's not being used?
- mmcardle - Thursday, August 17, 2017 11:10 AM
Tell the Infrastructure team to go to hell. And it's not unused memory, not sure what implied that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Page Life Expectancy doesn't tell you how much memory is being used, just a rough idea of how long a page in memory stays in memory. To see h ow much memory is being used try queries like these:
-- system level info 2008
SELECT cpu_count/hyperthread_ratio AS physical_cpu_count,
physical_memory_in_bytes / 1048576 AS physical_memory_meg,
bpool_committed / 128 AS bpool_commited_meg,
bpool_commit_target / 128 AS bpool_commit_target_meg
FROM sys.dm_os_sys_info-- SQL Server process memory
select physical_memory_in_use_kb / 1024 AS physical_memory_in_use_meg,
locked_page_allocations_kb / 1024 as locked_in_memory_meg,
page_fault_count AS sql_page_fault_count, memory_utilization_percentage, process_physical_memory_low, process_virtual_memory_low
from sys.dm_os_process_memoryhttps://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-info-transact-sql
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-process-memory-transact-sql -
You probably want to check things like memory usage and PLE over time, instead of making knee-jerk reactions to what you see on one particular day. It may be that this particular system has more activity on a specific day of the week or month, so reducing VM memory now would leave the more intense processes short on resources. A PLE that high typically means that it's been high for quite a while, the PLE starts at 0 when you first start the server, and will gradually increase until activity causes pages in memory to be removed so other pages can be read in (in a least recently used way) which will lower the PLE.
- GilaMonster - Thursday, August 17, 2017 11:45 AM
Haha...I will.
Cheers Gail
-
Thank you for all the replies on this post. All taken on board and will be acted upon.
Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply