Pozdravljeni vsi skupaj! Pred kratkim sem naletel na težavo, pri kateri je obdelava dokumenta trajala dolgo.

Vhodni podatki: konfiguracija “Vodenje proizvodnega podjetja, izdaja 1.3 (1.3.52.1)”, dokument “Vhodni nalog”. Pritožba: zadrževanje v delovni bazi traja 20-30 sekund, kar je zanimivo, v kopiji baze se isti dokument zadrži 2-4 sekunde. Preberite o preiskavah in razlogih za takšno vedenje spodaj.

Torej s pomočjo merjenje uspešnosti Mislim, da ga vsi vedo uporabljati, krivca so našli:

V tem primeru je bil na snemalnik zabeležen prazen niz zapisov, z drugimi besedami, premiki so bili izbrisani pred izvedbo. Omeniti velja, da je bil ta postopek poklican 26-krat, tj. za vsak register, v katerega bi naš dokument lahko pisal.

Po meritvah zmogljivosti je ta operacija trajala 13 sekund, če izračunate povprečje, dobite 0,5 sekunde na register, celo večnost!

Kot vsi vemo, snemanja ne moremo optimizirati, vendar je tukaj očitno nekaj narobe.
Za nadaljnjo analizo odprite SQL Server Profiler In . Za analizo sem uporabil razrede dogodkov:

• Statistični profil Showplan
• Profil statistike XML Showplan
• RPC končan
• SQL: Paket dokončan.

V nastavitvah sledenja obstaja filtrirajte po SPID:

SPID je ID procesa strežnika baze podatkov. V primeru 1C je v bistvu povezava med strežnikom 1C in DBMS, lahko si jo ogledate v skrbniški konzoli strežnikov 1C v stolpcu »Povezava z DBMS«.

Prikazano, če ta trenutek povezava z bazo podatkov je zajeta s sejo: ali se izvaja klic DBMS, ali je odprta transakcija, ali pa se zadrži objekt »Upravljalnik začasnih tabel«, v katerem je bila ustvarjena vsaj ena začasna tabela.

Napišimo obdelavo za hrambo SPID, vsebovala bo en postopek:

Pomembno je, da je objekt povezave, ki se hrani, v našem primeru začasni upravitelj tabel, definiran kot spremenljivka za obdelavo. Odpremo obdelavo, poženemo proceduro in dokler je odprta, bo SPID popravljen. Odprite skrbniško konzolo strežnika 1C:

Torej, SPID je bil prejet, njegovo vrednost vnesemo v filter in dobimo sled iz trenutne delujoče baze podatkov za našo sejo. Pri analizi sledi je bila ugotovljena operacija, ki je trajala 11 sekund:

V oči mi je padlo tudi število branj - 1872578 , vendar temu nisem takoj pripisal nobenega pomena in sem začel ugotavljati, kaj se tukaj počne in s katero mizo.

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"

Kot lahko vidite iz poizvedbe SQL, je tabela obdelana "AccRg1465" To je tabela v registru samonosnega računovodstva. Besedilna predstavitev načrta izvajanja poizvedbe:

Kot lahko vidite iz načrta izvajanja poizvedbe SQL, se ne zgodi nič slabega, tabela " AccRg1465", se iskanje po gručastem indeksu uporablja povsod.

Tudi v grafičnem načrtu nisem videl nič narobe, čeprav se mi je zdel preveč napihnjen - prišlo je do združitve in dveh ugnezdenih zank brez očitnega razloga. Od kod to število odčitkov in velikanski čas izvajanja?

Kot je bilo omenjeno zgoraj, težava ni bila reproducirana v novi kopiji baze podatkov; kopija je bila vzeta iz delovne baze podatkov, potem ko se je težava pojavila v njej, zato je bilo odločeno, da se analizira njeno vedenje v SQL Server Profilerju na istem dokumentu.
Tukaj so rezultati:

Besedilo poizvedbe v SQL:

EXEC sp_executesql N"SELECT TOP 1 0x01 FROM dbo._AccRg1465 T1 WHERE (T1._RecorderTRef = 0x0000022D IN T1._RecorderRRef = @P1) IN ((((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čni prikaz načrta poizvedbe:

Besedila zahtev so enaka, izvedbeni načrti so radikalno različni. Kaj bi lahko bilo narobe? Glede statistike v SQl sem se motil, vendar so enake med delovno in kopijo baze podatkov, statistika pa je shranjena v bazi podatkov za vsako tabelo:

Analizirajmo naprej: če so statistike enake, vendar so načrti poizvedb različni, to pomeni, da optimizator ne dostopa do statistike za izdelavo načrta poizvedb, ampak ima predpomnjen načrt, ki ga uporablja. Počistimo proceduralni predpomnilnik v naši bazi podatkov, za to uporabimo ukaz

DBCC FLUSHPROCINDB(< database_id >)

Kje< database_id >je identifikator baze podatkov. Če želite izvedeti ID baze podatkov, morate zagnati skript

izberite ime, database_id iz sys. baze podatkov

vrnil nam bo seznam baz podatkov in njihovih identifikatorjev.

Spet dobimo sled:

Besedilna predstavitev načrta poizvedbe:

Grafični prikaz načrta poizvedbe:

Kot lahko vidite, je optimizator znova pridobil načrt poizvedbe, stari predpomnjeni pa ni bil uporabljen, čas izvajanja se je vrnil v normalno stanje, prav tako število odčitkov. Ni jasno, kaj je povzročilo, morda veliko število izmenjav ali zaprtje prejšnjih obdobij, težko je reči. Konfigurirano je redno vzdrževanje baze podatkov. To je prvič, da sem naletel na prevaro s predpomnjenim načrtom izvajanja poizvedbe.

Hvala za vašo pozornost!

Vam je ta članek pomagal?

6 odgovorov

Obstaja več načinov za pridobitev izvedbenega načrta, ki bo odvisen od vaših okoliščin. Običajno lahko za pridobitev načrta uporabite SQL Server Management Studio, če pa iz nekega razloga ne morete zagnati svoje poizvedbe v SQL Server Management Studio, vam bo morda koristno pridobiti načrt prek SQL Server Profilerja ali tako, da preverite načrt predpomnilnik.

1. način - uporaba SQL Server Management Studio

SQL Server ima nekaj čednih funkcij, ki olajšajo zbiranje izvedbenega načrta, samo poskrbite, da je postavka menija »Vključi dejanski izvedbeni načrt« (najdete jo v meniju Poizvedba) označena in bo vaš deloval kot običajno.

Če poskušate pridobiti izvedbeni načrt za stavke v shranjeni proceduri, bi shranjeno proceduro izvedli takole:

Exec p_Primer 42

Ko je vaša poizvedba končana, se bo v podoknu z rezultati pojavil dodaten zavihek Izvedbeni načrt. Če ste opravili veliko odobritev, boste morda videli veliko načrtov, prikazanih na tem zavihku.

Tukaj lahko preverite izvedbeni načrt v SQL Server Management Studio ali z desno miškino tipko kliknete načrt in izberete »Shrani izvedbeni načrt kot ...«, da shranite načrt v datoteko XML.

2. način - Uporaba možnosti SHOWPLAN

Ta metoda je zelo podobna 1. metodi (to dejansko počne SQL Server Management Studio interno), vendar sem jo vključil zaradi popolnosti ali če nimate na voljo SQL Server Management Studio.

Preden izvedete poizvedbo, zaženite eno naslednje operaterje. Izjava mora biti edina izjava v paketu, tj. Hkrati ne morete izvesti drugega stavka:

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS XML ON -- je priporočena možnost za uporabo

To so parametri povezave, zato morate to zagnati samo enkrat za vsako povezavo. Odslej bodo vse objavljene izjave spremljale dodaten nabor rezultatov ki vsebuje vaš izvedbeni načrt v zahtevani obliki - samo zaženite svojo poizvedbo kot običajno, da vidite načrt.

Ko končate, lahko to možnost onemogočite z naslednjo izjavo:

NASTAVI<> IZKLOP

Primerjava formatov izvedbenih načrtov

Če imate veliko prednost, priporočam uporabo možnosti STATISTIKA XML. Ta možnost je enakovredna možnosti »Vključi dejanski izvedbeni načrt« v SQL Server Management Studio in zagotavlja največ informacij v najbolj uporabni obliki.

• SHOWPLAN_TEXT – prikaže osnovni besedilni predvideni izvedbeni načrt brez izvajanja poizvedbe
• SHOWPLAN_ALL – prikaže predviden besedilni izvedbeni načrt z oceno stroškov brez izvajanja poizvedbe
• SHOWPLAN_XML – prikaže predviden izvedbeni načrt na osnovi XML z ocenami stroškov brez izvedbe poizvedbe. To je enakovredno možnosti »Prikaži primer izvedbenega načrta ...« v SQL Server Management Studio.
• STATISTIČNI PROFIL - Izvede poizvedbo in prikaže dejanski izvedbeni načrt na podlagi besedila.
• STATISTIKA XML - Izvede poizvedbo in prikaže dejanski izvedbeni načrt, ki temelji na XML. To je enakovredno možnosti »Vključi dejanski izvedbeni načrt« v SQL Server Management Studio.

3. način - uporaba SQL Server Profilerja

Če poizvedbe ne morete zagnati neposredno (ali vaša poizvedba ne teče počasi, ko jo zaženete neposredno – ne pozabite, da želimo, da se načrt poizvedbe izvaja slabo), potem lahko načrt zajamete s programom SQL Server Profiler. Ideja je, da zaženete svojo poizvedbo, medtem ko se izvaja sled, ki zajame enega od dogodkov »Showplan«.

Upoštevajte, da glede na obremenitev ti lahko uporabite to metodo v proizvodnem okolju, vendar morate biti seveda previdni. Mehanizmi profiliranja strežnika SQL Server so zasnovani tako, da zmanjšajo vpliv na bazo podatkov, vendar to ne pomeni, da ne bo vpliva na zmogljivost. Morda boste imeli tudi težave s filtriranjem in določanjem pravilnega načrta v vašem sledenju, če je vaša baza podatkov zelo obremenjena. Očitno bi morali preveriti pri svojem DBA, da se prepričate, da so zadovoljni s tem, da to počnete v svoji dragoceni bazi podatkov!

• Odprite SQL Server Profiler in ustvarite novo sled, ki se poveže z želeno bazo podatkov, iz katere želite posneti sled.
• Na zavihku Izbira dogodkov potrdite potrditveno polje Prikaži vse dogodke, označite vrstico Performance -> Showplan XML in zaženite sledenje.
• Medtem ko se sledenje izvaja, naredite vse, kar morate storiti, da začnete izvajati počasno poizvedbo.
• Počakajte, da se zahteva zaključi in se sledenje ustavi.
• Če želite shraniti sled, z desno miškino tipko kliknite načrt xml v profilu strežnika SQL Server in izberite »Extract Event Data ...«, da shranite načrt v datoteko XML.

Načrt, ki ga prejmete, je enakovreden možnosti »Vključi dejanski izvedbeni načrt« v SQL Server Management Studio.

4. način – Preverjanje predpomnilnika poizvedb

Če svoje poizvedbe ne morete zagnati neposredno in prav tako ne morete zajeti sledi profilerja, bi vseeno morali dobiti ocenjeni načrt tako, da preverite načrt predpomnilnika poizvedbe SQL.

Predpomnilnik načrta preverimo s poizvedovanjem DMV strežnika SQL Server. Spodaj je osnovna poizvedba, ki bo navedla vse predpomnjene načrte poizvedb (kot xml) skupaj z njihovim besedilom SQL. V večini baz podatkov boste morali dodati tudi dodatne pogoje filtra, da filtrirate rezultate do načrtov, ki vas zanimajo.

IZBERI 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)

Zaženite to poizvedbo in kliknite načrt XML, da odprete načrt v novem oknu – kliknite z desno miškino tipko in izberite »Shrani izvedbeni načrt kot ...«, da shranite načrt v datoteko v formatu XML.

Opombe:

Ker obstaja toliko dejavnikov (od tabele in sheme indeksa do shranjenih podatkov in statistike tabele), morate Nenehno poskusite pridobiti izvedbeni načrt iz baze podatkov, ki vas zanima (običajno tiste, ki ima težave z zmogljivostjo).

Izvedbenega načrta za šifrirane shranjene procedure ne morete potrditi.

»dejanski« in »ocenjeni« izvedbeni načrti

Dejanski izvedbeni načrt je tisti, pri katerem SQL Server dejansko izvede poizvedbo, medtem ko predvideni izvedbeni načrt SQL Server deluje na tem, kar bi lahko naredil, ne da bi izvedel poizvedbo. Čeprav je logično enakovreden, je dejanski izvedbeni načrt veliko bolj uporaben, ker vsebuje dodatne podatke in statistiko o tem, kaj se je dejansko zgodilo, ko je bila poizvedba izvedena. To je pomembno pri diagnosticiranju težav, ko so ocene strežnika SQL onemogočene (na primer, ko je statistika zastarela).

Kako razlagati načrt izvajanja poizvedbe?

To je dovolj vredna tema za brezplačno knjigo.

Poleg izčrpnega odgovora, ki je včasih že objavljen, je koristno imeti možnost programskega dostopa do izvedbenega načrta za pridobivanje informacij. Vzorčna koda za to je spodaj.

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

Moje najljubše orodje za pridobivanje in poglobljeno analizo načrtov izvajanja poizvedb je SQL Sentry Plan Explorer. Je veliko bolj priročen, uporabniku prijazen in popoln za podrobno analizo in vizualizacijo izvedbenih načrtov kot SSMS.

Tukaj je primer zaslona, ​​da boste razumeli, katere funkcije ponuja orodje:

To je le eden od pogledov, ki so na voljo v orodju. Bodite pozorni na niz zavihkov na dnu okna aplikacije, ki vam omogočajo, da dobite različne vrste pogledov izvedbenih načrtov in uporabne dodatne informacije.

Poleg tega v njeni brezplačni različici nisem opazil nobenih omejitev, ki bi vam preprečile vsakodnevno uporabo ali bi vas prisilile, da sčasoma kupite različico Pro. Torej, če se raje držite brezplačne različice, nič ni prepovedano.

Poleg metod, opisanih v prejšnjih odgovorih, lahko uporabite tudi brezplačen pregledovalnik izvedbenih načrtov in orodje za optimizacijo poizvedb ApexSQL Plan (na katerega sem nedavno naletel).

