Zdravo svima! Nedavno sam naišao na problem u kojem je trebalo mnogo vremena za obradu dokumenta.

Ulazni podaci: konfiguracija “Upravljanje proizvodnim preduzećem, izdanje 1.3 (1.3.52.1)”, dokument “Ulazni nalog za plaćanje”. Prigovor: zadržavanje u radnoj bazi podataka traje 20-30 sekundi, što je zanimljivo, u kopiji baze podataka isti dokument se drži 2-4 sekunde. U nastavku pročitajte o istragama i razlozima ovakvog ponašanja.

Dakle, uz pomoć mjerenje performansi Mislim da svi znaju kako se to koristi, krivac je pronađen:

U ovom slučaju, prazan skup zapisa je zabilježen na snimaču, drugim riječima, pokreti su izbrisani prije izvršenja. Vrijedi napomenuti da je ovaj postupak pozvan 26 puta, tj. za svaki registar u koji bi naš dokument mogao pisati.

Prema mjerenjima performansi, ova operacija je trajala 13 sekundi, a ako izračunate prosjek, dobijate 0,5 sekundi po registru, cijelu vječnost!

Kao što svi znamo, ne možemo optimizirati snimanje, ali ovdje očito nešto nije u redu.
Za dalju analizu otvorite SQL Server Profiler i . Za analizu sam koristio klase događaja:

• Showplan Statistics Profil
• Showplan XML statistički profil
• RPC završen
• SQL: BatchCompleted.

U postavkama praćenja postoji filter prema SPID-u:

SPID je ID procesa servera baze podataka. U slučaju 1C, to je u suštini veza između 1C servera i DBMS-a; možete je pogledati u administrativnoj konzoli 1C servera u koloni „Veza sa DBMS-om“.

Prikazuje se ako ovog trenutka veza s bazom podataka se hvata sesijom: ili se vrši DBMS poziv, ili je transakcija otvorena, ili se drži objekt „Upravljač privremenim tablicama“ u kojem je kreirana barem jedna privremena tablica.

Napišimo obradu za držanje SPID-a, ona će sadržavati jednu proceduru:

Važno je da objekt veze koji se drži, u našem slučaju privremeni upravitelj tablice, bude definiran kao varijabla za obradu. Otvaramo obradu, pokrećemo proceduru i sve dok je otvorena SPID će biti popravljen. Otvorite administrativnu konzolu 1C servera:

Dakle, SPID je primljen, unosimo njegovu vrijednost u filter i dobijamo trag iz trenutne radne baze podataka za našu sesiju. Prilikom analize traga pronađena je operacija za koju je trebalo 11 sekundi:

Ono što mi je takođe zapalo za oko je broj čitanja - 1872578 , ali nisam odmah pridao značaj ovome i počeo sam da shvaćam šta se tu radi i sa kojim stolom.

exec sp_executesql <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))’,N’@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)’,0x8A2F00155DBF491211E87F56DD1A416E,’4018-05-31 23:59:59′,0x00000000000000000000000000000000,’4018-05-31 23:59:59′,0x9A95A0369F30F8DB11E46684B4F0A05F,’4018-05-31 23:59:59"

Kao što možete vidjeti iz SQL upita, tabela je obrađena "AccRg1465" Ovo je tabela u samonosnom računovodstvenom registru. Tekstualni prikaz plana izvršenja upita:

Kao što možete vidjeti iz plana izvršenja SQL upita, ništa loše se ne dešava, tabela “ AccRg1465", klasterizovano pretraživanje indeksa se koristi svuda.

Također nisam vidio ništa loše u grafičkom planu, iako mi se činio previše naduvan - došlo je do spajanja i dvije ugniježđene petlje bez ikakvog razloga. Odakle dolazi ovoliki broj čitanja i gigantsko vrijeme izvršenja?

Kao što je gore navedeno, problem nije reprodukovan u novoj kopiji baze podataka, već je kopija preuzeta iz radne baze podataka nakon što se problem pojavio u njoj, pa je odlučeno da se analizira njegovo ponašanje u SQL Server Profiler-u na istom dokumentu.
Evo rezultata:

Tekst upita u SQL-u:

EXEC sp_executesql N"SELECT TOP 1 0x01 IZ dbo._AccRg1465 T1 GDJE (T1._RecorderTRef = 0x0000022D AND T1._RecorderRRef = @P1) I ((((T1._Period<= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))" , N"@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)",6e, "4018-05-31 23:59:59", 00, "4018-05-31 23:59:59",5f, "401 8-05-31 23:59:59"

Grafički prikaz plana upita:

Tekstovi zahtjeva su isti, planovi izvršenja su radikalno različiti. Šta bi moglo biti? Pogriješio sam u vezi statistike u SQL-u, ali one su iste između radne i kopije baze podataka, a statistika se pohranjuje u bazi podataka za svaku tablicu:

Analizirajmo dalje: ako je statistika ista, ali su planovi upita različiti, to znači da optimizator ne pristupa statistici da bi napravio plan upita, već ima keširani plan koji koristi. Brišemo proceduralni keš u našoj bazi podataka, za to koristimo naredbu

DBCC FLUSHPROCINDB(< database_id >)

Gdje< database_id >je identifikator baze podataka. Da biste saznali ID baze podataka, morate pokrenuti skriptu

odaberite ime, database_id iz sys . baze podataka

vratiće nam listu baza podataka i njihove identifikatore.

Ponovo dobijamo trag:

Tekstualni prikaz plana upita:

Grafički prikaz plana upita:

Kao što vidite, optimizator je ponovo dobio plan upita, a stari keširani nije korišten, vrijeme izvršenja se vratilo na normalu, kao i broj čitanja. Nije jasno šta je to izazvalo, možda veliki broj razmjena ili zatvaranje prethodnih perioda, teško je reći. Konfigurirano je redovno održavanje baze podataka. Ovo je prvi put da sam naišao na prevaru s planom izvršavanja keširanih upita.

Hvala vam na pažnji!

Da li vam je ovaj članak pomogao?

6 odgovora

Postoji nekoliko načina da dobijete plan izvršenja, koji će zavisiti od vaših okolnosti. Obično možete koristiti SQL Server Management Studio za dobivanje plana, međutim, ako iz nekog razloga ne možete pokrenuti svoj upit u SQL Server Management Studiju, možda će vam biti korisno da dobijete plan putem SQL Server Profilera ili provjerom plana skladiste.

Metoda 1 - Korištenje SQL Server Management Studio

SQL Server ima neke zgodne karakteristike koje olakšavaju prikupljanje plana izvršenja, samo se uverite da je stavka menija "Uključi stvarni plan izvršenja" (koja se nalazi u meniju upita) označena i da će vaš raditi normalno.

Ako pokušavate da dobijete plan izvršenja za izraze u pohranjenoj proceduri, izvršili biste pohranjenu proceduru, ovako:

Exec p_Primjer 42

Kada je vaš upit dovršen, vidjet ćete dodatnu karticu Plan izvršenja koja se pojavljuje u oknu s rezultatima. Ako ste pokrenuli mnogo odobrenja, možda ćete vidjeti mnogo planova prikazanih na ovoj kartici.

Ovdje možete provjeriti plan izvršenja u SQL Server Management Studiju ili desnim klikom na plan i odabrati "Sačuvaj plan izvršenja kao..." da biste plan spremili u XML datoteku.

