PLE
UnfortRAMs are limited
Unfortunately, RAMs are limited and SQL Server needs memory. If you wonder that you have any memory bottleneck, PLE is the great measure to look at. As summary PLE is the time that your data remains in cache. If PLE is more than thousands that is OK for SQL Server. All SQL books tells 300 is OK for PLE but depending on technology and huge RAM usages shows me that Microsoft rearrange this limit. If PLE value is less than 1000, it means that your data always travelling between your disk and cache (which is called swapping) and your cache-hit ratio is terrible low.
SELECT
*
FROM sys.dm_os_performance_counters
WHERE dm_os_performance_counters.object_name LIKE ‘%Buffer Manager%’
AND dm_os_performance_counters.counter_name = ‘Page life expectancy’;
Performing huge reports may decrease PLE value. So it is better to observe this parameter for a while. Creating new indexes or adding new conditions to your queries may result to read less data and use less RAM. Otherwise you should think to add more memory to your SQL instance.
Comments are closed