Performance and Tuning: Page Life Expectancy

SQL Server 2012 – Performance and Tuning: Page Life Expectancy

The Buffer Manager object provides counters to monitor how SQL Server uses:
* Memory to store data pages.
* Counters to monitor the physical I/O as SQL Server reads and writes database pages.
* Buffer pool extension to extend the buffer cache by using fast non-volatile storage such as solid-state drives (SSD).

A Performance Profile: how one would approach performance tuning each.  The performance profile of OLTP differs significantly from a Relational Data Warehouse or Reporting application.  It is helpful to understand these differences and the objectives for high performance.

OLTP applications are characterized by high volumes of small identical transactions.  These can include SELECT, INSERT, UPDATE and DELETE operations.  The implications are significant in terms of database design, resource utilization and system performance.

You can find the value of the PLE by running the following query.

SELECT [object_name],

[counter_name],

[cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%Manager%’

AND [counter_name] = ‘Page life expectancy’

There are performance problems if any of the following are true, here we go

Resource issue Rule Description Value Source Problem Description
Database Design Rule 1 High Frequency queries having # table joins >4 Sys.dm_exec_sql_text,

Sys.dm_exec_cached_plans

High Frequency queries with lots of joins may be too normalized for high OLTP scalability
Rule 2 Frequently updated tables having # indexes >3 Sys.indexes, sys.dm_db_operational_index_stats Excessive index maintenance for OLTP
Rule 3 Big IOs

Table Scans

Range Scans

>1 Perfmon object

SQL Server Access Methods

Sys.dm_exec_query_stats

Missing index, flushes cache
Rule 4 Unused Indexes index not in* * Sys.dm_db_index_usage_stats Index maintenance for unused indexes
CPU Rule 1 Signal Waits > 25% Sys.dm_os_wait_stats Time in runnable queue is

pure CPU wait.

Rule 2 Plan re-use < 90% Perfmon object

SQL Server Statistics

OLTP identical transactions should ideally have >95% plan re-use
Rule 3 Parallelism: Cxpacket waits >5% Sys.dm_os_wait_stats Parallelism reduces OLTP throughput
Memory Rule 1 Avg page life expectancy < 300 (seconds) Perfmon object

SQL Server Buffer Manager

SQL Server Buffer Nodes

Cache flush, due to big read

Possible missing index

Rule 2 Avg page life expectancy Drops by 50% Perfmon object

SQL Server Buffer Manager

Cache flush, due to big read

Possible missing index

Rule 3 Memory Grants Pending >1 Perfmon object

SQL Server Memory Manager

Current number of processes waiting for a workspace memory grant
IO Rule 1 Avg Disk seconds / read > 20 ms Perfmon object

Physical Disk

Reads should take 4-8ms with NO IO pressure
Rule 2 Avg Disk seconds / write > 20 ms Perfmon object

Physical Disk

Writes (sequential) can be as fast as 1ms for transaction log.
Rule 3 Big IOs

Table Scans

Range Scans

>1 Perfmon object

SQL Server Access Methods

Missing index, flushes cache
Rule 4 If Top 2 values for wait stats are any of the following:

1. ASYNCH_IO_COMPLETION

2. IO_COMPLETION

3. LOGMGR

4. WRITELOG

5. PAGEIOLATCH_x

Top 2 Sys.dm_os_wait_stats If top 2 wait_stats values include IO, there is an IO bottleneck
Blocking Rule 1 Block percentage > 2% Sys.dm_db_index_operational_stats Frequency of blocks
Rule 2 Block process report 30 sec Sp_configure, profiler Report of statements
Rule 3 Avg Row Lock Waits > 100ms Sys.dm_db_index_operational_stats Duration of blocks
Rule 4 If Top 2 values for wait stats are any of the following:

1. LCK_x

Top 2 Sys.dm_os_wait_stats If top 2 wait_stats values include locking, there is a blocking bottleneck

OLTP identical transactions mean plan re-use is desirable.  CPU utilization can thusly be reduced with plan re-use and join reduction.  IO performance can be improved with good indexing, join reduction, and high page life expectancy.  Sorts can be limited with index usage.  Blocking can be reduced with index design and short transactions.

 

Mehboob
— Microsoft Certified Solutions Associate (MCSA)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: