Praćenje učinkovitosti MS SQL Servera. Praktični savjeti

SQL Profiler je softverski alat koji se koristi za praćenje SQL Servera. "Trace" - sesija za prikupljanje informacija o radu SQL Servera 2008

Glavna svrha:

SQL Profiler administratori koriste za:

· Analiza prijave;

· Određivanje optimalnosti zahtjeva koji se šalju na poslužitelj;

· Identificiranje Transact-SQL naredbi koje generiraju pogrešku;

· Prikupljanje informacija o aktivnostima korisnika tijekom dužeg vremenskog razdoblja;

· Praćenje rada poslužitelja u stvarnom vremenu.

Nove prilike:

a. Profiliranje usluga analize

b. Profiliranje događaja Integration Services

c. mogućnost snimanja očitanja brojila s Performance Monitora prilikom snimanja informacija o izvršenju naredbe;

d. Mnogi novi događaji i izvori informacija dodani su Profileru koji se mogu odabrati da budu prijavljeni u datoteku praćenja;

f. mogućnost grupiranja događaja u prozoru profilera.

Rad sa SQL Server Profilerom

1. Pokrenite SQL Server Profiler - iz izbornika Startà Programsà SQL Server 2008à Performance Toolsà SQL Server Profiler.

2. U prozoru koji se otvori, u izborniku Datoteka Odaberi Novi trag i spojite se na poslužitelj SQL Server 2008 koji ćemo pratiti.

3. Konfigurirajte parametre sesije u prozoru Svojstva traga, koji se automatski otvara prije pokretanja sesije praćenja (vidi sliku 8.1).

Riža. 8.1. Postavljanje parametara sesije praćenja

a. U kartici Općenito odaberite s popisa Koristite predložak najprikladniji predložak. Predložak se odabire pomoću izbornika Datoteka à Predlošci u SQL Server Profiler. U početku vam je na raspolaganju osam predložaka:

1). Standardno (zadano)- zadani predložak koji vam omogućuje praćenje svih pohranjenih procedura i Transact-SQL naredbi koje se pokreću za izvršenje;

2). SP_Broji- prikupljanje podataka o pohranjenim procedurama i funkcijama pokrenutim za izvršenje, razvrstani po nazivima;

3). TSQL- prikupljanje informacija o svim Transact-SQL naredbama pokrenutim za izvršenje na poslužitelju, s naznakom identifikatora korisničkih procesa i vremena početka;

4). TSQL_Trajanje- sličan je prethodnom predlošku, ali umjesto informacije o vremenu kada je TSQL naredba pokrenuta, bilježi se vrijeme potrebno za njeno izvršenje;

5). TSQL_Grupiran- osim informacija o Transact-SQL naredbenom kodu i vremenu njegovog pokretanja, bilježe se i podaci o nazivu aplikacije, korisničkom računu u OS-u i korisničkom računu koji je korišten za povezivanje;

6). TSQL_Replay- bilježenje najdetaljnijih informacija o izvršenim Transact-SQL naredbama;

7). TSQL_SP-ovi- osim snimanja informacija o početku pohranjene procedure (SP: Starting), bilježe se i informacije o izvršavanju svake od naredbi pohranjene procedure (SP: StmtStarting);

8). Ugađanje- koristi se za prikupljanje informacija koje zahtijeva Savjetnik za podešavanje baze podataka.

b. U kartici Općenito ako je potrebno navesti mjesto za pohranjivanje podataka o tragovima:

1). Podaci o praćenju mogu se zabilježiti u datoteku (5 MB prema zadanim postavkama):

· parametar Omogućite prelazak datoteke određuje hoće li se sljedeća datoteka automatski kreirati prilikom popunjavanja jedne datoteke. Ime sljedeće datoteke bit će isto kao i naziv prethodne, ali će njenom nazivu biti dodan broj (1, 2, 3, itd.)

· parametar Poslužitelj obrađuje podatke praćenja može se koristiti za povećanje pouzdanosti snimanja podataka o tragovima. Kada je ovaj potvrdni okvir odabran, poslužitelj će upravljati obradom informacija o praćenju.

2). Informacije o praćenju mogu se pohraniti u tablicu SQL Servera. Tablica s potrebnim skupom stupaca bit će kreirana automatski.

3). Koristeći parametar Omogućite vrijeme zaustavljanja praćenja možete odrediti vrijeme kada će praćenje biti automatski onemogućeno.

c. U kartici Odabir događaja odrediti parametre za prikupljanje informacija. U tablici na ovoj kartici morate odabrati tražene događaje (u recima) i podatke (u stupcima) koji će se za njih bilježiti. Da biste prikazali sve retke i stupce, morate označiti okvire Prikaži sve događaje i Prikaži sve stupce.

1). pomoću gumba Filtri stupaca(Filtri stupaca) postavljaju filtre za prikupljanje potrebnih informacija (praćenje radnji izvršenih u određenoj bazi podataka, određenoj aplikaciji ili određenom korisniku) - Kao ili Ne kao;

2). pomoću gumba Organizirajte stupce(Organizirajte stupce) prilagodite redoslijed stupaca za prikaz ili snimanje u profileru s mogućnošću grupiranja podataka - odjeljak Grupa.

4. Nakon što odredite sve parametre praćenja, kliknite gumb Trčanje(Pokreni) (vidi sliku 8.2)

Riža. 8.2. Pregled informacija tijekom sesije praćenja

Gornji dio prozora prikazuje događaje koji se događaju na poslužitelju, a donji dio pruža detaljne informacije o svakom događaju (na primjer, kod SQL naredbe).

Značajke dostupne u prozoru praćenja:

1. Ako kartica Organizirajte stupce u svojstvima predloška ste odabrali stupce za grupiranje, po tim stupcima možete grupirati zapise u okviru za prikaz. U tu svrhu u izborniku Pogled postoji naredba Grupirani prikaz;

2. Ako popis Skupina postavljen je samo jedan stupac, tada imate priliku koristiti način prikaza Skupni prikaz(vidi sliku 8.3). Ovaj način rada je omogućen pomoću naredbe Skupni prikaz iz istog izbornika Pogled.

Riža. 8.3. Način prikaza Skupni prikaz

3. Možete otvoriti događaje spremljene u datotekama i tablice praćenja u profileru. Također je moguće ponoviti zabilježene operacije pomoću izbornika Ponovi;

4. Informacije o praćenju možete povezati s brojačima performansi Monitora sustava. Za ovo:

Definirajte sesiju praćenja tijekom koje se moraju zabilježiti informacije za stupce Vrijeme početka i Vrijeme završetka;

· Započnite sesiju praćenja sa snimanjem informacija u datoteku ili tablicu. Istodobno sakupite protokol očitanja brojila u datoteku Monitor performansi;

Otvorite prikupljene informacije iz datoteke praćenja u profileru, a zatim upotrijebite naredbu Uvoz podataka o izvedbi iz izbornika Datoteka.

Što ćete naučiti iz ovog članka?

Svrha alata za praćenje SQL Profilera
Kako pratiti tekst upita u DBMS, u koji je preveden 1C upit
Postavke filtra praćenja
Kako prilagoditi SQL Profiler

Često se u radu javlja situacija kada zahtjev u 1C iz nekog razloga radi sporo, ali analiza teksta zahtjeva ne govori o problemima.

U ovom slučaju morate proučiti ovaj problem na nižoj razini. Da bismo to učinili, moramo pogledati tekst SQL upita i plan upita. Za to možete koristiti SQL Profiler.

SQL Profiler - svrha

SQL Profiler je program uključen u MS SQL Server koji je dizajniran za pregled svih događaja koji se događaju u SQL Serveru. Drugim riječima, potrebno je zabilježiti trag.

U kojim slučajevima ovaj alat može biti koristan za 1C programera? Prije svega, možete dobiti tekst upita u SQL-u i vidjeti njegov plan. To se također može učiniti u tehnološkom časopisu (TJ), ali plan za traženje u TJ-u nije tako prikladan i zahtijeva određene vještine i sposobnosti. Osim toga, u profileru možete vidjeti ne samo tekstualni, već i grafički plan izvršenja upita, što je praktičnije.

Profiler vam također omogućuje da saznate:

zahtjeve dulje od određenog vremena
upite prema određenoj tablici
čeka na brave
isteka vremena
zastoji itd.

Analiza upita pomoću SQL Profilera

Profiler se često koristi posebno za analizu upita. Pritom je potrebno analizirati ne sve izvršne upite, već kako se određeni 1C upit prevodi u SQL, te obratiti pozornost na njegov plan izvršenja.

Posebno je potrebno razumjeti zašto je zahtjev spor. Ili, kada gradite veliki i složeni upit, morate biti sigurni da SQL upit ne sadrži spojeve podupita.

Da biste pratili zahtjev u praćenju, izvršite sljedeće korake:

1. Pokrenite SQL Profiler: Start - Svi programi - Microsoft SQL Server 2008 R2 - Alati za produktivnost - SQLProfiler.

2. Napravite novi trag: Datoteka - Kreiraj trag(Ctrl + N).

3. Navedite DBMS poslužitelj na kojem se nalazi naša baza podataka i kliknite Spojiti:

Ništa nas ne sprječava da pratimo DBMS poslužitelj koji se nalazi na bilo kojem drugom računalu.

4. U prozoru koji se pojavi Svojstva tragova prijeđite na oznaku Odabir događaja:

Budući da su nam potrebni upiti i planovi upita, moramo omogućiti odgovarajuće događaje. Za prikaz cijelog popisa svojstava i događaja uključite zastavice Prikaži sve događaje i Prikaži sve stupce... Sada morate odabrati samo događaje prikazane na donjoj slici, ostali moraju biti onemogućeni:

Opis ovih događaja:

ShowplanStatisticsProfile - tekstualni plan izvršenja upita
ShowplanXMLStatisticsProfile - grafički plan izvršenja upita
RPC: Dovršeno – tekst zahtjeva, ako se izvršava kao procedura (ako se izvršava 1C zahtjev s parametrima)
SQL: BatchCompleted - tekst zahtjeva, ako se izvršava kao normalan zahtjev (ako je 1C zahtjev izvršen bez parametara)

6. U ovoj fazi trebate konfigurirati filtar za odabrane događaje. Ako filter nije instaliran, tada ćemo vidjeti zahtjeve za sve baze podataka koje se nalaze na ovom DBMS poslužitelju. Kliknite gumb Filtri stupaca da biste postavili filtar prema nazivu baze podataka:

Sada u praćenju vidimo samo upite bazi podataka "TestBase_8_2".

Filter možete staviti i na druga polja, a najzanimljivija su:

Trajanje
TextData (obično tekst zahtjeva)
RowCounts (broj redaka koje je vratio upit)

Recimo da trebamo "uhvatiti" sve zahtjeve prema tablici "_InfoRg4312" koji traju više od 3 sekunde u bazi podataka "TestBase_8_2". Za to je potrebno:

a) Postavite filter prema bazi podataka (vidi gore)
b) Postavite filtar po trajanju (podešeno u milisekundama):

c) Postavite filter prema tekstu zahtjeva:

Da biste postavili filtar prema tekstu zahtjeva, koristite masku. Ako želite pratiti upite koji pristupaju više tablica, u odjeljku "Izgleda" stvara se više stavki. Uvjeti preklapanja filtera rade zajedno.

7. Sada počnite pratiti pomoću gumba Trčanje u prozoru Svojstva tragova i promatrajte događaje koji spadaju pod instalirane filtere, čiji je prikaz konfiguriran.

Gumbi na naredbenoj ploči koriste se za kontrolu usmjeravanja:

Namjena gumba:

Gumica za brisanje- briše prozor za praćenje
Početak- počinje tragati
Pauza- pauzira praćenje, kada kliknete na Start, praćenje se nastavlja
Stop- zaustavlja praćenje

8. Trace prozor se sastoji od dva dijela. U gornjem dijelu nalaze se događaji i njihova svojstva, u donjem - informacije ovisno o vrsti događaja. Za naš primjer, ovdje će se prikazati ili tekst zahtjeva ili njegov plan.

9. Pokrenimo zahtjev u 1C konzoli za upite i vidimo kako će se odraziti u profileru:

Iz ponašanja u tragovima možete vidjeti da je na kraju bilo nekoliko zahtjeva, a samo jedan od njih nas zanima. Ostali zahtjevi su zahtjevi za uslugom.

10. Svojstva događaja omogućuju procjenu:

koliko sekundi je zahtjev trajao (trajanje)
koliko je bilo logičnih čitanja
koliko je redaka upit vratio kao rezultat (RowCounts) itd.

U našem slučaju, zahtjev je trajao 2 milisekunde, napravio 4 logička čitanja i vratio 1 red.

11. Ako pogledate jedan događaj iznad, možete vidjeti plan upita u grafičkom obliku:

Iz plana je vidljivo da se pretraživanje indeksa vrši po cijeni, ovaj plan se ne može nazvati idealnim, budući da indeks ne pokriva, polja koda i imena dobivaju se pomoću KeyLookupa, što oduzima 50% vremena .

Koristeći kontekstni izbornik, dobiveni grafički plan upita može se spremiti u zasebnu datoteku s nastavkom * .SQLPlan i otvoriti u profileru na drugom računalu ili pomoću programa SQL Sentry Plan Explorer, koji je napredniji.

12. Ako idemo još više, tada ćemo vidjeti isti plan upita, ali u tekstualnom obliku. Upravo je ovaj plan prikazan u TZ, MCC i drugim alatima za praćenje performansi 1C.

U formatu samog profilera, odnosno s nastavkom * .trc
U xml format
Napravite predložak od traga (vidi sljedeću stavku)
Spremite rezultirajući trag kao tablicu baze podataka. Ovo je vrlo prikladan način kada, na primjer, trebate pronaći najsporiji upit u praćenju ili filtrirati upite prema nekom parametru.

Zatim odabiremo bazu podataka na navedenom poslužitelju, navedemo naziv tablice u kojoj će se trag spremiti. Možete koristiti postojeću tablicu ili joj dati novo ime i tada će se ova tablica automatski kreirati.

Sada je moguće izraditi upite bilo koje složenosti za našu tablicu: na primjer, tražiti najdulje pokrenute upite.

Također morate imati na umu da je trajanje pohranjeno u tablici u milijuntim dijelovima sekunde, a kada se prikazuje rezultat, trebate pretvoriti vrijednost u milisekunde. U tablici postoji i stupac RowNumber koji prikazuje broj ovog retka u tragu.

14. Uz često korištenje profilera za analizu upita, stalno prilagođavanje željenih događaja i filtara će vam stalno oduzimati puno vremena.

U ovom slučaju pomoći će nam predlošci praćenja, gdje konfiguriramo filtere i redoslijed stupaca koji su nam potrebni, a zatim samo koristimo postojeći predložak prilikom kreiranja novog traga.

Za izradu predloška koristite izbornik Datoteka - Predlošci - Novi predložak:

Na prvoj kartici odredite vrstu poslužitelja, naziv predloška i, ako je potrebno, postavite oznaku da se ovaj predložak koristi prema zadanim postavkama.

Na drugoj kartici odabiremo tražene događaje i postavljamo filtre (kao što je prikazano gore).

Prilikom izrade novog traga možemo odrediti traženi predložak, a zatim će se na drugoj kartici automatski popuniti svi filteri i događaji prema kreiranom predlošku.

Burmistrov Andrej

SQL Server 2005 Profiler, Praćenje upita aplikacije, Uzorci praćenja, Grupiranje informacija o upitima

Jedan od najkorisnijih alata za praćenje aktivnosti korisnika je profiler (Profiler). Pomoću ovog alata možete saznati koje naredbe SQL Server trenutno izvršava. Potreba za korištenjem profilera javlja se vrlo često. Evo nekoliko standardnih situacija u kojima može biti vrlo teško bez njega:

q želite analizirati rad aplikacije i vidjeti koje naredbe ona izvršava na poslužitelju. Ove informacije mogu biti korisne:

· Da biste razumjeli s kojim tablicama u bazi podataka ova aplikacija radi prilikom izvođenja određenih operacija. Vrlo često u poduzeću postoji potreba za kreiranjem izvješća u obliku koji nije dostupan u aplikaciji, a programeri rijetko daju detaljne informacije o strukturi baze podataka;

· Da biste saznali koliko je optimalno sa stajališta izvedbe zahtjeva aplikacija šalje poslužitelju. U praksi, kada koristite profilator, često možete identificirati potpuno neoptimalne upite, na primjer, kada se filtriranje ili sortiranje podataka izvodi na klijentu;

· Razumjeti pri izvršavanju koje Transact -SQL naredbe iz aplikacije na poslužitelju dolazi do pogreške;

q za prikupljanje informacija o aktivnostima korisnika tijekom dužeg vremenskog razdoblja (na primjer, možete prikupiti sve zahtjeve koje je određena aplikacija poslala poslužitelju tijekom radnog dana). Prikupljene informacije se zatim mogu ručno analizirati ili proslijediti Database Tuning Advisoru za automatiziranu analizu;

q za praćenje performansi poslužitelja u stvarnom vremenu. Na primjer, ako poslužitelj iznenada uspori, možete vidjeti u prozoru profilera koje se naredbe trenutno izvršavaju na njemu.

U SQL Serveru 2005, profiler ima puno novih stvari:

q Uvedeno je profiliranje događaja Integration Services. Sada možete koristiti profiler za praćenje napretka novih DTS paketa;

q sada je moguće bilježiti očitanja brojača iz System Monitora prilikom pisanja informacija o izvršenju naredbe;

q Mnogi novi događaji i izvori informacija dodani su u profiler koji se može odabrati za pisanje u datoteku praćenja. Definicija onoga što treba napisati u datoteku praćenja sada se može spremiti u XML formatu;

q Sada možete spremati rezultate praćenja u XML formatu (mogućnost pisanja u ANSI, OEM, UNICODE formate je također spremljena);

q Čak se i planovi izvršavanja Transact -SQL naredbi koje je uhvatio profiler mogu spremiti u XML formatu. Ovi planovi se zatim mogu otvoriti u SQL Server Management Studiju za daljnju analizu;

q sada je moguće grupirati događaje izravno u prozoru profilatora. Korištenjem grupiranja, na primjer, možete vrlo jednostavno izračunati koliko je puta određena Transact -SQL naredba izvršena na poslužitelju tijekom dana.

Rad s profilerom izgleda vrlo jednostavno. Ova se aplikacija može pokrenuti iz izbornika Početak| Programi| Microsoft SQL Server 2005 | Alati za izvedbu | SQL Server Profiler ... Za početak, u prozoru profilera koji se otvara u izborniku Datoteka(Datoteka) koju trebate odabrati NoviTrag(Novo praćenje) i povežite se na poslužitelj SQL Server 2005 koji ćete nadzirati. Praćenje se odnosi na sesiju koja prikuplja informacije o radu SQL Servera 2005. Međutim, prije nego što počnete prikupljati informacije, morate konfigurirati parametre ove sesije. Ova postavka se vrši u prozoru TragSvojstva(Svojstva praćenja), koji se automatski otvara prije pokretanja sesije praćenja (slika 11.1).

Riža. 11.1. Postavljanje parametara sesije praćenja

U kartici Općenito(Općenito) na popisu Koristitithepredložak(Upotrijebi predložak) možete odabrati najprikladniji predložak za prikupljanje informacija unutar vaše sesije. U principu, možete zanemariti postavke predloška, ali ručno definirati parametre za prikupljanje informacija (pomoću susjedne kartice DogađajiIzbor(Odabir događaja)). Međutim, navođenje ispravnog predloška može uštedjeti vrijeme i izbjeći pogreške. Stoga ćemo se detaljnije zadržati na predlošcima.

Predložak se sprema u posebnu datoteku s ekstenzijom tdf postavke sesije praćenja. Rad s predlošcima (dodavanje novih, promjena postojećih, uvoz i izvoz izvješća u druge direktorije) obavlja se pomoću izbornika Datoteka| Predlošci(Datoteka | Predlošci) u SQL Server Profileru. U početku vam je na raspolaganju osam predložaka:

q Standardno (zadano)- kao što naziv implicira, ovaj predložak je prikladan za većinu situacija i stoga je odabran prema zadanim postavkama. Omogućuje vam praćenje svih pohranjenih procedura i Transact -SQL naredbi koje se pokreću za izvršenje;

q SP _Broji- prikupljaju se podaci o pohranjenim procedurama i funkcijama pokrenutim za izvršenje. U ovom slučaju, informacije u prozoru profilera su razvrstane (u terminologiji profilera - grupirane) prema nazivima pohranjenih procedura;

q TSQL- prikupljaju se podaci o svim Transact -SQL naredbama koje su pokrenute za izvršenje na poslužitelju. Osim koda naredbe, bilježe se i informacije o identifikatorima korisničkih procesa i vremenu početka. Obično se ovaj predložak koristi za praćenje naredbi koje aplikacija šalje poslužitelju;

q TSQL _Trajanje- gotovo isto kao i prethodni predložak, ali umjesto snimanja informacija o vremenu kada je Transact -SQL naredba pokrenuta, bilježi se vrijeme potrebno za njezino izvršenje. Obično se ovaj predložak koristi za ručno praćenje performansi poslužitelja;

q TSQL _Grupirano- osim informacija o kodu naredbe Transact -SQL i vremenu njegovog pokretanja, bilježe se i podaci o nazivu aplikacije, korisničkom računu u operacijskom sustavu i korisničkom imenu koje je korišteno za povezivanje. U ovom slučaju, zapisi su grupirani prema prijavi. Obično se ovaj obrazac koristi u situacijama kada želite pratiti aktivnost određene aplikacije;

q TSQL _Ponovi- bit će zabilježene najdetaljnije informacije o izvršenim naredbama Transact -SQL. Tada se te informacije mogu koristiti za reproduciranje opterećenja na poslužitelju s maksimalnom točnošću. Obično se ovaj predložak koristi za pisanje skupa naredbi koje će se zatim koristiti za testiranje različitih postavki poslužitelja s gledišta izvedbe;

q TSQL _SP-ovi- osim snimanja informacija o početku cijele pohranjene procedure (događaj SP: Početak), ova opcija praćenja također bilježi informacije o izvršenju svake naredbe u ovoj pohranjenoj proceduri (događaj SP: StmtStarting). Ovaj obrazac se obično koristi za praćenje rada složenih pohranjenih procedura;

q Ugađanje- Ovaj predložak je dizajniran za snimanje informacija najprikladnijih za prijenos Savjetnika za podešavanje baze podataka. Kako raditi s ovim automatiziranim alatom za analizu i optimizaciju performansi bit će razmotreno u sekta. 11.5.5.

Kao što je već spomenuto, uopće nije potrebno ograničiti se samo na skup gotovih predložaka. Možete koristiti vlastite parametre sesije praćenja tako da ih konfigurirate na kartici DogađajiIzbor... U tablici na ovoj kartici morate odabrati tražene događaje (u recima) i podatke (u stupcima) koji će se za njih bilježiti. Imajte na umu da je prema zadanim postavkama vidljiv samo mali dio dostupnih redaka i stupaca. Da biste omogućili prikaz svih redaka i stupaca, morate označiti potvrdne okvire PokazatisviDogađaji(Prikaži sve događaje) i PokazatisviKolumne(Prikaži sve stupce).

Često se događa da trebate pratiti samo radnje izvršene u određenoj bazi podataka, ili određenoj aplikaciji, ili određenom korisniku, ili odabrati sve ove uvjete u isto vrijeme. Filteri za prikupljanje podataka mogu se konfigurirati klikom na gumb StupacFiltri(Filtri stupaca) na kartici DogađajiIzbor... Za svaki stupac možete konfigurirati snimanje samo određenih vrijednosti ( Kao) ili zabrana snimanja određenih vrijednosti ( Ne kao). Prema zadanim postavkama, konfiguriran je jedan filtar. Ne kao za stupac Naziv aplikacije... To uzrokuje zanemarivanje svih događaja SQL Server Profilera, odnosno svih događaja koji se odnose na sam proces prikupljanja informacija o praćenju. Bolje je ne uklanjati ovaj filtar, jer inače može doći do pozitivnih povratnih informacija s beskonačnim snimanjem informacija.

