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 * 1. / 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 unakrsno primjena naredbe sys.qs.dm_star_exec 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 iu 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.

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 Metodi 1 (ovo je zapravo ono što SQL Server Management Studio radi interno), ali sam je uključio 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 ne koristite SQL 2012 ili noviju verziju, toplo bih predložio jedan od drugih odgovora objavljenih ovdje.

dijeliti

Aleksandar Kuklin napisao je odličan članak „Planirajte keš memoriju i parametrizaciju upita. Dio 1. Analiza keša plana ". Preporučujem svima da ga pročitaju.

Evo malog odlomka iz njega:

Procesor upita, koji je odgovoran za izvršavanje SQL upita primljenih na SQL server i izdavanje njihovih rezultata klijentu, sastoji se od dvije glavne komponente:

1. Optimizator upita.
2. Izvršitelj zahtjeva (Relaciona mašina).

Budući da izraz SELECT ne specificira točne korake koje SQL server mora poduzeti da klijentu da podatke koje traži, sam SQL server mora procijeniti izraz kako bi odredio najefikasniji način za dohvat traženih podataka. Prvo, instrukciju obrađuje optimizator upita, gdje se slijedeći koraci izvode pomoću komponenti optimizatora:

1. Parser posmatra naredbu SELECT i rastavlja je na logičke jedinice kao što su ključne riječi, izrazi, operatori i identifikatori, te normalizira upit.
2. Iz parsera podaci idu na ulaz komponente Algebrizer, koja vrši semantičku analizu teksta. Algebrizer provjerava postojanje objekata baze podataka i njihovih polja navedenih u zahtjevu, ispravnu upotrebu operatora i izraza upita i izdvaja literale iz koda zahtjeva kako bi omogućio automatsku parametrizaciju.
   Na primjer, ovo je razlog zašto će upit koji ima polja u odjeljku SELECT koja nisu sadržana ni u agregatnim funkcijama niti u odjeljku GROUP BY proći provjeru Ctrl+F5 (parsing) u SQL Server Management Studio-u (SSMS), ali neće uspjeti sa greška pri pokušaju pokretanja preko F5 (semantička analiza neće proći).
3. Zatim Algebrizer gradi stablo raščlanjivanja upita koje opisuje logičke korake potrebne za transformaciju izvornih podataka u željeni rezultat. Za stablo upita izdvajaju se metapodaci objekata upita (tipovi podataka, statistika indeksa, itd.), izvode se implicitne konverzije tipova (ako je potrebno) i uklanjaju se redundantne operacije (na primjer, nepotrebno ili suvišno spajanje tablica).
4. Optimizator upita zatim analizira različite načine na koje se može pristupiti izvornim tabelama. I bira niz koraka koji, po mišljenju optimizatora, najbrže vraćaju rezultate i koriste najmanje resursa. Redoslijed ovih dobivenih koraka se bilježi u stablu upita i plan izvršenja upita se generira iz konačne, optimizirane verzije stabla.

Zatim, rezultirajući plan izvršenja upita pohranjuje se u keš plana. A izvršitelj upita, na osnovu slijeda instrukcija (koraka) navedenih u planu izvršenja, zahtijeva tražene podatke od podsistema za skladištenje, pretvara ih u format specificiran za rezultujući skup podataka i vraća ga klijentu.

Priča je stara koliko i vrijeme. dva stola:

• Gradovi – 100 jedinstvenih gradova.
• Ljudi – 10 miliona ljudi. Za neke ljude grad možda nije naveden.

Raspodjela ljudi po gradovima je ujednačena.
Dostupni su indeksi za polja Cites.Id, Cites.Name, People .CityId.

Morate odabrati prvih 100 ljudi zapisa, sortiranih prema Cites.

Zasučući rukave, veselo pišemo:

Odaberite top 100 p.Ime, c.Ime kao Grad iz Ljudi str
poredak po c.ime

U ovom slučaju dobićemo nešto poput:

Za... 6 sekundi. (MS SQL 2008 R2, i5/4Gb)

Ali kako to može biti! Odakle dolazi 6 sekundi?! Znamo da će u prvih 100 unosa biti samo Almaty! Na kraju krajeva, postoji 10 miliona zapisa, što znači da ih ima 100 hiljada po gradu. Čak i ako nije tako, možemo odabrati prvi grad na listi i provjeriti da li ima najmanje 100 stanovnika.

Zašto SQL server, imajući statistiku, ne uradi ovo:

Izaberite * iz Ljudi str
lijevo pridruži se Gradovi c na c.Id=p.CityId
gdje je p.CityId
u (odaberite top 1 id iz reda gradova po imenu)
naručiti c.

Ovaj upit vraća približno 100 hiljada zapisa za manje od jedne sekunde! Pobrinuli smo se da imamo 100 ploča koje smo tražili i vrlo, vrlo brzo ih poklonili.

Međutim, MSSQL radi sve po planu. I on ima plan, “čisti termonuklearni” (c).

Pitanje za strucnjake:
Kako trebate ispraviti SQL upit ili izvršiti neke radnje na serveru da biste dobili rezultat prvog upita 10 puta brže?

P.S.
CREATE TABLE. (


jedinstveni identifikator
ON
GO

CREATE TABLE. (
jedinstveni identifikator NOT NULL,
nvarchar(50) NIJE NULL,
ON
GO

P.P.S.
Odakle dolaze noge?
Zadatak je sasvim realan. Postoji tabela sa glavnim entitetom, od koje se po principu „zvezde“ granaju mnoge dimenzije. Korisnik treba da ga prikaže u mreži, omogućavajući sortiranje po poljima.
Počevši od određene veličine glavne tabele, sortiranje se svodi na odabir prozora sa identičnim (ekstremnim) vrijednostima (poput „Almaty“), ali u isto vrijeme sistem počinje užasno usporavati.
Želeo bih da imam JEDAN parametrizovan upit koji će efikasno raditi i sa malom veličinom tabele Ljudi i sa velikom.

P.P.P.S.
Zanimljivo, da je City NotNull i da se koristi InnerJoin, tada bi zahtjev bio izvršen trenutno.
Zanimljivo je da ČAK DA je polje City bilo NotNull ali je korišteno LeftJoin, zahtjev bi se usporio.

Ideja u komentarima: Prvo odaberite sve InnerJoin, a zatim Union by Null vrijednosti. Sutra cu provjeriti ovo i ostale lude ideje)

P.P.P.P.S Probao. Upalilo je!

UZ POMOĆ AS
izaberite top 100 p.Ime, c.Ime kao Grad iz Ljudi str
INNER pridruži se gradovima c na c.Id=p.CityId
poredak po c.Nazivu ASC
UNION
izaberite top 100 p. Ime, NULL kao Grad iz Ljudi str
GDJE JE p.CityId NULL
ODABERITE TOP 100 * IZ pomoći

Daje 150 milisekundi pod istim uslovima! Hvala ti

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 je veza s bazom podataka trenutno uhvaćena sesijom: ili je DBMS poziv u toku, ili je transakcija otvorena, ili se drži objekt „Upravitelj privremenih tablica“ 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?