Metoda 2 - Korišćenje opcija SHOWPLAN-a

Ova metoda je vrlo slična metodu 1 (ovo je zapravo ono što SQL Server Management Studio radi interno), međutim uključio sam ga radi potpunosti ili ako nemate dostupan SQL Server Management Studio.

Prije izvršavanja upita, pokrenite jedan sledeći operateri. Izjava mora biti jedina izjava u paketu, tj. Ne možete izvršiti još jednu naredbu u isto vrijeme:

POSTAVI SHOWPLAN_TEXT ON POSTAVI SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFIL ON SET STATISTICS XML ON -- Preporučena je opcija za korištenje

Ovo su parametri veze, tako da ovo trebate samo jednom pokrenuti za svaku vezu. Od sada će sve pokrenute izjave biti propraćene dodatni skup rezultata koji sadrži vaš plan izvršenja u potrebnom formatu - samo pokrenite upit kao i obično da vidite plan.

Kada završite, možete onemogućiti ovu opciju pomoću sljedeće izjave:

SET<> OFF

Poređenje formata plana izvršenja

Ako imate jaku sklonost, preporučujem korištenje opcije STATISTICS XML. Ova opcija je ekvivalentna opciji "Uključi stvarni plan izvršenja" u SQL Server Management Studiju i pruža najviše informacija u najkorisnijem formatu.

• SHOWPLAN_TEXT - Prikazuje osnovni tekstualni procijenjeni plan izvršenja bez izvršavanja upita
• SHOWPLAN_ALL - Prikazuje procijenjeni plan izvršenja zasnovan na tekstu sa procjenom troškova bez izvršavanja upita
• SHOWPLAN_XML - Prikazuje procijenjeni plan izvršenja zasnovan na XML-u sa procjenama troškova bez izvršavanja upita. Ovo je ekvivalentno opciji "Prikaži primjer plana izvršenja..." u SQL Server Management Studiju.
• STATISTIČKI PROFIL - Izvršava upit i prikazuje stvarni plan izvršenja na osnovu teksta.
• STATISTICS XML - Izvršava upit i prikazuje stvarni plan izvršenja zasnovan na XML-u. Ovo je ekvivalentno opciji "Uključi stvarni plan izvršenja" u SQL Server Management Studiju.

Metoda 3 - Korištenje SQL Server Profilera

Ako ne možete direktno pokrenuti upit (ili vaš upit ne radi sporo kada ga pokrenete direktno - zapamtite da želimo da plan upita loše radi), onda možete snimiti plan koristeći SQL Server Profiler. Ideja je da pokrenete vaš upit dok traje praćenje koje bilježi jedan od događaja "Showplan".

Imajte na umu da ovisno o opterećenju možeš koristite ovu metodu u proizvodnom okruženju, međutim očito biste trebali biti oprezni. Mehanizmi za profilisanje SQL Servera su dizajnirani da minimiziraju uticaj na bazu podataka, ali to ne znači da neće biti uticaja na performanse. Također možete imati problema s filtriranjem i određivanjem ispravnog plana u vašem praćenju ako je vaša baza podataka pod velikom upotrebom. Očigledno biste trebali provjeriti sa svojim DBA kako biste bili sigurni da su zadovoljni što to radite sa svojom dragocjenom bazom podataka!

• Otvorite SQL Server Profiler i kreirajte novi trag koji se povezuje sa željenom bazom podataka iz koje želite da snimite praćenje.
• Na kartici Odabir događaja potvrdite okvir Prikaži sve događaje, označite Performanse -> Showplan XML red i pokrenite praćenje.
• Dok se praćenje izvodi, učinite sve što trebate da pokrenete spori upit.
• Sačekajte dok se zahtjev ne završi i praćenje se ne zaustavi.
• Da biste sačuvali praćenje, kliknite desnim tasterom miša na xml plan u profilu SQL Servera i izaberite "Izdvoji podatke o događaju..." da biste plan sačuvali u XML datoteci.

Plan koji dobijete je ekvivalentan opciji "Uključi plan stvarnog izvršenja" u SQL Server Management Studiju.

Metoda 4 - Provjera keša upita

Ako ne možete direktno pokrenuti svoj upit, a također ne možete uhvatiti praćenje profilatora, i dalje biste trebali moći dobiti procijenjeni plan provjerom plana keš memorije SQL upita.

Predmemoriju plana provjeravamo upitom za SQL Server DMV. Ispod je osnovni upit koji će navesti sve keširane planove upita (kao xml) zajedno sa njihovim SQL tekstom. U većini baza podataka, također ćete morati dodati dodatne uvjete filtera kako biste filtrirali rezultate prema planovima koji vas zanimaju.

ODABERITE UseCounts, Cacheobjtype, Objtype, TEXT, query_plan IZ sys.dm_exec_cached_plans UKRASNA PRIMJENA sys.dm_exec_sql_text(plan_handle) UNAKRSNA PRIMJENA sys.dm_exec_query_plan(plan_handle)

Pokrenite ovaj upit i kliknite na XML plan da otvorite plan u novom prozoru - kliknite desnim tasterom miša i izaberite "Sačuvaj plan izvršenja kao..." da biste plan sačuvali u datoteku u XML formatu.

napomene:

Budući da postoji toliko mnogo faktora (u rasponu od sheme tablice i indeksa do pohranjenih podataka i statistike tablice), morate Uvijek pokušajte da dobijete plan izvršenja iz baze podataka koja vas zanima (obično one koja ima problem sa performansama).

Ne možete predati plan izvršenja za šifrirane pohranjene procedure.

"stvarni" i "procijenjeni" planovi izvršenja

Stvarni plan izvršenja je onaj u kojem SQL Server zapravo izvršava upit, dok procijenjeni plan izvršenja SQL Server radi na onome što bi mogao učiniti bez izvršavanja upita. Iako je logički ekvivalentan, stvarni plan izvršenja je mnogo korisniji jer sadrži dodatne podatke i statistiku o tome šta se zapravo dogodilo kada je upit izvršen. Ovo je važno prilikom dijagnosticiranja problema kada su evaluacije SQL servera onemogućene (na primjer, kada je statistika zastarjela).

Kako protumačiti plan izvršenja upita?

Ovo je tema koja je dovoljno vrijedna za besplatnu knjigu.

Pored sveobuhvatnog odgovora koji je ponekad već objavljen, korisno je imati mogućnost da programski pristupite planu izvršenja kako biste izvukli informacije. Uzorak koda za ovo je ispod.

DECLARE @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help "sys.objects" /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

Moj omiljeni alat za dobijanje i dubinsku analizu planova izvršenja upita je SQL Sentry Plan Explorer. Mnogo je praktičniji, lakši za upotrebu i potpuniji za detaljnu analizu i vizualizaciju planova izvršenja od SSMS-a.

Evo primjera ekrana kako biste razumjeli koje funkcije nudi alat:

Ovo je samo jedan od prikaza dostupnih u alatu. Obratite pažnju na skup kartica na dnu prozora aplikacije koje vam omogućavaju da dobijete različite vrste prikaza plana izvršenja i korisne dodatne informacije.