Načrt ApexSQL lahko namestite in integrirate v SQL Server Management Studio, tako da si lahko načrte izvajanja ogledate neposredno iz SSMS.

Oglejte si predvidene izvedbene načrte v načrtu ApexSQL

• Kliknite gumb Nova zahteva v SSMS in prilepite besedilo poizvedbe v besedilno polje poizvedbe. Z desno miškino tipko kliknite in v kontekstnem meniju izberite "Prikaži vzorčni načrt izvedbe".

1. Diagram izvedbenega načrta bo prikazal zavihek Načrtovanje izvedbe v razdelku z rezultati. Nato z desno tipko miške kliknite načrt izvajanja in v kontekstnem meniju izberite možnost »Odpri v načrtu ApexSQL«.

1. Predvideni izvedbeni načrt bo odprt v načrtu ApexSQL in ga bo mogoče analizirati za optimizacijo poizvedb.

Ogled dejanskih izvedbenih načrtov v načrtu ApexSQL

Če si želite ogledati dejanski načrt izvajanja poizvedbe, pojdite na drugi korak, omenjen prej, zdaj pa, ko se prikaže predvideni načrt, kliknite gumb »Dejansko« na glavni tračni vrstici v načrtu ApexSQL.

Po kliku na gumb Dejansko bo prikazan dejanski izvedbeni načrt s podrobnim predogledom parametrov stroškov skupaj z drugimi podatki izvedbenega načrta.

Več informacij o ogledu izvedbenih načrtov najdete na tej povezavi.

Načrte poizvedb je mogoče pridobiti iz seje razširjenih dogodkov prek dogodka query_post_execution_showplan. Tukaj je primer seje XEvent:

/* Ustvarjeno prek predloge "Sledenje podrobnostim poizvedbe". */ USTVARI SEJO DOGODKA NA STREŽNIKU DODAJ DOGODEK 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)), / * Po želji odstranite katerega koli od naslednjih dogodkov (ali vključite dodatne dogodke). */ DODAJ DOGODEK 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)) IN .(.,(0)))), ADD EVENT 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)) IN .(.,(0))), DODAJ DOGODEK 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)) IN .(.,(0))), DODAJ DOGODEK 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)) IN .(.,(0)))), DODAJ DOGODEK 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 )) IN .(.,(0)))), DODAJ DOGODEK 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), kjer (. ATENCY =30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) POJDI

Ko je seja ustvarjena (v SSMS), pojdite v Brskalnik po predmetih in pojdite na Upravljanje | Razširjeni dogodki | Seje. Z desno miškino tipko kliknite sejo "GetExecutionPlan" in jo zaženite. Z desno miškino tipko kliknite in izberite »Ogled podatkov v živo«.

Nato odprite novo okno za poizvedbo in zaženite eno ali več poizvedb. Tukaj je ena za AdventureWorks:

UPORABA AdventureWorks; POJDI IZBERI 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 ProductName DESC; POJDI

Po minuti ali dveh boste na zavihku »GetExecutionPlan: Live Data« videli nekaj rezultatov. Izberite enega od dogodkov query_post_execution_showplan v mreži in nato kliknite zavihek Načrt poizvedbe pod mrežo. Videti bi moralo nekako takole:

UREDI: koda XEvent in posnetek zaslona sta bila ustvarjena iz SQL/SSMS 2012 s servisnim paketom SP2. Če uporabljate SQL 2008/R2, lahko nastavite skript za njegovo izvajanje. Toda ta različica nima grafičnega uporabniškega vmesnika, zato boste morali ekstrahirati datoteko XML showplan, jo shraniti kot datoteko *.sqlplan in jo odpreti v SSMS. To je okorno. XEvents ni obstajal v SQL 2005 ali starejših. Torej, če ne uporabljate SQL 2012 ali novejšega, bi toplo predlagal enega od drugih odgovorov, objavljenih tukaj.

deliti

Optimizacija poizvedb v SQL Server 2005, statistika baze podatkov SQL Server 2005, USTVARJANJE STATISTIKE, POSODOBITEV STATISTIKE, NASTAVITE NOCOUNT ON, načrti izvajanja poizvedb, število logičnih branj, namigi optimizatorja, MAXDOP, OPTIMIZE FOR, načrti izvajanja vadnic (vodniki po načrtih), sp_create_plan_guide

Če so vsi drugi načini optimizacije delovanja že izčrpani, imajo razvijalci in skrbniki SQL Serverja na voljo še zadnjo rezervo – optimizacijo izvajanja posameznih poizvedb. Na primer, če vaša naloga absolutno zahteva pospešitev ustvarjanja enega določenega poročila, lahko analizirate poizvedbo, ki je bila uporabljena za ustvarjanje tega poročila, in poskusite spremeniti njen načrt, če ni optimalen.

Mnogi strokovnjaki imajo dvoumen odnos do optimizacije poizvedb. Po eni strani delovanje programskega modula Query Optimizer, ki generira načrte izvajanja poizvedb, povzroča številne poštene kritike tako v SQL Server 2000 kot SQL Server 2005. Query Optimizer pogosto izbere ne najbolj optimalne načrte izvajanja poizvedb in v nekaterih situacijah izgubi podobnim modulom Oracle in Informix. Po drugi strani pa je ročna optimizacija poizvedb izjemno delovno intenziven proces. Za takšno optimizacijo lahko porabite veliko časa in na koncu ugotovite, da nič ni bilo optimizirano: načrt, ki ga je prvotno predlagal Query Optimizer, se je izkazal za najbolj optimalnega (to se zgodi v večini primerov). Poleg tega se lahko zgodi, da se bo načrt izvajanja poizvedbe, ki ste ga ročno ustvarili, čez nekaj časa (po dodajanju novih informacij v bazo) izkazal za neoptimalnega in bo zmanjšal zmogljivost pri izvajanju poizvedb.

Upoštevajte tudi, da orodje za optimiziranje poizvedb zahteva pravilne statistične podatke za izbiro najboljših načrtov poizvedb. Ker po izkušnjah avtorja vsi skrbniki ne vedo, kaj je to, vam bomo povedali več o statistiki.

Statistika- to so posebne storitvene informacije o porazdelitvi podatkov v stolpcih tabele. Predstavljajmo si na primer, da se izvaja poizvedba, ki bi morala vrniti vse Ivanove, ki živijo v mestu Sankt Peterburg. Predpostavimo, da ima 90 % zapisov v tej tabeli enako vrednost v stolpcu Mesto - "Saint Petersburg". Seveda je z vidika izvajanja poizvedbe najprej bolj donosno izbrati vse Ivanove v tabeli (očitno ne bodo 90%) in nato preveriti vrednost stolpca Mesto za vsak izbran zapis. Vendar, če želite izvedeti, kako so vrednosti v stolpcu porazdeljene, morate najprej zagnati poizvedbo. Zato SQL Server neodvisno sproži izvajanje takšnih poizvedb in nato shrani informacije o distribuciji podatkov (ki se imenujejo statistika) v tabelah storitve baze podatkov.

