Speranța de viață a paginilor (PLE), de unde să începem? (Administrarea bazelor de date, Server Sql, Performanță)

James Jenkins a intrebat.
a intrebat.

Am moștenit un server SQL {2012 (SP3), dar această întrebare se dorește a fi generică} folosim SCOM pentru a-l monitoriza. Anterior, primeam o alertă o dată sau de două ori pe lună pentru PLE < 300. Acum primesc uneori 2 sau 3 pe zi.

Există mai multe postări pe blog despre PLE, câteva instrumente pe care le puteți obține pentru a le monitoriza și multe opinii diferite cu privire la ceea ce este bun, rău sau indiferent. În cele din urmă există o mulțime de variabile. Nu există soluții unice pentru toate. PLE scăzută nu este o problemă, ci mai degrabă un simptom, cu o mulțime de cauze potențiale și măsuri conexe care trebuie luate în considerare.

{este posibil ca acest paragraf să nu aducă un plus de valoare întrebării, sunt dispus să îl elimin} Cred că toată lumea poate fi de acord că scăderea PLE la 299 o dată pe lună, în timpul creării unui raport peste noapte, este un simptom care nu trebuie să fie abordat (presupunând că raportul se finalizează înainte de orele de lucru). De asemenea, cei mai mulți sunt de acord că o PLE constantă la 350, nu este bună. Există o mână de cauze care trebuie analizate înainte de a face o schimbare de hardware, interogările și indexul fiind aproape de vârf.

După ce am citit aproximativ o duzină de articole de blog despre PLE. am încercat să restrâng simptomele cheie pentru a obține o imagine bună a ceea ce se întâmplă. Interogarea de mai jos este ceea ce am găsit. Aceasta oferă valori pentru 4 elemente Buffer Manager care se interconectează cu PLE

  • ‘Page life expectancy’ (Speranța de viață a paginii)
  • „Free list stalls/sec” (Lista liberă de blocaje/sec)
  • ‘Lazy writes/sec’ (Scrieri leneșe/sec)
  • „Buffer cache hit ratio

