Page life expectancy (PLE)
Når jeg bliver bedt om at kigge på en SQL Server, der performer dårligt, er der nogle faste ting jeg kigger efter; er serveren sat rigtigt op, hvordan bruges index, og endelig hvordan bruger SQL Serveren sin hukommelse (memory)?
Ofte kigges der på Page life expectancy (PLE) for at få en ide om SQL Serveren har nok memory. Men hvad er PLE, og hvordan hænger det egentligt sammen med hvordan SQL Serveren bruger memory?
SQL Serveren er meget afhængig af memory, da alle data du som bruger forespørger, er et smut forbi bufferen. Bufferen er et memory område, typisk den største forbruger af memory, hvor alle data og index pages læses op fra diske og herfra sendes videre til den bruger eller applikation, som har forespurgt data. Det vil sige, at nærmest alt det data, som kommer ud af SQL Serveren altså har været et smut forbi bufferen, som altså er en del af alt den memory SQL Serveren tager fra serveren.
Vi kan med følgende Query se hvor meget memory serveren har og hvor stor procentandel SQL Serveren tager af det:
SELECT m.physical_memory_kb / 1024.0 AS physical_mb,
(m.committed_kb * 1.0 / m.physical_memory_kb) * 100.0 AS sql_percentage
FROM sys.dm_os_sys_info m
sys.dm_os_sys_info er en system ”tabel”, som indeholder en række med informationer om SQL Serveren bla. Physical_memory_kb, hvilket er den totale mængde af memory i serveren og committed_kb hvilket er den andel hele SQL Serveren har taget.
Vi kan se hvor meget at den totale mængde memory bufferen tager med dette Query:
SELECT
COUNT(file_id) * 8/1024.0 AS buffer_size_mb
FROM sys.dm_os_buffer_descriptors
Dette er endnu en system ”tabel”, som indeholder informationer om de pages, der er læst op i bufferen lige nu. file_id eller page_id kan bruges, da de fremgår 1 gang pr page i bufferen, en page er 8kb så derfor er regnestykket antal_pages *8/1024.0 for at regne det om til mb.
For at få en ide om hvor stor en andel af det memory SQL Serveren tager er bufferen, kan vi sammenligne det med den totale mængde af ram som SQL Serveren har taget, ved at kigge på committed_kb fra tabellen sys.dm_os_sys_info, i mit eksempel:
SELECT
m.committed_kb/1024 AS committed_mb
FROM sys.dm_os_sys_info mSom omregnet til mb er:
Så af de godt 54 GB SQL Serveren tager udgør 43 GB bufferen.
Hvad er en page (meget kort fortalt)
SQL Serveren gemmer data og index på ”pages”, som er 8KB med en 96 bytes header. Disse pages læses fra disken op i bufferen. For at SQL Serveren kan arbejde så effektivt som muligt, er den afhængig af at have så mange pages, som bruges jævnligt, i bufferen så længe som muligt. Den tid pages er i bufferen kaldes Page life expectancy forkortet PLE, som er tid i sekunder en page er i bufferen.
Vi kan finde det aktuelle PLE ved at køre denne Query, som forespørger performance counters i SQL Serveren.
SELECT cntr_value AS ple
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy'(vær opmærksom på at hvis serveren kører med NUMA nodes, så kan PLE forespørges pr. NUMA node så skal object name være SQLServer:Buffer Node)
Forespørgslen returnerer et tal, som er tiden i sekunder en page er i bufferen:
Hvordan kan vi så bruge dette tal til at se om SQL Serveren har ram nok?
Er PLE lav betyder det, at pages ikke er i memory ret længe, hvilke kan betyde at SQL Serveren ikke har nok memory til rådighed. Der skal altså både frigives plads i bufferen og læses fra disken ofte, for at få pages op i bufferen.
Desværre findes der ikke et magisk tal man skal gå efter og desværre betyder et lavt PLE heller ikke altid at der er mangel på memory. Hvornår er PLE så højt nok? Ja det er et rigtig godt spørgsmål, som kan være svært at svare på entydigt. Men for at få en ide det, kan vi prøve at forholde os til hvor meget data der flyder gennem bufferen pr sec samt hvor meget memory SQL Serverens buffer har. Det kan give os en ide om der er nok memory i serveren, eller om vi skal til at kigge andre steder, for at finde vores performance udfordringer.
Jeg benytter et Query (kommer sidst i artiklen), som viser hvordan memory er fordelt samt lidt info om lazy writes (som kort fortalt er den proces, der sørger for at skrive dirty pages til disk og frigive dem, så der bliver plads i bufferen). CPU’en arbejder kun på pages, der ligger i buffer. Derfor vil der naturligt være et flow af pages ud og ind af bufferen, kunsten er at finde den rette mængde memory så SQL Serveren kan holde de pages, der benyttes ofte i bufferen og samtidig tillade dette flow af data.
Som sagt er det svært at komme med et entydigt tal for PLE, men har du 200 er det nok lige lavt nok og er den over 10.000 kan det være helt fint. Er du i tvivl, så er du velkommen til at kontakte mig eller en af mine dygtige kollegaer i Unit IT på tlf.: 88 333 333, så hjælper vi gerne med at kigge på din server.
DROP TABLE IF EXISTS #memory_info
SELECT m.committed_kb / 1024.0 AS committed_mb, -- Allocated for buffer cache
perf_cntr.ple,
m.physical_memory_kb / 1024.0 AS physical_mb,
(m.committed_kb * 1.0 / m.physical_memory_kb) * 100.0 AS SQL_percentage,
lazy.avg_lazy_writes_sec,
lazy.[lazy_writes(sec)],
buf.buffer_size_mb
INTO #memory_info
FROM sys.dm_os_sys_info m
CROSS APPLY(
SELECT cntr_value AS ple
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy'
) perf_cntr
CROSS APPLY (
SELECT cntr_value AS 'lazy_writes(sec)',
info.ms_ticks,
CAST((cntr_value * 1.0 / (info.ms_ticks/1000.0)) AS DECIMAL(20,6)) AS avg_lazy_writes_sec
FROM sys.dm_os_performance_counters
CROSS APPLY (SELECT *
FROM sys.dm_os_sys_info) info
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Lazy writes/sec') lazy
CROSS APPLY (
SELECT
COUNT(page_id) * 8/1024.0 AS buffer_size_mb
FROM sys.dm_os_buffer_descriptors
) buf
SELECT m.committed_mb / m.ple AS mb_sec_read_buffer,
m.ple AS 'ple (pages in buffer (sec))',
CAST(m.physical_mb AS DECIMAL(12,2)) AS physical_mb,
CAST(m.committed_mb AS DECIMAL(12,2)) AS committed_mb,
CAST(m.buffer_size_mb AS DECIMAL(12,2)) AS buffer_size_mb,
CAST(m.SQL_percentage AS DECIMAL(6,2)) AS sql_percentage,
CAST((m.buffer_size_mb / m.committed_mb) * 100.0 AS DECIMAL(6,2)) AS buffer_percentage,
m.avg_lazy_writes_sec,
m.[lazy_writes(sec)] AS 'total lazy_writes_from_startup',
uptime.days_up
FROM #memory_info m
CROSS APPLY (
SELECT DATEDIFF(DAY, create_date, GETDATE()) AS days_up
FROM sys.databases
WHERE name = 'tempdb'
) uptime
DROP TABLE IF EXISTS #memory_info