Home
  •  

Page Life Expectancy

Microsoft.SQLServer.2016.DBEngine.PageLifeExpectancyMonitor (UnitMonitor)

Page Life Expectancy (s) for 2016 DB Engine

Knowledge Base article:

Summary

Page Life Expectancy is number of seconds a page will stay in the buffer pool without references. High page life expectancy means that required data can be found in cache instead of going to hard drive.

Causes

Extremely low Page Life Expectancy values can be an indication of:

Too small memory on the system

Too small memory configured for SQL Server’s use

Poor index design

Resolutions

Check I/O system to verify how it handles the load, if values of Avg. Disk sec/Read and Avg. Disk sec/Write counters for object PhysicalDisk are high (usually higher than 10 msec.), it means your system may overloaded or opportunities exist for query and index improvement.

Buffer Pool can be overloaded because SQL Server works with high amount of tables simultaneously, or full scanning is used in place of search by criteria. Check SQL Server:Access Methods:Full Scans/sec counter to verify the problem.

You may use SQL Server Profiler and browse Showplan Statistics in the Performance category to find an application that cause full scanning.

Overrideable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

Error

Enabled

Enables or disables the workflow.

Yes

Generates Alerts

Defines whether the workflow generates an Alert.

Yes

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

300

Number of samples

Indicates how many times a measured value should breach a threshold before the state is changed.

6

Threshold

The collected value will be compared against this parameter.

300

Element properties:

TargetMicrosoft.SQLServer.2016.DBEngine
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityError
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2016.DBEngine.PageLifeExpectancy.MonitorType
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL 2016: Page Life Expectancy is too low
Page Life Expectancy of SQL instance "{0}" on computer "{1}" is too low. See "Alert Context" tab for more details.
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2016.DBEngine.PageLifeExpectancyMonitor" Accessibility="Public" Enabled="true" Target="SQL2016Core!Microsoft.SQLServer.2016.DBEngine" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2016.DBEngine.PageLifeExpectancy.MonitorType" ConfirmDelivery="false">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2016.DBEngine.PageLifeExpectancyMonitor.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter2>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Normal" MonitorTypeStateID="Normal" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <IntervalSeconds>300</IntervalSeconds>

    <Threshold>300</Threshold>

    <NumSamples>6</NumSamples>

  </Configuration>

</UnitMonitor>