Page Life Expectancy

Microsoft.SQLServer.Linux.Monitor.DBEngine.PageLifeExpectancy (UnitMonitor)

Page Life Expectancy (in seconds) for 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 may indicate:

Not enough system memory
Not enough memory for SQL Server DB Engine
Poor index design

Resolutions

Check I/O system to verify how it handles the load, if values of Average Disk sec/Read and Average 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 per Second 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.

Overridable Parameters

Name	Description	Default Value
Alert Priority	Defines Alert Priority.	Normal
Alert Severity	Defines Alert Severity.	Error
Enabled	Enables or disables the workflow.	No
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
Synchronization Time	The synchronization time specified by using a 24-hour format. May be omitted.
Threshold	The collected value will be compared against this parameter.	300
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	200
Timeout for query execution (seconds)	The workflow will fail and register an event, if the query execution takes longer than the specified period.	60
Timeout for database connection (seconds)	The workflow will fail and register an event, if it cannot access the database during the specified period.	15

Element properties:

Target

Microsoft.SQLServer.Linux.DBEngine

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

False

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.Linux.MonitorType.DBEngine.PageLifeExpectancy

Remotable

True

Accessibility

Public

Alert Message

MSSQL on Linux: Page Life Expectancy is too low

Page life expectancy on SQL Server instance "{1}", computer "{0}" is too low. See the "Alert Context" tab for more details.

RunAs

Default

Source Code:

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

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.Linux.Monitor.DBEngine.PageLifeExpectancy.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</AlertParameter1>

      <AlertParameter2>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</AlertParameter2>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <MachineName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>

    <NetbiosComputerName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>

    <InstanceName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>

    <ConnectionString>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>

    <InstanceVersion>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>

    <InstanceEdition>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <Threshold>300</Threshold>

    <NumSamples>6</NumSamples>

    <TimeoutSeconds>200</TimeoutSeconds>

    <SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

    <PerformanceCounterObject>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/PerformanceCounterObject$</PerformanceCounterObject>

  </Configuration>

</UnitMonitor>