Za baze podatkov SQL Server 2005 so privzete nastavitve AUTO_CREATE_STATISTICS in AUTO_UPDATE_STATISTICS. V tem primeru bodo statistike za stolpce baze podatkov ustvarjene in posodobljene samodejno. Pri največjih in najpomembnejših bazah podatkov se lahko zgodi, da lahko operacije za ustvarjanje in posodabljanje statistik motijo ​​trenutno uporabniško izkušnjo. Zato so za takšne baze podatkov včasih ti parametri onemogočeni, operacije za ustvarjanje in posodabljanje statistik pa se izvajajo ročno ponoči. Za to se uporabljajo ukazi USTVARJANJE STATISTIKE in POSODOBITE STATISTIKO.

Zdaj pa se pogovorimo o optimizaciji poizvedb.

Najprej je treba najti tiste poizvedbe, ki so v prvi vrsti predmet optimizacije. To najlažje storite s pomočjo profilerja, tako da nastavite filter za čas trajanja zahteve (filter Trajanje v oknu UrediFilter(Uredi filter), ki ga lahko odprete z gumbom StolpecFiltri na zavihku DogodkiIzbira okno lastnosti seje sledenja). Na primer, kandidati za optimizacijo lahko vključujejo poizvedbe, katerih čas izvajanja je bil daljši od 5 sekund. Uporabite lahko tudi informacije o poizvedbi, ki jih zagotovi svetovalec za uravnavanje baze podatkov.

Nato morate preveriti, ali je parameter nastavljen za vaše povezave, shranjene procedure in funkcije NI ŠTEV. Namestite ga lahko z ukazom NASTAVI ŠTEV. Pri nastavitvi tega parametra sta najprej onemogočena vrnitev s strežnika in prikaz informacij o številu vrstic v rezultatih poizvedbe (tj. vrstica "Prizadeta je N vrstic" na zavihku Sporočila(C sporočila) okna za delo s kodo pri izvajanju zahteve v programu Management Studio). Drugič, onemogočen je prenos posebnega sporočila strežnika DONE_IN_PROC, ki je privzeto vrnjen za vsak korak shranjene procedure. Ko kličete večino shranjenih procedur, potrebujete samo rezultat njihove izvedbe in nikogar ne zanima število obdelanih vrstic za vsako stopnjo. Zato nastavitev parametra NI ŠTEV za shranjene procedure lahko resno izboljša njihovo delovanje. Poveča se tudi hitrost izvajanja običajnih poizvedb, vendar v manjši meri (do 10%).

Po tem lahko začnete delati z načrti izvajanja poizvedbe.

Načrt izvajanja poizvedbe si najlažje ogledate v programu SQL Server Management Studio. Če želite pridobiti informacije o pričakovanem načrtu izvedbe poizvedbe, lahko uporabite meni Poizvedba(Poizvedba) izberite ukaz ZaslonOcenjenoIzvedbaNačrtujte(Prikaži pričakovani izvedbeni načrt). Če želite izvedeti dejanski načrt za izvedbo poizvedbe, lahko pred izvedbo poizvedbe nastavite parameter v istem meniju VključiDejanskoIzvedbaNačrtujte(Vključite pravi izvedbeni načrt). V tem primeru se bo po izvedbi poizvedbe v oknu z rezultati v programu SQL Server Management Studio pojavil drug zavihek IzvedbaNačrtujte(Izvedbeni načrt), ki bo prikazal dejanski načrt izvajanja poizvedbe. Ko se z miško pomaknete nad katero koli stopnjo, lahko dobite dodatne informacije o njej (slika 11.15).

riž. 11.15. Izvedbeni načrt poizvedbe v SQL Server Management Studio

V načrtu izvajanja poizvedbe, kot lahko vidite na sliki, je lahko veliko elementov. Njihovo razumevanje in predlaganje drugačnega načrta izvedbe je precej težka naloga. Povedati je treba, da je vsak od možnih elementov optimalen v svoji situaciji. Zato običajno faze optimizacije poizvedbe izgledajo takole:

q Najprej v oknu Management Studio zaženite ukaz NASTAVITE STATISTIKE IO ON. Posledično se po vsaki izvedbi zahteve prikažejo dodatne informacije. V njem nas zanima vrednost samo enega parametra - Logično branje. Ta parameter pomeni število logičnih branj pri izvajanju poizvedb, tj. koliko bralnih operacij je bilo treba izvesti pri izvajanju določene poizvedbe brez upoštevanja vpliva predpomnilnika (število branj iz predpomnilnika in diska). To je najpomembnejši parameter. Število fizičnih branj (samo branje z diska) ni zelo reprezentativen podatek, saj je odvisno od tega, ali so bili prejšnji dostopi do teh tabel ali ne. Časovna statistika je prav tako spremenljiva in je odvisna od drugih operacij, ki jih strežnik izvaja v tem času. Toda število logičnih branj je najbolj objektiven pokazatelj, na katerega najmanj vplivajo dodatni dejavniki;

q nato poskusite spremeniti načrt izvajanja poizvedbe in ugotovite skupno število logičnih branj za vsak načrt. Običajno se načrt izvajanja poizvedbe spremeni z uporabo namigov optimizatorja. Optimizatorju izrecno povedo, kateri izvedbeni načrt naj uporabi.

V SQL Server 2005 je veliko namigov za optimizator. Informacije o njih lahko preberete v Books Online (na seznamu na zavihku Kazalo(Indeks) mora biti izbrano PoizvedbaNamigi [SQLstrežnik ](Namigi za poizvedbo), Pridruži seNamigi(Pridruži se namigom) oz TabelaNamigi [SQLstrežnik ](Namigi v tabeli)). Najpogosteje uporabljeni namigi so:

q NOLOCK, ROWLOCK, PAGLOCK, TABLOK, HOLDLOCK, READCOMMITTEDLOCK, UPDLOCK, XLOCK- ti namigi se uporabljajo za upravljanje ključavnic (glejte razdelek 11.5.7);

q HITRO število vrstic - izbran bo načrt izvajanja poizvedbe, v katerem bo čim hitreje prikazano podano število vrstic (prva od začetka nabora zapisov). Če uporabnik potrebuje točno prve zapise (na primer zadnja naročila), potem lahko s tem namigom le-te čim hitreje naloži v okno aplikacije;

q VSILA VRSTA- združevanje tabel pri izvajanju poizvedbe bo izvedeno natančno v vrstnem redu, v katerem so te tabele navedene v poizvedbi;

q MAXDOP(iz Maximum Degree of Parallelism - največja stopnja vzporednosti zahteve) - s tem namigom je navedeno največje število procesorjev, ki jih je mogoče uporabiti za izvedbo zahteve. Običajno se ta namig uporablja v dveh primerih:

· ko zaradi preklapljanja med procesorji ( kontekstupreklapljanje) se hitrost izvajanja poizvedbe močno zmanjša. To vedenje je bilo značilno za SQL Server 2000 v večprocesorskih sistemih;

· ko želite, da ima kakšna težka zahteva minimalen vpliv na trenutno uporabniško izkušnjo;

q OPTIMIZIRAJTE ZA- ta namig vam omogoča, da določite, da je zahteva optimizirana za določeno vrednost parametra, ki ji je bil posredovan (na primer za vrednost filtra za KJE);

