Memory drop

The alert will be triggered if a sudden memory drop occurs in the buffer pool.

Note, the alert will not be triggered, if a database on the server has been set offline or deleted within the last hour, or if a new database has been created on the server within the last hour. Nor will it be triggered if the physical memory on the server is 1024 MB or less.

To query the Performance Store database for memory drops, use:

select s1.server_name, null object_name, 1 [count]
from
(
    select c.server_name, c.memoryclerk_sqlbufferpool_size_mb current_buffer_pool_size_mb
    , l.last_hour_avg_size_mb
    , ((c.memoryclerk_sqlbufferpool_size_mb - l.last_hour_avg_size_mb) * convert(bigint, 100)) / l.last_hour_avg_size_mb deviation_buffer_pool_size_pct
    from dbo.v_memory_clerks c
    inner join dbo.v_servers s on s.server_name = c.server_name
    inner join
    (
        select c.server_name, avg(c.memoryclerk_sqlbufferpool_size_mb) last_hour_avg_size_mb
        from dbo.v_memory_clerks c
        where c.sample_time > dateadd(minute, -60, getutcdate())
        and c.sample_id not in
        (
            select top 1 xs.sample_id
            from dbo.v_execution_info xs
            where xs.batch_executing = 1
            order by xs.sample_time desc
        )
        group by c.server_name
    ) l on l.server_name = c.server_name
    where c.sample_id in
    (
        select top 1 xs.sample_id
        from dbo.v_execution_info xs
        where xs.batch_executing = 1
        order by xs.sample_time desc
    )
    and s.computer_name not in
    (
        select s.computer_name
        from dbo.v_servers s
        group by s.computer_name, s.physical_memory_mb
        having sum(convert(bigint, s.max_mem_mb)) >= s.physical_memory_mb
    )
    and not exists
    (
        select 1
        from dbo.v_offline_databases o
        where o.set_offline_date > dateadd(minute, -60, getutcdate()) 
        and o.server_name = c.server_name
    )
    and not exists
    (
        select 1
        from dbo.databases d
        where d.discovered = 0
        and d.sample_time > dateadd(minute, -60, getutcdate())
        and d.server_name = c.server_name
    )
    and not exists
    (
        select 1
        from dbo.databases d
        where d.create_date > dateadd(minute, -60, getutcdate())
        and d.sample_time > dateadd(minute, -60, getutcdate())
        and d.server_name = c.server_name
    )
    and s.physical_memory_mb > 1024
) s1
where s1.deviation_buffer_pool_size_pct < -50

Sudden memory drops can also be seen on the Memory dashboard:

To query the Performance Store database for historical memory data, use the dbo.v_hist_memory_clerks view.

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.