Narcis Miclaus

5 min de citit

SQL Server, de la zero Lecția 38 / 40

sp_BlitzIndex și sp_BlitzCache: scanarea full-body

Audit profund de indecși cu sp_BlitzIndex. Revizuirea planurilor de query cu sp_BlitzCache. Citirea output-ului, triajul celor 50 de lucruri pe care le găsesc.

sp_Blitz e prezentarea de ansamblu a serverului. sp_BlitzIndex și sp_BlitzCache sunt uneltele de specialitate: prima auditează indecșii, a doua auditează performanța interogărilor. Împreună îți spun cam 80% din ce nu e în regulă cu performanța unei baze de date.

Astăzi săpăm în ambele.

sp_BlitzIndex: cum arată indecșii tăi

USE Runehold;
EXEC sp_BlitzIndex;

Patru moduri, prin parametrul @Mode:

  • @Mode = 0 (implicit) — Diagnose — găsește probleme.
  • @Mode = 1 — Summarize — statistici per index.
  • @Mode = 2 — Detail — fiecare index, fiecare coloană, fiecare statistică.
  • @Mode = 3 — Missing indexes — raportul „ar trebui să adaugi astea”.
  • @Mode = 4 — All — pur și simplu varsă tot.

Zi de zi folosești @Mode = 0. Pentru investigații ocazionale folosești @Mode = 4.

Output-ul de diagnose

Câte un rând per constatare. Coloane:

  • Priority — 1 (urgent) la 250 (informațional).
  • Finding — problema.
  • URL — explicație (la fel ca sp_Blitz).
  • Database Name / Schema Name / Table Name.
  • Details: schema.table.index(keys) / includes.
  • Definition: [Property1], [Property2] — orice context relevant.
  • Secret columns — coloane ascunse pe care optimizatorul le cunoaște, dar tu poate nu (cheia clustered atașată la indecșii non-clustered, de exemplu).
  • Usage: Reads vs Writes; Size.
  • More Info — cum să sapi mai adânc (de obicei „rulează sp_BlitzIndex @Mode=2 @TableName='...'” pentru tabelul specific).

Constatările sunt prioritizate dintr-un motiv. Rezolvă P1-urile mai întâi.

Constatări comune sp_BlitzIndex

P1 — Indecși multipli cu chei duplicat. Ai doi indecși pe (CustomerId, OrderDate). Șterge unul. Câștig ușor, economie imediată de spațiu + cost de scriere.

P10 — Indecși nefolosiți. Un index cu mii de scrieri și zero citiri. Candidat la ștergere. Verifică mai întâi: user_seeks + user_scans + user_lookups = 0 pentru o perioadă semnificativă (nu doar de la ultima repornire).

P20 — Heap-uri (fără clustered index). Aproape întotdeauna greșit. Adaugă un index clustered. Lecția 21 pentru teorie.

P50 — Indecși lipsă sugerați. Din DMV-ul de missing-index. Evaluează, nu crea orbește.

P100 — Indecși neactualizați recent. N-au fost reorganizați/reconstruiți. Probabil în regulă; scripturile lui Ola se ocupă (lecția 35).

P150 — Statistici lipsă sau învechite. UPDATE STATISTICS ... WITH FULLSCAN și rerulează sp_BlitzIndex ca să o vezi dispărând.

Modul detail pentru un tabel specific

EXEC sp_BlitzIndex @Mode = 2, @DatabaseName = 'Runehold', @SchemaName = 'Sales', @TableName = 'Orders';

Arată fiecare index, fiecare coloană, fiecare statistică. Plus indecși non-clustered sugerați pe baza DMV-urilor de missing-index. Plus care interogări din plan cache ar beneficia de fiecare index sugerat.

Asta e ce te uiți când cineva spune „de ce e tabelul ăsta lent?” — primești o imagine completă într-un singur set de rezultate.

sp_BlitzCache: cum arată interogările tale

USE DBA;
EXEC sp_BlitzCache;

Parsează plan cache-ul (ca interogările DMV din lecția 36, dar mult mai inteligent). Arată top interogări după o metrică specificată, adnotate cu fiecare anti-tipar cunoscut detectat în fiecare plan.

