1

Everyday we receive an alert on a specific period (03:00 AM - 04:00 AM) indicating that Page life expectancy is too low :

SCOM :Alert: SQL DB 2008 Engine Page Life Expectancy is too low

We have reviewed if any Memory intensive operation is running on this period but nothing was found.

We assume that the cause is a query run through a windows service that cause pages life in the buffer cache to reach a very low value (8 - 22 seconds)

what is a good way to troubleshoot this issue ?

Thanks Osama Waly

5

It's almost certainly going to be full backups or indexing. The queries won't show up as a bunch of memory allocated but rather as intensive disk IO. If you are tracking those counters then have a look. Some other easy ways to confirm that these are the cause of that disk IO:

  • Looking at agent job schedules. If it wasn't checkdb, or indexing, then apps often do their own internal database clean-ups (e.g. deleting records) late at night also.
  • When checkdb runs it will be written to the ERRORLOG, so run sp_readerrorlog and look for anything at the PLE drop time. This is also useful because some apps like OpsMgr will run their own indexing and checkdb routines at random times without using Agent jobs.
  • There are also other queries you can run to identify if indexes or stats were recently updated (you'd run it in each database and cast an eye over the results). They aren't 100% accurate but if you saw lots of indexes or stats updated at the PLE drop time it would be an indicator.

Otherwise just stay up late one night and run sp_WhoIsActive interactively to see what's happening. You can also make sp_WhoIsActive output to a table, which means you can schedule an Agent job to run it to a table a few times during your PLE drop window, and then check the output in the morning.

If it turns out to be checkdb or indexing this PLE behaviour is fairly normal and doesn't cause a problem unless you have a very high throughput or time sensitive system that's very busy that early in the morning; if nobody is complaining (and you don't see an increase in query timeouts at that time if you're even tracking them) then likely it's just smoke and no fire.

What if it's not checkdb or indexes? You could have a bad query compilation combined with special parameters suddenly chewing up disk IO and wrecking the buffer. If you're lucky it'll be obvious when you run sp_WhoIsActive, but if you're unlucky then you'd attempt to go through the plan cache using DMVs to find it.

That's a very tough thing to do and there are entire books on it; even if you have the scripts they often don't really aggregate how a plan changes for a query over time along with execution times. You can waste days and weeks trying to work it out while setting up extended events only to find they didn't capture the plan handle for some reason or it's a cursor and you don't know what it's really doing.

If you reach that point then tell your boss you need a tool to assist and buy a license of a proper monitor like SQL Sentry to deploy just during the real tricky problems like this. That's what I did. You can also use an evaluation or use another similar tool to drill down.

Anyway if it does turn out to be a false alarm, surprise, that's a common downside of OpsMgr. It may be amazing for some tasks but for SQL Server it's never been a good fit no matter how they try to shoehorn it. I haven't been able to tune its alerting to a functionally acceptable level so can't comment on improving that.

  • Massive +1 on your last paragraph about OpsMgr – Molenpad Jul 21 '16 at 12:33
1

Unfortunately PLE alone isn't going to tell you much.

What's your baseline PLE? Does it dip then steadily rise again, or does it stay low for an extended amount of time? Are you getting any performance hits?

My advice would be to build up a bigger picture of the issue by looking at other memory related performance measures.

Page Life Expectancy is just one of a number of measures that relate to memory pressure. It needs to correlated with other memory counters, such as Buffer Cache Hit Ratio, Stolen Memory, Lazy Writes per Second, Memory Grants, CPU utilisation - to name but a few off the top of my head.

Once you get an overall picture of a wider array of memory related counters you will be able to build up a picture of where the actual issue might lie - and branch off into different investigations.

All you have with the Low PLE alert is exactly that, an alert that PLE has dipped below your set threshold in SCOM (What is your threshold by the way?)

The below query will take a ten second sample and return some useful memory pressure counter, but is by no means extensive.

/* -- Get the Instance Name of the Current Connection -- */
DECLARE @instancename AS VARCHAR(20) = (
        SELECT @@SERVICENAME
        )

/* -- Take a 10 second sample of read/write activities -- */
DECLARE @PageReads BIGINT
DECLARE @PageWrites BIGINT
DECLARE @LazyWrites BIGINT

SET @PageReads = (
        SELECT [cntr_value]
        FROM sys.dm_os_performance_counters
        WHERE [object_name] LIKE '%Buffer Manager%'
            AND [counter_name] IN ('Page reads/sec')
        )
--,'Page writes/sec'
--,'Lazy writes/sec'
SET @PageWrites = (
        SELECT [cntr_value]
        FROM sys.dm_os_performance_counters
        WHERE [object_name] LIKE '%Buffer Manager%'
            AND [counter_name] IN (
                --'Page reads/sec'
                'Page writes/sec'
                )
        )
--,'Lazy writes/sec'
SET @LazyWrites = (
        SELECT [cntr_value]
        FROM sys.dm_os_performance_counters
        WHERE [object_name] LIKE '%Buffer Manager%'
            AND [counter_name] IN (
                --'Page reads/sec'
                --'Page writes/sec'
                'Lazy writes/sec'
                )
        )

WAITFOR DELAY '00:00:10'

/* -- Buffer Cache Hit Ratio -- */
--The % of requests that can be satisifed by pages already in the memory buffer.
--The higher the percentage the better the performance and less physical I/0 required.
SELECT 'Buffer cache hit ratio' AS TheCounter
    ,(a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS Value
FROM sys.dm_os_performance_counters a
JOIN (
    SELECT cntr_value
        ,OBJECT_NAME
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
        AND OBJECT_NAME = 'MSSQL$' + @instancename + ':Buffer Manager'
    ) b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
    AND a.OBJECT_NAME = 'MSSQL$' + @instancename + ':Buffer Manager'

UNION ALL

/*-- Page Life Expectancy --*/
--The measure, in seconds, of how long a page can remain in the buffer for before it
--is trashed to make room for more pages. A value of lower than 300 is generally seen
--as indicative of poor performance
SELECT counter_name AS TheCounter
    ,cntr_value AS Value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
    AND OBJECT_NAME = 'MSSQL$' + @instancename + ':Buffer Manager'

UNION ALL

--Total Server Memory vs. Target Server Memory
--Target Server Memory = The max available to SQL Server
--Total Server Memory = The amount of RAM SQL is actually using
SELECT counter_name AS TheCounter
    ,cntr_value AS Value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
        'Total Server Memory (KB)'
        ,'Target Server Memory (KB)'
        ,'Stolen Server Memory (KB)'
        )

UNION ALL

--Memory Grants Pending
--Should be <= 1. If higher than 1 it suggests that some operations
--are waiting for memory to come available before SQL Server will allow
--them to proceed.
SELECT 'Memory grants pending' AS TheCounter
    ,[cntr_value] AS Value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Memory Manager%'
    AND [counter_name] = 'Memory Grants Pending'

UNION ALL

SELECT [counter_name] AS TheCounter
    ,CASE 
        WHEN counter_name = 'Page reads/sec'
            THEN ([cntr_value] - @PageReads) / 10
        WHEN counter_name = 'Page writes/sec'
            THEN ([cntr_value] - @PageWrites) / 10
        WHEN counter_name = 'Lazy writes/sec'
            THEN ([cntr_value] - @LazyWrites) / 10
        END AS Value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
    AND [counter_name] IN (
        'Page reads/sec'
        ,'Page writes/sec'
        ,'Lazy writes/sec'
        )
1

Before you say PLE is too low, you should know threshold calculation for PLE. You can visit the URL to know that http://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy.

PLE alone don't impact performance of SQL server. There are various performance counter we should consider like Buffer cache hit ratio, disk queue length, checkpoint etc. But it's also true, "pressure on any part of server impact each other.

For example if your disk perform slow, increment in wait queue will impact you CPU and memory both.

So, I would suggest to use bellow query to find out high memory consuming query ([granted_memory_kb] column) and optimize that to use less memory or add more memory to server.

    select
                db_name(sp.dbid),sp.spid,er.wait_type,er.wait_time,er.wait_resource,er.total_elapsed_time,st.text,qp.query_plan
                ,ec.net_packet_size,ec.client_net_address,es.host_name,es.program_name,es.client_interface_name
                ,es.status,es.cpu_time,qmg.granted_memory_kb,es.total_scheduled_time,es.total_elapsed_time
                ,es.reads,es.writes,es.logical_reads

    from
                    sys.dm_exec_requests er
    inner join      master.dbo.sysprocesses sp
    on              er.session_id=sp.spid
    inner join      sys.dm_exec_connections ec
    on              er.session_id=ec.session_id
    inner join      sys.dm_exec_sessions es
    on              ec.session_id=es.session_id
    inner join      sys.dm_exec_query_memory_grants qmg
    on er.session_id=qmg.session_id
    cross apply     (select text from sys.dm_exec_sql_text(er.sql_handle)) st
    cross apply     (select * from sys.dm_exec_query_plan(er.plan_handle)) qp

Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.