SELECT [object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters -- https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql
WHERE [counter_name] = 'Page life expectancy' --if multiple NUMA on a server should return multiple Nodes, 
OR [counter_name] = 'Free list stalls/sec'  -- Number of requests per second that had to wait for a free page https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-buffer-manager-object
OR [counter_name] = 'Lazy writes/sec' --Flushes of dirty pages before a checkpoint runs.  
OR [counter_name] = 'Buffer cache hit ratio' --percentage of pages found in the buffer cache without having to read from disk you want this ratio to be high
Order by [counter_name] DESC, [object_name];

În plus, dacă vă uitați la Lazy Writes pe un server moștenit, ar trebui să verificați Recovery Interval (Interval de recuperare)

EXEC sp_configure @configname='recovery interval (min)';  --The  'config_value' default 0 indicates SQL is applying Checkpoints completely automatically https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-recovery-interval-server-configuration-option

Dacă această primă interogare nu returnează valori:

SELECT COUNT(*) FROM sys.dm_os_performance_counters;  --If no values from the firs query, an value of 0 here indicates a seperate issue  https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql

Am o idee destul de bună despre ce reprezintă toate aceste valori și cum funcționează împreună. Am inclus comentarii și surse în codul meu de mai sus.

Întrebarea mea are două părți

  1. Lista mea de elemente/valori ale bufferului de mai sus este adecvată pentru un punct de plecare atunci când examinez PLE? (adică valorile care vor fi întotdeauna utile pentru a fi luate în considerare împreună, ar trebui ceva exclus sau inclus)

  2. Cum să pun valorile în context bun unele cu altele? (de exemplu, există un răspuns bun aici care spune „Verificați și valoarea Free List Stalls/sec. Dacă este peste 2, luați în considerare adăugarea de memorie la server”, în timp ce corpul răspunsului este util, nu cred că o valoare de 2 pentru „Free List Stalls/sec” este o problemă în majoritatea cazurilor)

NOTĂ: Această întrebare nu se referă la rezolvarea problemei PLE, ci la cum/unde să începeți să căutați atunci când evaluați simptomele. Medicul dumneavoastră vă verifică la începutul fiecărei examinări Pleoanele, tensiunea arterială, respirația și temperatura.

Editare 4/13/2018; Încercare de clarificareNu este vorba despre reacții de genunchi, cum ar fi verificarea indicilor sau așteptări. Este vorba despre identificarea altor date native de performanță SQL care ar trebui să fie întotdeauna examinate cu PLE. PLE este unul dintre obiectele de gestionare a bufferului, ce alte obiecte de gestionare a bufferului sau contoare de performanță ar trebui sau nu ar trebui să facă întotdeauna parte din interogări atunci când doriți cu adevărat să examinați gestionarea bufferului?

Comentarii

  • PLE de 350, de ce trebuie să fie toată lumea de acord că acest lucru nu este bun? Pentru anumite sarcini de lucru, acest lucru este perfect și, de fapt, este de așteptat. Existența unui prag magic de 300 și a unor alerte bazate pe acesta este partea care nu este bună, în opinia mea. –  > Por Aaron Bertrand.
  • @AaronBertrand 🙂 „Cele mai multe pot, de asemenea”. Dar da, nu există absolut nimic aici. –  > Por James Jenkins.
  • Ei bine, ai putea spune, de asemenea, că majoritatea oamenilor cred că o milă de 4 minute este bună. Dacă voi ajunge vreodată să fac o milă de 10 minute, voi fi extaziat. 350 ar putea fi perfect normal pentru un anumit volum de muncă și ar putea fi chiar grozav pentru o MULTE sarcini de lucru. Dacă vrei cu adevărat să știi despre PLE (iar Brent explică exact de ce nu ar trebui să te concentrezi pe acest număr, mai ales în mod izolat), trebuie să înveți SCOM să alerteze în cazul unor schimbări drastice bazate pe volumul tău de lucru specific, nu pe un număr magic pe care l-a extras dintr-un articol de blog din 2002. PLE-ul dvs. este mai mic acum decât a fost toată săptămâna, dar ce problemă reală pe care o aveți? Aveți una? –  > Por Aaron Bertrand.
  • @AaronBertrand RE: „dar ce problemă reală ai? Aveți una?” Exact asta vreau să pot afla. Când alergi o milă de 10 minute și pulsul tău este ridicat, nu ești îngrijorat. Dar când stai pe canapea și pulsul este ridicat, vrei să verifici și alte câteva lucruri, cum ar fi tensiunea și respirația. Dacă/când verificați semnele vitale PLE, ce altceva mai verificați? –  > Por James Jenkins.
  • Cred că ceea ce vreau să sugerez este că nu trebuie să vă panicați de fiecare dată când PLE scade. Nu va exista întotdeauna o problemă de rezolvat. Eu nu aș avea nimic setat pentru a alerta la PLE. doar pentru PLE pentru că este atât de rar un indiciu al unei probleme pe care o pot rezolva, sau chiar o problemă. –  > Por Aaron Bertrand.
2 răspunsuri
Brent Ozar

Practic, ați întrebat: „Ce ar trebui să fac atunci când se schimbă Speranța de viață a paginii?”.

Răspunsul meu: nimic. Nu încep prin a mă uita la speranța de viață a paginilor. Această măsurătoare avea sens în zilele SQL Server 7/2000, când era tot ce aveam, dar astăzi, în 2018, putem face mai mult.

Începeți prin a vă uita la statisticile de așteptare – asta vă spune ce așteaptă SQL Server.

Nu-mi pasă dacă PLE este 300 sau 3.000 – spuneți-mi ce AȘTEAPTĂ, SQL Server, și apoi voi merge să remediez această metrică.

Modalitatea mea preferată de a verifica așteptările este de a utiliza aplicația open source sp_BlitzFirst (declarație de renunțare: eu l-am scris.) În mod implicit, acesta ia o mostră de 5 secunde din metricile serverului dvs. și vă oferă câteva presupuneri cu privire la motivul pentru care este lent în acest moment.

Pentru că vă place să scrieți întrebări lungi, probabil că vă vor plăcea și acestea:

sp_BlitzFirst @SinceStartup = 1;

Primul set de rezultate vă oferă așteptările de la pornire și:

sp_Blitz @ExpertMode = 1, @Seconds = 60;

Preia un eșantion mai lung și vă spune care sunt așteptările pe acest interval de timp.

Statisticile de așteptare pot fi cam criptice, așa că, lângă fiecare tip de așteptare, am făcut un link către SQLskills wait stats repository pentru acel tip de așteptare. Poți să copiezi/lipesti numele tipului de așteptare de top, să mergi pe site-ul lor și să afli mai multe despre cauzele așteptării respective și cum să o rezolvi.

Dacă PLE scade din cauza interogărilor care citesc o mulțime de pagini de date de pe disc, de exemplu, s-ar putea să vedeți tipuri de așteptare PAGEIOLATCH%. Dacă scade din cauza interogărilor care primesc granturi de memorie uriașe, este posibil să vedeți RESOURCE_SEMAPHORE. Dacă PLE nu este problema, atunci veți vedea cu totul alte tipuri de așteptare.

Comentarii

  • În primul rând, vreau să spun; am citit multe dintre postările dvs. de pe blog și am urmărit videoclipurile dvs. și am învățat foarte mult, vă mulțumesc. Dar acest lucru nu este un răspuns la întrebare. Dacă PLE = 300, iar Lazy Writes = 500.000 și Free list stalls/sec = 0, atunci nu am o problemă. Îmi pot executa mica mea interogare folosind doar părțile OOTB și pot trece la alte probleme. Aceste probleme pot fi așteptări excesive, interogări lente sau ceva pe o instanță diferită, fără nicio legătură. –  > Por James Jenkins.
  • Ați întrebat: „Cum ar trebui să examinez PLE?” Am răspuns: „Nu ar trebui”. –  > Por Brent Ozar.
  • În primul rând, știu v. puțin despre SQL Server. Cu toate acestea, cred că partea critică a răspunsului lui @BrentOzar ‘s este Start by looking at wait stats - that tells you what SQL Server is waiting on.. adică unde este afectată performanța? Oraclke are Interfața de așteptare Oracle – aceeași treabă. Ajustarea numerelor pe un cadran nu este același lucru cu a ajunge la rădăcina unei probleme. p.s. +1 pentru tine și Brent – tu pentru o întrebare bine scrisă și Brent pentru un răspuns bine scris! –  > Por Vérace.
  • Reluând întrebarea mea în această dimineață, pot vedea unde ați citit-o ca fiind „Ce ar trebui să fac atunci când se schimbă Page Life Expectancy?”, dar nu asta vreau să știu. am citit multe bloguri (inclusiv al tău) despre PLE, voi încerca să clarific mai mult întrebarea. –  > Por James Jenkins.
  • @JamesJenkins Voi fi sincer: probabil că doriți să începeți o nouă întrebare de la zero. În forma actuală, întrebarea ta este foarte clară că PLE nu este problema ta – chiar tu însuți spui: „PLE scăzută nu este o problemă, ci mai degrabă un simptom”. De asemenea, spuneți „Medicul dumneavoastră verifică” și apoi enumerați metricile, iar aici aveți o grămadă de experți din industrie (medici) care vă spun că nu ne uităm la PLE. Este ca și cum te-ai duce la un medic și i-ai spune: „Spune-mi la ce te uiți când examinezi rata de creștere a unghiilor de la picioare”. Pur și simplu nu ne uităm la asta. –  > Por Brent Ozar.
James Jenkins

A trecut ceva timp de când am pus această întrebare, am învățat multe de atunci.

După cum subliniază Brent în răspunsul său, alertele PLE în sine nu vă spun cu adevărat nimic. Prin proiectare, aceste pagini ar trebui să vină și să plece, dacă nu rămân mult timp atunci când nu mai sunt necesare, este în regulă.

Cu toate acestea, am o instanță specifică care aruncă alerte PLE de mai multe ori pe zi, am analizat-o cu mai multe instrumente, inclusiv query store, și nu am găsit nimic care să necesite atenție. Chiar dacă aș adăuga memorie, nu pare că alertele PLE s-ar opri. Am căutat o modalitate de a „dovedi” dacă este nevoie de mai multă memorie sau nu.

La instanțele SQL mici, cu 4 GB de memorie RAM disponibilă, 75% sau 3 GB pot fi dedicați memoriei cache a planului. În mod normal, acest lucru este NU epurat cu paginile de date, pe care PLE le alertează. Am găsit câteva modalități de a vedea ce se întâmplă cu memoria și memoria cache a planului.

În cele din urmă am dezvoltat (bazându-mă pe linkurile de mai sus) interogarea de mai jos care arată speranța de viață (în minute) pentru planurile din memoria cache.

    --plan cache Life expectancy
    SELECT sys.dm_exec_cached_plans.objtype AS [CacheType] 
    ,    COUNT_BIG(*) AS [Total Plans]
    ,    SUM(CAST(sys.dm_exec_cached_plans.size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs]
    ,   AVG(sys.dm_exec_cached_plans.usecounts) AS [Avg Use Count]
    ,   AVG (DATEDIFF(MINUTE, PH_Time.creation_time, (GETDATE()))) AS [Avg Age in Minutes]
    FROM sys.dm_exec_cached_plans
    left join (
                Select  plan_handle
                , Min (creation_time) as creation_time --A plan can have several unique related quiries, this gets just one time per plan
                from sys.dm_exec_query_stats
                group by plan_handle
                ) as PH_Time On sys.dm_exec_cached_plans.plan_handle = PH_Time.plan_handle
    --left join sys.dm_exec_query_stats On sys.dm_exec_cached_plans.plan_handle = sys.dm_exec_query_stats.plan_handle 
    GROUP BY objtype
    ORDER BY [Total MBs] DESC
    GO

Deși niciun element nu este în sine concludent, se poate argumenta că, dacă durata medie de viață a planurilor din memoria cache este mai mare decât timpul dintre două re-executări ale interogărilor, nu este nevoie de memorie suplimentară. Timpul specific va varia în funcție de cazul de utilizare.

Există o mulțime de motive pentru care planurile sunt recompilate, a se vedea cele legate de De ce lipsesc detaliile din Query Store? La început, m-am concentrat foarte mult pe recompilarea ridicată cu PLE și nu am găsit o corelație utilă.

TL:DR Memoria este menită să aibă lucruri care vin și pleacă, o PLE scăzută nu este o problemă. DAR prin concepție, planurile folosite des ar trebui să rămână în memorie suficient de mult timp pentru a fi reutilizate. Dacă puteți demonstra că planurile rămân în memorie suficient de mult timp pentru a fi refolosite, este dificil de justificat adăugarea de memorie fără un alt indicator.