在查詢中刪除 PLE

在公司中，我們正在從事非常大的數據庫項目。它使用 100GB 記憶體。奇怪的是，在第一次執行查詢 PLE 之前是 11k~，執行後它下降到大約 70，無論如何，當 15 分鐘後我再次檢查 PLE 時，它大約是 1k~，當我再次執行查詢時它下降到 60。為什麼會這樣？如果在執行查詢之間的時間 PLE 增加，這是否意味著所有需要的數據都在記憶體中？如果是這樣，為什麼在 15 分鐘後執行相同的查詢會導致 PLE 再次下降？

這是查詢：

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')

我有一個聚集索引datetimestamp和非聚集索引typeid, datetimestamp，resultdatetime, resultid, typeid還有其他幾個…… @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

返回

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

這裡是計數器，因為 freepages 在 2012 版本中被刪除了，所以我添加了一些其他計數器

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 003 4892 65792
MSSQL$ServerNameC3SCW:Buffer Node Database pages 002 2938151 65792
MSSQL$ServerNameC3SCW:Buffer Node Page life expectancy 002 4051 65792
MSSQL$ServerNameC3SCW:Buffer Node Database pages 001 3002872 65792
MSSQL$ServerNameC3SCW:Buffer Node Page life expectancy 001 4052 65792
MSSQL$ServerNameC3SCW:Buffer Node Database pages 000 2440433 65792
MSSQL$ServerNameC3SCW:Buffer Node Page life expectancy 000 4052 65792
MSSQL$ServerNameC3SCW:Memory Manager Database Cache Memory (KB) 90855800 65792
MSSQL$ServerNameC3SCW:Memory Manager Free Memory (KB) 286672 65792
MSSQL$ServerNameC3SCW:Memory Manager Memory Grants Pending 0 65792
MSSQL$ServerNameC3SCW:Memory Manager Total Server Memory (KB) 104857608 65792
MSSQL$ServerNameC3SCW:Memory Node Database Node Memory (KB) 003 23804152 65792
MSSQL$ServerNameC3SCW:Memory Node Free Node Memory (KB) 003 73256 65792
MSSQL$ServerNameC3SCW:Memory Node Database Node Memory (KB) 002 23505208 65792
MSSQL$ServerNameC3SCW:Memory Node Free Node Memory (KB) 002 76392 65792
MSSQL$ServerNameC3SCW:Memory Node Database Node Memory (KB) 001 24022976 65792
MSSQL$ServerNameC3SCW:Memory Node Free Node Memory (KB) 001 77504 65792
MSSQL$ServerNameC3SCW:Memory Node Database Node Memory (KB) 000 19523464 65792
MSSQL$ServerNameC3SCW:Memory Node Free Node Memory (KB) 000 59520 65792

和查詢後的計數器

object_name counter_name instance_name cntr_value cntr_type
MSSQL$ ServerNameC3SCW:Buffer Manager Database pages 11355652 65792
MSSQL$ ServerNameC3SCW:Buffer Manager Checkpoint pages/sec 1054000434 272696576
MSSQL$ ServerNameC3SCW:Buffer Manager Page life expectancy 310 65792
MSSQL$ ServerNameC3SCW:Buffer Node Database pages 003 2980418 65792
MSSQL$ ServerNameC3SCW:Buffer Node Page life expectancy 003 5417 65792
MSSQL$ ServerNameC3SCW:Buffer Node Database pages 002 2946298 65792
MSSQL$ ServerNameC3SCW:Buffer Node Page life expectancy 002 4591 65792
MSSQL$ ServerNameC3SCW:Buffer Node Database pages 001 2995850 65792
MSSQL$ ServerNameC3SCW:Buffer Node Page life expectancy 001 155 65792
MSSQL$ ServerNameC3SCW:Buffer Node Database pages 000 2433086 65792
MSSQL$ ServerNameC3SCW:Buffer Node Page life expectancy 000 165 65792
MSSQL$ ServerNameC3SCW:Memory Manager Database Cache Memory (KB) 90845216 65792
MSSQL$ ServerNameC3SCW:Memory Manager Free Memory (KB) 219240 65792
MSSQL$ ServerNameC3SCW:Memory Manager Memory Grants Pending 0 65792
MSSQL$ ServerNameC3SCW:Memory Manager Total Server Memory (KB) 104857608 65792
MSSQL$ ServerNameC3SCW:Memory Node Database Node Memory (KB) 003 23843344 65792
MSSQL$ ServerNameC3SCW:Memory Node Free Node Memory (KB) 003 51864 65792
MSSQL$ ServerNameC3SCW:Memory Node Database Node Memory (KB) 002 23570384 65792
MSSQL$ ServerNameC3SCW:Memory Node Free Node Memory (KB) 002 59680 65792
MSSQL$ ServerNameC3SCW:Memory Node Database Node Memory (KB) 001 23966800 65792
MSSQL$ ServerNameC3SCW:Memory Node Free Node Memory (KB) 001 58144 65792
MSSQL$ ServerNameC3SCW:Memory Node Database Node Memory (KB) 000 19464688 65792
MSSQL$ ServerNameC3SCW:Memory Node Free Node Memory (KB) 000 49552 65792
Target Server Memory (KB) MSSQL$GKKTSQLC3SCW:Memory Manager 104857608 65792

SELECT @@version返回：

微軟 SQL Server 2012 - 11.0.5058.0 (X64)
2014 年 5 月 14 日 18:34:29
版權所有 (c) 微軟公司
企業版：Windows NT 6.3（內部版本 9600：）上基於核心的許可（64 位）

在執行計劃中，我可以看到聚集索引查找的成本為 100%，排序、雜湊匹配的成本為 0%。有一個關於缺少索引的提示，但對於一個有 2900 萬行的表來說，這將是一個超過 5 列的附加索引。

查詢刪除 PLE 並不罕見。查詢可以消耗任意數量的記憶體。這通常有兩個原因：

1. 用於排序和散列的工作記憶體。
2. 用讀取的數據填充緩衝池。

您可以通過在查詢執行時查看來自相應 DMV 的未完成的記憶體授予來證明 (1) 正在發生。如果查詢消耗大量記憶體，您應該嘗試實現不需要該記憶體量的執行計劃。您還可以嘗試使用資源調控器來限制消耗的記憶體。

可以通過嘗試使查詢讀取更少的數據來改善問題 (2)。在這裡，您將創建一個索引，其中設置了支持where子句的鍵列和覆蓋選定列的包含列。您可以啟用DATA_COMPRESSION.

通常，由於“頁面不受歡迎”，大量掃描不會對緩衝池造成破壞。處理新讀取的大型掃描頁面，以便它們是在必須釋放記憶體時被驅逐的第一個，而不是最後一個頁面。我不記得確切的規則，但通常 SQL Server 可以掃描巨大的數據集而不會殺死緩衝池。

因此，作為一個實驗，創建該索引（如果可能）和/或觀察查詢記憶體授予。

引用自：https://dba.stackexchange.com/questions/139468