The Memory - Page Life Expectancy alarm becomes active when the page life expectancy falls below a threshold.
A low page life expectancy indicates that Azure SQL Managed Instance is evicting pages from the buffer cache thus reducing the value of the cache. This has the effect of requiring Azure SQL Managed Instance to perform more disk reads. Performing extra disk reads degrades Azure SQL Managed Instance performance.
This often has one of two causes: Azure SQL Managed Instance has insufficient memory to work with, or SQL queries are accessing a very large number of pages in a non-sequential manner. Microsoft recommends that page life expectancy be greater than 5 minutes (300 seconds).
When the alarm is raised:
- Ensure that Azure SQL Managed Instance is configured to use as much physical memory as possible. Check, and if necessary alter, the Max Server Memory (MB) parameter in the SQL Server | Configuration drilldown.
- If you have applications other than Azure SQL Managed Instance running on the Windows server, and the SQL Memory gauge on the Spotlight Overview page shows that Azure SQL Managed Instance is not using all the memory it could, then these applications could be taking memory away from Azure SQL Managed Instance. Consider increasing your Azure SQL Managed Instance Min Memory parameter setting so that Azure SQL Managed Instance gets more memory. You can check this setting on the SQL Server | Configuration drilldown.
- Consider adding more physical RAM to the server.
- Identify inefficient SQL using the SQL Server | SQL Activity drilldown | Sessions page. Look for sessions that are active and that are generating a large number of I/O operations. The Sessions page | Session Trace sub-page or Microsoft’s SQL Server Profiler tool can help here.