Page life expectancy spada do 0

Witam

Jak w temacie mam problem z tym że Page life expectancy spada do 0, pomimo że

a mam dostępne:

Problem pojawił się po przeniesieniu plików tempdb na osobny dysk (któremu zmieniłem rozmiar alokacji 64KB jak zalecane jest), oraz ustawiłem:

Dodam że inny dysk na którym są pliki (.mdf) ma alokacje 4KB czy to może powodować problem ? Czy to że np. użytkownik SQL nie jest dodany Lock pages in memory ?

Trudno powiedzieć, najlepiej zmierzyć co się dzieje:

• waity
• statystyki I/O
• porównać do jakiegoś baseline'a (o ile taki masz)

Na logikę, skoro page life expectancy zmierza do 0, tzn. że jest duża presja na pamięć (bufory w pamięci są zapełnione i serwer robi miejsce na nowe strony)
To co mogło się wydarzyć:

• zmiany planów zapytań (było po indeksach, a teraz jakieś zapytanie robi full table scan, albo był hash a teraz leci nested loop)
• doszły nowe aplikacje ze słabymi zapytaniami
• nadszedł ten czas w miesiącu, gdzie lecą jakieś raporty, przebudowy indeksów itp.

Szukałbym, która aplikacja generuje dużo I/O i zastanowił się dlaczego i czy to normalne dla tej aplikacji. Nie wiem czy przeniesienie tempa na inne dyski mogło być powodem zmiany planów zapytań.

Dodam że tempdb były zmieniane z 5 na 8 na stała wartość

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 10000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp2, SIZE = 10000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp3, SIZE = 10000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp4, SIZE = 10000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp5, SIZE = 10000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp6, SIZE = 10000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp7, SIZE = 10000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = temp8, SIZE = 10000MB, FILEGROWTH = 512MB);
GO

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 5000MB, FILEGROWTH = 256MB);
GO

SELECT name, physical_name, size/128 AS size_MB FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
GO

SELECT file_id, io_stall_read_ms, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), NULL);

SELECT wait_type, waiting_tasks_count, wait_time_ms / 1000 AS wait_time_sec
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('WRITELOG', 'PAGEIOLATCH_SH', 'PAGEIOLATCH_EX')
ORDER BY wait_time_sec DESC;

A różnice alokacji

4KB -> dla dysku na którym są .mdf
64KB -> dla dysku na którym są tempdb

Nowe aplikacje nie doszły, raporty teraz też się nie mielą jakieś duże :(

Czy ten nowy dysk, to jest dedykowany pod tempdb? Czy może jest współdzielony z innym workloadem? Jak jest podłączony do serwera? Co to za dysk (model)?
Jak wygląda użycie tego dysku od strony Performance Monitora? Przez jaki % czasy się nudzi? Jaka jest średnia długość kolejki requestów?

Wstaw coś więcej.

SELECT log_pool_cache_size_mb, log_pool_cache_used_size_mb  FROM sys.dm_os_sys_info;

SELECT
    instance_name AS DatabaseName,
    cntr_value AS LogFlushesPerSec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log Flushes/sec';

SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL);

SELECT wait_type, waiting_tasks_count, wait_time_ms / 1000 AS wait_time_s
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE '%IO%'
ORDER BY wait_time_ms DESC;

Wg AWS allocation size nie ma zbyt dużego znaczenia -> https://aws.amazon.com/blogs/modernizing-with-aws/do-aws-customers-benefit-from-64kb-block-size-for-sql-server/

Dobrze byłoby sprawdzić, które transakcje mają największy wkład w tego WRITELOGa.

SELECT 
    transaction_id, 
    database_id, 
    DB_NAME(database_id) AS database_name,
    database_transaction_begin_time, 
    database_transaction_log_bytes_used / 1024 AS log_kb_used,
    database_transaction_log_bytes_reserved / 1024 AS log_kb_reserved
FROM sys.dm_tran_database_transactions
ORDER BY log_kb_used DESC;

(do powyższego możesz dorzucić joina z sys.dm_exec_requests i pogrupować np. po userze i jeśli któryś user generuje ewidentnie dużo obciążenia, to przyjrzeć się bliżej sprawie).

Nie jest dedykowany, aczkolwiek na ten moment są na nim jedynie tempdb'

Mam wersje

Microsoft SQL Server 2019 (RTM-CU30) (KB5049235) - 15.0.4415.2 (X64) Nov 18 2024 17:45:37 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor)

Natomiast:

SELECT
COUNT() AS Log_Pages,
COUNT() * 8 / 1024 AS Log_Buffer_MB
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID() AND page_type = 'LOG';

