Which cup will you prefer to go the less number of times to the coffee maker? |
image was taken from here |
Let's say one day you lose your cup and you have to grab one of the tiny-ugly cups available in the office, now you have to put the coffee on it several times to drink the same 24 oz, this means you will have to leave your desk several times.
Something similar happens with Page Life Expectancy (PLE). This is a measure that indicates us how long a data page stays in memory(in seconds) so it can be reused by other SQL Processes without having to read the same page from disk again (which translates in less I/O and less overhead). So with this in mind, the higher the value the better, but, how much is high enough for our system?
Right PLE value for my SQL Server
Long time ago, when servers were not so powerful, the recommendation was to have our PLE higher than 300, but now times have changed and servers too, so the recommendation is to have a PLE higher than 300 for each 4 GB of RAM assigned to SQL Server if you have Max server memory set (or for your server memory if you don't have a maximum set to SQL Server).
Keep in mind that you have to measure this value over a period of time and then average it to have a more consistent value or to identify sudden drops and correlate them with queries executed at that time.
To Calculate it
I take it from performance counters, more specifically from the Buffer Manager counter.
To calculate it I use the following T-SQL (please note that this only gives you the current value):
DECLARE @SQLmemory NUMERIC(9, 2) DECLARE @Maxmemory INT SET @SQLmemory = ( SELECT physical_memory_kb / 1024.0 / 1024.0 FROM sys.dm_os_sys_info ) SET @Maxmemory = ( SELECT convert(INT, value_in_use) FROM sys.configurations WHERE [name] = 'max server memory (MB)' ) IF (@Maxmemory) <> 2147483647 BEGIN SET @SQLmemory = @Maxmemory / 1024.0 END SELECT [counter_name] ,@SQLmemory AS [Server Memory_GB] ,cast([cntr_value] AS INT) AS [PLE_Current] ,(@SQLmemory / 4) * 300 AS [PLE_Expected] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy'
What if the value is too low?
This means SQL Server has to flush memory very often. this could be a symptom that your SQL Server is struggling with the currently allocated memory, so you need to tune your Database checking (but not limited to):
- Check your indexes (missing or unused)
- Select statements that return data you don't need
- Tune your queries
- Check your statistics
- Check for ad-hoc queries
There are 2 posts I want to recommend to for further reading about this topic:
NUMA considerations for PLE from Paul Randal
Fixing PLE from Steve Hood
Comments
Post a Comment