Bok svima! Nedavno sam naišao na problem gdje je obrada dokumenta trajala dugo.

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

Dakle, uz pomoć mjerenje učinkovitosti Mislim da ga svi znaju koristiti, krivac je pronađen:

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

Prema mjerenju performansi, ova operacija je trajala 13 sekundi, ako izračunate prosjek, dobijete 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 daljnju analizu otvorite SQL poslužitelj Profiler i . Za analizu sam koristio klase događaja:

• Showplan Statistički profil
• Showplan XML Statistički profil
• RPC dovršen
• SQL: Batch Completed.

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

SPID je ID procesa poslužitelja baze podataka. U slučaju 1C, to je u suštini veza između 1C poslužitelja i DBMS-a, možete ga vidjeti u administracijskoj konzoli 1C poslužitelja u stupcu "Veza na DBMS".

Prikazuje se ako ovaj trenutak vezu s bazom podataka hvata sesija: ili se vrši poziv DBMS-a, ili je transakcija otvorena, ili se drži objekt "Temporary Table Manager", u kojem je stvorena barem jedna privremena tablica.

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

Važno je da objekt veze koji se drži, u našem slučaju upravitelj privremene tablice, bude definiran kao varijabla za obradu. Otvorimo obradu, pokrenemo proceduru i sve dok je otvorena, SPID će biti popravljen. Otvorite konzolu za administraciju poslužitelja 1C:

Dakle, SPID je primljen, njegovu vrijednost unosimo u filtar i dobivamo trag iz trenutne radne baze podataka za našu sesiju. Prilikom analize traga pronađena je operacija kojoj je trebalo 11 sekundi da se dovrši:

Ono što mi je također zapelo za oko je broj čitanja - 1872578 , ali nisam odmah tome pridao nikakvu važnost i počeo sam shvaćati što se ovdje radi i s 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, tablica je obrađena "AccRg1465" Ovo je tablica u registru samonosivog knjigovodstva. Tekstualni prikaz plana izvršenja upita:

Kao što možete vidjeti iz plana izvršenja SQL upita, ne događa se ništa loše, tablica “ AccRg1465", pretraživanje grupiranog indeksa koristi se posvuda.

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

Kao što je gore spomenuto, problem nije reproduciran u novoj kopiji baze podataka; kopija je preuzeta iz radne baze podataka nakon što se problem pojavio u njoj, pa je odlučeno analizirati njegovo ponašanje u SQL Server Profileru na istom dokumentu.
Evo rezultata:

Tekst upita u SQL-u:

EXEC sp_executesql N"SELECT TOP 1 0x01 FROM dbo._AccRg1465 T1 WHERE (T1._RecorderTRef = 0x0000022D AND T1._RecorderRRef = @P1) AND ((((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)", 0x8A2F00155DBF491211E87F56DD1A416E, "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , 0x9A95A0369F30F8DB11E46684B4F0A05F, "401 8-05-31 23:59:59"

Grafički prikaz plana upita:

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

Analizirajmo dalje: ako su statistike iste, ali su planovi upita različiti, to znači da optimizator ne pristupa statistici za izradu plana upita, već ima predmemorirani plan koji koristi. Brišemo proceduralnu predmemoriju 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

vratit će nam popis baza podataka i njihovih identifikatora.

Opet dobivamo trag:

Tekstualni prikaz plana upita:

Grafički prikaz plana upita:

Kao što vidite, optimizator je ponovno dobio plan upita, a stari predmemorirani nije korišten, vrijeme izvršenja se vratilo na normalu, kao i broj čitanja. Nije jasno što je uzrok tome, možda veliki broj razmjena ili zatvaranje prethodnih razdoblja, teško je reći. Konfigurirano je redovito održavanje baze podataka. Ovo je prvi put da sam naišao na prijevaru s predmemoriranim planom izvršenja upita.

Hvala na pozornosti!

Je li vam ovaj članak pomogao?

6 odgovora

Postoji nekoliko načina za dobivanje plana izvršenja, koji će ovisiti o vašim okolnostima. 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 Studio, možda će vam biti korisno dobiti plan putem SQL Server Profilera ili provjerom plana predmemorija.

Metoda 1 - Korištenje SQL Server Management Studio

SQL Server ima neke zgodne značajke koje olakšavaju prikupljanje plana izvršenja, samo provjerite je li označena stavka izbornika "Uključi stvarni plan izvršenja" (nalazi se u izborniku upita) i pokrenut će vaš kao i obično.

Ako pokušavate dobiti plan izvršenja za izjave 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 Studio ili desnom tipkom miša kliknite na plan i odaberite "Spremi plan izvršenja kao..." da biste spremili plan u XML datoteku.

Metoda 2 - Korištenje opcija SHOWPLAN

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

Prije izvršenja upita pokrenite jedan sljedećih operatora. Izjava mora biti jedina izjava u paketu, tj. Ne možete izvršiti drugu naredbu u isto vrijeme:

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS XML ON -- Opcija je preporučena za korištenje

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

Kada završite, možete onemogućiti ovu opciju sljedećom izjavom:

SET<> ISKLJUČENO

Usporedba formata plana izvršenja