SELECT
instance_name AS DatabaseName,
cntr_value AS LogFlushesPerSec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log Flushes/sec'
order by LogFlushesPerSec desc;

SELECT wait_type, waiting_tasks_count, wait_time_ms / 1000 AS wait_time_s
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE '%IO%'
ORDER BY wait_time_ms DESC;

Wygląda, że masz bardzo dużo log flush na sekundę, czyli albo log buffer za mały, albo masz od groma maleńkich transakcji. Dużo log flushów -> presja na dysk -> presja na pamięć -> niskie page life expectancy.

Ile TPS ?

SELECT 
    instance_name AS DatabaseName, 
    cntr_value AS TransactionsPerSecond
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
AND instance_name <> '_Total'
ORDER BY cntr_value DESC;

Jak to wygląda per baza.

SELECT 
    instance_name AS DatabaseName, 
    cntr_value AS TransactionsPerSecond
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
AND instance_name <> '_Total'
ORDER BY cntr_value DESC;

Może ostatnio doszły jakieś nie cachowane sekwencje, które generuję te częste log flushe ?

SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.reads,
    r.writes,
    r.logical_reads,
    est.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) est
WHERE est.text LIKE '%NEXT VALUE FOR%'
ORDER BY r.writes DESC;

Która baza generuje najwięcej log flush?

WITH LogFlushStats AS (
    SELECT 
        database_id,
        DB_NAME(database_id) AS DatabaseName,
        num_of_log_flushes,
        (num_of_log_flushes * 1.0) / NULLIF(io_stall_write_ms, 0) AS LogFlushesPerMs
    FROM sys.dm_io_virtual_file_stats(NULL, NULL)
    WHERE num_of_log_flushes > 0
)
SELECT 
    DatabaseName,
    num_of_log_flushes AS TotalLogFlushes,
    LogFlushesPerMs * 1000 AS LogFlushesPerSec
FROM LogFlushStats
ORDER BY LogFlushesPerSec DESC;

dla:

SELECT
instance_name AS DatabaseName,
cntr_value AS TransactionsPerSecond
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
AND instance_name <> '_Total'
ORDER BY cntr_value DESC;

Ta pierwsza wartość to tempdb

zmieniłem

EXEC sp_configure 'recovery interval (min)', 5;
RECONFIGURE;

ale również nie zdało egzaminu

Pytanie, które sesje generują tyle TPSów na tempdb? I co on robią?

SELECT 
    req.session_id,
    req.command,
    req.database_id,
    DB_NAME(req.database_id) AS DatabaseName,
    req.status,
    req.cpu_time,
    req.reads,
    req.writes,
    req.row_count,
    est.text AS sql_text
FROM sys.dm_exec_requests req
JOIN sys.dm_exec_sessions ses ON req.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) est
WHERE req.database_id = 2  -- tempdb 
ORDER BY req.writes DESC;

jedynie:

dla tempdb

Ale moment jednak po zmianie:

EXEC sp_configure 'recovery interval (min)', 5;
RECONFIGURE;

Problem występował nadal, ale po wykonaniu:

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
GO

i zostawieniu

recovery interval (min)', 5

mam

Jednak presja na presję na log flush chyba jest dalej dość spora

Skoro wcześniej zrobiłeś recovery interval -> 5 i nie pomogło, tzn. że częsty checkpoint prawdopodobnie nie był przyczyną. Jeśli pomogło FREEPROCCACHE, to możliwe, że był tam jakiś słaby plan wykonania, który powodował nadmierne I/O.

Co do loga, to pozostaje obserwować (np. co jakiś czas zapisywać wynik ostatniego zapytania, które podałem) i sprawdzić, które sesje generuję te zapisy. Możliwe, że któraś ma okres pobierania sekwencji, które nie są cachowane (lektura: https://sqlperformance.com/2018/11/sql-performance/understanding-log-buffer-flushes ) i problem za jakiś czas powróci.

Racja i długo nie trzeba było czekać aby to nastąpiło :/ dodatkowo zauważyłem że ile nie przydzielę RAM to tyle SQL Serwer zje (przed zmianą plików temp) którye były jak niżej:

Następną rzecz jaką zaobserwowałem to to że PLE spada do 0 po indeksacji indeksów, która wynika z maintance plan na poszczególnych bazach danych

Okazuje się jednak że rozmiar alokacji miał w tym wypadku znaczenie, mix w postaci takiej że 4KB było dla dysku z bazami .mdf i .log , a 64KB dla dysku na którym są (były) pliki temp.db powodował problem po zmienieniu na 4KB problem przestał się pojawiać po indeksacjach