Dropping PLE on query

In company we are working in project on very big database. It uses 100GB RAM. What's weird before first running a query PLE is 11k~, after running it drops to about 70, anyway when after 15 mins I check PLE again its about 1k~ and when I run query again it drops to 60. Why is it happening? If in time between running queries PLE increase doesn’t it mean that all needed data is in cache? If so why then running same query after 15 minutes cause PLE to drop again?

Here is the query:

select
    ResultType = case r.TypeID
        when 'dlp' then 'DLP'
        when 'bill' then 'BILL'
        when 'evtlog' then 'EVTLOG'
    end,
    SerialNumber, 
    ESerialNumber, 
    ResultDateTime, 
    DateTimeStamp as SavedInSystem
from 
    Results r
where
    TypeID in ('typeid1','typeid2')
    and DateTimeStamp > '2016-05-19 23:00:00'
    and SerialNumber in ('serialnumber')

I have a clustered index on datetimestamp and non clustered on typeid, datetimestamp and resultdatetime, resultid, typeid and few others...
@update

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

returns

Memory_usedby_Sqlserver_MB  Locked_pages_used_Sqlserver_MB  Total_VAS_in_MB process_physical_memory_low process_virtual_memory_low
102569  0   134217727   0   0

and here are counters, since freepages was removed in 2012 version i added few other counters

```
object_name counter_name instance_name cntr_value cntr_type
MSSQL$ServerNameC3SCW:Buffer Manager Database pages 11356975 65792
MSSQL$ServerNameC3SCW:Buffer Manager Checkpoint pages/sec 1053996662 272696576
MSSQL$ServerNameC3SCW:Buffer Manager Page life expectancy 4233 65792
MSSQL$ServerNameC3SCW:Buffer Node Database pages 003 2975519 65792
MSSQL$ServerNameC3SCW:Buffer Node Page life expectancy 0

It is not unusual for a query to drop PLE. Queries can consume arbitrary amounts of memory. This generally happens for two reasons:

• Work memory for sorting and hashing.

• Filling the buffer pool with data that was read.

You can prove that (1) is happening by watching outstanding memory grants from the respective DMV while the query runs. If the query consumes large amounts of memory you should try to achieve an execution plan that does not need that amount of memory. You can also try to limit the memory consumed using resource governor.

Issue (2) can be ameliorated by trying to make the query read less data. Here, you'd create an index with the key columns set to support the where clause and the include columns to cover the selected columns. You can enable DATA_COMPRESSION.

Normally, huge scans are not devastating for the buffer pool because of "page disfavoring". Newly read pages of large scans are treated such that they are the first, not the last, pages to be evicted when memory must be freed. I don't recall the exact rules but generally SQL Server can scan huge data sets without killing the buffer pool.

So as an experiment create that index (if possible) and/or watch query memory grants.

StackExchange Database Administrators Q#139468, answer score: 5