Ako imate snažnu preferenciju, preporučujem korištenje opcije STATISTICS XML. Ova je opcija ekvivalentna opciji "Uključi stvarni plan izvršenja" u SQL Server Management Studio 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 tekstualni plan izvršenja s procjenom troškova bez izvršavanja upita
• SHOWPLAN_XML - Prikazuje procijenjeni plan izvršenja temeljen na XML-u s procjenama troškova bez izvršavanja upita. Ovo je ekvivalentno opciji "Prikaži primjer plana izvršenja..." u SQL Server Management Studio.
• STATISTIČKI PROFIL - Izvršava upit i prikazuje stvarni plan izvršenja na temelju teksta.
• STATISTIKA XML - Izvršava upit i prikazuje stvarni plan izvršenja na temelju XML-a. Ovo je ekvivalentno opciji "Uključi stvarni plan izvršenja" u SQL Server Management Studio.

Metoda 3 - Korištenje SQL Server Profilera

Ako ne možete izravno pokrenuti upit (ili vaš upit ne radi sporo kada ga izravno pokrenete - zapamtite da želimo da se plan upita loše izvodi), tada možete snimiti plan pomoću SQL Server Profilera. Ideja je pokrenuti vaš upit dok se izvodi praćenje koje bilježi jedan od događaja "Showplan".

Imajte na umu da ovisno o opterećenju možeš koristite ovu metodu u produkcijskom okruženju, ali očito trebate biti oprezni. Mehanizmi profiliranja SQL Servera osmišljeni su za smanjenje utjecaja na bazu podataka, ali to ne znači da neće biti utjecaja 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 jako korištena. Očito biste trebali provjeriti sa svojim DBA kako biste bili sigurni da su zadovoljni time što to radite svojoj dragocjenoj bazi podataka!

• Otvorite SQL Server Profiler i stvorite novo praćenje koje se povezuje sa željenom bazom podataka iz koje želite snimiti praćenje.
• Na kartici Odabir događaja označite potvrdni okvir Prikaži sve događaje, provjerite Performanse -> XML liniju Showplan i pokrenite praćenje.
• Dok je praćenje u tijeku, učinite sve što trebate da pokrenete spori upit.
• Pričekajte da se zahtjev završi i praćenje zaustavi.
• Da biste spremili praćenje, desnom tipkom miša kliknite xml plan u profilu SQL Servera i odaberite "Extract Event Data..." da biste spremili plan u XML datoteku.

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

Metoda 4 - Provjera predmemorije upita

Ako ne možete izravno pokrenuti svoj upit, a također ne možete uhvatiti profiler trag, ipak biste trebali moći dobiti procijenjeni plan provjerom plana predmemorije SQL upita.

Predmemoriju plana provjeravamo postavljanjem upita DMV-ovima SQL Servera. Ispod je osnovni upit koji će navesti sve predmemorirane planove upita (kao xml) zajedno s njihovim SQL tekstom. U većini baza podataka također ćete morati dodati dodatne uvjete filtra kako biste filtrirali rezultate do planova koji vas zanimaju.

ODABERI UseCounts, Cacheobjtype, Objtype, TEXT, query_plan IZ sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Pokrenite ovaj upit i kliknite na XML plan da biste otvorili plan u novom prozoru - desnom tipkom miša kliknite i odaberite "Spremi plan izvršenja kao..." da biste spremili plan u datoteku u XML formatu.

Bilješke:

Budući da postoji toliko mnogo čimbenika (od sheme tablice i indeksa do pohranjenih podataka i statistike tablice), morate Stalno pokušajte dobiti plan izvršenja iz baze podataka koja vas zanima (obično one koja ima problema s izvedbom).

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 stvarno 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 mnogo je korisniji jer sadrži dodatne podatke i statistiku o tome što se zapravo dogodilo kada je upit izvršen. Ovo je važno prilikom dijagnosticiranja problema kada su evaluacije SQL poslužitelja onemogućene (na primjer, kada su statistike zastarjele).

Kako protumačiti plan izvršenja upita?

Ovo je tema dostojna besplatne knjige.

Osim opsežnog odgovora koji je već ponekad objavljen, korisno je imati mogućnost pristupiti planu izvršenja programski za izvlačenje informacija. Uzorak koda za ovo nalazi se u nastavku.

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 dobivanje i dubinsku analizu planova izvršenja upita je SQL Sentry Plan Explorer. Mnogo je praktičniji, lakši za korištenje i potpuniji za detaljnu analizu i vizualizaciju planova izvršenja od SSMS-a.

Evo primjera zaslona 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 koji vam omogućuju da dobijete različite vrste prikaza plana izvršenja i korisne dodatne informacije.

Osim toga, nisam primijetio nikakva ograničenja u njegovoj besplatnoj verziji koja vas sprječavaju u svakodnevnom korištenju 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, također možete koristiti besplatni preglednik plana izvršenja i alat za optimizaciju upita ApexSQL Plan (na koji sam nedavno naišao).

ApexSQL plan možete instalirati i integrirati u SQL Server Management Studio, tako da se planovi izvršenja mogu izravno vidjeti iz SSMS-a.

Pregledajte predviđene planove izvršenja u ApexSQL planu

• Pritisnite gumb Novi zahtjev u SSMS i zalijepite tekst upita u tekstni okvir upita. Kliknite desnom tipkom miša i odaberite "Prikaži primjer plana izvršenja" iz kontekstnog izbornika.

1. Dijagram plana izvršenja prikazat će karticu Planiranje izvršenja u odjeljku s rezultatima. Zatim desnom tipkom miša kliknite plan izvršenja i odaberite opciju "Otvori u ApexSQL planu" iz kontekstnog izbornika.