S još jednim gumbom OrganiziratiKolumne(Organizirajte stupce) koji se nalazi na kartici DogađajiIzbor, možete prilagoditi redoslijed stupaca za prikaz ili unos u profiler. Obratite pažnju na odjeljak Skupina(Grupa) na ovom popisu. Za one stupce koji se nalaze u njemu, grupiranje će se izvršiti automatski. Ako stavite samo jedan stupac u ovaj odjeljak, tada ćete prilikom gledanja imati priliku koristiti vrlo prikladan način rada AgregiranoPogled(Agregirani prikaz) (kada se informacije automatski grupiraju, na primjer, prema bazi podataka, prema aplikaciji, korisničkom imenu itd., a unosi za željenu bazu podataka, aplikaciju ili korisnika mogu se proširiti i sažimati).

Nakon što odaberete željeni predložak ili konfigurirate vlastiti skup događaja za prijavu, samo se trebate vratiti na karticu Općenito i konfigurirajte nekoliko dodatnih parametara za sesiju praćenja.

Podaci o praćenju mogu se zabilježiti u datoteku. Ova se datoteka može koristiti u različitim situacijama:

q se može proslijediti kao izvor informacija Savjetniku za podešavanje baze podataka;

q se može više puta "reproducirati" u profileru ponavljanjem svih snimljenih naredbi, na primjer, za procjenu izvedbe pod različitim postavkama poslužitelja;

q se može predstaviti razvojnim programerima kao podrška njihovim tvrdnjama o aplikaciji.

Zabilježimo neke točke koje se odnose na bilježenje sesije praćenja u datoteku:

q Zadana veličina datoteke od 5 MB vrlo je mala. Prilikom profiliranja proizvodnog poslužitelja, ova se veličina upisuje u minutama. Istina, po zadanom je potvrdni okvir označen Omogućitidatotekaprevrtanje(Omogući promjenu datoteka), odnosno nakon popunjavanja jedne datoteke automatski će se stvoriti druga datoteka, čiji će naziv biti dodan brojem 1, zatim 2, itd., ali nije uvijek prikladno raditi s veliki broj datoteka. Ako prikupljate podatke za prijenos Database Tuning Advisor, bolje je postaviti ograničenje veličine datoteke na 1 GB (pomoću parametra Setmaksimumdatotekaveličina(Prilagodi maksimalnu veličinu datoteke). Općenito). Snimanje praćenja u datoteku najčešće se izvodi s administratorske radne stanice, tako da će biti potreban prostor na disku na radnoj stanici, a ne na poslužitelju;

q parametar Poslužiteljprocesatragpodaci(Poslužitelj obrađuje podatke o tragovima) može se koristiti za povećanje pouzdanosti snimanja podataka o tragovima. Prema zadanim postavkama, SQL Server Profiler upravlja obradom podataka praćenja na računalu na kojem se izvodi (ne nužno na poslužitelju). Ako odaberete ovaj potvrdni okvir, poslužitelj će upravljati obradom informacija praćenja. To osigurava da će se prikupiti sve informacije o praćenju (ako je potvrdni okvir poništen u vrijeme najvećeg opterećenja poslužitelja, neke od informacija mogu se preskočiti), ali će povećati opterećenje poslužitelja.

Druga opcija za pisanje informacija o praćenju je upisivanje u tablicu SQL Servera. Tablica s potrebnim skupom stupaca bit će kreirana automatski. Možete prilagoditi samo maksimalni broj unosa u ovoj tablici. Imajte na umu da je na ovoj kartici maksimalni broj zapisa naveden u tisućama.

Posljednji parametar na kartici Općenito- OmogućitiTragStopvrijeme(Uključuje vrijeme zaustavljanja praćenja). Možete odrediti vrijeme kada će praćenje biti automatski onemogućeno. Obično ima smisla isključiti praćenje prije pokretanja bilo koje operacije usluge koje vas ne zanimaju s gledišta zapisivanja (sigurnosne kopije, masovno učitavanje podataka, obrada OLAP kocki, itd.).

Nakon što su svi parametri praćenja konfigurirani, možete kliknuti gumb Trčanje kartica (Pokreni). Općenito i počnite pratiti (slika 11.2).

Riža. 11.2. Pregled informacija tijekom sesije praćenja

Rad u pregledniku informacija o praćenju je sasvim očit: gornji dio prikazuje događaje koji se događaju na poslužitelju, a donji dio pruža detaljne informacije za njih (na primjer, kod SQL naredbi). Napomenimo neke od mogućnosti koje su dostupne u ovom prozoru:

q ako kartica OrganiziratiKolumne u svojstvima predloška odabrali ste stupce za grupiranje, tada možete grupirati po tim stupcima zapise u pregledniku. U tu svrhu u izborniku Pogled(Prikaz) naredba GrupiranoPogled(Grupni prikaz);

q ako se nalazi na istoj kartici u svojstvima predloška na popisu Skupina postavljen je samo jedan stupac, tada možete koristiti još praktičniji način prikaza AgregiranoPogled(sl. 11.3). Ovaj način rada je omogućen pomoću naredbe AgregiranoPogled iz istog izbornika Pogled i omogućuje vam da pretvorite vrijednosti iz stupca po vašem izboru u čvorove stabla koje možete skupiti i proširiti. Osim toga, broj događaja se automatski izračunava za svaki od ovih čvorova.

Riža. 11.3. Način prikaza AgregiranoPogled

q U profileru možete prikazati ne samo događaje koji su upravo uhvaćeni, već i spremljene datoteke i tablice praćenja. Alternativno, možete otvoriti obične skripte SQL Servera s naredbama Transact -SQL. Informacije iz ovih datoteka ili tablica mogu se koristiti za ponavljanje zabilježenih operacija. Naredbe izbornika namijenjene su za tu svrhu. Ponovi(Ponoviti);

