vinsim ple_SQL Server中的页面预期寿命（PLE）

vinsim ple

Page Life Expectancy (PLE) is an age of a data page in seconds in the buffer cache or buffer memory after querying the tables with the loading data page into the buffer memory. Page Life Expectancy value indicates the memory pressure in allocated memory to the SQL Server instance. In most of the cases, a page will be dropped from buffer periodically.

页面预期寿命（PLE）是查询表后将数据页面加载到缓冲存储器中后，数据在缓冲高速缓存或缓冲存储器中的使用期限（以秒为单位）。 Page Life Expectancy值指示分配给SQL Server实例的内存中的内存压力。 在大多数情况下，页面将定期从缓冲区中删除。

The basic fundamental of Microsoft SQL Server for querying data is to load the necessary data pages into the buffer pool from the disk and return it to the client. If the buffer does not have enough space to process it, then the oldest data pages will be eradicated to make some room as required for new pages.

Microsoft SQL Server查询数据的基本原理是将必要的数据页从磁盘加载到缓冲池中，然后将其返回给客户端。 如果缓冲区没有足够的空间来处理它，则最早的数据页将被删除，以便为新页腾出一些空间。

As a traditional standard, PLE (Page Life Expectancy) should not be less than 300 seconds (5 Minutes), but actually, that value can fluctuate based on the SQL Server load. Anyway, a buffer is managed by the numa node, and that can be divided into the multiple numa nodes. Even, it can be more challenging to manage load distribution for the various numas when heavy workload occurs. PLE can raise performance issues by building pressure on buffer when your PLE goes down and not coming up for a while. There can be many more activities which can cause this issue.

作为传统标准，PLE（页面预期寿命）不应少于300秒（5分钟），但实际上，该值会根据SQL Server负载而波动。 无论如何，缓冲区由numa节点管理，并且可以分为多个numa节点。 即使发生繁重的工作量时，管理各种数量的负载分配也可能更具挑战性。 当您的PLE下降而暂时没有上升时，PLE会通过在缓冲区上施加压力来提高性能问题。 可能还有更多活动可能导致此问题。

• Index Maintenance
  索引维护
• Big Queries (i.e., Report Query)
  大查询（即报表查询）
• Queries with an improper and less efficient execution plan
  执行计划不正确且效率较低的查询
• Data Migration Activity
  数据迁移活动
• Parameter Sniffing issue
  参数嗅探问题
• Duplicate Indexes
  索引重复
• Multiple Execution Plans for a single procedure and many more
  单个过程的多个执行计划等等

在T-SQL语句的帮助下为您SQL Server实例获取PLE (Get PLE for your SQL Server instance with the help of T-SQL statement)

SELECT*
FROM sys.dm_os_performance_counters
WHERE [counter_name] = 'Page life expectancy'

Here, cntr_value represents the PLE which is 29570 in the above query result set.

此处，cntr_value表示上述查询结果集中的PLE，其值为29570。

We always assume that the query performance is degrading by the lake of buffer memory, and SQL Server does not have enough buffer pool to cache the information. It is not blaming the side for the worst query performance because a query execution plan is everything. If your query plan is improper, then the table will be scanned with unnecessary loading data to the buffer memory.

我们始终假定查询性能会因缓冲内存的不足而下降，并且SQL Server没有足够的缓冲池来缓存信息。 它并没有责怪查询性能最差，因为查询执行计划就是一切。 如果查询计划不正确，则将扫描该表，并将不必要的数据加载到缓冲存储器中。

The above situations discuss the factors on what PLE can get down. But what about PLE getting down at a specific time? Hopefully, each production SQL Server will have the internal data maintenance task and standard maintenance, which is running by the SQL Server job and executing on the off-peak hours. Even, you can observe fluctuation in PLE (Page Life Expectancy) when any heavy I/O performs by the SQL Server with the big CRUD operation.