q NAČRT UPORABE- to je najmočnejša priložnost. Z uporabo takega namiga lahko izrecno definirate načrt izvajanja poizvedbe tako, da posredujete načrt kot vrednost niza v formatu XML. Namig NAČRT UPORABE pojavil šele v SQL Server 2005 (v prejšnjih različicah je bilo mogoče eksplicitno definirati načrte izvajanja poizvedbe, vendar je bilo to storjeno z drugimi sredstvi). Načrt v formatu XML lahko napišete ročno ali pa ga ustvarite samodejno (na primer z desnim klikom na grafični zaslon z izvedbenim načrtom, prikazanim na sliki 11.15, in izbiro ukaza v kontekstnem meniju ShraniIzvedbaNačrtujteKot(Shrani izvedbeni načrt kot)).

SQL Server 2005 predstavlja pomembno novo funkcijo, ki vam omogoča ročno spreminjanje izvedbenega načrta poizvedbe, ne da bi morali posegati v besedilo poizvedbe. Pogosto se zgodi, da kode zahteve ni mogoče spremeniti: vgrajena je v kodo prevedene aplikacije. Za boj proti tej težavi je SQL Server 2005 predstavil shranjeno proceduro sp_create_plan_guide. Omogoča vam ustvarjanje t.i Vodniki po izvedbenem načrtu (načrtvodniki), ki bo samodejno uporabljen za ujemajoče se poizvedbe.

Če analizirate poizvedbe, ki jih v zbirko podatkov pošlje aplikacija, je smiselno najprej biti pozoren na naslednje točke:

q kako pogosto se operacija pojavi v načrtih izvajanja poizvedbe TabelaSkeniraj(Skeniranje celotne tabele). Lahko se izkaže, da bo dostop do tabele z uporabo indeksov učinkovitejši;

q ali so v kodi uporabljeni kazalci. Kazalci so zelo preprosti v smislu sintakse programa, vendar izjemno neučinkoviti v smislu zmogljivosti. Zelo pogosto se lahko izognete uporabi kazalcev z uporabo drugih sintaktičnih konstruktov in tako pridobite veliko hitrost;

q ali koda uporablja začasne tabele ali podatkovni tip Tabela. Ustvarjanje začasnih tabel in delo z njimi zahteva veliko sredstev, zato se jim raje izogibajte, če je le mogoče;

q Spreminjanje njihove strukture poleg ustvarjanja začasnih tabel zahteva tudi znatno porabo sistemskih virov. Zato bi morali ukazi za spremembo strukture začasnih tabel takoj pritegniti vašo pozornost. Običajno je mogoče takoj ustvariti začasno tabelo z vsemi potrebnimi stolpci;

q včasih poizvedbe vrnejo več podatkov, kot jih aplikacija dejansko potrebuje (dodatni stolpci ali vrstice). Seveda to ne izboljša produktivnosti;

q če aplikacija pošlje ukaze strežniku IZVEDI, potem je smiselno razmisliti o njihovi zamenjavi s klicem shranjene procedure sp_executesql. Ima prednosti v zmogljivosti pred navadnim ukazom IZVEDI;

q Izboljšave zmogljivosti je včasih mogoče doseči z odpravo potrebe po ponovnem prevajanju shranjenih procedur in izdelavi novih načrtov izvajanja poizvedbe. Pozorni morate biti na uporabo parametrov, poskušajte ne mešati ukazov DML in DDL v kodi shranjene procedure in se prepričajte, da parametri povezave NASTAVITE ANSI_DEFAULTS, NASTAVI ANSI_NULLS, NASTAVI ANSI_PADDING, NASTAVI ANSI_WARNINGS in NASTAVI CONCAT_NULL_YIELDS_NULL niso spremenili med zahtevami (kakršna koli sprememba teh parametrov razveljavi stare izvedbene načrte). Običajno lahko do težave pride, ko so ti parametri nastavljeni na ravni posamezne zahteve ali v kodi shranjenega postopka.

Upoštevajte, da je v vsakem primeru ročno ustvarjanje načrtov izvajanja poizvedbe in uporaba namigov zadnja možnost in se ji morate izogibati, če je to mogoče.

Načrt izvajanja poizvedbe SQL, ali načrt poizvedbe je zaporedje korakov ali navodil DBMS, potrebnih za izvedbo poizvedbe SQL. V vsakem koraku operacija, ki je sprožila ta korak izvajanja poizvedbe SQL, pridobi vrstice podatkov, ki lahko tvorijo končni rezultat ali se uporabijo za nadaljnjo obdelavo. Navodila načrta izvajanja poizvedbe SQL so predstavljena kot zaporedje operacij, ki jih IZVAJAjo stavki DBMS FOR SQL SELECT, INSERT, izbriši in nadgradnja. Vsebina načrta poizvedbe je običajno predstavljena v drevesni strukturi in vključuje naslednje informacije:

• vrstni red povezovanja podatkovnih virov (tabel, pogledov ipd.);
• način dostopa za vsak vir podatkov;
• metode povezovanja podatkovnih virov;
• operacije omejevanja izbire, razvrščanja in združevanja podatkov;
• stroški in resnost vsake operacije;
• možna uporaba particioniranja in paralelizma. Informacije, ki jih zagotavlja načrt izvajanja poizvedbe SQL, omogočajo razvijalcu, da vidi, katere pristope in metode optimizator izbere za izvajanje operacij SQL.

Razlaga izvedbenega načrta poizvedbe SQL

Vizualizacija izvedbenega načrta poizvedbe SQL je odvisna od orodij in razvojnih orodij, ki so lahko del DBMS, katerega poizvedba je zanimiva za analizo, ali pa so ločeni komercialni ali prosto distribuirani programski izdelki, ki niso neposredno povezani z določenim DBMS. proizvajalec. Uporaba enega ali drugega orodja za vizualizacijo načrta poizvedbe običajno ne vpliva bistveno na dojemanje tega, kar predstavljeni načrt poizvedbe opisuje. Odločilni dejavnik v procesu analize, katero pot bo ubral optimizator pri izvajanju določene poizvedbe, je zmožnost pravilne interpretacije informacij, ki so predstavljene v načrtu poizvedbe.

Kot že omenjeno, ima načrt poizvedbe SQL drevesno strukturo, ki opisuje ne le zaporedje izvajanja operacij SQL, ampak tudi razmerja med temi operacijami. Vsako vozlišče v drevesu načrta poizvedbe je operacija, kot je razvrščanje ali metoda dostopa do tabele. Med vozlišči obstaja odnos starš-otrok. Odnose med staršem in otrokom urejajo naslednja pravila:

• starš ima lahko enega ali več otrok;
• otrok ima samo enega starša;
• operacija, ki nima nadrejene operacije, je vrh drevesa;
• Odvisno od metode vizualizacije načrta poizvedbe SQL je podrejeni postavljen z nekaj zamika glede na nadrejenega. Potomci enega od staršev se nahajajo na enaki razdalji od svojega starša.

Oglejmo si podrobneje informacije, ki jih ponuja načrt izvajanja poizvedbe SQL. Navedeni primeri so bili izvedeni v okolju Oracle DBMS. Oracle SQL Developer je bil uporabljen kot orodje za izvajanje poizvedb in vizualizacijo načrta SQL poizvedbe. Del načrta poizvedbe SQL je prikazan na sl. 10.11.

I ID I Operacija

• 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK INFORMACIJE O IZDELKU

IZBERI STAVE RAZVRSTI VRSTNI RED PO GNEZDENIH ZANKAH GNEZDENIH ZANKAH DOSTOP TABELE POLNI INDEKS EDINSTVENO SKANIRANJE TABELE DOSTOP PO INDEKSU ROWID