q SQL Server 2005 Profiler uvodi novu značajku za povezivanje informacija praćenja s brojačima performansi u Monitoru performansi. Kako biste iskoristili ovu priliku, trebate:

Definirajte sesiju praćenja tijekom koje se moraju zabilježiti informacije za stupce Vrijeme početka i Vrijeme završetka;

· Započnite sesiju praćenja sa snimanjem informacija u datoteku ili tablicu. Istovremeno prikupiti očitanja mjerača Performance Monitora u datoteku;

Otvorite prikupljene informacije iz datoteke praćenja u profileru, a zatim upotrijebite naredbu UvozIzvođenjePodaci(Uvezi podatke o izvedbi) iz izbornika Datoteka.

SQL Server 2005 pruža rezervirano mjesto za profiler. To su procedure pohranjene u tragovima. Njihova je funkcionalnost gotovo identična onoj u profileru. Na primjer, također možete odabrati događaje za praćenje i zapisati ih u tekstualnu datoteku. Glavna razlika je u tome što će se sve postavke morati izvršiti iz Transact -SQL koda.

Pohranjene procedure praćenja teže su i manje prikladne za rad nego s profilatorom i ne pružaju dodatne značajke. Stoga ih nećemo detaljno razmatrati. Ovdje je samo popis takvih pohranjenih procedura s kratkim opisom:

q sp_trace_create- omogućuje vam konfiguriranje parametara sesije praćenja;

q sp_trace_setevent- omogućuje vam odabir potrebnih događaja za kreiranu sesiju praćenja;

q sp_trace_setfilter- omogućuje vam da konfigurirate filtar za prikupljanje podataka o tragovima;

q sp_trace_setstatus- omogućuje vam da pokrenete praćenje, zaustavite ga ili izbrišete trag kreiran pohranjenom procedurom sp_trace_create definicija trenutne sesije;

q sp_trace_generateevent- omogućuje vam generiranje prilagođenog događaja koji će biti presretnut tijekom praćenja.

