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:
Trudno powiedzieć, najlepiej zmierzyć co się dzieje:
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ć:
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.
SQL 
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
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:
