Low PLE Issue (Lack of SQL Memory)

[Former Member]

Hi All,

I am a database administrator. I am recently noted the database performance statistic is bad, the average of PLE (Page Life Expectancy) is at 100 seconds. From the opinion of DBA and my experience , this is at critical status. Slowness and connection timeout might happen frequently.

However we don't receive any complain from user, but I checked the most of wait_type(refer to below Top 10 wait_type in our environment) in SQL server is PAGEIOLATCH. Is anybody has experience about it? How about the PLE in your SAP databases?

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms

PAGEIOLATCH_SH 1156589452 2659974311 63800 15378169

BACKUPIO 19610826 657041858 4670360 262921

BACKUPBUFFER 25824541 648582462 2970 556802

ASYNC_IO_COMPLETION 577 638762337 48232539 52

ASYNC_NETWORK_IO 190891365 251906374 20017 14915510

OLEDB 3647296854 119428335 558205 0

SOS_SCHEDULER_YIELD 665857762 117258566 127194 116444918

PAGEIOLATCH_EX 39470662 97370920 22808 598543

WRITELOG 29602224 96961716 7191 3102182

MSQL_XP 191835 15575437 190774 0

You can get the PLE from the SQL server by below command:

SELECT getdate() [current_time],[counter_name],[cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Manager%'

AND [counter_name] = 'Page life expectancy'

end

[Matt_Fraser]

Hi James,

To be honest, I don't really monitor Page Life Expectancy much. I think it can be difficult to really have a sense of how your server is performing from this single number, but I guess that's true of any single number. However, I took a look, and over the past 24 hours, the average PLE for my production R/3 system is close to 1,200. However, that number is misleading, as of course it's very high during the off-hours. If I look at business hours average PLE only, it's much, much lower, around 150.

FYI, the easy way to get this number is via DBACOCKPIT, drill into Performance, History, Database Collector Data, Time Series (Category = Overview, Memory (Detailed), or Memory (Overview)). You can get the other wait_type data you mentioned in DBACOCKPIT as well, from Performance, Wait Events. You know, for those who are averse to writing SQL queries.

There are a couple of really good articles about PLE that explain why you have to look at PLE with a skeptical eye, and dig deeper to know if it really represents a problem or not: Page Life Expectancy isn't what you think... - Paul S. Randal, and Page Life Expectancy and Finding Queries using a Specific Index. The discussions in the comments on both are quite interesting.

In my case, I do have some memory pressure going on. This is an older server (due to be replaced in two weeks!), running both central and database instance, so although it has 20 GB of RAM, it has only 7 GB fixed for SQL Server. So, my data cache hit ratios can be all over the map. Sometimes it looks pretty good, other times it's quite low. PAGEIOLATCH_SH tends to be close to the top of wait types, like yours (my numbers are higher than yours, though) -- it's third in the list for total wait time. I think this is pretty normal, or at least it's been my experience of normal. If I sort by time per request, it's way down the list, but for total requests again it's high, 2nd overall. I will be very interested to see how these numbers change after the migration to new hardware, after several weeks of activity. The new hardware will not only have a lot more RAM, I'll also have a dedicated database instance, so most of the RAM will be available for SQL Server. The I/O subsystem will be a little bit faster, but not a lot, so I'm not sure yet how that will impact things.

Anyway, I'm hoping your post here generates some further discussion. I think it could be an interesting one.

Cheers,

Matt