Microsoft SQL Server Optimizarea performanței

De câte ori v-ați pus problema de a îmbunătăți performanța interogărilor voastre pe SQL Server fără să știți de unde să vă apucați? Eu, cel puțin, mi-am suflecat mânecile de câteva ori până am ajuns să înțeleg o funcționalitate specifică SQL Server care îmi rezolvă problema.

Toți știm că în faza de dezvoltare problemele de performanță majore sunt rare, și asta se întâmplă din mai multe motive: date puține, mediul de dezvoltare perfect, scenarii principale de lucru fără variații, etc.

Soluția e bazată pe statisticile făcute de SQL Server. Cu ele puteți să vă dați seama de stresul aplicat serverului, însă necesită un ciclu de rulare a aplicației pe scenarii utilizator și nu cele de dezvoltare. Se pretează la aplicații care rulează într-un mediu de producție sau cât mai aproape de cel real.

Dacă vă întrebați ce înseamnă statisticile, răspunsul dat de wikipedia e: "Statistics is the study of the collection, organization, analysis, and interpretation of data". Mdah, nici mie nu mi-a plăcut statistica în facultate.

Specific însă pe o bază de date, de la creare se setează o opțiune care întreține statisticile de pe fiecare tabelă. Statisticile sunt folosite pentru a optimiza interogările pe SQL Server și a crea planurile de execuție. SQL Server creează câte o statistică pentru fiecare tabelă și stochează informații despre cum s-a folosit tabela respectivă, proces care se realizează automat prin opțiunile "auto create" și "auto update" pe baza de date.

Cum se folosesc? Se pornește de la un view de sistem, și anume

sys.dm_db_missing_index_group_stats

în care se adună statistici legate de, așa cum îi spune și titlul, indecși lipsă în baza de date. Informația se modifică la fiecare execuție de query, și se șterge când serverul e repornit. Deci atenție la ce date interpretați, fiind informații doar de la ultimul restart. Apoi e nevoie de încă două view-uri pentru a scoate informații relevante:

sys.dm_db_missing_index_details și sys.dm_db_missing_index_groups.

O interogare care oferă un set de date pertinent arată astfel:

select top 100

priority = s.user_seeks * s.avg_total_user_cost * s.avg_user_impact,

s.user_seeks,

s.avg_total_user_cost,

s.avg_user_impact,

d.statement,

d.equality_columns,

d.inequality_columns,

d.included_columns

from

sys.dm_db_missing_index_group_stats s

join sys.dm_db_missing_index_groups g on s.group_handle = g.index_group_handle

join sys.dm_db_missing_index_details d on g.index_handle = d.index_handle

order by priority desc

Un rezultat din viața reală se poate vedea în Figura 1.

După cum observați coloana "priority" e calculată având ca scop calcularea impactului total asupra performanței. Dar cel mai bine ar fi să le analizăm pe rând:

• Rezultatul 1, sugerează planuri de execuție care iau in considerare coloana StatusID din tabela 1, care participă in condiții de filtrare cu egalitate pe coloană. Având în vedere că sunt ~30k căutari cu condiția respectivă cu un impact mare asupra utilizatorului (78.4%), merită studiată adăugarea indexului pe coloana respectivă.
• Rezultatul 3, sugerează index pe aceeași coloană, precizând execuții care folosesc coloana pentru căutari pe diferențe (>,<, !=). Încă un motiv în plus să luăm în considerare un index pe StatusID.
• Ultimul rezultat are unele caracteristici interesante. Impactul asupra utilizatorului e 98%. Dacă ar fi să ne uităm mai în detaliu sugestia afirmă că indexul ar trebui aplicat pe 3 coloane, din care una este probabil cheie primară. Sugestia ar fi de fapt un index compus, însă nu săriți în decizie până nu faceți o analiză atentă pe interogarile care cauzează înregistrarea.

Și de-aici incepe munca propriu-zisă. Trebuie analizată fiecare înregistrare în parte. După primele câteva zeci încep să se contureze cele pentru care e nevoie de atenție mărită și cele care nu intră in aria de interes imediat. E nevoie și de cineva care să cunoască bine regulile de business ale aplicației pentru a putea aplica regula 80/20.

Atenție foarte mare la câți indecși folosiți pentru o tabelă, având in vedere dezavantajele indecșilor multipli. Statisticile nu sunt destinate unui astfel de proces, însă pot fi folosite ca și informații de intrare. Mai jos prezentăm câteva dezavantaje ale funcționalității oferite:

• Destinația principală nu este pentru optimizări.
• Nu poate aduna statistici pentru mai mult de 500 grupuri de indecși lipsă.
• Nu spune care să fie ordinea de indexare.
• Pentru condiții de filtrare inegale rezultatele nu sunt precise.
• Coloanele sugerate pentru incluziune nu sunt returnate consistent. Analize ulterioare sunt necesare.

Concluzie

Folosirea unor astfel de informații în optimizarea indecșilor și a interogărilor e utilă. Însă nu vă apucați să puneți indecși pe tot ce mișcă, acesta nefiind scopul statisticilor. Munca nu vi se simplifică, pentru că tot e nevoie de o analiză ulterioară. Rămâne la decizia voastră să hotarâți dacă folosiți sau nu informația. De exemplu: un index pe o coloană unde variația datelor e mica nu-și are rostul. Dar știți unde sunt coarnele taurului. Suflecări de mâneci plăcute!