1. Procijenjeni plan izvršenja otvorit će se u ApexSQL planu i može se analizirati za optimizaciju upita.

Pregled stvarnih planova izvršenja u ApexSQL planu

Da biste vidjeli stvarni plan izvršenja upita, idite na drugi korak spomenut ranije, ali sada, kada se procijenjeni plan pojavi, kliknite gumb "Stvarno" na glavnoj traci vrpce u ApexSQL planu.

Nakon klika na gumb Stvarno, stvarni plan izvršenja bit će prikazan s detaljnim pregledom parametara troškova zajedno s ostalim podacima plana izvršenja.

Više informacija o pregledu izvedbenih planova možete pronaći na ovoj poveznici.

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

/* Generirano putem predloška "Praćenje detalja upita". */ KREIRAJTE SESIJU DOGAĐAJA NA POSLUŽITELU DODAJTE DOGAĐAJ sqlserver.query_post_execution_showplan(ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_ 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_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserv er.t sql_frame,sqlserver.tsql_stack ) WHERE (.(.,(4)) AND .(.,(0)))), DODAJ 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) WHERE (.(.,(4)) AND .(.,(0))), DODAJ DOGAĐAJ 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.session_id,sqlserver.sql_text,sqlserver.tsql_ okvir,sqlserver.tsql_stack) WHERE (. ( .,(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,sqlserver.plan_handle,sqlserver. query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)))), DODAJ DOGAĐAJ sqlserver.sql_batch_completed( 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_ st ack) WHERE (.(.,( 4 )) I .(.,(0)))), DODAJ DOGAĐAJ sqlserver.sql_statement_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.ses sion_id , sqlserver.sql_text, sqlserver.tsql_frame, sqlserver.tsql_stack) gdje je (. (., (., (4)) i. (., (0))) dodajte ciljani paket0.ring_buffer s (max_Memory = 4096 KB, Event ATENCY =30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) KRENI

Nakon što je sesija kreirana (u SSMS-u), idite na Preglednik objekata i idite na Upravljanje | Prošireni događaji | Sjednice. Desnom tipkom miša kliknite sesiju "GetExecutionPlan" i pokrenite je. Kliknite ga desnom tipkom miša i odaberite "Gledaj podatke uživo".

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

KORISTITE AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY Naziv proizvoda DESC; IĆI

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

UREDI: XEvent kôd i snimka zaslona generirani su iz SQL/SSMS 2012 s SP2. Ako koristite SQL 2008/R2, možete postaviti skriptu za njegovo pokretanje. Ali ova verzija nema GUI, tako da ćete morati izdvojiti showplan XML datoteku, spremiti je kao *.sqlplan datoteku i otvoriti je u SSMS-u. To je glomazno. XEvents nije postojao u SQL 2005 ili ranije. Dakle, ako ne koristite SQL 2012 ili noviji, toplo bih preporučio jedan od drugih odgovora objavljenih ovdje.

udio

Optimizacija upita u SQL Serveru 2005, statistika baze podataka SQL Servera 2005, STRATIRAJ STATISTIKU, AŽURIRAJ STATISTIKU, POSTAVITE NOCOUNT ON, planovi izvršenja upita, broj logičkih čitanja, savjeti optimizatora, MAXDOP, OPTIMIZE FOR, planovi izvršenja uputa (vodiči za plan), sp_create_plan_guide

Ako su sve druge metode optimizacije performansi već iscrpljene, tada programeri i administratori SQL Servera imaju na raspolaganju još jednu posljednju rezervu - optimizaciju izvršavanja pojedinačnih upita. Na primjer, ako vaš zadatak apsolutno zahtijeva ubrzanje izrade jednog određenog izvješća, možete analizirati upit koji se koristi za izradu ovog izvješća 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 generira planove izvršenja upita, izaziva mnoge poštene kritike u SQL Serveru 2000 i SQL Serveru 2005. Query Optimizer često odabire ne najoptimalnije planove izvršenja upita iu nekim situacijama gubi na slične module iz Oraclea i Informixa. S druge strane, ručna optimizacija upita iznimno je naporan proces. Možete potrošiti puno vremena na takvu optimizaciju i na kraju saznati da ništa nije optimizirano: plan koji je inicijalno 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šenja upita koji ste ručno izradili nakon nekog vremena (nakon dodavanja novih informacija u bazu podataka) pokaže neoptimalnim i da će smanjiti performanse prilikom izvršavanja upita.

Također imajte na umu da Query Optimizer zahtijeva točne statističke podatke za odabir najboljih planova upita. Budući da, prema iskustvu autora, ne znaju svi administratori što je to, reći ćemo vam više o statistici.

Statistika- ovo su posebne servisne informacije o rasporedu podataka u stupcima tablice. Zamislimo, na primjer, da se izvršava upit koji bi trebao vratiti sve Ivanove koji žive u gradu St. Pretpostavimo da 90% zapisa u ovoj tablici ima istu vrijednost u stupcu Grad - "Sankt Peterburg". Naravno, sa stajališta izvršavanja upita, najprije je isplativije odabrati sve Ivanove u tablici (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 pokreće izvršavanje takvih upita, a zatim pohranjuje informacije o distribuciji podataka (koje se nazivaju statistike) u servisne tablice baze podataka.

Za baze podataka SQL Server 2005, zadane postavke su AUTOMATSKO_KREIRANJE_STATISTIKE I AUTOMATSKO_AŽURIRANJE_STATISTIKE. U tom će se slučaju statistika za stupce baze podataka automatski izraditi i ažurirati. Za najveće i najvažnije baze podataka može se dogoditi da operacije stvaranja 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 izvode se ručno noću. Naredbe koje se za to koriste su KREIRAJTE STATISTIKU I AŽURIRAJ STATISTIKU.

Sada razgovarajmo o optimizaciji upita.

Prvo što treba učiniti je pronaći one upite koji su primarno podložni optimizaciji. Najlakši način da to učinite je uz pomoć profilera, postavljanjem filtra za trajanje zahtjeva (filtar Trajanje u prozoru Uredifiltar(Uredi filter), koji se otvara pomoću gumba StupacFilteri na kartici DogađajiIzbor prozor svojstava sesije praćenja). Na primjer, kandidati za optimizaciju mogu uključivati ​​upite čije je vrijeme izvršenja bilo dulje od 5 sekundi. Također možete koristiti informacije o upitu koje pruža Savjetnik za podešavanje baze podataka.

Zatim morate provjeriti je li parametar postavljen za vaše veze, pohranjene procedure i funkcije NEMA. Možete ga instalirati pomoću naredbe POSTAVITE NO COUNT ON. Prilikom postavljanja ovog parametra, prvo, onemogućen je povratak s poslužitelja i prikaz informacije o broju redaka u rezultatima upita (tj. "N redaka(ova) zahvaćeno" na kartici Poruke(C poruke) prozori za rad s kodom prilikom izvršavanja zahtjeva u Management Studiju). Drugo, onemogućen je prijenos posebne poruke poslužitelja DONE_IN_PROC, koji se prema zadanim postavkama vraća za svaki korak pohranjene procedure. Kada pozivate većinu pohranjenih procedura, trebate samo rezultat njihovog izvršenja, a nitko ne mari za broj redaka obrađenih za svaku fazu. Stoga, postavljanje parametra NEMA jer pohranjene procedure mogu ozbiljno poboljšati njihovu izvedbu. Brzina izvršavanja uobičajenih upita također se povećava, ali u manjoj mjeri (do 10%).

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

Najlakši način za pregled plana izvršenja upita je iz SQL Server Management Studio. Kako biste dobili informacije o očekivanom planu izvršenja upita, možete koristiti izbornik Upit(Upit) odaberite naredbu PrikazProcijenjenoIzvrš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čitiStvarnoIzvršenjePlan(Uključite pravi plan izvršenja). U ovom slučaju, nakon izvršenja upita, druga kartica će se pojaviti u prozoru rezultata u SQL Server Management Studio IzvršenjePlan(Plan izvršenja), koji će prikazati stvarni plan izvršenja upita. Kada mišem prijeđete preko bilo koje faze, možete dobiti dodatne informacije o njoj (Sl. 11.15).

Riža. 11.15. Plan izvršenja upita u SQL Server Management Studio

U planu izvršenja upita, kao što možete vidjeti na slici, može postojati mnogo elemenata. Razumijevanje istih, 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 POSTAVITE STATISTIKU IO ON. Kao rezultat toga, nakon svakog izvršenja zahtjeva prikazat će se dodatne informacije. U njemu nas zanima vrijednost samo jednog parametra - Logičko čitanje. Ovaj parametar označava broj logičkih čitanja prilikom izvršavanja upita, tj. koliko je operacija čitanja moralo biti izvršeno prilikom izvršavanja određenog upita bez uzimanja u obzir utjecaja predmemorije (broj čitanja i iz predmemorije i sa diska). Ovo je najvažniji parametar. Broj fizičkih čitanja (samo čitanja s diska) nije baš reprezentativan podatak, jer ovisi o tome je li bilo prethodnih pristupa tim tablicama ili ne. Vremenska statistika također je promjenjiva i ovisi o drugim operacijama koje poslužitelj izvodi u to vrijeme. Ali broj logičkih čitanja je najobjektivniji pokazatelj, na koji najmanje utječu dodatni čimbenici;

q zatim pokušajte promijeniti plan izvršenja upita i saznajte ukupan broj logičkih čitanja za svaki plan. Obično se plan izvršenja upita mijenja korištenjem savjeta optimizatora. Oni izričito govore optimizatoru koji plan izvršenja treba koristiti.

Postoji mnogo savjeta za optimizaciju u SQL Serveru 2005. Informacije o njima možete pročitati u Books Online (na popisu na kartici Indeks(Indeks) mora biti odabrano UpitSavjeti [SQLposlužitelj ](Savjeti za upit), PridružitiSavjeti(Spojite savjete) ili StolSavjeti [SQLposlužitelj ](Savjeti u tablici)). Najčešće korišteni savjeti su:

q NOLOCK, ROWLOCK, PAGLOCK, TABLOK, ZADRŽI, READCOMMITTEDLOCK, UPDLOCK, XLOCK- ovi se savjeti koriste za upravljanje bravama (vidi odjeljak 11.5.7);

q BRZO broj linija - odabrat će se plan izvršenja upita u kojem će se što brže prikazati navedeni broj redaka (prvi od početka skupa zapisa). Ako korisnik treba točno prve zapise (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 NASILA NAREDBE- spajanje tablica prilikom izvršavanja upita izvršit će se točno redoslijedom kojim su te tablice navedene u upitu;

q MAXDOP(od Maximum Degree of Parallelism - maksimalni stupanj paralelizacije zahtjeva) - korištenjem ovog savjeta, naznačen je najveći broj procesora koji se mogu koristiti za izvršenje zahtjeva. Ovaj se savjet obično koristi u dvije situacije:

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

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

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

q PLAN KORIŠTENJA- 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. Savjet PLAN KORIŠTENJA pojavio samo u SQL Serveru 2005 (u prethodnim verzijama bilo je moguće eksplicitno definirati planove izvršenja upita, ali to je učinjeno drugim sredstvima). Plan u XML formatu može se napisati ručno ili se može generirati automatski (na primjer, desnim klikom na grafički ekran s planom izvršenja prikazanim na sl. 11.15 i odabirom naredbe u kontekstnom izborniku UštedjetiIzvršenjePlanKao(Spremi plan izvršenja kao)).

SQL Server 2005 predstavlja važnu novu značajku koja vam omogućuje ručnu promjenu plana izvršenja upita bez potrebe za diranjem u tekst upita. Često se događa da se kod zahtjeva ne može promijeniti: ugrađen je u kod kompajlirane aplikacije. Kako bi riješio ovaj problem, SQL Server 2005 uveo je pohranjenu proceduru sp_create_plan_guide. Omogućuje vam stvaranje tzv Vodiči za plan izvršenja (planvodiči), koji će se automatski primijeniti na podudarne upite.

Ako analizirate upite koje aplikacija šalje bazi podataka, ima smisla prvo obratiti pozornost na sljedeće točke:

q koliko se često operacija pojavljuje u planovima izvršenja upita StolSkenirati(Skeniranje cijele tablice). Moglo bi se ispostaviti da će pristup tablici korištenjem indeksa biti učinkovitiji;

q koriste li se kursori u kodu. Kursori su vrlo jednostavni u smislu programske sintakse, ali izuzetno neučinkoviti u smislu izvedbe. Vrlo često možete izbjeći korištenje pokazivača korištenjem drugih sintaktičkih konstrukata i dobiti veliki dobitak u brzini;

q koristi li kod privremene tablice ili tip podataka Stol. Stvaranje privremenih tablica i rad s njima zahtijeva mnogo resursa, stoga ih trebate izbjegavati ako je moguće;

q Osim izrade privremenih tablica, promjena njihove strukture zahtijeva i značajnu potrošnju resursa sustava. Stoga bi naredbe za promjenu strukture privremenih tablica trebale odmah privući vašu pozornost. Obično je moguće odmah stvoriti privremenu tablicu sa svim potrebnim stupcima;

q ponekad upiti vraćaju više podataka nego što aplikacija zapravo treba (dodatni stupci ili reci). Naravno, to ne poboljšava produktivnost;

q ako aplikacija šalje naredbe poslužitelju IZVRŠITI, onda ima smisla razmisliti o njihovoj zamjeni pozivom pohranjene procedure sp_executesql. Ima prednosti izvedbe u odnosu na običnu naredbu IZVRŠITI;

q Poboljšanja performansi ponekad se mogu postići uklanjanjem potrebe za ponovnim kompajliranjem pohranjenih procedura i izgradnjom novih planova izvršenja upita. Morate obratiti pozornost na korištenje parametara, pokušajte ne miješati DML i DDL naredbe u kodu pohranjene procedure i pobrinite se da parametri veze POSTAVITE ANSI_DEFAULTS, POSTAVITE ANSI_NULLS, POSTAVITE ANSI_PADDING, POSTAVITE ANSI_UPOZORENJA I SET CONCAT_NULL_YIELDS_NULL nisu se promijenili između zahtjeva (svaka promjena takvih parametara poništava stare planove izvršenja). Tipično, problem može nastati kada su ti parametri postavljeni na razini pojedinačnog zahtjeva ili u kodu pohranjene procedure.

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

plan izvršenja SQL upita, ili plan upita, je niz koraka ili DBMS uputa potrebnih za izvršenje SQL upita. U svakom koraku, operacija koja je pokrenula ovaj korak izvršenja SQL upita dohvaća retke podataka koji mogu oblikovati konačni rezultat ili se koristiti za daljnju obradu. Upute plana izvršenja SQL upita predstavljene su kao slijed operacija koje IZVODE DBMS FOR SQL naredbe SELECT, INSERT, izbrisati i Ažuriraj. Sadržaj plana upita obično je predstavljen u strukturi stabla i uključuje sljedeće informacije:

• redoslijed povezivanja izvora podataka (tablica, pogleda i sl.);
• način pristupa za svaki izvor podataka;
• metode povezivanja izvora podataka;
• operacije ograničavanja odabira, sortiranja i združivanja podataka;
• trošak i ozbiljnost svake operacije;
• moguće korištenje particioniranja i paralelizma. Informacije koje daje plan izvršenja SQL upita omogućuju programeru da vidi koje pristupe i metode optimizator odabire za izvođenje SQL operacija.

Tumačenje plana izvršenja SQL upita

Vizualizacija plana izvršenja SQL upita ovisi o alatima i razvojnim alatima, koji mogu biti ili dio DBMS-a čiji je upit od interesa za analizu, ili biti zasebni komercijalni ili slobodno distribuirani softverski proizvodi koji nisu izravno povezani s određenim DBMS-om. proizvođač. Korištenje jednog ili drugog alata za vizualizaciju plana upita obično ne utječe značajno na percepciju onoga što prikazani plan upita opisuje. Odlučujući čimbenik u procesu analize kojim putem će optimizator krenuti prilikom izvršavanja određenog upita je sposobnost ispravnog tumačenja informacija 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 tih 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 roditelj-dijete 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, podređeni je postavljen s određenim uvlačenjem u odnosu na roditelja. Potomci jednog roditelja nalaze se na istoj udaljenosti od svog roditelja.

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

I ID I Operacija

• 0RDER_STAVKI

PR0DUCT_INF0RMATI0N_PK INFORMACIJE O PROIZVODU

ODABIR IZJAVE SORTIRAJ REDOSLIJED PO UGNJIŽĐENIM PETLJAMA UGNJIŽĐENE PETLJE PRISTUP TABLICI PUNIM INDEKSOM JEDINSTVENO SKENIRANJE PRISTUP TABLICI PO INDEKSU ROWID

Riža. 10.11. Fragment plana izvršenja SQL upita u Oracle DBMS okruženju

Koristeći pravila odnosa operacija plana upita, možemo definirati njihov sljedeći 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 dva djeteta: operaciju 3 i operaciju 6.

Operacija 3 - operacija ima dva djeteta: operaciju 4 i operaciju 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 slici. 10.12.

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

Autonomna		Operacije nepovezane		Povezane operacije
operacije		udruge		udruge

Riža. 10.12.

Riža. 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 izvodi samo jednom.
• 3. Svaka podređena operacija vraća svoj rezultat roditeljskoj operaciji.

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

SELECT o.order_id,o.order_status FROM narudžbe 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 izvršenja bit će sljedeći.

• 1. U skladu s pravilom praćenja autonomnih operacija broj 1, prva će se izvršiti operacija s ID = 2. Svi redovi tablice naloga čitaju se sekvencijalno.
• 2. Zatim se izvodi operacija s ID = 1. Redovi vraćeni operacijom s ID = 2 sortiraju se prema uvjetima klauzule za sortiranje ORDER BY.
• 3. Izvodi se operacija s ID = 0. Rezultirajući skup podataka se vraća.

Unbound Union Operations

Unbound Union Operations su operacije koje imaju više od jedne neovisno izvršavajuće podređene operacije. Primjer: HASH JOIN, MERGE JOIN, INTERSECTION, MINUS, UNION ALL.

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

• 1. Operacija dijete se izvodi prije operacije roditelja.
• 2. Podređene operacije se izvršavaju sekvencijalno, počevši od najmanje vrijednosti ID-a operacije uzlaznim redoslijedom ovih vrijednosti.
• 3. Prije početka svake sljedeće podređene operacije, trenutna operacija mora biti u potpunosti dovršena.
• 4. Svaka podređena operacija se izvodi samo jednom, bez obzira na ostale podređene operacije.
• 5. Svaka podređena operacija vraća svoj rezultat roditeljskoj 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 nevezanu operaciju spajanja UNIJA sve. Preostale dvije operacije su autonomne.

Riža. 10.15. Nevezane operacije spajanja, plan upita

	1 ODABERITE IZJAVU I

S obzirom na pravila za praćenje nepovezanih operacija spajanja, redoslijed njihovog izvođenja bit će sljedeći.

• 1. U skladu s pravilima 1 i 2 sljedećih nepovezanih operacija spajanja, prva će se izvršiti operacija s ID = 2. Svi redovi tablice naloga čitaju se uzastopno.
• 2. U skladu s pravilom 5, operacija s ID = 2 vraća retke nadređene operacije s ID = 1 pročitane u koraku 1.
• 3. Operacija s ID = 3 počet će se izvršavati tek kada operacija s ID = 2 završi.
• 4. Nakon završetka operacije s ID = 2, započinje operacija s ID = 3. Svi redovi tablice order_items se čitaju redom.
• 5. U skladu s pravilom 5, operacija s ID = 3 vraća retke nadređene operacije s ID = 1 pročitane u koraku 4.
• 6. Operacija s ID = 1 generira skup rezultata podataka na temelju podataka primljenih od svih svojih podređenih operacija (s ID = 2 i ID = 3).
• 7. Izvodi se operacija s ID = 0. Rezultirajući skup podataka se vraća.

Stoga se može primijetiti da neovisna 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 funkcioniraju operacije lančanog spajanja mogu se formulirati na sljedeći način.

• 1. Operacija dijete se izvodi prije operacije roditelja.
• 2. Podređena operacija s najnižim brojem operacije (ID) kontrolira izvođenje preostalih podređenih operacija.
• 3. Podređene operacije koje imaju zajedničku nadređenu operaciju izvode se počevši od najniže vrijednosti ID-a operacije uzlaznim redoslijedom ovih vrijednosti. Preostale podređene operacije NE izvode se sekvencijalno.
• 4. Samo se prva podređena operacija izvodi jednom. Sve druge podređene operacije izvode se nekoliko puta ili se uopće ne izvode.

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

FROM stavke_narudžbe oi, narudžbe oko

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 povezanu operaciju spajanja, NESTED LOOPS.

I ID I Operacija

		ODABIR IZJAVE |

Riža. 10.16. Povezane operacije spajanja, plan upita

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

• 1. Prema Pravilima 1 i 2 sljedećih operacija ulančanog spajanja, prvo se mora izvesti operacija s ID = 2. Međutim, operacije s 1D = 2 i 1D = 3 su autonomne, a prema Pravilu 1 sljedećih autonomnih operacija, prvo će se izvršiti operacija s ID = 2. ID = 3. Raspon indeksa ORDCUSTOMERIX pregledava se na temelju uvjeta: o. ID kupca između 100 i 1000.
• 2. Operacija s ID=3 vraća roditeljskoj operaciji (sa Š=2) popis identifikatora reda Rowld dobivenih u koraku 1.
• 3. Operacija s ID = 2 čita retke u tablici naloga u kojima Rowld vrijednost odgovara popisu Rowld vrijednosti dobivenih u koraku 2.
• 4. Operacija s ID = 2 vraća pročitane retke nadređene operacije (s ID = 1).
• 5. Za svaki redak koji vrati operacija s ID = 2, izvršava se druga podređena operacija (s ID = 4) operacije ugniježđene petlje. To jest, za svaki redak vraćen operacijom s ID = 2, izvodi se potpuno sekvencijalno skeniranje tablice order_items kako bi se pronašlo podudaranje na atributu pridruživanja.
• 6. Korak 5 se ponavlja onoliko puta koliko je redaka vratila operacija s ID = 2.
• 7. Operacija s ID = 1 vraća rezultate nadređene operacije (s ID = 0).
• 8. Izvodi se operacija s ID = 0. Rezultirajući skup podataka se vraća.

Ovisno o složenosti analiziranog upita, njegov plan izvršenja može imati prilično složenu strukturu, što se na prvi pogled čini teškim za tumačenje. Metodična implementacija gore opisanih pravila i dekompozicija operacija omogućit će vam učinkovitu analizu plana izvršenja SQL upita bilo koje složenosti. Pogledajmo primjer upita koji generira popis kupaca, broj robe koju su kupili i njihovu ukupnu cijenu:

Odaberite% s. custom_first_name customer_name,

COUNT(DISTINCT oi.product_id) kao product_qty,

SUM(oi.quantity* oi.unit_price) kao total_cost FROM oe.orders o INNER JOIN kupci c ON

o.customer_id=c.customer_id

INNER JOIN oe.order_items oi ON o.order_id= oi.order_id GROUP BY c. prilagođeno_ime

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

ODABERITE IZJAVU I

SORTIRAJ GRUPIRAJ PO YG

	PRISTUP STOLU PUN
	SKENIRANJE RASPONA INDEKSA
PRISTUP TABLICI PO INDEKSU ROWIDd
PRISTUP STOLU PUN

Riža. 10.17. Plan upita, redoslijed operacija

Opišimo mogući pristup tumačenju plana izvršenja 80b upita prikazanog na slici. 10.17. Ovaj pristup uključuje dvije glavne faze: rastavljanje operacija u blokove i određivanje redoslijeda operacija.

U prvoj fazi potrebno je rastaviti operacije koje se izvode u blokove. Da bismo to učinili, pronalazimo sve sindikalne operacije, tj. operacije koje imaju više od jedne podređene operacije (na sl. 10.17 to su operacije 2, 3 i 4), te razdvojite te podređene operacije u blokove. Kao rezultat toga, korištenjem primjera na Sl. 10.17, dobivamo tri sindikalne operacije i sedam blokova operacija.

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

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

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

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

Operacija GO = 3 je povezana operacija unije, to je roditeljska operacija za operacije GO = 4, GO = 7. Operacija GO = 3 izvodi se prije operacije GO = 2.

Operacija GO = 4 je povezana operacija unije, to je nadređena operacija za operacije GO = 5, GO = 6. Operacija GO = 4 izvodi se 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 temelju gornjeg razmišljanja i sljedećih pravila formuliramo slijed izvedenih operacija:

• 1. Prvo se izvodi autonomna operacija GO = 5, pogledajte pravila za slijed povezanih operacija spajanja. Čita se cijela tablica redom.
• 2. Rezultat operacije GO = 5 - očitani redovi tablice - prenosi se u operaciju GO = 4.
• 3. Izvodi se operacija GO = 4: za svaki red vraćen operacijom GO = 5, izvodi se operacija GO = 6. To jest, raspon indeksa se skenira prema atributu spajanja. Dobivanje popisa identifikatora reda Yaou1s1.
• 4. Rezultat operacije GO = 4 prenosi se u 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 tablice čitaju se iz zadane liste identifikatora redova ITMI, dobivenih nakon izvođenja operacije Š = 4.
• 6. Provodi se autonomna operacija GO=8 - sekvencijalno čitanje cijele tablice.
• 7. Izvodi se nepovezana operacija spajanja GO = 2: spajanje se izvodi raspršivanjem 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 dobiveni kao rezultat operacije GO = 2 se agregiraju i sortiraju.
• 10. Izvodi se operacija GO = 0. Vraća se dobiveni skup podataka.

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, podupita ili predikata protiv spajanja. U svakom slučaju, proces tumačenja plana izvršenja BSGO upita ne podrazumijeva samo korištenje određenog broja pravila koja će osigurati najtočniju analizu onoga što će optimizator učiniti prilikom izvršavanja BSGO upita. Drugi BSGO zahtjev uvijek je pojedinačni slučaj; a kako će se izvršiti u DBMS-u ovisi o mnogim čimbenicima, uključujući verziju DBMS-a, verziju i vrstu operativnog sustava na kojem je DBMS instanca postavljena, hardver koji se koristi, kvalifikacije autora 80b upita, itd.

1 msdevcon.ru #msdevcon

3 Olontsev Sergey SQL Server MCM, MVP Kaspersky Lab

4 Jezik strukturiranih upita

5 Primjer upita select pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber iz HumanResources.Employee as emp inner join Person.Person as pers on pers.businessentityid = emp.businessentityid gdje pers.firstname = N"John" i 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" and emp.hiredate >= " " pers.businessentityid = emp.businessentityid Person.Person as pers Get Data Get Data HumanResources.Employee as emp

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

8 Nekoliko načina

9 DEMO Jednostavan plan Odabir svih podataka iz tablice, kako dobiti plan 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 na Init(), iako obično prima samo jedan. GetNext() Metoda GetNext() uzrokuje da fizički operator dobije prvi ili sljedeći redak podataka. Fizički operater može primiti mnogo GetNext() poziva ili nijedan. Metoda GetNext() vraća jedan redak podataka, a koliko puta je pozvana naznačeno je vrijednošću ActualRows u izlazu naredbe Showplan. Close() Kada se pozove metoda Close(), fizički operater izvodi neko čišćenje i zatvara se. Fizički operater prima samo jedan poziv Close().

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

13 Interakcija između operatora 1. Red zahtjeva Operator 1 Operator 2 Operator 3

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

15 Interakcija između operatora 1. Redak zahtjeva 2. Redak zahtjeva Operator 1 Operator 2 Operator 3 3. Redak slanja

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

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

18 DEMO Operator TOP Ili zašto je bolje operator zvati iterator

19 Stolovi 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

22 DEMO Operatori za pristup podacima Scan, Seek, Lookup

23 Tko ima samo jednu tablicu u bazi podataka?

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

25 Operatori spajanja Ugniježđene petlje unutarnje spajanje, lijevo vanjsko spajanje, lijevo polu-spajanje, lijevo anti-polu spajanje Spajanje spajanje unutarnje spajanje, lijevo vanjsko spajanje, lijevo polu-spajanje, lijevo anti-polu spajanje, desno vanjsko spajanje, desno polu-spajanje, desno anti-polu spajanje , unija Hash Pridružite sve vrste logičkih operacija

26 DEMO Spajanje, sortiranje i prvi operator Ugniježđene petlje, spajanje spajanjem, hash spajanje, sortiranje, prvi operator

27 Upozorenja

28 DEMO Pogreške i upozorenja u planovima upita

29 Znam da ništa ne znam. Sokrate

30 DEMO Mali primjer nečeg nejasnog

31 Dijagnosticiranje planova upita -- TOP 10 upita koji troše najviše CPU-a i njihovi planovi odabiru top(10) substring(t.text, qs.statement_start_offset / 2, slučaj kada je qs.statement_end_offset = -1 zatim len(t.text) else (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, cast(qs.total_worker_time / as decimal(18, 2)) as total_worker_time_ms, cast(qs.total_worker_time * 1. / qs.execution_count / as decimal (18, 2)) kao avg_worker_time_ms, cast(p.query_plan kao xml) kao query_plan iz sys.dm_exec_query_stats kao qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as t cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs. statement_start_off postaviti , qs.statement_end_offset) kao p poredak prema qs.total_worker_time desc; ići

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

33 DEMO Veliki plan upita

35 DEMO SQL Sentry Plan Explorer

36 Rezimirajmo prvi operator Razina optimizacije Vrijeme kompajliranja Veličina u predmemoriji Parametri, kompilacijske vrijednosti Razlog za rano prekidanje Troškovi iteratora Prvo pogledajte operatore s najvećom cijenom. Imajte na umu da su to samo procijenjene vrijednosti (čak iu stvarnim planovima izvršenja).

37 Rezimirajmo Bookmark\Key Lookup Ako ih je malo, onda najvjerojatnije nema problema. Ako ih ima mnogo, stvaranje indeksa pokrivanja pomoći će ih se riješiti. Upozorenja Morate provjeriti zašto se to događa i poduzeti mjere ako je potrebno.

38 Rezimirajmo veze između operatora (protokovi podataka) Što je veza deblja, to više podataka prolazi između ovih operatora. Posebno vrijedi obratiti pozornost ako se u nekoj fazi protok podataka naglo poveća. Redoslijed spajanja tablica Š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 tablici nema dovoljno indeksa za učinkovitije pretraživanje. S druge strane, ako trebate odabrati cijelu tablicu ili veliki dio, skeniranje će biti učinkovitije. Pretraživanje ne znači da je sve u redu. Velik broj pretraživanja na neklasteriziranim indeksima može biti problem. Sve što ne znate o planu moglo bi predstavljati problem.

40 pitanja

41 Kontakti Olontsev Sergey Kaspersky Lab

42 2013 Microsoft Corporation. Sva prava pridržana. Microsoft, Windows, Windows Vista i drugi nazivi proizvoda jesu ili mogu biti registrirani zaštitni znakovi i/ili zaštitni znakovi u SAD-u. i/ili drugim zemljama. Ovdje navedene informacije služe samo u informativne svrhe i predstavljaju trenutno stajalište Microsoft Corporation na datum ove prezentacije. Budući da Microsoft mora odgovoriti na promjenjive tržišne uvjete, to se ne bi trebalo tumačiti kao obveza od strane Microsofta i Microsoft ne može jamčiti točnost bilo koje informacije dane nakon datuma ove prezentacije. MICROSOFT NE DAJE NIKAKVA JAMSTVA, IZRIČITA, IMPLICITNA ILI ZAKONSKA, U VEZI SA INFORMACIJAMA U OVOJ PREZENTACIJI.