U prethodnim izdanjima (vidi ComputerPress # 1, 3-5, 7, 9'2006) razmatrali smo probleme vezane uz prijelaz na SQL Server 2005 migracijom ili nadogradnjom, kao i glavne scenarije za korištenje SQL Servera 2005. Ovo članak će se usredotočiti na različite metode optimizacije upita i moguće pristupe rješavanju problema povezanih s pronalaženjem problema izvedbe.

SQL Server pruža usluge koje rade u dinamičnom okruženju koje se stalno mijenja. Stoga vam redovito praćenje rada poslužitelja omogućuje prepoznavanje problema u fazi njihovog nastanka i pravovremeno poduzimanje mjera za njihovo uklanjanje. Akumulacijom statistike pojavljuje se razumijevanje glavnih trendova u radu poslužitelja. Redovito prikupljanje podataka - čak i kada poslužitelj radi glatko - stvorit će takozvanu bazu performansi poslužitelja koja može poslužiti kao mjerilo za daljnja mjerenja performansi poslužitelja.

Nakon što imate osnovnu referentnu vrijednost izvedbe, stječete mogućnost analize izvedbe upita u različitim scenarijima. Preporuča se da se mjerenja provedu barem prije, tijekom i nakon izvođenja različitih postupaka optimizacije kako bi se osiguralo da su poduzeti koraci uspješni. Osim toga, redovita standardna mjerenja performansi (tjedno, mjesečno, tromjesečno, itd.) pružit će jasniju sliku performansi poslužitelja.

Osnovna se linija može koristiti za određivanje vršnih i izvan vršnih sati poslužitelja, vremena odgovora na zahtjeve i pakete naredbi, vremena potrebnog za sigurnosno kopiranje i vraćanje podataka itd.

Izvedba upita

Izvedbu zahtjeva treba promatrati s dva stajališta - resursa koji se koriste za ispunjenje određenog zahtjeva (resursi uključuju objekte kojima pristupamo tijekom izvršenja zahtjeva, blokirane objekte itd.), i vremena utrošenog na izvršavanje zahtjeva , - što je manje vremena potrebno za dovršenje zahtjeva, manja je vjerojatnost da ćemo blokirati druge zahtjeve i transakcije tijekom procesa zahtjeva.

SQL Server 2005 uključuje dva glavna alata za mjerenje performansi upita - Performance Monitor i SQL Server Profiler. Osim toga, T-SQL konstrukcije iz grupe SET STATISTICS — SET STATISTICS IO, SET STATISTICS PROFILE i SET STATISTICS TIME — i dinamički prikazi upravljanja mogu se koristiti za dobivanje informacija o izvršavanju upita. Zatim ćemo pobliže pogledati korištenje Performance Monitora i SQL Server Profilera.

Korištenje Monitora performansi

Uslužni program Performance Monitor koristi se za analizu performansi hardverskih i softverskih resursa, uključujući memoriju, korištenje mreže, vrijeme procesora i informacije povezane s radom SQL Servera i drugih softverskih proizvoda - Microsoft Message Queuing (MSMQ), Microsoft .NET Framework i Microsoft Exchange Server. Konkretno, možete koristiti Performance Monitor za praćenje resursa SQL Servera kao što su brave i transakcije.

Da biste dodali brojače od interesa za nas, morate izvršiti sljedeće korake:

Na jelovniku Početak odabrati tim Trčanje i u dijaloškom okviru Trčanje uvesti perfmon... Time se pokreće Microsoftova konzola za upravljanje performansama (MMC), koja prikazuje grafički prikaz brojnih brojača performansi.
U prozoru Monitor sustava desnom tipkom miša kliknite grafikon i odaberite naredbu Dodajte brojače.
U dijaloškom okviru Dodajte brojače odabrati računalo i predmet koji nas zanima za praćenje. Objekti specifični za SQL Server imaju odgovarajući prefiks.
Da biste odabrali brojače, morate odabrati bilo koju od opcija Svi brojači, ili jedan ili više brojača s padajućeg popisa (slika 1).

Nakon toga trebate odabrati instance baze podataka ili omogućiti opciju Sve instance.
Klikom na gumbe Dodaj i Zatvori dovršava se dodavanje brojača.

Nakon što se dodaju brojači koji nas zanimaju, njihove vrijednosti možemo vidjeti u obliku grafikona ili ih spremiti u datoteku praćenja (slika 2).

U potonjem slučaju potrebno je proširiti element Zapisnici performansi i upozorenja i kliknite na naredbu Nove postavke dnevnika... U dijaloškom okviru Nove postavke dnevnika potrebno je odrediti naziv protokola i kliknuti gumb OK. Dalje u dijaloškom okviru CounterLogName biramo tim Dodajte brojače... Dodavanje brojača od interesa za nas je isto kao što je gore opisano. Imajte na umu da veliki broj brojača može utjecati na performanse sustava. Kada završite s dodavanjem brojača, kliknite Zatvoriti.

U kartici Općenito U poglavlju Uzorak podataka svaki možete postaviti frekvenciju uzorkovanja (hvatanje podataka). Preporuča se početi s prosječnom učestalošću, na primjer svakih 5 minuta, a zatim je po potrebi smanjiti ili povećati. Imajte na umu da što je kraći interval uzorkovanja, potrebno je više resursa sustava i diska. Zapamtite da intervali kraći od razdoblja kvantizacije brojača također mogu rezultirati netočnim prikazom podataka. U kartici Datoteke dnevnika možete konfigurirati svojstva datoteke dnevnika i dalje Raspored postavljen je raspored praćenja. Pritiskom na tipku OK dolazi do kreiranja protokola na početku prikupljanja podataka (slika 3).

Da biste ručno prekinuli bilježenje, potrebno je desnom tipkom miša kliknuti element Counter Logs i odaberite tim Stop.

Za pregled podataka prikupljenih u datoteci dnevnika u Monitoru performansi, odaberite stavku Monitor sustava, u programskoj traci - naredba Pregledajte podatke dnevnika, i u dijaloškom okviru Svojstva monitora sustava u kartici Izvor navedite naziv datoteke dnevnika.

Najčešće korišteni brojači Performance Monitora, s kojih možete koristiti informacije za prepoznavanje problema s izvedbom, podijeljeni su u sljedeće kategorije:

pristup podacima - objekt se koristi za reviziju metoda pristupa podacima koje koristi sam SQL Server SQLServer: Metode pristupa;
statistika - objekt se koristi za praćenje kompilacije i ponovnog kompiliranja upita SQLServer: SQL statistika... Pruža informacije o tome koliko brzo i učinkovito SQL Server obrađuje upite;
transakcije – brojači koji se odnose na objekte služe za određivanje broja transakcija u sekundi SQLServer: Baze podataka i SQLServer: Transakcije;
brave - objekt se koristi za reviziju zaključavanja SQL Servera postavljenih na određene vrste resursa SQLServer: Brave(Stol 1).

Tablica 1. Brojači SQL Server Profilera

Naziv brojača	Opis

	Broji skeniranje raspona za indekse u sekundi
	Broji broj potpunih skeniranja dovršenih u posljednjoj sekundi
Indeks pretraživanja / sec	Broji broj pretraživanja indeksa u posljednjoj sekundi
Eskalacije zaključavanja tablice / sec	Broji broj brava za stol
Radni stolovi Kreirani / sec	Broji broj radnih listova stvorenih u posljednjoj sekundi

Skupni zahtjevi / sec	Broji broj Transact-SQL paketa naredbi u sekundi. Veliki broj paketa znači dobru propusnost
SQL kompilacije / sec	Broji broj kompilacija upita u sekundi. Vrijednost ovog brojača trebala bi postati gotovo konstantna nakon što korisnik izvrši osnovne radnje.
SQL ponovne kompilacije / sec	Broji broj ponovno kompajliranih zahtjeva u sekundi

SQLServer objekt: Baze podataka. Brojač transakcija / sek	Broji broj transakcija započetih u bazi podataka u posljednjoj sekundi
SQLServer objekt: Transakcije. Najduže vrijeme izvođenja transakcije brojača	Izračunava broj sekundi od početka transakcije koja je aktivna dulje od bilo koje druge trenutne transakcije. Ako ovaj brojač pokazuje vrlo dugu transakciju, koristite pohranjenu proceduru sustava sys.dm_tran_active_transactions () da biste dobili informacije o ovoj transakciji.
SQLServer objekt: Transakcije. Ažurirajte brojač omjera sukoba	Izračunava postotak transakcija koje koriste izolaciju snimka za rješavanje sukoba koji se javljaju prilikom ažuriranja podataka u posljednjoj sekundi

prosječno vrijeme čekanja (ms)	Izračunava prosječno vrijeme čekanja za svaki zahtjev za zaključavanje koji je uzrokovao čekanje
Zahtjevi za zaključavanje / sec	Broji broj zaključavanja i konverzija zaključavanja u sekundi
Vrijeme čekanja zaključavanja (ms)	Izračunava kumulativno čekanje za zaključavanje u posljednjoj sekundi
	Broji broj zahtjeva za zaključavanje u sekundi koji su rezultirali čekanjem

Drugi uslužni program koji možete koristiti za mjerenje performansi upita je SQL Server Profiler. Zatim ćemo pogledati glavne načine korištenja.

Korištenje programa SQL Server Profiler

SQL Server Profiler mjeri izvedbu pojedinačnih upita i upita koji su dio pohranjenih procedura i Transact-SQL paketa naredbi. Može se koristiti za prikupljanje informacija o izvedbi, uključujući vrijeme potrebno za izvršavanje pojedinačne naredbe, vrijeme blokiranja za naredbu i plan izvršenja.

Da biste stvorili novi profil, morate slijediti niz sljedećih koraka:

Pokrenite SQL Server Profiler ( Microsoft SQL Server 2005 => Alati za performanse => SQL Server Profiler).
Na jelovniku Datoteka odabrati tim Novi trag.
U dijaloškom okviru Povežite se s poslužiteljem odaberite željeni poslužitelj i kliknite gumb Spojiti.
U dijaloškom okviru Svojstva traga morate postaviti naziv profila (naziv traga), i na popisu Koristite predložak odaberite jedan od dostupnih predložaka ili Prazan ako se ne koristi predložak.
Da biste spremili rezultate profiliranja, morate odabrati opciju Spremi u datoteku za pisanje podataka u datoteku i navođenje maksimalne veličine datoteke (zadana vrijednost je 5 MB), opcionalno možete omogućiti opciju Omogući prelazak datoteke za automatsku izradu nove datoteke kada se dosegne navedena veličina profila. Druga mogućnost je pohranjivanje podataka u tablicu baze podataka (Spremi u tablicu)- opcija Postavite maksimalan broj redaka omogućuje postavljanje maksimalnog broja zapisa.
Da biste postavili vrijeme završetka praćenja, možete koristiti opciju Omogući vrijeme zaustavljanja praćenja(slika 4).

Da biste definirali događaje i stupce podataka za profil, morate izvesti sljedeće korake:

U dijaloškom okviru Svojstva traga idite na karticu Odabir događaja.
Dodajte ili uklonite događaje iz profila pomoću tablice klasa događaja.
Da biste dobili popis svih dostupnih događaja, omogućite opciju Prikaži sve događaje(sl. 5).

Stol Tablica 2 navodi najčešće korištene događaje SQL Server Profilera. Poput brojača performansi, događaji SQL Server Profilera spadaju u brojne kategorije, od kojih su neke zanimljive za naše svrhe.

Tablica 2. Događaji programa SQL Server Profiler

Naziv događaja	Opis

	Ovaj događaj se događa kada se završi poziv udaljene procedure.
	Ovaj događaj se pokreće kada se pohranjena procedura završi s izvođenjem.
SP: StmtCompleted	Ovaj događaj se događa kada se jedna od Transact-SQL naredbi unutar pohranjene procedure završi s izvršavanjem.

SQL: StmtCompleted	Ovaj događaj se pokreće kada se naredba Transact-SQL dovrši.
SQL: BatchCompleted	Ovaj događaj se događa kada se serija Transact-SQL naredbi završi s izvršavanjem.

	Ovaj događaj se pokreće kada transakcija zatekne neki resurs
	Ovaj događaj se događa kada transakcija oslobodi prethodno zaključani resurs
	Ovaj događaj se pokreće kada se prekorači vremensko ograničenje za stjecanje zaključavanja, budući da je traženi resurs već zaključan drugom transakcijom

Kako odrediti postoje li blokade

Svaki put kada transakcija koristi resurs (tablicu, stranicu, indeks itd.), na njega se postavlja zaključavanje. Ako druga transakcija pokuša pristupiti ovom resursu, a tip zaključavanja nije kompatibilan s već zadržanom bravom, kreira se nova brava.

Za utvrđivanje prisutnosti brava mogu se koristiti sljedeće metode:

koristite uslužni program SQL Server Management Studio Activity Monitor, koji prikazuje informacije o procesima, zaključavanja na razini procesa i na razini objekta. Za pristup Monitoru aktivnosti u SQL Server Management Studiju, odaberite stavku Upravljanje, a u njemu - Monitor aktivnosti i dvaput kliknite na tu stavku. Monitor aktivnosti omogućuje vam pregled:

Zaključani objekti po procesu - koristite ID procesa da biste identificirali zahtjev koji je izazvao zaključavanje ID procesa poslužitelja (SPID) Na stranici Informacije o procesu,

Procesi zbog kojih drugi procesi čekaju - koristite stupac za identifikaciju takvih procesa. Blokirao Na stranici Informacije o procesu(sl. 6);

koristite SQL Server Profiler da biste dobili izvješće o blokiranim procesima - ovaj popis prikazuje informacije o procesima koji su blokirani dulje od određenog vremena O

Ovaj članak sadrži nekoliko materijala objavljenih na mailing listi "MS SQL Server je delikatna stvar..." i posvećenih problemima praćenja učinkovitosti MS SQL Servera, metodama otkrivanja uzroka hardverskih problema i njihovom rješavanju. Osim toga, bit će vam ponuđeno nekoliko korisnih, praktičnih preporuka i upozorenja.

Ako vaš poslužitelj baze podataka intenzivno koristi I/O, možete promijeniti postavku I/O Page Lock Limit operativnog sustava, što može povećati efektivnu brzinu čitanja/pisanja operativnog sustava na tvrdi disk.
Prvo, usporedite I/O za tipično opterećenje poslužitelja. Zatim u regedit.exe otvorite ključ:

HKLM \ SYSTEM \ CurrentControlSet \ Control \ SessionManager \ MemoryManagement \ IoPageLockLimit

Smisao vaših radnji je korak po korak odabir vrijednosti ovog ključa do najoptimalnije vrijednosti u smislu promjena u rezultatima benchmarkinga.
U ovom ključu operativni sustav čita maksimalni broj bajtova koje može koristiti za I/O operacije. Zadana vrijednost je 0, što odgovara 512 KB. Povećajte ovu vrijednost u koracima, dodajući svaki put 512 KB (na primjer: "512", "1024" itd.) i usporedite svoj sustav nakon svake promjene. Ovaj parametar ima smisla povećavati samo sve dok promatrate povećanje propusnosti I/O operacija, što se može manifestirati u smanjenju vremena provedenog na standardnim diskovnim operacijama. Kada više ne vidite značajno poboljšanje, vratite se u uređivač registra i izbrišite zadnji porast.

Upozorenje: Postoji ograničenje maksimalne veličine vrijednosti ovog ključa. Ako imate 16 MB RAM-a, nemojte postavljati IoPageLockLimit više od 2048 bajtova; za 32MB RAM-a, ne prelazi 4096 bajtova i tako dalje.

Važna nota:

Operacije koje je naveo Sergej, ako se koriste netočno ili pogrešno, mogu dovesti do kolapsa vašeg sustava. Stoga se unaprijed pobrinite za dostupnost sigurnosne kopije baze podataka i sustava, spremite radnu NT konfiguraciju i simulirajte moguće promjene na mjestu testiranja. Nikada nemojte mijenjati više od jedne konfiguracije odjednom.