riž. 10.11. Fragment izvedbenega načrta poizvedbe SQL v okolju Oracle DBMS

Z uporabo relacijskih pravil operacij načrta poizvedbe lahko definiramo njihov naslednji formalni opis.

Operacija 0 je koren drevesa načrta poizvedbe. Koren ima enega otroka: operacija 1.

Operacija 1 - operacija ima enega otroka: operacija 2.

Operacija 2 - operacija ima dva otroka: operacijo 3 in operacijo 6.

Operacija 3 - operacija ima dva otroka: operacijo 4 in operacijo 5.

Operacija 4 - operacija nima otrok.

Operacija 5 - operacija nima otrok.

Operacija 6 - operacija nima otrok.

Interakcija starš-otrok med operacijami načrta poizvedbe je prikazana na sliki. 10.12.

Operacije, ki se izvajajo v načrtu poizvedbe, lahko razdelimo na tri vrste: samostojne, nevezane operacije združevanja in povezane operacije združevanja (slika 10.13).

Avtonomna		Operacije nepovezane		Povezane operacije
operacije		asociacije		asociacije

riž. 10.12.

riž. 10.13.

Avtonomno delovanje - To so operacije, ki imajo največ eno podrejeno operacijo.

Naslednja pravila, po katerih se izvajajo avtonomne operacije, je mogoče formulirati na naslednji način.

• 2. Vsaka podrejena operacija se izvede samo enkrat.
• 3. Vsaka podrejena operacija vrne svoj rezultat nadrejeni operaciji.

Na sl. Slika 10.14 prikazuje načrt za naslednjo poizvedbo:

IZBERI o.order_id,o.order_status IZ naročil o ORDER BY o.order_status

Ta poizvedba vsebuje samo samostojne operacije.

Ob upoštevanju pravil za sledenje avtonomnim operacijam bo zaporedje njihovega izvajanja naslednje.

• 1. V skladu s pravilom sledenja avtonomnim operacijam številka 1 bo najprej izvedena operacija z ID = 2. Vse vrstice tabele naročil se berejo zaporedno.
• 2. Nato se izvede operacija z ID = 1. Vrstice, vrnjene z operacijo z ID = 2, so razvrščene v skladu s pogoji klavzule za razvrščanje ORDER BY.
• 3. Izvede se operacija z ID = 0. Vrne se nastali niz podatkov.

Neobvezno delovanje sindikata

Neobvezno delovanje sindikata so operacije, ki imajo več kot eno neodvisno izvajajočo podrejeno operacijo. Primer: HASH JOIN, MERGE JOIN, INTERSECTION, MINUS, UNION ALL.

Naslednja pravila, po katerih delujejo nevezane operacije združevanja, je mogoče formulirati na naslednji način.

• 1. Podrejena operacija se izvede pred nadrejeno operacijo.
• 2. Podrejene operacije se izvajajo zaporedno, začenši z najmanjšo vrednostjo ID operacije v naraščajočem vrstnem redu teh vrednosti.
• 3. Preden se začne vsaka naslednja podrejena operacija, mora biti trenutna operacija v celoti dokončana.
• 4. Vsaka podrejena operacija se izvede samo enkrat, ne glede na druge podrejene operacije.
• 5. Vsaka podrejena operacija vrne svoj rezultat nadrejeni operaciji.

Na sl. Slika 10.15 prikazuje načrt za naslednjo poizvedbo:

IZBERITE o.order_id med naročili o UNION ALL

IZBERITE oi.order_id iz order_items oi

Ta poizvedba vsebuje nevezano operacijo združevanja UNIJA vse. Preostali dve operaciji sta avtonomni.

riž. 10.15. Nevezane operacije združevanja, načrt poizvedbe

	1 IZBERI IZJAVO I

Ob upoštevanju pravil za sledenje nepovezanim operacijam združevanja bo zaporedje njihovega izvajanja naslednje.

• 1. V skladu s pravili 1 in 2 naslednjih nepovezanih operacij združevanja bo najprej izvedena operacija z ID = 2. Vse vrstice tabele naročil se berejo zaporedno.
• 2. V skladu s pravilom 5 vrne operacija z ID = 2 vrstice nadrejene operacije z ID = 1, prebrane v koraku 1.
• 3. Operacija z ID = 3 se bo začela izvajati šele, ko se operacija z ID = 2 konča.
• 4. Po zaključku operacije z ID = 2 se začne operacija z ID = 3. Vse vrstice tabele order_items se berejo zaporedno.
• 5. V skladu s pravilom 5 vrne operacija z ID = 3 vrstice nadrejene operacije z ID = 1, prebrane v 4. koraku.
• 6. Operacija z ID = 1 generira nabor rezultatov podatkov na podlagi podatkov, prejetih iz vseh svojih podrejenih operacij (z ID = 2 in ID = 3).
• 7. Izvede se operacija z ID = 0. Vrne se nastali niz podatkov.

Tako je mogoče opaziti, da operacija neodvisnega združevanja izvaja svoje podrejene operacije zaporedno.

Povezane operacije pridružitve

Povezane operacije pridružitve - To so operacije, ki imajo več kot eno podrejeno operacijo, pri čemer ena od operacij nadzoruje izvajanje drugih. Primer: ugnezdene zanke, posodobitev.

Naslednja pravila, po katerih delujejo operacije verižnega združevanja, je mogoče formulirati na naslednji način.

• 1. Podrejena operacija se izvede pred nadrejeno operacijo.
• 2. Podrejena operacija z najnižjo številko operacije (ID) nadzoruje izvajanje preostalih podrejenih operacij.
• 3. Podrejene operacije, ki imajo skupno nadrejeno operacijo, se izvajajo, začenši z najnižjo vrednostjo ID-ja operacije v naraščajočem vrstnem redu teh vrednosti. Preostale podrejene operacije se NE izvajajo zaporedno.
• 4. Samo prva podrejena operacija se izvede enkrat. Vse druge podrejene operacije se izvedejo večkrat ali pa sploh ne.

Na sl. Slika 10.16 prikazuje načrt za naslednjo poizvedbo:

FROM order_items oi, naročila približno

WHERE o.order_id= oi.order_id

IN oi.product_id>100

IN o.customer_id med 100 in 1000

Ta poizvedba vsebuje povezano operacijo združevanja, NESTED LOOPS.

I ID I Operacija

		IZBERI IZJAVO |

riž. 10.16. Povezane operacije združevanja, načrt poizvedbe

Ob upoštevanju pravil za sledenje operacijam verižnega združevanja bo zaporedje njihovega izvajanja naslednje.