Parametrul @SortOrder — alege metrica:

  • 'CPU' (implicit) — cel mai mult CPU consumat.
  • 'Reads' — cele mai multe citiri logice.
  • 'Writes' — cele mai multe scrieri.
  • 'Duration' — timp wall-clock.
  • 'Executions' — numărul de rulări.
  • 'Recent Compilations' — interogări compilate recent (utile pentru depistarea parameter sniffing-ului).
  • 'Memory Grant' — interogări care înghit cea mai multă memorie.

Output-ul

Câte un rând per top interogare. Coloane:

  • Query — textul SQL (trunchiat).
  • Query Plan — click ca să-l vezi în SSMS.
  • Executions / Total CPU / Avg CPU / Total Reads / Avg Reads etc.
  • Warnings — listă cu virgulă a problemelor detectate în plan.

Coloana de warnings e aurul. Fiecare warning îți spune ceva specific:

  • Implicit Conversion Affecting Cardinality — WHERE-ul tău are o conversie implicită de tip care dezactivează seek-urile pe index.
  • Key Lookups: N% — interogarea face N% din costul ei în key lookups. E timpul pentru un index acoperitor.
  • Missing Index: high value — planul sugerează un index lipsă care ar ajuta mult.
  • Parameter Sniffing — această interogare are timpi de execuție dramatic diferiți între rulări.
  • Spills to tempdb — sortările/hash-urile n-au încăput în memorie.
  • Unparameterized Query — o valoare literală e compilată în textul planului; fiecare valoare distinctă primește propriul plan. De obicei umple plan cache-ul degeaba.

Rezolvarea câtorva infractori de top îți dă adesea o reducere de 30–50% a CPU-ului pe server.

O sesiune exemplu

Pe un server nou pe care ești rugat să-l tunezi:

USE DBA;

-- Top 10 consumatori de CPU
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'CPU';

-- Top 10 după citiri (devoratori de I/O)
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'Reads';

-- Top 10 cu memory grants
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'Memory Grant';

Patru minute de muncă, top 30 de interogări scumpe din cache, cu fiecare warning adnotat. Citește-l pe primul. Repară-l (de obicei: adaugă un index acoperitor sau rescrie pentru a elimina o conversie implicită). Treci la următorul.

Runehold a tunat baza de date de producție de la 65% CPU mediu la 22% într-o lună, rezolvând top 15 constatări sp_BlitzCache, una pe săptămână. Atât. Fără magie, doar sistematic.

Rularea ambelor în mod regulat

Programează sp_Blitz, sp_BlitzIndex și sp_BlitzCache săptămânal prin Agent, descarcă rezultatele în tabele de logare și revizuiește schimbările. Constatările noi primesc atenție; cele vechi rămân rezolvate.

EXEC sp_Blitz
    @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzResults';

EXEC sp_BlitzCache @Top = 100,
    @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzCacheResults';

EXEC sp_BlitzIndex @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzIndexResults';

Acum ai istoric. Pune-l pe grafic. Poți răspunde la „lucrurile merg mai bine sau mai rău?” cu date în loc de presupuneri.

Personalizarea sp_BlitzCache

Parametri suplimentari utili:

  • @MinimumExecutionCount — ignoră interogările cu mai puțin de N execuții. Ajută la filtrarea rebuturilor ad-hoc one-off.
  • @StoredProcName — analizează doar o procedură.
  • @ExportToExcel = 1 — formatează output-ul pentru paste în Excel.
  • @IgnoreSqlHandles — listă cu virgulă de sql_handles de exclus (interogări gălăgioase pe care le-ai rezolvat deja sau lente intenționat).

Rulează asta pe propria mașină

USE Runehold;

-- 1. Audit de indecși
EXEC sp_BlitzIndex;

-- 2. Detaliu pe un tabel specific
EXEC sp_BlitzIndex @Mode = 2, @SchemaName = 'Sales', @TableName = 'Orders';

-- 3. Sugestii de indecși lipsă
EXEC sp_BlitzIndex @Mode = 3;

USE DBA;

-- 4. Top 10 interogări CPU
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'CPU';

-- 5. Top interogări cu probleme de parameter sniffing
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'Recent Compilations';

-- 6. Interogări care se varsă în tempdb
EXEC sp_BlitzCache @Top = 20, @SortOrder = 'Memory Grant';

Alege o constatare. Click pe URL. Citește explicația. Rezolvă sau documentează. Mergi mai departe.

Următorul: fratele lui sp_BlitzCache pentru „ce se întâmplă CHIAR ACUM” — sp_WhoIsActive și sp_BlitzFirst. Uneltele pe care le rulezi când sună telefonul.

Caută