以上情况讨论了PLE降低的因素。 但是，PLE在特定时间下降呢？ 希望每个生产SQL Server都有内部数据维护任务和标准维护，这些任务由SQL Server作业运行并在非高峰时间执行。 甚至，当SQL Server使用大CRUD操作执行任何繁重的I / O时，您也可以观察到PLE（页面预期寿命）的波动。

监视页面预期寿命 (Monitoring Page Life Expectancy)

PLE counter value is an essential measurement of pressure on the buffer memory in units of seconds. Users can capture it with the help of SSMS, any other third-party tools, or Microsoft performance monitor; It returns average value always. Users can find the current value by the above T-SQL statement. Still, analysis is required in a particular period to see if fluctuation happens in point of time. Users can use the Windows Performance Monitor by adding the PLE counter to track the same.

PLE计数器值是对缓冲存储器上压力的基本度量，以秒为单位。 用户可以借助SSMS，任何其他第三方工具或Microsoft性能监视器来捕获它。 它总是返回平均值。 用户可以通过上面的T-SQL语句找到当前值。 尽管如此，仍需要在特定时期内进行分析，以查看时间点是否发生波动。 用户可以通过添加PLE计数器来跟踪Windows性能监视器。

Multiple SQL Server instances can be configured in single SQL Server. For monitoring a particular SQL Server instance, the user can select in the counter drop-down list of the performance monitor screen. In the below image, you can see that the PLE counter exists in MSSQL$JERRY: Buffer Manager.

可以在单个SQL Server中配置多个SQL Server实例。 为了监视特定SQL Server实例，用户可以在性能监视器屏幕的计数器下拉列表中选择。 在下图中，您可以看到PLE计数器存在于MSSQL $ JERRY：缓冲区管理器中。

Here, MSSQL$JERRY is SQL Server instance service details, and Buffer Manager is the object name.

在此，MSSQL $ JERRY是SQL Server实例服务的详细信息，而Buffer Manager是对象名。

For example, the above SQL Server instance service is restarted to get some fluctuation in Page Life Expectancy. After restarting it, the PLE count starts from 0 and increases with the querying data in the background. Again count is decreased when memory allocation is reduced and data is fetched from the tables in the background.

例如，重新启动上述SQL Server实例服务以使Page Life Expectancy有所波动。 重新启动后，PLE计数从0开始，并随着后台的查询数据增加。 当减少内存分配并从后台的表中获取数据时，计数也会再次减少。

Here, we can conclude that the PLE count will be decreased when buffer memory does not have enough space to store new data pages when any READ/WRITE operation is performed.

在这里，我们可以得出结论，当执行任何读/写操作时，如果缓冲存储器没有足够的空间来存储新的数据页，则PLE计数将减少。

如何计算页面预期寿命 (How to Calculate Page Life Expectancy)

As per Microsoft standard, we follow a rule of 300 seconds. If PLE goes less than 300 seconds (5 Minutes), then memory pressure is very high, and we have to take care of the performance side. Still, that calculation was for just 4GB memory allocation to the SQL Server. For your server, it should be different as per the formula. You should worry about the SQL Server instance when PLE goes down than the below calculation:

根据Microsoft标准，我们遵循300秒的规则。 如果PLE少于300秒（5分钟），则内存压力非常高，我们必须注意性能方面。 尽管如此，该计算仅是分配给SQL Server的4GB内存。 对于您的服务器，它应根据公式不同。 当PLE下降时，您应该担心SQL Server实例比以下计算方式更严重：

PLE (Page Life Expectancy) threshold = ((Buffer Memory Allocation (GB)) / 4 ) * 300

PLE（页面预期寿命）阈值=（（（缓冲内存分配（GB））/ 4）* 300

For example, the machine is configured with 128GB, and 110GB is allocated to the SQL Server instance, then the PLE threshold will be as below:

例如，计算机配置了128GB，并为SQL Server实例分配了110GB，则PLE阈值如下：

PLE (Page Life Expectancy) threshold = (110 / 4) * 300

PLE（页面预期寿命）阈值=（110/4）* 300

PLE (Page Life Expectancy) threshold = 8250 Seconds

PLE（页面预期寿命）阈值= 8250秒