• 1. V skladu s pravili 1 in 2 naslednjih operacij verižnega združevanja je treba najprej izvesti operacijo z ID = 2. Vendar sta operaciji z 1D = 2 in 1D = 3 avtonomni in v skladu s pravilom 1 naslednjih avtonomnih operacij je najprej bo izvedena operacija z ID = 2. ID = 3. Obseg indeksa ORDCUSTOMERIX je prikazan na podlagi pogoja: o. ID stranke med 100 in 1000.
• 2. Operacija z ID=3 vrne nadrejeni operaciji (s Ш=2) seznam identifikatorjev vrstic Rowld, pridobljenih v 1. koraku.
• 3. Operacija z ID = 2 prebere vrstice v tabeli naročil, v katerih se vrednost Rowld ujema s seznamom vrednosti Rowld, pridobljenih v koraku 2.
• 4. Operacija z ID = 2 vrne prebrane vrstice nadrejene operacije (z ID = 1).
• 5. Za vsako vrstico, ki jo vrne operacija z ID = 2, se izvede druga podrejena operacija (z ID = 4) operacije ugnezdene zanke. To pomeni, da se za vsako vrstico, ki jo vrne operacija z ID = 2, izvede popolno zaporedno skeniranje tabele order_items, da se najde ujemanje na atributu pridružitve.
• 6. Korak 5 se ponovi tolikokrat, kot je število vrstic, ki jih vrne operacija z ID = 2.
• 7. Operacija z ID = 1 vrne rezultate nadrejene operacije (z ID = 0).
• 8. Izvede se operacija z ID = 0. Vrne se nastali niz podatkov.

Odvisno od kompleksnosti analizirane poizvedbe ima lahko njen izvedbeni načrt precej zapleteno strukturo, ki se na prvi pogled zdi težko razlagati. Metodično izvajanje zgoraj opisanih pravil in razčlenitev operacij vam bo omogočilo učinkovito analizo načrta izvajanja poizvedbe SQL katere koli kompleksnosti. Oglejmo si primer poizvedbe, ki ustvari seznam strank, število blaga, ki so ga kupili, in njihove skupne stroške:

IZBERI s. cust_first_name customer_name,

COUNT(DISTINCT oi.product_id) kot product_qty,

SUM(oi.quantity* oi.unit_price) kot total_cost FROM oe.orders o stranke INNER JOIN 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

Zaporedje operacij tega načrta poizvedbe je prikazano na sl. 10.17.

IZBERI IZJAVO I

RAZVRSTI ZGRUPI PO YG

	DOSTOP ZA MIZO POLEN
	PREGLED OBSEGA INDEKSA
DOSTOP DO TABELE PO INDEKSNI VRSTICIdd
DOSTOP ZA MIZO POLEN

riž. 10.17. Načrt poizvedbe, zaporedje operacij

Naj opišemo možen pristop k razlagi izvedbenega načrta poizvedbe 80b, predstavljenega na sliki. 10.17. Ta pristop vključuje dve glavni stopnji: razčlenitev operacij na bloke in določitev vrstnega reda operacij.

Na prvi stopnji je treba operacije, ki se izvajajo, razstaviti na bloke. Da bi to naredili, najdemo vse sindikalne operacije, tj. operacije, ki imajo več kot eno podrejeno operacijo (na sliki 10.17 so to operacije 2, 3 in 4), in te podrejene operacije ločite v bloke. Kot rezultat, z uporabo primera na sl. 10.17 dobimo tri sindikalne operacije in sedem blokov operacij.

Na drugi stopnji se določi zaporedje izvajanja blokov operacij. Če želite to narediti, morate uporabiti pravila za naslednje operacije, opisane zgoraj. Oglejmo si vrsto premislekov glede izvajanja vsake operacije glede na njeno identifikacijsko številko (III).

Operacija Ш = 0 je avtonomna in je nadrejena operaciji сШ = 1.

Tudi delovanje Yu = 1 je avtonomno; je nadrejeni operaciji W = 2 in se izvede pred operacijo Y = 0.

Operacija GO = 2 je nepovezana operacija združevanja in je nadrejena operacija za operacije Yu = 3, Yu = 8. Operacija GO = 2 se izvede pred operacijo GO = 1.

Operacija GO = 3 je operacija povezane unije in je nadrejena operacija za operacije GO = 4, GO = 7. Operacija GO = 3 se izvede pred operacijo GO = 2.

Operacija GO = 4 je operacija povezane unije in je nadrejena operacija za operacije GO = 5, GO = 6. Operacija GO = 4 se izvede pred operacijo GO = 3.

Operacija GO = 5 je avtonomna operacija, izvedena pred operacijo GO = 4.

Operacija GO = 6 je avtonomna operacija, izvedena pred operacijo GO = 5.

Operacija GO = 7 je avtonomna operacija, izvedena po izvedbi bloka operacij “C”.

Operacija GO = 8 je avtonomna operacija, ki se izvede po bloku operacij “E”.

Na podlagi zgornjega razmišljanja in naslednjih pravil oblikujemo zaporedje izvedenih operacij:

• 1. Najprej se izvede avtonomna operacija GO = 5, glejte pravila za zaporedje povezanih operacij združevanja. Celotna tabela se bere zaporedno.
• 2. Rezultat operacije GO = 5 - prebrane vrstice tabele - se prenese v operacijo GO = 4.
• 3. Izvede se operacija GO = 4: za vsako vrstico, vrnjeno z operacijo GO = 5, se izvede operacija GO = 6. To pomeni, da se obseg indeksa pregleda glede na atribut spoja. Pridobivanje seznama identifikatorjev vrstic Yaou1s1.
• 4. Rezultat operacije GO = 4 se prenese v operacijo GO = 3. To pomeni, da se prenese seznam identifikatorjev vrstic Kosh1s1.
• 5. Izvede se operacija GO = 3: za vsako vrednost 11оу1с1, vrnjeno kot rezultat operacije bloka operacij “C”, se izvede operacija GO = 7, tj. Vrstice tabele se berejo iz danega seznama identifikatorjev vrstic ITMI, pridobljenega po izvedbi operacije Ш = 4.
• 6. Izvede se avtonomna operacija GO = 8 - zaporedno branje celotne tabele.
• 7. Izvede se nepovezana operacija združevanja GO = 2: združevanje se izvede z zgoščevanjem rezultatov operacijskih blokov "E" in "E".
• 8. Rezultat operacije GO = 2 se prenese v operacijo GO = 1.
• 9. Izvede se nepovezana operacija spajanja GO = 1: podatki, pridobljeni kot rezultat operacije GO = 2, se združijo in razvrstijo.
• 10. Izvede se operacija GO = 0. Vrne se nastali niz podatkov.

Naslednja pravila, oblikovana za glavne vrste operacij, veljajo za večino načrtov za izvajanje poizvedbe BSGO. Vendar pa obstajajo konstrukcije, uporabljene v poizvedbah BSGO, ki pomenijo kršitev vrstnega reda operacij, opisanega v naslednjih pravilih. Do takšnih situacij lahko pride zaradi uporabe na primer podpoizvedb ali predikatov za preprečevanje združevanja. Vsekakor pa proces interpretacije izvedbenega načrta poizvedbe BSGO ne pomeni le uporabe številnih pravil, ki bodo zagotovila najbolj natančno analizo, kaj bo optimizator naredil pri izvajanju poizvedbe BSGO. Druga zahteva BSGO je vedno posamezen primer; in kako se bo izvajal v DBMS, je odvisno od številnih dejavnikov, vključno z različico DBMS, različico in vrsto operacijskega sistema, v katerem je nameščen primerek DBMS, uporabljene strojne opreme, kvalifikacij avtorja poizvedbe 80b, itd.

1 msdevcon.ru #msdevcon

3 Olontsev Sergey SQL Server MCM, MVP Kaspersky Lab

4 Jezik strukturiranih poizvedb