Osim toga, nisam primijetio nikakva ograničenja u besplatnoj verziji koja vas sprečavaju da je koristite na dnevnoj bazi ili vas prisiljavaju da na kraju kupite Pro verziju. Dakle, ako se radije držite besplatne verzije, ništa nije zabranjeno.

Osim metoda opisanih u prethodnim odgovorima, možete koristiti i besplatni preglednik plana izvršavanja i alat za optimizaciju upita ApexSQL Plan (na koji sam nedavno naišao).

Možete instalirati i integrirati ApexSQL plan u SQL Server Management Studio, tako da se planovi izvršavanja mogu direktno gledati iz SSMS-a.

Pogledajte predviđene planove izvršenja u ApexSQL planu

• Kliknite na dugme Novi zahtjev u SSMS-u i zalijepite tekst upita u okvir za tekst upita. Kliknite desnim tasterom miša i izaberite "Prikaži plan izvršenja uzorka" iz kontekstnog menija.

1. Dijagram plana izvršenja će prikazati karticu Planiranje izvršenja u odjeljku rezultata. Zatim kliknite desnim tasterom miša na plan izvršenja i izaberite opciju "Otvori u ApexSQL planu" iz kontekstnog menija.

1. Procijenjeni plan izvršenja će se otvoriti u ApexSQL planu i može se analizirati radi optimizacije upita.

Pregled stvarnih planova izvršenja u ApexSQL planu

Da vidite stvarni plan izvršenja upita, idite na drugi korak koji je ranije naveden, ali sada, kada se procijenjeni plan pojavi, kliknite na dugme "Stvarni" na glavnoj traci u ApexSQL planu.

Nakon klika na dugme Stvarno, biće prikazan stvarni plan izvršenja sa detaljnim pregledom parametara troškova zajedno sa ostalim podacima plana izvršenja.

Više informacija o pregledu planova izvršenja možete pronaći na ovom linku.

Planovi upita mogu se dobiti iz proširene sesije događaja kroz događaj query_post_execution_showplan. Evo primjera XEvent sesije:

/* Generirano preko šablona "Praćenje detalja upita". */ KREIRAJTE SESIJU DOGAĐAJA NA SERVERU DODAJTE DOGAĐAJ sqlserver.query_post_execution_showplan(ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server.qlserver,sqlserver.qsl_server,sqlserver.qlserver. okvir,sqlserver.tsql_stack)), / * Uklonite bilo koji od sljedećih događaja (ili uključite dodatne događaje) po želji. */ DODAJ DOGAĐAJ sqlserver.error_reported(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_sequence,qlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan. lserver.t sql_frame,sqlserver.tsql_stack ) GDJE (.(.,(4)) I .(.,(0)))), DODAJTE DOGAĐAJ sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) GDJE (.(.,(4)) ADDEV( AND 0) sqlserver.rpc_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server.sql_server,sqlserver.sql. okvir,sqlserver.tsql_stack) GDJE (. ( .,(4)) I .(.,(0))), DODAJ DOGAĐAJ sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sql_server,sqlserver,sqlserver. query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) GDJE (.(.,(4)) I .(.,(ENTs)), qlserver. ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sqlserver.sqlsl,sqlserver.sqlsl. _st ack) GDJE (.(.,( 4 )) I .(.,(0)))), DODAJTE DOGAĐAJ sqlserver.sql_statement_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.plan_handle,sqlserver.server. sion_id , sqlserver.sql_text, sqlserver.tsql_frame, sqlserver.tsql_stack) gdje (. (. (4))) i. (. (max_memory = 4096 kB, event_retent_mode = dopušta_single_event_loss, max_dispatch_l ATENCY =30 SEKUNDI,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NEMA,TRACK_CAUSALITY=UKLJUČENO,STARTUP_STATE=ISKLJUČENO) IDI

Kada se sesija kreira (u SSMS), idite na Pregledač objekata i idite na Upravljanje | Prošireni događaji | Sesije. Kliknite desnim tasterom miša na sesiju "GetExecutionPlan" i pokrenite je. Kliknite desnim tasterom miša i izaberite "Gledanje podataka uživo".

Zatim otvorite novi prozor upita i pokrenite jedan ili više upita. Evo jednog za AdventureWorks:

USE AdventureWorks; IDI SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail KAO sod ON p.ProductID = sodER. ProductName DESC; GO

Nakon minut ili dva, vidjet ćete neke rezultate na kartici "GetExecutionPlan: Live Data". Odaberite jedan od događaja query_post_execution_showplan u mreži, a zatim kliknite karticu Plan upita ispod mreže. Trebalo bi izgledati otprilike ovako:

EDIT: XEvent kod i snimak ekrana generirani su iz SQL/SSMS 2012 w/SP2. Ako koristite SQL 2008/R2, možete postaviti skriptu za njegovo pokretanje. Ali ova verzija nema GUI, tako da ćete morati izdvojiti XML datoteku showplan, spremiti je kao *.sqlplan datoteku i otvoriti je u SSMS-u. To je glomazno. XEvents nisu postojali u SQL-u 2005 ili ranije. Dakle, ako niste na SQL 2012 ili novijoj verziji, toplo bih predložio jedan od drugih odgovora objavljenih ovdje.

dijeliti

Optimizacija upita u SQL Serveru 2005, SQL Server 2005 statistika baze podataka, CREATE STATISTICS, UPDATE STATISTICS, POSTAVI NOCOUNT ON, planovi izvršenja upita, broj logičkih čitanja, savjeti za optimizaciju, MAXDOP, OPTIMIZE FOR, tutorijali za izvršavanje planova (planguzide planova)

Ako su sve druge metode optimizacije performansi već iscrpljene, tada programeri i administratori SQL Servera imaju na raspolaganju posljednju rezervu - optimizaciju izvršavanja pojedinačnih upita. Na primjer, ako vaš zadatak apsolutno zahtijeva ubrzanje kreiranja jednog određenog izvještaja, možete analizirati upit koji se koristi za kreiranje ovog izvještaja i pokušati promijeniti njegov plan ako nije optimalan.

Mnogi stručnjaci imaju dvosmislen stav prema optimizaciji upita. S jedne strane, rad softverskog modula Query Optimizer, koji generiše planove izvršenja upita, izaziva mnoge poštene kritike i u SQL Serveru 2000 i SQL Serveru 2005. Optimizator upita često bira ne najoptimalnije planove izvršenja upita iu nekim situacijama gubi sličnim modulima iz Oraclea i Informixa. S druge strane, ručna optimizacija upita je izuzetno radno intenzivan proces. Možete potrošiti dosta vremena na takvu optimizaciju i na kraju otkriti da ništa nije optimizirano: plan koji je prvobitno predložio Query Optimizer pokazao se najoptimalnijim (to se događa u većini slučajeva). Osim toga, može se dogoditi da se plan izvršavanja upita koji ste ručno kreirali nakon nekog vremena (nakon dodavanja novih informacija bazi podataka) pokaže neoptimalnim i da će smanjiti performanse prilikom izvršavanja upita.

Imajte na umu i da Optimizator upita zahtijeva ispravne statističke informacije za odabir najboljih planova upita. Pošto, prema iskustvu autora, ne znaju svi administratori šta je to, reći ćemo vam više o statistici.

Statistika- ovo je posebna servisna informacija o distribuciji podataka u kolonama tabele. Zamislimo, na primjer, da se izvršava upit koji bi trebao vratiti sve Ivanove koji žive u gradu Sankt Peterburgu. Pretpostavimo da 90% zapisa u ovoj tabeli ima istu vrijednost u koloni Grad - "Sankt Peterburg". Naravno, sa stanovišta izvršenja upita, prvo je isplativije odabrati sve Ivanove u tabeli (oni očito neće biti 90%), a zatim provjeriti vrijednost stupca Grad za svaki odabrani zapis. Međutim, da biste saznali kako su vrijednosti u stupcu raspoređene, prvo morate pokrenuti upit. Stoga SQL Server samostalno inicira izvršavanje takvih upita, a zatim pohranjuje informacije o distribuciji podataka (koja se naziva statistika) u tablicama usluga baze podataka.

Za SQL Server 2005 baze podataka, podrazumevane postavke su AUTO_CREATE_STATISTICS I AUTO_UPDATE_STATISTICS. U ovom slučaju, statistika za stupce baze podataka će se kreirati i ažurirati automatski. Za najveće i najvažnije baze podataka može se dogoditi da operacije kreiranja i ažuriranja statistike mogu ometati trenutno korisničko iskustvo. Stoga su za takve baze podataka ponekad ovi parametri onemogućeni, a operacije kreiranja i ažuriranja statistike se izvode ručno noću. Komande koje se koriste za ovo su CREATE STATISTICS I AŽURIRAJ STATISTIKU.

Sada razgovarajmo o optimizaciji upita.

Prvo što treba učiniti je pronaći one upite koji su prvenstveno podložni optimizaciji. Najlakši način da to učinite je uz pomoć profilera, postavljanjem filtera za vrijeme trajanja zahtjeva (filter Trajanje u prozoru UrediFilter(Uredi filter), koji se može otvoriti pomoću dugmeta KolonaFilteri na kartici DogađajiOdabir prozor sa svojstvima sesije praćenja). Na primjer, kandidati za optimizaciju mogu uključivati ​​upite čije je vrijeme izvršenja bilo duže od 5 sekundi. Također možete koristiti informacije o upitu koje vam daje Savjetnik za podešavanje baze podataka.

Zatim morate provjeriti je li parametar postavljen za vaše veze, pohranjene procedure i funkcije NOCOUNT. Možete ga instalirati pomoću naredbe UKLJUČI NOCOUNT. Prilikom postavljanja ovog parametra, prvo je onemogućen povratak sa servera i prikaz informacija o broju redova u rezultatima upita (tj. red "N red(ova) zahvaćeno" na kartici Poruke(C poruke) prozori za rad sa kodom prilikom izvršavanja zahtjeva u Management Studiju). Drugo, prijenos posebne poruke servera je onemogućen DONE_IN_PROC, koji se vraća po defaultu za svaki korak pohranjene procedure. Kada pozivate većinu pohranjenih procedura, potreban vam je samo rezultat njihovog izvršenja, a niko ne brine o broju redova obrađenih za svaku fazu. Stoga, postavljanje parametra NOCOUNT jer uskladištene procedure mogu ozbiljno poboljšati njihov učinak. Brzina izvršavanja redovnih upita također se povećava, ali u manjoj mjeri (do 10%).

Nakon toga možete početi raditi s planovima izvršavanja upita.

Najlakši način da vidite plan izvršenja upita je iz SQL Server Management Studio. Da biste dobili informacije o očekivanom planu izvršenja upita, možete koristiti meni Upit(Upit) odabir naredbe DisplayProcijenjenoIzvršenjePlan(Prikaži očekivani plan izvršenja). Ako želite znati stvarni plan za izvršavanje upita, možete postaviti parametar u istom izborniku prije nego što ga izvršite UključiStvarnoIzvršenjePlan(Uključuje pravi plan izvršenja). U tom slučaju, nakon izvršenja upita, u prozoru rezultata u SQL Server Management Studio-u pojavit će se još jedna kartica IzvršenjePlan(Plan izvršenja), koji će prikazati stvarni plan izvršenja upita. Kada pređete mišem preko bilo koje faze, možete dobiti dodatne informacije o njoj (Sl. 11.15).

Rice. 11.15. Plan izvršenja upita u SQL Server Management Studiju

U planu izvršenja upita, kao što možete vidjeti na slici, može biti mnogo elemenata. Njihovo razumijevanje, kao i predlaganje drugačijeg plana izvršenja, prilično je težak zadatak. Mora se reći da je svaki od mogućih elemenata optimalan u svojoj situaciji. Stoga, obično faze optimizacije upita izgledaju ovako:

q Prvo, u prozoru Management Studio, pokrenite naredbu POSTAVI STATISTIKU IO UKLJUČENO. Kao rezultat toga, nakon svakog izvršenja zahtjeva, bit će prikazane dodatne informacije. U njemu nas zanima vrijednost samo jednog parametra - Logical Reads. Ovaj parametar označava broj logičkih čitanja pri izvršavanju upita, odnosno koliko je operacija čitanja moralo biti izvršeno prilikom izvršavanja datog upita bez uzimanja u obzir utjecaja keša (broj čitanja i iz keša i diska). Ovo je najvažniji parametar. Broj fizičkih čitanja (čitanja samo sa diska) nije baš reprezentativna informacija, jer zavisi od toga da li je bilo prethodnih pristupa ovim tabelama ili ne. Statistika vremena je također promjenjiva i ovisi o drugim operacijama koje server izvodi u to vrijeme. Ali broj logičkih čitanja je najobjektivniji pokazatelj, na koji najmanje utiču dodatni faktori;

q zatim pokušajte promijeniti plan izvršenja upita i saznati ukupan broj logičkih čitanja za svaki plan. Tipično, plan izvršenja upita se mijenja korištenjem savjeta optimizatora. Oni eksplicitno govore optimizatoru koji plan izvršenja da koristi.

Postoji mnogo savjeta za optimizaciju u SQL Serveru 2005. Informacije o njima možete pročitati u Books Online (na listi na kartici Indeks(Indeks) mora biti odabran UpitSavjeti [SQLServer](Savjeti za upite), Pridružite seHints(Pridruži se savjetima) ili TableSavjeti [SQLServer](Tabelarni savjeti)). Najčešće korišteni savjeti su:

q NOLOCK, ROWLOCK, PAGLOCK, TABLOCK, HOLDLOCK, READCOMMITTEDLOCK, UPDLOCK, XLOCK- ovi savjeti se koriste za upravljanje bravama (vidi odjeljak 11.5.7);

q FAST broj linija - biće odabran plan izvršenja upita u kojem će se što je brže moguće prikazati navedeni broj redova (prvi od početka skupa zapisa). Ako su korisniku potrebni upravo prvi zapisi (na primjer, najnovije narudžbe), onda se ovaj savjet može koristiti za njihovo učitavanje u prozor aplikacije što je brže moguće;

q FORCE ORDER- spajanje tabela prilikom izvršavanja upita vršiće se tačno onim redom kojim su te tabele navedene u upitu;

q MAXDOP(od maksimalnog stepena paralelizma - maksimalnog stepena paralelizacije zahteva) - koristeći ovaj nagoveštaj, naznačuje se maksimalni broj procesora koji se mogu koristiti za izvršavanje zahteva. Obično se ovaj savjet koristi u dvije situacije:

· kada zbog prebacivanja između procesora ( kontekstuprebacivanje) brzina izvršenja upita je znatno smanjena. Ovo ponašanje je bilo tipično za SQL Server 2000 na višeprocesorskim sistemima;

· kada želite da neki težak zahtjev ima minimalan utjecaj na trenutno korisničko iskustvo;

q OPTIMIZE ZA- ovaj savjet vam omogućava da navedete da je zahtjev optimiziran za određenu vrijednost parametra koji mu je proslijeđen (na primjer, za vrijednost filtera za GDJE);

q KORISTI PLAN- ovo je najmoćnija prilika. Koristeći takav savjet, možete eksplicitno definirati plan izvršenja upita prosljeđivanjem plana kao vrijednosti niza u XML formatu. Nagovještaj KORISTI PLAN pojavio se samo u SQL Serveru 2005 (u prethodnim verzijama bilo je moguće eksplicitno definirati planove izvršavanja upita, ali to je učinjeno drugim sredstvima). Plan u XML formatu se može napisati ručno, ili se može generirati automatski (na primjer, desnim klikom na grafički ekran sa planom izvršenja prikazanim na slici 11.15 i odabirom naredbe u kontekstnom meniju SačuvajIzvršenjePlanAs(Sačuvaj plan izvršenja kao)).

SQL Server 2005 uvodi važnu novu funkciju koja vam omogućava da ručno promijenite plan izvršenja upita bez potrebe da mijenjate tekst upita. Često se dešava da se kod zahtjeva ne može promijeniti: on je ugrađen u kod kompajlirane aplikacije. Za borbu protiv ovog problema, SQL Server 2005 uveo je uskladištenu proceduru sp_create_plan_guide. Omogućava vam da kreirate tzv Vodiči plana izvršenja (planvodiči), koji će se automatski primijeniti na podudarne zahtjeve.

Ako analizirate upite koje aplikacija šalje u bazu podataka, ima smisla prvo obratiti pažnju na sljedeće točke:

q koliko se često operacija pojavljuje u planovima izvršenja upita TableSkeniraj(Cjelokupno skeniranje tablice). Može se ispostaviti da će pristup tabeli pomoću indeksa biti efikasniji;

q da li se kursori koriste u kodu. Kursori su vrlo jednostavni u smislu sintakse programa, ali izuzetno neefikasni u smislu performansi. Vrlo često možete izbjeći korištenje kursora korištenjem drugih sintaktičkih konstrukcija i dobiti veliki dobitak u brzini;

q da li kod koristi privremene tablice ili tip podataka Table. Kreiranje privremenih tabela i rad s njima zahtijeva mnogo resursa, tako da biste ih trebali izbjegavati ako je moguće;

q Osim kreiranja privremenih tabela, promjena njihove strukture također zahtijeva značajnu potrošnju sistemskih resursa. Stoga bi naredbe za promjenu strukture privremenih tabela trebale odmah privući vašu pažnju. Obično je moguće odmah kreirati privremenu tabelu sa svim potrebnim kolonama;

q ponekad upiti vraćaju više podataka nego što je aplikaciji stvarno potrebno (dodatni stupci ili redovi). Naravno, to ne poboljšava produktivnost;

q ako aplikacija šalje komande serveru EXECUTE, onda ima smisla razmisliti o njihovoj zamjeni pozivom pohranjene procedure sp_executesql. Ima prednosti u performansama u odnosu na redovnu komandu EXECUTE;

q Poboljšanja performansi se ponekad mogu postići eliminacijom potrebe za ponovnim kompajliranjem pohranjenih procedura i izgradnjom novih planova izvršenja upita. Morate obratiti pažnju na upotrebu parametara, pokušajte da ne miješate DML i DDL naredbe u kodu pohranjene procedure i pazite da parametri veze POSTAVI ANSI_DEFAULTS, POSTAVI ANSI_NULLS, POSTAVI ANSI_PADDING, POSTAVI ANSI_WARNINGS I SET CONCAT_NULL_YIELDS_NULL nisu se mijenjale između zahtjeva (svaka promjena takvih parametara poništava stare planove izvršenja). Tipično, problem može nastati kada su ovi parametri postavljeni na razini pojedinačnog zahtjeva ili u kodu pohranjene procedure.

Imajte na umu da je u svakom slučaju, kreiranje planova izvršenja upita ručno i korištenje savjeta posljednje sredstvo i treba ga izbjegavati ako je moguće.

plan izvršenja SQL upita, ili plan upita, je niz koraka ili DBMS instrukcija potrebnih za izvršavanje SQL upita. U svakom koraku, operacija koja je pokrenula ovaj korak izvršenja SQL upita dohvaća redove podataka koji mogu formirati konačni rezultat ili se koristiti za dalju obradu. Instrukcije plana izvršenja SQL upita predstavljene su kao niz operacija koje IZVRŠE DBMS FOR SQL naredbe SELECT, INSERT, izbrisati i ažurirati. Sadržaj plana upita je obično predstavljen u strukturi stabla i uključuje sljedeće informacije:

• redosled povezivanja izvora podataka (tabele, prikazi, itd.);
• način pristupa za svaki izvor podataka;
• metode za povezivanje izvora podataka;
• operacije ograničavanja selekcije podataka, sortiranja i agregiranja;
• trošak i težina svake operacije;
• moguća upotreba particioniranja i paralelizma. Informacije koje pruža plan izvršavanja SQL upita omogućavaju programeru da vidi koje pristupe i metode optimizator bira za izvođenje SQL operacija.

Tumačenje plana izvršavanja SQL upita

Vizualizacija plana izvršenja SQL upita ovisi o alatima i razvojnim alatima, koji mogu biti dio DBMS-a čiji je upit od interesa za analizu, ili biti zasebni komercijalni ili slobodno distribuirani softverski proizvodi koji nisu direktno povezani sa određenim DBMS-om. proizvođač. Upotreba jednog ili drugog alata za vizualizaciju plana upita obično ne utiče značajno na percepciju onoga što predstavljeni plan upita opisuje. Odlučujući faktor u procesu analize kojim će putem optimizator krenuti prilikom izvršavanja određenog upita je sposobnost da se ispravno interpretiraju informacije koje su predstavljene u planu upita.

Kao što je već spomenuto, plan SQL upita ima strukturu stabla koja opisuje ne samo redoslijed izvršavanja SQL operacija, već i odnose između ovih operacija. Svaki čvor u stablu plana upita je operacija, kao što je sortiranje ili metoda pristupa tablici. Između čvorova postoji odnos roditelj-dijete. Odnosi roditelja i djece regulirani su sljedećim pravilima:

• roditelj može imati jedno ili više djece;
• dijete ima samo jednog roditelja;
• operacija koja nema nadređenu operaciju je vrh stabla;
• Ovisno o metodi vizualizacije plana SQL upita, dijete se pozicionira s nekim uvlačenjem u odnosu na roditelja. Potomci jednog roditelja nalaze se na istoj udaljenosti od svog roditelja.

Pogledajmo bliže informacije koje pruža plan izvršavanja SQL upita. Navedeni primjeri izvedeni su u Oracle DBMS okruženju. Oracle SQL Developer je korišten kao alat za izvršavanje upita i vizualizaciju plana SQL upita. Fragment plana SQL upita prikazan je na Sl. 10.11.

Id I Operation

• 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK INFORMACIJE O PROIZVODU

ODABIR IZJAVA SORTIRAJ RED PO Ugniježđenim petljama Ugniježđene petlje PRISTUP TABELI PUNOM INDEKSU JEDINSTVENI PRISTUP TABELI ZA SCENIRANJE PREMA RIDU INDEKSA

Rice. 10.11. Fragment plana izvršavanja SQL upita u Oracle DBMS okruženju

Koristeći pravila relacije operacija plana upita, možemo definirati sljedeći njihov formalni opis.

Operacija 0 je korijen stabla plana upita. Korijen ima jedno dijete: operacija 1.

Operacija 1 - operacija ima jedno dijete: operacija 2.

Operacija 2 - operacija ima dvoje djece: operacija 3 i operacija 6.

Operacija 3 - operacija ima dvoje djece: operacija 4 i operacija 5.

Operacija 4 - operacija nema djece.

Operacija 5 - operacija nema djece.

Operacija 6 - operacija nema djece.

Interakcija roditelj-dijete između operacija plana upita prikazana je na Sl. 10.12.

Operacije koje se izvode u planu upita mogu se podijeliti u tri tipa: samostalne, operacije nevezanog spajanja i povezane operacije spajanja (slika 10.13).

Autonomno		Operacije nepovezane		Povezane operacije
operacije		udruženja		udruženja

Rice. 10.12.

Rice. 10.13.

autonomne operacije - To su operacije koje imaju najviše jednu podređenu operaciju.

Sljedeća pravila po kojima se izvode autonomne operacije mogu se formulirati na sljedeći način.

• 2. Svaka podređena operacija se izvršava samo jednom.
• 3. Svaka podređena operacija vraća svoj rezultat nadređenoj operaciji.

Na sl. Slika 10.14 prikazuje plan za sljedeći upit:

SELECT o.order_id ,o.order_status IZ naloga o ORDER BY o.order_status

Ovaj upit sadrži samo samostalne operacije.

Uzimajući u obzir pravila za praćenje autonomnih operacija, redoslijed njihovog izvođenja bit će sljedeći.

• 1. U skladu sa pravilom praćenja autonomnih operacija br. 1, prva će se izvršiti operacija sa ID = 2. Svi redovi tabele naloga se čitaju uzastopno.
• 2. Zatim se izvodi operacija sa ID = 1. Redovi vraćeni operacijom sa ID = 2 sortiraju se prema uslovima sortiranja ORDER BY.
• 3. Izvodi se operacija sa ID = 0. Dobijeni skup podataka se vraća.

Unbound Union Operations

Unbound Union Operations su operacije koje imaju više od jedne podređene operacije koje se nezavisno izvršavaju. Primjer: HASH JOIN, MERGE JOIN, INTERSEction, MINUS, UNION SVE.

Sljedeća pravila po kojima rade operacije nevezanog spajanja mogu se formulirati na sljedeći način.

• 1. Podređena operacija se izvodi prije nadređene operacije.
• 2. Podređene operacije se izvode sekvencijalno, počevši od najmanje vrijednosti ID-a operacije u rastućem redoslijedu ovih vrijednosti.
• 3. Prije nego što započne svaka naredna podređena operacija, trenutna operacija mora biti dovršena u potpunosti.
• 4. Svaka podređena operacija se izvršava samo jednom, bez obzira na druge podređene operacije.
• 5. Svaka podređena operacija vraća svoj rezultat nadređenoj operaciji.

Na sl. Slika 10.15 prikazuje plan za sljedeći upit:

SELECT o.order_id iz naloga o UNION ALL

SELECT oi.order_id iz order_items oi

Ovaj upit sadrži operaciju nevezanog spajanja UNION all. Preostale dvije operacije su autonomne.

Rice. 10.15. Nevezane operacije spajanja, plan upita

	1 ODABIR IZJAVA I

Uzimajući u obzir pravila za praćenje nepovezanih operacija spajanja, redoslijed njihovog izvršavanja bit će sljedeći.

• 1. U skladu sa pravilima 1 i 2 sljedećih nepovezanih operacija spajanja, prva će se izvršiti operacija s ID = 2. Svi redovi tabele naloga se čitaju sekvencijalno.
• 2. U skladu sa pravilom 5, operacija sa ID = 2 vraća redove nadređene operacije sa ID = 1 pročitanim u koraku 1.
• 3. Operacija sa ID = 3 će početi da se izvršava tek kada završi operacija sa ID = 2.
• 4. Nakon završetka operacije s ID = 2, počinje operacija s ID = 3. Svi redovi tabele order_items se čitaju sekvencijalno.
• 5. U skladu sa pravilom 5, operacija sa ID = 3 vraća redove nadređene operacije sa ID = 1 pročitanim u koraku 4.
• 6. Operacija sa ID = 1 generiše skup rezultata podataka na osnovu podataka primljenih od svih njenih podređenih operacija (sa ID = 2 i ID = 3).
• 7. Izvodi se operacija sa ID = 0. Dobijeni skup podataka se vraća.

Stoga se može primijetiti da nezavisna operacija spajanja izvršava svoje podređene operacije sekvencijalno.

Povezane operacije pridruživanja

Povezane operacije pridruživanja - To su operacije koje imaju više od jedne podređene operacije, pri čemu jedna od operacija kontrolira izvršenje ostalih. Primjer: ugniježđene petlje, ažuriranje.

Sljedeća pravila po kojima rade operacije lančanog spajanja mogu se formulirati na sljedeći način.

• 1. Podređena operacija se izvodi prije nadređene operacije.
• 2. Podređena operacija s najmanjim brojem operacije (ID) kontrolira izvršenje preostalih podređenih operacija.
• 3. Podređene operacije koje imaju zajedničku nadređenu operaciju se izvode počevši od najniže vrijednosti ID-a operacije u rastućem redoslijedu ovih vrijednosti. Preostale podređene operacije NE izvode se sekvencijalno.
• 4. Samo prva podređena operacija se izvršava jednom. Sve ostale podređene operacije se izvode nekoliko puta ili se ne izvršavaju uopće.

Na sl. Slika 10.16 prikazuje plan za sljedeći upit:

FROM narudžbe_stavke oi, narudžbe o

WHERE o.order_id= oi.order_id

I oi.product_id>100

I o.customer_id između 100 i 1000

Ovaj upit sadrži srodnu operaciju spajanja, Ugniježđene petlje.

Id I Operation

		ODABIR IZJAVA |

Rice. 10.16. Povezane operacije pridruživanja, plan upita

Uzimajući u obzir pravila za praćenje lančanih operacija spajanja, redoslijed njihovog izvršavanja će biti sljedeći.

• 1. Prema pravilima 1 i 2 sljedećih lančanih operacija spajanja, prvo se mora izvršiti operacija sa ID = 2. Međutim, operacije sa 1D = 2 i 1D = 3 su autonomne, a prema pravilu 1 sljedećih autonomnih operacija, Prvo će se izvršiti operacija sa ID = 2. ID = 3. Opseg indeksa ORDCUSTOMERIX se pregledava na osnovu uslova: o. ID korisnika između 100 i 1000.
• 2. Operacija sa ID=3 vraća roditeljskoj operaciji (sa Š=2) listu Rowldovih identifikatora reda dobijenih u koraku 1.
• 3. Operacija sa ID = 2 čita redove u tabeli narudžbi u kojima se Rowld vrednost poklapa sa listom Rowld vrednosti dobijenih u koraku 2.
• 4. Operacija sa ID = 2 vraća pročitane redove nadređene operacije (sa ID = 1).
• 5. Za svaki red koji vraća operacija sa ID = 2, izvršava se druga podređena operacija (sa ID = 4) operacije ugniježđene petlje. To jest, za svaki red koji vraća operacija s ID = 2, izvodi se potpuno sekvencijalno skeniranje tabele order_items kako bi se pronašlo podudaranje na atributu pridruživanja.
• 6. Korak 5 se ponavlja onoliko puta koliko je broj redova vraćen operacijom sa ID = 2.
• 7. Operacija s ID = 1 vraća rezultate nadređene operacije (sa ID = 0).
• 8. Izvodi se operacija sa ID = 0. Dobijeni skup podataka se vraća.

U zavisnosti od složenosti analiziranog upita, njegov plan izvršenja može imati prilično složenu strukturu, koja se na prvi pogled čini teškom za interpretaciju. Metodička implementacija gore opisanih pravila i dekompozicija operacija omogućit će vam da efikasno analizirate plan izvršenja SQL upita bilo koje složenosti. Pogledajmo primjer upita koji generiše listu kupaca, broj robe koju su kupili i njihov ukupni trošak:

SELECT s. cust_first_name customer_name,

COUNT(DISTINCT oi.product_id) kao product_qty,

SUM(oi.quantity* oi.unit_price) kao ukupni_trošak IZ oe.porudžbina o INNER JOIN kupaca c ON

o.customer_id=c.customer_id

INNER JOIN oe.order_items oi ON o.order_id= oi.order_id GROUP BY c. cust_first_name

Redoslijed operacija ovog plana upita prikazan je na Sl. 10.17.

ODABIR IZJAVA I

SORT GROUP PO YG

	PRISTUP TABELI PUNI
	INDEX RANGE SCAN
PRISTUP TABELI PREMA INDEKSOM REDUd
PRISTUP TABELI PUNI

Rice. 10.17. Plan upita, redoslijed operacija

Hajde da opišemo mogući pristup tumačenju plana izvršenja 80b upita predstavljenog na Sl. 10.17. Ovaj pristup uključuje dvije glavne faze: dekomponiranje operacija u blokove i određivanje redoslijeda operacija.

U prvoj fazi potrebno je operacije koje se izvode rastaviti u blokove. Da bismo to učinili, nalazimo sve sindikalne operacije, tj. operacije koje imaju više od jedne podređene operacije (na slici 10.17 to su operacije 2, 3 i 4) i razdvojite ove podređene operacije u blokove. Kao rezultat toga, koristeći primjer na sl. 10.17 dobijamo tri sindikalne operacije i sedam blokova operacija.

U drugoj fazi se određuje redoslijed izvođenja blokova operacija. Da biste to učinili, morate primijeniti pravila za sljedeće operacije opisane gore. Hajde da sprovedemo niz razmatranja u vezi izvođenja svake operacije u odnosu na njen identifikacioni broj (III).

Operacija Š = 0 je autonomna i roditelj je operacije sŠ = 1.

Operacija Yu = 1 je takođe autonomna; je roditelj operacije W = 2 i izvršava se prije operacije Y = 0.

Operacija GO = 2 je nepovezana operacija ujedinjenja i nadređena je operacija za operacije Yu = 3, Yu = 8. Operacija GO = 2 se izvodi prije operacije GO = 1.

Operacija GO = 3 je operacija povezane unije, to je nadređena operacija za operacije GO = 4, GO = 7. Operacija GO = 3 se izvodi prije operacije GO = 2.

Operacija GO = 4 je operacija povezane unije, to je nadređena operacija za operacije GO = 5, GO = 6. Operacija GO = 4 se izvodi prije operacije GO = 3.

Operacija GO = 5 je autonomna operacija, izvedena prije operacije GO = 4.

Operacija GO = 6 je autonomna operacija, izvedena prije operacije GO = 5.

Operacija GO = 7 je autonomna operacija, koja se izvodi nakon izvršenja bloka operacija “C”.

Operacija GO = 8 je autonomna operacija, koja se izvodi nakon bloka operacija “E”.

Na osnovu gornjeg rezonovanja i sljedećih pravila, formuliramo redoslijed izvršenih operacija:

• 1. Prvo se izvodi autonomna operacija GO = 5, pogledajte pravila za redoslijed pridruženih operacija spajanja. Čitava se čitava tabela uzastopno.
• 2. Rezultat operacije GO = 5 - pročitani redovi tabele - prenosi se na operaciju GO = 4.
• 3. Izvodi se operacija GO = 4: za svaki red koji vraća operacija GO = 5, izvodi se operacija GO = 6. To jest, raspon indeksa se skenira prema atributu pridruživanja. Dobivanje liste identifikatora reda Yaou1s1.
• 4. Rezultat operacije GO = 4 prenosi se na operaciju GO = 3. To jest, prenosi se lista identifikatora reda Kosh1s1.
• 5. Izvodi se operacija GO = 3: za svaku vrijednost 11ou1s1 vraćenu kao rezultat operacije bloka operacija “C”, izvodi se operacija GO = 7, tj. redovi tabele se čitaju sa date liste identifikatora redova ITMI, dobijenih nakon izvođenja operacije Š = 4.
• 6. Izvodi se autonomna operacija GO = 8 - sekvencijalno očitavanje cijele tabele.
• 7. Izvodi se nepovezana operacija spajanja GO = 2: spajanje se izvodi heširanjem rezultata operacijskih blokova “E” i “E”.
• 8. Rezultat operacije GO = 2 prenosi se u operaciju GO = 1.
• 9. Izvodi se nepovezana operacija spajanja GO = 1: podaci dobijeni kao rezultat operacije GO = 2 se agregiraju i sortiraju.
• 10. Izvodi se operacija GO = 0. Dobijeni skup podataka se vraća.

Sljedeća pravila formulirana za glavne tipove operacija primjenjiva su na većinu planova za izvršavanje BSGO upita. Međutim, postoje konstrukcije koje se koriste u BSGO upitima koje impliciraju kršenje redoslijeda operacija opisanih u sljedećim pravilima. Takve situacije mogu nastati kao rezultat korištenja, na primjer, potupita ili predikata protiv spajanja. U svakom slučaju, proces tumačenja plana izvršenja BSGO upita ne podrazumijeva samo upotrebu niza pravila koja će osigurati najprecizniju analizu onoga što će optimizator uraditi prilikom izvršavanja BSGO upita. Drugi BSGO zahtjev je uvijek individualan slučaj; a kako će se izvršiti u DBMS-u ovisi o mnogim faktorima, uključujući verziju DBMS-a, verziju i tip operativnog sistema na kojem je DBMS instanca raspoređena, korišćeni hardver, kvalifikacije autora 80b upita, itd.

1 msdevcon.ru #msdevcon

3 Olontsev Sergey SQL Server MCM, MVP Kaspersky Lab

4 Strukturirani jezik upita

5 Primjer upita za odabir pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber iz HumanResources.Employee kao emp inner join Person.Person as pers na pers.businessentityid = emp.businessentityid gdje je pers.firstname = N"John emp.hiredate >= " "

6 Logičko stablo upita Projekt pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber D A T A Filter Join pers.firstname = N"John" i emp.hiredate >= " " pers.businessentityid = emp.businessentityid kao Person.Person. pers Get Data Get Data HumanResources.Employee as emp

7 Plan upita Pokazuje kako se T-SQL upit izvršava na fizičkom nivou.

8 Nekoliko načina

9 DEMO Jednostavan plan Odabirom svih podataka iz tabele, kako doći do plana upita

11 Metode operatora Init() Metoda Init() uzrokuje da se fizički operator inicijalizira i pripremi sve potrebne strukture podataka. Fizički operater može primiti mnogo poziva za Init(), iako obično prima samo jedan. GetNext() Metoda GetNext() uzrokuje da fizički operator dobije prvi ili sljedeći red podataka. Fizički operater može primiti mnogo GetNext() poziva ili nijedan. Metoda GetNext() vraća jedan red podataka, a koliko puta je pozvana označava vrijednost ActualRows u izlazu naredbe Showplan. Close() Kada se pozove Close() metoda, fizički operater obavlja određeno čišćenje i zatvara. Fizički operater prima samo jedan poziv Close().

12 Interakcija između operatera Operator 1 Operator 2 Operator 3

13 Interakcija između operatera 1. Zahtjev Red Operator 1 Operator 2 Operator 3

14 Interakcija između operatera 1. Red zahtjeva 2. Red zahtjeva Operator 1 Operator 2 Operator 3

15 Interakcija između operatera 1. Red zahtjeva 2. Red zahtjeva Operator 1 Operator 2 Operator 3 3. Red za slanje

16 Interakcija između operatera 1. Red zahtjeva 2. Red zahtjeva Operator 1 Operator 2 Operator 3 4. Pošalji red 3. Pošalji red

17 Interakcija između operatera 1. Red zahtjeva 2. Red zahtjeva Operator 1 Operator 2 Operator 3 4. Pošalji red 3. Pošalji red

18 DEMO VRH operatora Ili zašto je bolje nazvati operatora iteratorom

19 Tabele ne postoje!

20 HoBT Stranica 1 Stranica 2 Stranica 3 Stranica 4 Red 1 Red 3 Red 5 Red 7 Red 2 Red 4 Red 6 Red 8

21 HoBT stranica Stranica Stranica Stranica Stranica Stranica Stranica

22 DEMO Operatori pristupa podacima Scan, Seek, Lookup

23 Ko ima samo jednu tabelu u bazi podataka?

24 Ugniježđene petlje, Hash Join i Merge Join

25 Operatori pridruživanja Ugniježđene petlje unutrašnje spajanje, lijevo vanjsko spajanje, lijevo polupridruživanje, lijevo anti polu spajanje Spajanje Pridruživanje unutrašnje spajanje, lijevo vanjsko spajanje, lijevo polupridruživanje, lijevo anti polu spajanje, desno vanjsko spajanje, desno polu spajanje, desno anti polu spajanje , union Hash Pridružite se svim vrstama logičkih operacija

26 DEMO Pridruživanje, sortiranje i prvi operator Ugniježđene petlje, spajanje spajanja, hash pridruživanje, sortiranje, prvi operator

27 Upozorenja

28 DEMO Greške i upozorenja u planovima upita

29 Znam da ne znam ništa. Sokrat

30 DEMO Mali primjer nečega nejasnog

31 Dijagnostikovanje planova upita -- TOP 10 upita koji troše najviše CPU-a i njihovi planovi biraju top(10) podniz(t.text, qs.statement_start_offset / 2, slučaj kada je qs.statement_end_offset = -1 zatim len(t.text) drugo (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, cast(qs.total_worker_time / as decimal(18, 2)) kao total_worker_time_ms, cast(qs.total_worker_time_count) kao decicum. / exem. (18, 2)) kao avg_worker_time_ms, cast(p.query_plan kao xml) kao query_plan iz sys.dm_exec_query_stats kao qs unakrsna primjena sys.dm_exec_sql_text(qs.sql_handle) kao t križna primjena naredbe sys.qs.dm_star_exec_plan_qs.dm_star_ offset , qs.statement_end_offset) kao p poredak prema qs.total_worker_time desc; idi

32 Tehnike za čitanje velikih planova upita Pokušajte ih razbiti na logičke blokove i analizirati ih postupno. U SSMS-u, kada je plan grafički prikazan, u donjem desnom uglu se pojavljuje dugme za lakšu navigaciju kroz plan upita. Možete koristiti XQuery\XPath.

33 DEMO Veliki plan upita

35 DEMO SQL Sentry Plan Explorer

36 Hajde da sumiramo Prvi operator Nivo optimizacije Vrijeme kompajliranja Veličina u kešu Parametri, vrijednosti kompajliranja Razlog za prijevremeni prekid Trošak iteratora Prvo pogledajte operatore s najvećom cijenom. Imajte na umu da su ovo samo procijenjene vrijednosti (čak i u stvarnim planovima izvršenja).

37 Hajde da sumiramo Bookmark\Key Lookup Ako ih je malo, onda najvjerovatnije nema problema. Ako ih ima puno, kreiranje indeksa pokrivanja pomoći će da ih se riješite. Upozorenja Morate provjeriti zašto se to događa i poduzeti mjere ako je potrebno.

38 Hajde da sumiramo veze između operatora (tokovi podataka) Što je veza deblja, više podataka prolazi između ovih operatora. Posebno je vrijedno obratiti pažnju ako se u nekoj fazi protok podataka naglo poveća. Redoslijed spajanja tabela Što su tokovi podataka manji, to ih je lakše spojiti. Stoga, prije svega, trebate spojiti one tablice čiji će rezultirajući protok podataka biti manji.

39 Sažetak skeniranja Skeniranja ne znače da postoji problem. Moguće je da na tabeli nema dovoljno indeksa za efikasniju pretragu. S druge strane, ako trebate odabrati cijeli ili veći dio tabele, skeniranje će biti efikasnije. Traženje ne znači da je sve u redu. Veliki broj pretraživanja na negrupisanim indeksima može biti problem. Sve što ne znate o planu može potencijalno predstavljati problem.

40 pitanja

41 Kontakti Olontsev Sergey Kaspersky Lab

42 2013 Microsoft Corporation. Sva prava zadržana. Microsoft, Windows, Windows Vista i drugi nazivi proizvoda su ili mogu biti registrovani zaštitni znaci i/ili zaštitni znaci u SAD-u. i/ili drugim zemljama. Ove informacije su samo u informativne svrhe i predstavljaju trenutni stav korporacije Microsoft na dan ove prezentacije. Budući da Microsoft mora odgovoriti na promjenjive tržišne uslove, to ne treba tumačiti kao obavezu od strane Microsofta, a Microsoft ne može garantovati tačnost bilo koje informacije date nakon datuma ove prezentacije. MICROSOFT NE DAJE NIKAKVE GARANCIJE, IZRIČITI, PODRAZUMEVANI ILI ZAKONSKI, U ODNOSU NA INFORMACIJE U OVOJ PREZENTACIJI.