No one can say that what number will be recommended for the standard threshold because each SQL Server has different configurations according to the use case and requirement. If your PLE goes down suddenly, then the first thing should be to check memory allocation. Is it enough amount of size for the SQL Server? If yes, then check the currently executing request, is there any long-running thread that consumes more memory? If yes, then review the currently executing query statement.

没有人会说建议为标准阈值使用多少数字，因为每个SQL Server根据用例和要求具有不同的配置。 如果您的PLE突然下降，那么第一件事应该是检查内存分配。 SQL Server是否足够大小？ 如果是，则检查当前正在执行的请求，是否有任何长时间运行的线程消耗更多的内存？ 如果是，则查看当前正在执行的查询语句。

The performance will be based on the disk, which is a bit slower than the primary memory. Eventually, it produces a lot of disk IO and stresses on the storage because the memory is not getting processed efficiently by the server. Unnecessary memory usage issue should be observed and take a chance to get it optimized with a proper investigation.

性能将基于磁盘，该磁盘比主内存要慢一些。 最终，由于服务器无法有效处理内存，因此会产生大量磁盘IO并给存储造成压力。 应该观察到不必要的内存使用问题，并有机会通过适当的调查对其进行优化。

• Which objects are using the more buffer cache memory?
  哪些对象正在使用更多的缓冲区高速缓存？
• Allotment of object indexes and partitions are accurate and effective?
  对象索引和分区的分配是否准确有效？

Users can monitor it with querying on sys.dm_os_performance_counters DMV to find that PLE threshold is crossing the underline or not. If the counter value found is less than your calculation, then it should trigger a mail to the responsible team to observe and monitor the memory pressure-related parameters. We get the indication regarding the increased pressure on the buffer pool when page life expectancy drops down to a typical value and stays there for a more extended period. Possibly this is the sign that we might need more memory. It is advisable to add more memory when the workload is hiking gradually, and PLE is moving downward sluggishly. However, when something else is using more memory (e.g., plan cache). In this scenario, buffer pool pressure is increased, but there is no change in workload. A consecutive monitor enables the ability to measure stuff and evaluate it over more issues. After doing this, it is possible to let OpsMgr check the value and generate an alert when the value meets a specific threshold for X values.

用户可以通过查询sys.dm_os_performance_counters DMV来监视它，以发现PLE阈值是否超过了下划线。 如果找到的计数器值小于您的计算，则它应触发一封邮件给负责的团队，以观察和监视与内存压力相关的参数。 当页面预期寿命下降到典型值并停留更长的时间时，我们得到有关缓冲池压力增加的指示。 可能这表明我们可能需要更多的内存。 建议在工作负载逐渐增加且PLE缓慢下降时添加更多内存。 但是，当其他东西正在使用更多的内存时（例如，计划缓存）。 在这种情况下，缓冲池压力会增加，但是工作负载不会改变。 连续的监视器使您能够测量物料并评估更多问题。 之后，可以让OpsMgr检查该值并在该值达到X值的特定阈值时生成警报。

结论 (Conclusion)

PLE (Page Life Expectancy) is not a single buffer manager parameter which can affect the SQL Server performance; there are many more in the list of buffer manager. We can consider that the buffer memory pressure can be the primary issue if found. Nowadays, we see bigger memory boxes and quite common to have PLE in the thousands range.

PLE（页面预期寿命）不是一个会影响SQL Server性能的缓冲区管理器参数。 缓冲区管理器列表中还有更多内容。 我们可以认为，如果发现缓冲区内存压力可能是主要问题。 如今，我们看到更大的存储盒，并且拥有数千个PLE的情况很普遍。

There are a lot of things to monitor for DBAs in the production environment. If you encounter any buffer memory side issue, Windows Performance Monitor helps a lot to observe real-time statistics.

在生产环境中，有很多东西需要监视DBA。 如果遇到任何缓冲内存方面的问题，Windows Performance Monitor可以帮助您观察实时统计信息。

翻译自: https://www.sqlshack.com/page-life-expectancy-ple-in-sql-server/

vinsim ple