5 Primer poizvedbe izberite pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber iz HumanResources.Employee as emp inner join Person.Person as pers na pers.businessentityid = emp.businessentityid kjer je pers.firstname = N"John" in emp.hiredate >= " "

6 Logično drevo poizvedb Projekt pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber D A T A Filter Join pers.firstname = N"John" in emp.hiredate >= " " pers.businessentityid = emp.businessentityid Person.Person kot pers Pridobi podatke Pridobi podatke HumanResources.Employee kot emp

7 Načrt poizvedbe Prikazuje, kako se poizvedba T-SQL izvaja na fizični ravni.

8 Več načinov

9 DEMO Preprost načrt Izbira vseh podatkov iz tabele, kako do načrta poizvedbe

11 Metode operaterja Init() Metoda Init() povzroči, da se fizični operater inicializira in pripravi vse potrebne podatkovne strukture. Fizični operater lahko prejme veliko klicev na Init(), čeprav običajno prejme le enega. GetNext() Metoda GetNext() povzroči, da fizični operater pridobi prvo ali naslednjo vrstico podatkov. Fizični operater lahko prejme veliko klicev GetNext() ali nobenega. Metoda GetNext() vrne eno vrstico podatkov, število klicev pa je označeno z vrednostjo ActualRows v izhodu izjave Showplan. Close() Ko se pokliče metoda Close(), fizični operater izvede nekaj čiščenja in zapre. Fizični operater prejme samo en klic Close().

12 Interakcija med operaterji Operater 1 Operater 2 Operater 3

13 Interakcija med operaterji 1. Vrstica zahteve Operater 1 Operater 2 Operator 3

14 Interakcija med operaterji 1. Vrstica zahteve 2. Vrstica zahteve Operater 1 Operater 2 Operater 3

15 Interakcija med operaterji 1. Vrstica z zahtevami 2. Vrstica z zahtevami Operater 1 Operater 2 Operator 3 3. Vrstica za pošiljanje

16 Interakcija med operaterji 1. Vrstica z zahtevami 2. Vrstica z zahtevami Operater 1 Operater 2 Operater 3 4. Vrstica za pošiljanje 3. Vrstica za pošiljanje

17 Interakcija med operaterji 1. Vrstica z zahtevami 2. Vrstica z zahtevami Operater 1 Operater 2 Operater 3 4. Vrstica za pošiljanje 3. Vrstica za pošiljanje

18 DEMO Operator NA VRH Ali zakaj je bolje imenovati operator iterator

19 Mize ne obstajajo!

20 HoBT Stran 1 Stran 2 Stran 3 Stran 4 Vrstica 1 Vrstica 3 Vrstica 5 Vrstica 7 Vrstica 2 Vrstica 4 Vrstica 6 Vrstica 8

21 HoBT Stran Stran Stran Stran Stran Stran Stran

22 DEMO Operaterji za dostop do podatkov Scan, Seek, Lookup

23 Kdo ima samo eno tabelo v bazi?

24 Ugnezdene zanke, zgoščeno združevanje in združevanje z združevanjem

25 Operatorji združevanja Ugnezdene zanke notranje združevanje, levo zunanje združevanje, levo pol-združevanje, levo anti-pol-združevanje Spajanje spajanja notranje združevanje, levo zunanje združevanje, levo pol-združevanje, levo anti-pol združevanje, desno zunanje združevanje, desno pol-združevanje, desno anti-pol združevanje , unija Hash Združi vse vrste logičnih operacij

26 DEMO Združevanje, razvrščanje in prvi operator Ugnezdene zanke, spajanje združevanja, zgoščevanje združevanja, razvrščanje, prvi operator

27 Opozorila

28 DEMO Napake in opozorila v načrtih poizvedb

29 Vem, da ničesar ne vem. Sokrat

30 DEMO Majhen primer nečesa nejasnega

31 Diagnosticiranje načrtov poizvedb -- TOP 10 poizvedb, ki porabijo največ CPE, in njihovi načrti izberejo top(10) substring(t.text, qs.statement_start_offset / 2, primer, ko je qs.statement_end_offset = -1 nato len(t.text) else (qs.statement_end_offset - qs.statement_start_offset) / 2 konec), 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)) kot avg_worker_time_ms, cast(p.query_plan kot xml) kot query_plan iz sys.dm_exec_query_stats kot 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 set , qs.statement_end_offset) kot p vrstni red po qs.total_worker_time desc; pojdi

32 Tehnike za branje velikih načrtov poizvedb Poskusite jih razdeliti na logične bloke in jih postopoma analizirati. V SSMS, ko je načrt grafično prikazan, se v spodnjem desnem kotu prikaže gumb za lažjo navigacijo po načrtu poizvedbe. Uporabite lahko XQuery\XPath.

33 DEMO Načrt velike poizvedbe

35 DEMO SQL Sentry Plan Explorer

36 Povzemimo prvi operater Raven optimizacije Čas prevajanja Velikost v predpomnilniku Parametri, vrednosti prevajanja Razlog za zgodnjo prekinitev Stroški iteratorjev Najprej poglejte operaterje z najvišjimi stroški. Ne pozabite, da so to samo ocenjene vrednosti (tudi v dejanskih izvedbenih načrtih).

37 Povzemimo Bookmark\Key Lookup Če jih je malo, potem najverjetneje ni problema. Če jih je veliko, se jih bo znebilo ustvarjanje pokrivnega indeksa. Opozorila Preverite, zakaj se pojavi, in po potrebi ukrepajte.

38 Povzemimo povezave med operaterji (podatkovni tokovi) Debelejša ko je povezava, več podatkov preide med temi operaterji. Še posebej je vredno biti pozoren, če se na neki stopnji pretok podatkov močno poveča. Vrstni red združevanja tabel Manjši kot so podatkovni tokovi, lažje jih je združiti. Zato morate najprej združiti tiste tabele, katerih rezultat bo pretok podatkov manjši.

39 Povzetek Pregledi Pregledi ne pomenijo, da obstaja težava. Možno je, da v tabeli ni dovolj indeksa za učinkovitejše iskanje. Po drugi strani pa bo skeniranje bolj učinkovito, če morate izbrati celotno tabelo ali velik del. Iskanje ne pomeni, da je vse v redu. Veliko število iskanj na negručastih indeksih je lahko problem. Vse, česar ne veste o načrtu, bi lahko predstavljalo težavo.

40 vprašanj

41 Kontakti Olontsev Sergey Kaspersky Lab

42 2013 Microsoft Corporation. Vse pravice pridržane. Microsoft, Windows, Windows Vista in druga imena izdelkov so ali so lahko registrirane blagovne znamke in/ali blagovne znamke v ZDA. in/ali druge države. Informacije v tem dokumentu so zgolj informativne narave in predstavljajo trenutni pogled družbe Microsoft Corporation na dan te predstavitve. Ker se mora Microsoft odzvati na spreminjajoče se tržne razmere, si tega ne smemo razlagati kot zavezo s strani Microsofta in Microsoft ne more jamčiti za točnost kakršnih koli informacij, posredovanih po datumu te predstavitve. MICROSOFT GLEDE INFORMACIJ V TEJ PREDSTAVITVI NE DAJE NOBENIH JAMSTEV, IZRECNIH, IMPLICITNIH ALI ZAKONSKIH.