Sveiki visiem! Nesen saskāros ar problēmu, ka dokumenta apstrāde prasīja ilgu laiku.

Ievaddati: konfigurācija “Ražošanas uzņēmuma vadība, izdevums 1.3 (1.3.52.1)”, dokuments “Ienākošais maksājuma uzdevums”. Sūdzība: glabāšana darba datu bāzē ilgst 20-30 sekundes, kas ir interesanti, datu bāzes kopijā tas pats dokuments tiek turēts 2-4 sekundes. Lasiet tālāk par izmeklēšanu un šādas rīcības iemeslu.

Tātad, ar palīdzību veiktspējas mērīšana Domāju, ka visi prot to izmantot, vaininieks tika atrasts:

Šajā gadījumā ierakstītājā tika ierakstīta tukša ierakstu kopa, citiem vārdiem sakot, kustības tika izdzēstas pirms izpildes. Ir vērts atzīmēt, ka šī procedūra tika izsaukta 26 reizes, t.i. katram reģistram, kurā mūsu dokuments varētu rakstīt.

Saskaņā ar veiktspējas mērījumiem šī darbība aizņēma 13 sekundes, ja jūs aprēķināt vidējo, jūs saņemat 0,5 sekundes uz vienu reģistru, mūžība!

Kā mēs visi zinām, mēs nevaram optimizēt ierakstu, taču šeit noteikti kaut kas nav kārtībā.
Lai iegūtu papildu analīzi, atveriet SQL serveris Profilētājs Un . Analīzei izmantoju notikumu klases:

• Showplan statistikas profils
• Showplan XML statistikas profils
• RPC pabeigts
• SQL:BatchCompleted.

Izsekošanas iestatījumos ir filtrēt pēc SPID:

SPID ir datu bāzes servera procesa identifikators. 1C gadījumā tas būtībā ir savienojums starp 1C serveri un DBVS, to var apskatīt 1C serveru administrēšanas konsolē kolonnā “Savienojums ar DBVS”.

Parādīts, ja Šis brīdis savienojums ar datu bāzi tiek fiksēts ar sesiju: ​​vai nu tiek veikts DBVS izsaukums, vai ir atvērta transakcija, vai tiek turēts objekts “Pagaidu tabulu pārvaldnieks”, kurā ir izveidota vismaz viena pagaidu tabula.

Uzrakstīsim apstrādi SPID turēšanai, tajā būs viena procedūra:

Ir svarīgi, lai aizturētais savienojuma objekts, mūsu gadījumā pagaidu tabulu pārvaldnieks, tiktu definēts kā apstrādes mainīgais. Mēs atveram apstrādi, palaižam procedūru un, kamēr tā ir atvērta, SPID tiks labots. Atveriet 1C servera administrēšanas konsoli:

Tātad, SPID ir saņemts, mēs ievadām tā vērtību filtrā un iegūstam izsekojumu no pašreizējās mūsu sesijas darba datu bāzes. Analizējot pēdas, tika konstatēta operācija, kuras pabeigšana prasīja 11 sekundes:

Acīs iekrita arī rādījumu skaits - 1872578 , bet es tam uzreiz nepiešķīru nekādu nozīmi un sāku izdomāt, kas šeit tiek darīts un ar kuru tabulu.

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"

Kā redzat no SQL vaicājuma, tabula tiek apstrādāta "AccRg1465"Šī ir tabula pašpietiekamajā grāmatvedības reģistrā. Vaicājuma izpildes plāna teksta attēlojums:

Kā redzat no SQL vaicājuma izpildes plāna, nekas slikts nenotiek, tabula " AccRg1465", visur tiek izmantota kopu indeksu meklēšana.

Es arī nesaskatīju neko sliktu grafiskajā plānā, lai gan man tas šķita pārāk uzpūsts - notika saplūšana un divas ligzdotas cilpas bez redzama iemesla. No kurienes tāds nolasījumu skaits un gigantiskais izpildes laiks?

Kā minēts iepriekš, problēma netika reproducēta jaunā datu bāzes kopijā, pēc tam, kad tajā parādījās problēma, kopija tika izņemta no darba datu bāzes, tāpēc tika nolemts analizēt tās uzvedību SQL Server Profiler tajā pašā dokumentā.
Lūk, rezultāti:

Vaicājuma teksts SQL:

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

Vaicājuma plāna grafiskais attēlojums:

Pieprasījumu teksti ir vienādi, izpildes plāni ir radikāli atšķirīgi. Kas varētu būt par lietu? Es kļūdījos par statistiku SQL, bet tā ir vienāda starp darba un datu bāzes kopiju, un statistika tiek glabāta datu bāzē katrai tabulai:

Analizēsim tālāk: ja statistika ir vienāda, bet vaicājumu plāni atšķiras, tas nozīmē, ka optimizētājs nepiekļūst statistikai, lai izveidotu vaicājumu plānu, bet tam ir kešatmiņā saglabāts plāns, ko tas izmanto. Mēs savā datubāzē notīrām procesuālo kešatmiņu, šim nolūkam mēs izmantojam komandu

DBCC FLUSHPROCINDB(< database_id >)

Kur< database_id >ir datu bāzes identifikators. Lai uzzinātu datu bāzes ID, jums jāpalaiž skripts

atlasiet nosaukumu, datu bāzes_id no sys . datubāzēm

tas mums atgriezīs datu bāzu sarakstu un to identifikatorus.

Mēs atkal iegūstam pēdas:

Vaicājuma plāna teksta attēlojums:

Vaicājuma plāna grafiskais attēlojums:

Kā redzat, vaicājuma plānu optimizētājs ieguva no jauna, un vecais kešatmiņā saglabātais netika izmantots, izpildes laiks atgriezās normālā stāvoklī, tāpat kā nolasījumu skaits. Nav skaidrs, kas to izraisīja, iespējams, liels skaits apmaiņu vai iepriekšējo periodu slēgšana, grūti pateikt. Ir konfigurēta regulāra datu bāzes uzturēšana. Šī ir pirmā reize, kad saskāros ar kešatmiņā saglabāto vaicājumu izpildes plāna krāpniecību.

Paldies par jūsu uzmanību!

Vai šis raksts jums palīdzēja?

6 atbildes

Ir vairāki veidi, kā iegūt izpildes plānu, kas būs atkarīgi no jūsu apstākļiem. Parasti plāna iegūšanai varat izmantot SQL Server Management Studio, tomēr, ja kāda iemesla dēļ nevarat izpildīt vaicājumu programmā SQL Server Management Studio, jums var būt noderīgi iegūt plānu, izmantojot SQL Server Profiler vai pārbaudot plānu. kešatmiņa.

1. metode — SQL Server Management Studio izmantošana

SQL Server ir dažas glītas funkcijas, kas atvieglo izpildes plāna apkopošanu, vienkārši pārliecinieties, vai ir atzīmēts izvēlnes vienums "Iekļaut faktisko izpildes plānu" (atrodams izvēlnē Vaicājums), un tas darbosies kā parasti.

Ja mēģināt iegūt izpildes plānu paziņojumiem saglabātā procedūrā, jūs izpildītu saglabāto procedūru šādi:

Izpildīt p_Example 42

Kad vaicājums būs pabeigts, rezultātu rūtī tiks parādīta papildu cilne Izpildes plāns. Ja esat veicis daudzus apstiprinājumus, šajā cilnē var tikt parādīti daudzi plāni.

Šeit varat pārbaudīt izpildes plānu programmā SQL Server Management Studio vai ar peles labo pogu noklikšķiniet uz plāna un atlasiet "Saglabāt izpildes plānu kā...", lai plānu saglabātu XML failā.

2. metode — SHOWPLAN opciju izmantošana

Šī metode ir ļoti līdzīga 1. metodei (faktiski tas ir tas, ko SQL Server Management Studio dara iekšēji), tomēr es to iekļāvu, lai nodrošinātu pilnīgumu vai ja jums nav pieejama SQL Server Management Studio.

Pirms vaicājuma izpildes palaidiet viensšādus operatorus. Paziņojumam ir jābūt vienīgajam paziņojumam iepakojumā, t.i. Vienlaikus nevar izpildīt citu paziņojumu:

IESTATĪT SHOWPLAN_TEXT ON IESTATĪT SHOWPLAN_ALL IESLĒGT IESTATĪT SHOWPLAN_XML IESLĒGT STATISTIKAS PROFILU IESLĒGT IESTATĪT STATISTIKA XML — šī ir ieteicamā opcija lietošanai.

Šie ir savienojuma parametri, tāpēc katram savienojumam tas ir jāpalaiž tikai vienu reizi. Turpmāk visiem uzsāktajiem paziņojumiem tiks pievienots papildu rezultātu kopums kas satur izpildes plānu vajadzīgajā formātā — vienkārši palaidiet vaicājumu kā parasti, lai skatītu plānu.

Kad esat pabeidzis, varat atspējot šo opciju, izmantojot šādu paziņojumu:

IESTATĪT<> IZSL

Izpildes plānu formātu salīdzinājums

Ja jums ir liela izvēle, iesaku izmantot opciju STATISTIKA XML. Šī opcija ir līdzvērtīga opcijai "Iekļaut faktisko izpildes plānu" programmā SQL Server Management Studio un nodrošina visvairāk informācijas visnoderīgākajā formātā.

• SHOWPLAN_TEXT — parāda pamata teksta aptuveno izpildes plānu, neizpildot vaicājumu
• SHOWPLAN_ALL — parāda aptuveno teksta izpildes plānu ar izmaksu tāmi, neizpildot vaicājumu
• SHOWPLAN_XML — parāda uz XML balstītu aptuveno izpildes plānu ar izmaksu aprēķiniem, neizpildot vaicājumu. Tas ir līdzvērtīgs opcijai "Rādīt izpildes plāna piemēru..." programmā SQL Server Management Studio.
• STATISTIKAS PROFILS — izpilda vaicājumu un parāda faktisko izpildes plānu, pamatojoties uz tekstu.
• STATISTIKA XML — izpilda vaicājumu un parāda faktisko izpildes plānu, pamatojoties uz XML. Tas ir līdzvērtīgs opcijai "Iekļaut faktisko izpildes plānu" programmā SQL Server Management Studio.

3. metode — SQL Server Profiler izmantošana

Ja nevarat palaist vaicājumu tieši (vai vaicājums nedarbojas lēni, kad to palaižat tieši — atcerieties, ka mēs vēlamies, lai vaicājuma plāns darbotos slikti), varat tvert plānu, izmantojot SQL Server Profiler. Ideja ir izpildīt vaicājumu, kamēr darbojas izsekošana, kas tver kādu no "Showplan" notikumiem.

Lūdzu, ņemiet vērā, ka atkarībā no slodzes jūs tu vari izmantojiet šo metodi ražošanas vidē, tomēr jums noteikti jābūt piesardzīgiem. SQL Server profilēšanas mehānismi ir izstrādāti, lai samazinātu ietekmi uz datu bāzi, taču tas nenozīmē, ka nebūs ietekmes uz veiktspēju. Ja jūsu datu bāze tiek plaši izmantota, var rasties arī problēmas ar filtrēšanu un pareizā plāna noteikšanu izsekošanas sistēmā. Acīmredzot jums vajadzētu sazināties ar savu DBA, lai pārliecinātos, ka viņi ir apmierināti ar to, ka jūs to darāt ar jūsu vērtīgo datu bāzi!

• Atveriet SQL Server Profiler un izveidojiet jaunu trasējumu, kas savienojas ar vajadzīgo datu bāzi, no kuras vēlaties ierakstīt izsekošanu.
• Cilnē Notikumu atlase atzīmējiet izvēles rūtiņu Rādīt visus notikumus, atzīmējiet rindiņu Performance -> Showplan XML un palaidiet izsekošanu.
• Kamēr izsekošana darbojas, dariet visu, kas jums jādara, lai lēnais vaicājums darbotos.
• Pagaidiet, līdz pieprasījums tiek pabeigts un izsekošana tiek pārtraukta.
• Lai saglabātu izsekošanu, ar peles labo pogu noklikšķiniet uz xml plāna SQL Server profilā un atlasiet "Izvilkt notikumu datus...", lai plānu saglabātu XML failā.

Saņemtais plāns ir līdzvērtīgs opcijai "Iekļaut faktisko izpildes plānu" programmā SQL Server Management Studio.

4. metode — vaicājumu kešatmiņas pārbaude

Ja nevarat palaist vaicājumu tieši un arī nevarat tvert profilētāja izsekošanu, jums joprojām vajadzētu būt iespējai iegūt aptuveno plānu, pārbaudot SQL vaicājuma kešatmiņas plānu.

Mēs pārbaudām plāna kešatmiņu, vaicājot SQL Server DMV. Tālāk ir sniegts pamata vaicājums, kurā tiks uzskaitīti visi kešatmiņā saglabātie vaicājumu plāni (kā xml) kopā ar to SQL tekstu. Lielākajā daļā datu bāzu jums būs jāpievieno arī papildu filtra nosacījumi, lai filtrētu rezultātus līdz jums interesējošajiem plāniem.

ATLASĪT UseCounts, Cacheobjtype, Objtype, TEKSTS, vaicājuma_plāns NO sys.dm_exec_cached_plans KROSSĒTA LIETOŠANA sys.dm_exec_sql_text(plan_handle) KROSSĒTA LIETOŠANA sys.dm_exec_query_plan(plan_handle)

Palaidiet šo vaicājumu un noklikšķiniet uz XML plāna, lai atvērtu plānu jaunā logā – ar peles labo pogu noklikšķiniet un atlasiet "Saglabāt izpildes plānu kā...", lai plānu saglabātu failā XML formātā.

Piezīmes:

Tā kā ir tik daudz faktoru (sākot no tabulas un indeksa shēmas līdz saglabātajiem datiem un tabulu statistikai), jums tas ir jādara Vienmēr mēģiniet iegūt izpildes plānu no jūs interesējošās datu bāzes (parasti tās, kurā ir veiktspējas problēma).

Šifrētu saglabāto procedūru izpildes plānu nevar veikt.

"faktiskie" un "paredzamie" izpildes plāni

Faktiskais izpildes plāns ir tas, kurā SQL Server faktiski izpilda vaicājumu, savukārt paredzamais izpildes plāns SQL Server darbojas atbilstoši tam, ko tas varētu darīt, neizpildot vaicājumu. Lai gan loģiski līdzvērtīgs, faktiskais izpildes plāns ir daudz noderīgāks, jo satur papildu datus un statistiku par to, kas patiesībā notika vaicājuma izpildes laikā. Tas ir svarīgi, diagnosticējot problēmas, kad SQL servera novērtējumi ir atspējoti (piemēram, ja statistika ir novecojusi).

Kā interpretēt vaicājuma izpildes plānu?

Šī tēma ir pietiekami cienīga bezmaksas grāmatai.

Papildus izsmeļošajai atbildei, kas dažkārt jau tiek publicēta, ir lietderīgi programmatiski piekļūt izpildes plānam, lai iegūtu informāciju. Koda paraugs tam ir zemāk.

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

Mans iecienītākais rīks vaicājumu izpildes plānu iegūšanai un padziļinātai analīzei ir SQL Sentry Plan Explorer. Tas ir daudz ērtāks, lietotājam draudzīgāks un pilnīgāks detalizētai izpildes plānu analīzei un vizualizācijai nekā SSMS.

Šeit ir ekrāna piemērs, lai saprastu, kādu funkcionalitāti piedāvā rīks.

Šis ir tikai viens no rīkā pieejamajiem skatiem. Ievērojiet lietojumprogrammas loga apakšā esošo ciļņu kopu, kas ļauj iegūt dažāda veida izpildes plāna skatus un noderīgu papildu informāciju.

Turklāt es neesmu pamanījis nekādus ierobežojumus tās bezmaksas versijā, kas liegtu jums to izmantot ikdienā vai liktu jums iegādāties Pro versiju. Tātad, ja vēlaties palikt pie bezmaksas versijas, nekas nav aizliegts.

Papildus iepriekšējās atbildēs aprakstītajām metodēm varat izmantot arī bezmaksas izpildes plāna skatītāju un vaicājumu optimizācijas rīku ApexSQL Plan (ar kuru es nesen saskāros).

Varat instalēt un integrēt ApexSQL plānu programmā SQL Server Management Studio, lai izpildes plānus varētu skatīt tieši no SSMS.

Skatiet paredzētos izpildes plānus ApexSQL plānā

• Noklikšķiniet uz pogas Jauns pieprasījums SSMS un ielīmējiet vaicājuma tekstu vaicājuma tekstlodziņā. Ar peles labo pogu noklikšķiniet un konteksta izvēlnē atlasiet "Rādīt parauga izpildes plānu".

1. Izpildes plāna diagrammā rezultātu sadaļā tiks parādīta cilne Izpildes plānošana. Pēc tam ar peles labo pogu noklikšķiniet uz izpildes plāna un konteksta izvēlnē atlasiet opciju "Atvērt ApexSQL plānā".

1. Paredzamais izpildes plāns tiks atvērts ApexSQL plānā, un to var analizēt, lai optimizētu vaicājumus.

Faktisko izpildes plānu apskate ApexSQL plānā

Lai skatītu faktisko vaicājuma izpildes plānu, pārejiet uz iepriekš minēto otro darbību, bet tagad, kad tiek parādīts aprēķinātais plāns, ApexSQL plāna galvenajā lentes joslā noklikšķiniet uz pogas "Faktiskais".

Pēc noklikšķināšanas uz pogas Faktiskais tiks parādīts faktiskais izpildes plāns ar detalizētu izmaksu parametru priekšskatījumu kopā ar citiem izpildes plāna datiem.

Plašāku informāciju par izpildes plānu apskati var atrast, sekojot šai saitei.

Vaicājumu plānus var iegūt no pagarinātas notikumu sesijas, izmantojot notikumu query_post_execution_showplan. Šeit ir XEvent sesijas piemērs:

/* Ģenerēts, izmantojot veidni "Vaicājuma informācijas izsekošana". */ IZVEIDOT PASĀKUMA SESIJU SERVERĀ ADD EVENT sqlserver.query_post_execution_showplan(ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver_sqlserver.sqlserver. er.tsql _frame,sqlserver.tsql_stack)), / * Pēc vajadzības noņemiet jebkuru no šiem notikumiem (vai iekļaujiet papildu notikumus). */ ADD EVENT sqlserver.error_reported(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.squery_sqslserver,sqlserver.squery_sqlion l_text,sqlserv er.tsql_frame,sqlserver.tsql_stack ) WHERE (.(.,(4)) UN .(.,(0)))), ADD EVENT sqlserver.module_end(SET collection_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver plāns sqlserver.rpc_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.squery_plan_hash,sqlserver_sqlserver. .tsql _frame,sqlserver.tsql_stack) WHERE (. ( .,(4)) UN .(.,(0))), ADD EVENT sqlserver.sp_statement_completed(SET collection_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqldserver.sqldserver. query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) UN .(.,(0) comp_letchl 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.sql server.tsq l_stack) WHERE (.(.,( 4 )) Un. (., (0)))), pievienojiet notikumu SQLSERVER.SQL_STATEMENT_COMPLETED (darbība (PACKAGE0.Event_seQUENCE, SQLSERVER.CLIENT_APP_NAME, SQLSERVER.DATABASE_ID, SQLSERVER.P LAN_HANDLE, SQL_SERVERSH.QUE . SESIJAS_ID, sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) UN .(.,(0))) PIEVIENOT MĒRĶA pakotni0.ring_buffer WITH (MAX_MEMORY=4096, MONITORIJAS_ATMĒS! MAX_DISPATCH_ LATENCY = 30 SEKUNDES, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=IESLĒGTS, STARTUP_STATE=IZSLĒGTS)

Kad sesija ir izveidota (SSMS), dodieties uz objektu pārlūkprogrammu un dodieties uz Pārvaldīt | Paplašināti pasākumi | Sesijas. Ar peles labo pogu noklikšķiniet uz "GetExecutionPlan" sesijas un palaidiet to. Ar peles labo pogu noklikšķiniet uz tā un atlasiet “Watch Live Data”.

Pēc tam atveriet jaunu vaicājuma logu un palaidiet vienu vai vairākus vaicājumus. Šeit ir viens AdventureWorks:

IZMANTOT AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (Pasūtījuma daudzums * Vienības cena), Atlaides = ((Pasūtījuma daudzums * Vienības cena) * Vienības cenaAtlaide) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod. Produkta nosaukums DESC; AIZIET

Pēc minūtes vai divām cilnē “GetExecutionPlan: Live Data” tiks parādīti daži rezultāti. Režģī atlasiet vienu no query_post_execution_showplan notikumiem un pēc tam noklikšķiniet uz cilnes Vaicājumu plāns zem režģa. Tam vajadzētu izskatīties apmēram šādi:

REDIĢĒT: XEvent kods un ekrānuzņēmums tika ģenerēti no SQL/SSMS 2012 w/SP2. Ja izmantojat SQL 2008/R2, varat iestatīt skriptu, lai to palaistu. Bet šai versijai nav GUI, tāpēc jums būs jāizvelk showplan XML fails, jāsaglabā kā *.sqlplan fails un jāatver SSMS. Tas ir apgrūtinoši. XEvents nepastāvēja SQL 2005 vai agrākā versijā. Tātad, ja neizmantojat SQL 2012 vai jaunāku versiju, es ļoti ieteiktu kādu no citām šeit ievietotajām atbildēm.

dalīties

Vaicājumu optimizācija programmā SQL Server 2005, SQL Server 2005 datu bāzes statistika, STATISTIKAS IZVEIDE, STATISTIKAS ATJAUNINĀŠANA, IESTATĪT NOCOUNT ON, vaicājuma izpildes plāni, loģisko nolasījumu skaits, optimizētāja padomi, MAXDOP, OPTIMIZE FOR, apmācības izpildes plāni (plāna izveides ceļveži)

Ja visas pārējās veiktspējas optimizēšanas metodes jau ir izsmeltas, tad SQL Server izstrādātāju un administratoru rīcībā ir pēdējā rezerve – atsevišķu vaicājumu izpildes optimizēšana. Piemēram, ja jūsu uzdevums noteikti prasa paātrināt viena konkrēta pārskata izveidi, varat analizēt vaicājumu, kas tiek izmantots šī pārskata izveidei, un mēģināt mainīt tā plānu, ja tas nav optimāls.

Daudziem speciālistiem ir neviennozīmīga attieksme pret vaicājumu optimizāciju. No vienas puses, vaicājumu optimizētāja programmatūras moduļa darbība, kas ģenerē vaicājumu izpildes plānus, izraisa daudz godīgas kritikas gan SQL Server 2000, gan SQL Server 2005. Vaicājumu optimizētājs bieži izvēlas ne optimālākos vaicājumu izpildes plānus un dažās situācijās zaudē. līdzīgiem Oracle un Informix moduļiem. No otras puses, manuāla vaicājumu optimizācija ir ārkārtīgi darbietilpīgs process. Šādai optimizācijai varat pavadīt daudz laika un galu galā uzzināt, ka nekas nav optimizēts: vaicājuma optimizētāja sākotnēji piedāvātais plāns izrādījās visoptimālākais (tas notiek vairumā gadījumu). Turklāt var gadīties, ka manuāli izveidotais vaicājumu izpildes plāns pēc kāda laika (pēc jaunas informācijas pievienošanas datu bāzei) izrādīsies neoptimāls un samazinās veiktspēju, izpildot vaicājumus.

Ņemiet vērā arī to, ka vaicājumu optimizētājam ir nepieciešama pareiza statistikas informācija, lai izvēlētos labākos vaicājumu plānus. Tā kā saskaņā ar autora pieredzi ne visi administratori zina, kas tas ir, mēs jums pastāstīsim vairāk par statistiku.

Statistika- šī ir īpaša pakalpojuma informācija par datu sadalījumu tabulas kolonnās. Iedomāsimies, piemēram, ka tiek izpildīts vaicājums, kuram būtu jāatgriež visi Sanktpēterburgas pilsētā dzīvojošie Ivanovi. Pieņemsim, ka 90% šīs tabulas ierakstu kolonnā ir vienāda vērtība Pilsēta - "Sanktpēterburga". Protams, no vaicājuma izpildes viedokļa vispirms ir izdevīgāk tabulā atlasīt visus Ivanovus (tie acīmredzot nebūs 90%) un pēc tam pārbaudīt kolonnas vērtību Pilsēta katram atlasītajam ierakstam. Tomēr, lai uzzinātu, kā tiek sadalītas kolonnas vērtības, vispirms ir jāpalaiž vaicājums. Tāpēc SQL Server patstāvīgi uzsāk šādu vaicājumu izpildi un pēc tam datu bāzes pakalpojumu tabulās saglabā informāciju par datu sadalījumu (ko sauc par statistiku).

SQL Server 2005 datu bāzēm noklusējuma iestatījumi ir AUTO_CREATE_STATISTICS Un AUTO_UPDATE_STATISTICS. Šajā gadījumā statistika datu bāzes kolonnām tiks izveidota un atjaunināta automātiski. Attiecībā uz lielākajām un svarīgākajām datu bāzēm statistikas izveides un atjaunināšanas darbības var traucēt pašreizējo lietotāju pieredzi. Tāpēc šādām datu bāzēm dažreiz šie parametri tiek atspējoti, un statistikas izveides un atjaunināšanas darbības tiek veiktas manuāli naktī. Šim nolūkam izmantotās komandas ir VEIDOT STATISTIKU Un ATJAUNINĀT STATISTIKU.

Tagad parunāsim par vaicājuma optimizāciju.

Vispirms ir jāatrod tie vaicājumi, kas galvenokārt tiek optimizēti. Vienkāršākais veids, kā to izdarīt ar profilētāja palīdzību, iestatot filtru pieprasījuma darbības laikam (filtrs Ilgums logā RediģētFiltrs(Rediģēt filtru), kuru var atvērt, izmantojot pogu KolonnaFiltri uz cilnes PasākumiAtlase izsekošanas sesijas rekvizītu logs). Piemēram, optimizācijas kandidāti var ietvert vaicājumus, kuru izpildes laiks bija ilgāks par 5 sekundēm. Varat arī izmantot vaicājuma informāciju, ko nodrošina datu bāzes regulēšanas padomnieks.

Tālāk jums jāpārbauda, ​​vai parametrs ir iestatīts jūsu savienojumiem, saglabātajām procedūrām un funkcijām NAV NOSKAITĪT. Varat to instalēt, izmantojot komandu IESTATĪT NOCOUNT ON. Iestatot šo parametru, pirmkārt, tiek atspējota atgriešana no servera un informācijas rādīšana par rindu skaitu vaicājuma rezultātos (t.i., rinda "Ietekmētas N rindas" uz cilnes Ziņojumi(C ziņojumi) logi darbam ar kodu, izpildot pieprasījumu programmā Management Studio). Otrkārt, tiek atspējota īpaša servera ziņojuma pārraide DONE_IN_PROC, kas pēc noklusējuma tiek atgriezta katram saglabātās procedūras posmam. Izsaucot lielāko daļu saglabāto procedūru, jums ir nepieciešams tikai to izpildes rezultāts, un neviens nerūpējas par katrā posmā apstrādāto rindu skaitu. Tāpēc parametra iestatīšana NAV NOSKAITĪT uzglabātajām procedūrām var nopietni uzlabot to veiktspēju. Palielinās arī parasto vaicājumu izpildes ātrums, bet mazākā mērā (līdz 10%).

Pēc tam varat sākt strādāt ar vaicājumu izpildes plāniem.

Vienkāršākais veids, kā skatīt vaicājuma izpildes plānu, ir SQL Server Management Studio. Lai iegūtu informāciju par paredzamo vaicājuma izpildes plānu, var izmantot izvēlni Vaicājums(Vaicājums) atlasiet komandu DisplejsAptuvenaisIzpildePlānot(Parādīt paredzamo izpildes plānu). Ja vēlaties uzzināt faktisko vaicājuma izpildes plānu, varat iestatīt parametru tajā pašā izvēlnē pirms tā izpildes IekļautFaktiskaisIzpildePlānot(Iekļaut reālu izpildes plānu). Šajā gadījumā pēc vaicājuma izpildes SQL Server Management Studio rezultātu logā parādīsies cita cilne IzpildePlānot(Izpildes plāns), kurā tiks parādīts faktiskais vaicājuma izpildes plāns. Novietojot peles kursoru virs jebkura posma, varat iegūt papildu informāciju par to (11.15. att.).

Rīsi. 11.15. Vaicājuma izpildes plāns programmā SQL Server Management Studio

Vaicājuma izpildes plānā, kā redzams attēlā, elementi var būt daudz. To izpratne, kā arī cita īstenošanas plāna ierosināšana ir diezgan grūts uzdevums. Jāsaka, ka katrs no iespējamajiem elementiem ir optimāls savā situācijā. Tāpēc parasti vaicājuma optimizācijas posmi izskatās šādi:

q Vispirms logā Management Studio palaidiet komandu IESLĒGTS STATISTIKA. Rezultātā pēc katras pieprasījuma izpildes tiks parādīta papildu informācija. Tajā mūs interesē tikai viena parametra vērtība - Loģiski lasījumi. Šis parametrs nozīmē loģisko nolasījumu skaitu, izpildot vaicājumus, t.i., cik lasīšanas operācijas bija jāveic, izpildot doto vaicājumu, neņemot vērā kešatmiņas ietekmi (nolasījumu skaitu gan no kešatmiņas, gan diska). Šis ir vissvarīgākais parametrs. Fizisko nolasījumu skaits (nolasa tikai no diska) nav īpaši reprezentatīva informācija, jo tas ir atkarīgs no tā, vai šīm tabulām ir bijušas iepriekšējas piekļuves vai nē. Laika statistika ir arī mainīga un ir atkarīga no citām darbībām, kuras serveris veic tajā laikā. Bet loģisko nolasījumu skaits ir objektīvākais rādītājs, ko vismazāk ietekmē papildu faktori;

q pēc tam mēģiniet mainīt vaicājuma izpildes plānu un uzzināt kopējo loģisko nolasījumu skaitu katram plānam. Parasti vaicājuma izpildes plāns tiek mainīts, izmantojot optimizētāja ieteikumus. Tie skaidri norāda optimizētājam, kuru izpildes plānu izmantot.

Programmā SQL Server 2005 ir daudz optimizēšanas ieteikumu. Informāciju par tām varat lasīt pakalpojumā Books Online (cilnes sarakstā Rādītājs(Indekss) ir jāatlasa VaicājumsPadomi [SQLserveris](Vaicājuma padomi), PievienojietiesPadomi(Pievienoties padomiem) vai TabulaPadomi [SQLserveris](Tabulas padomi)). Visbiežāk izmantotie padomi ir:

q NOLOCK, ROLOKS, PAGLOCK, TABLOKS, HOLDLOCK, READCOMMITTED LOCK, UPDLOCK, XLOCK- šie padomi tiek izmantoti, lai pārvaldītu slēdzenes (sk. 11.5.7. sadaļu);

q ĀTRI rindu skaits - tiks izvēlēts vaicājuma izpildes plāns, kurā pēc iespējas ātrāk tiks parādīts norādītais rindu skaits (pirmā no ierakstu kopas sākuma). Ja lietotājam nepieciešami tieši pirmie ieraksti (piemēram, pēdējie pasūtījumi), tad ar šo mājienu var tos pēc iespējas ātrāk ielādēt aplikācijas logā;

q SPĒKA RĪCĪBA- tabulu savienošana, izpildot vaicājumu, tiks veikta tieši tādā secībā, kādā šīs tabulas ir uzskaitītas vaicājumā;

q MAXDOP(no Maximum Degree of Parallelism - pieprasījuma maksimālā paralēluma pakāpe) - izmantojot šo mājienu, tiek norādīts maksimālais procesoru skaits, ko var izmantot pieprasījuma izpildei. Parasti šo padomu izmanto divās situācijās:

· ja notiek pārslēgšanās starp procesoriem ( kontekstāpārslēgšana) vaicājuma izpildes ātrums ir ievērojami samazināts. Šī darbība bija raksturīga SQL Server 2000 daudzprocesoru sistēmās;

· ja vēlaties, lai kāds smags pieprasījums minimāli ietekmētu pašreizējo lietotāja pieredzi;

q OPTIMIZĒT PAR- šis padoms ļauj norādīt, ka pieprasījums ir optimizēts noteiktai tam nodotā ​​parametra vērtībai (piemēram, filtra vērtībai KUR);

q IZMANTOT PLĀNU- šī ir visspēcīgākā iespēja. Izmantojot šādu mājienu, varat skaidri definēt vaicājuma izpildes plānu, nododot plānu kā virknes vērtību XML formātā. Padoms IZMANTOT PLĀNU parādījās tikai SQL Server 2005 (iepriekšējās versijās bija iespējams skaidri definēt vaicājumu izpildes plānus, bet tas tika darīts, izmantojot citus līdzekļus). Plānu XML formātā var uzrakstīt manuāli, vai arī to var ģenerēt automātiski (piemēram, ar peles labo pogu noklikšķinot uz grafiskā ekrāna ar 11.15. att. parādīto izpildes plānu un konteksta izvēlnē izvēloties komandu SaglabātIzpildePlānotKā(Saglabāt izpildes plānu kā)).

SQL Server 2005 ievieš svarīgu jaunu līdzekli, kas ļauj manuāli mainīt vaicājuma izpildes plānu, nemainot vaicājuma tekstu. Bieži gadās, ka pieprasījuma kodu nevar mainīt: tas ir savienots ar kompilētās lietojumprogrammas kodu. Lai risinātu šo problēmu, SQL Server 2005 ieviesa saglabāto procedūru sp_create_plan_guide. Tas ļauj izveidot t.s Izpildes plāna rokasgrāmatas (plānsceļveži), kas tiks automātiski lietoti atbilstošiem vaicājumiem.

Ja analizējat vaicājumus, ko lietojumprogramma nosūta datu bāzei, vispirms ir lietderīgi pievērst uzmanību šādiem punktiem:

q cik bieži operācija parādās vaicājuma izpildes plānos TabulaSkenēt(Pilna tabulas skenēšana). Var izrādīties, ka piekļuve tabulai, izmantojot indeksus, būs efektīvāka;

q vai kodā tiek izmantoti kursori. Kursori ir ļoti vienkārši programmas sintakses ziņā, bet ārkārtīgi neefektīvi veiktspējas ziņā. Ļoti bieži jūs varat izvairīties no kursoru izmantošanas, izmantojot citas sintaktiskās konstrukcijas un iegūt lielu ātruma pieaugumu;

q vai kods izmanto pagaidu tabulas vai datu tipu Tabula. Pagaidu tabulu izveide un darbs ar tām prasa daudz resursu, tāpēc, ja iespējams, no tām vajadzētu izvairīties;

q Papildus pagaidu tabulu izveidei to struktūras maiņa prasa arī ievērojamu sistēmas resursu patēriņu. Tāpēc komandām mainīt pagaidu tabulu struktūru nekavējoties vajadzētu piesaistīt jūsu uzmanību. Parasti ir iespējams uzreiz izveidot pagaidu tabulu ar visām nepieciešamajām kolonnām;

q dažreiz vaicājumi atgriež vairāk datu, nekā lietojumprogrammai faktiski nepieciešams (papildu kolonnas vai rindas). Protams, tas neuzlabo produktivitāti;

q ja programma nosūta komandas serverim IZPILDĪT, tad ir jēga padomāt par to aizstāšanu ar saglabāto procedūru izsaukumu sp_executesql. Tam ir veiktspējas priekšrocības salīdzinājumā ar parasto komandu IZPILDĪT;

q Veiktspējas uzlabojumus dažkārt var panākt, novēršot nepieciešamību atkārtoti kompilēt saglabātās procedūras un veidot jaunus vaicājumu izpildes plānus. Jāpievērš uzmanība parametru lietošanai, jāmēģina nesajaukt DML un DDL komandas saglabātās procedūras kodā un jāpārliecinās, ka savienojuma parametri IESTATĪT ANSI_DEFAULTS, IESTATĪT ANSI_NULLS, IESTATĪT ANSI_PADDING, IESTATĪT ANSI_WARNINGS Un IESTATĪT CONCAT_NULL_YIELDS_NULL starp pieprasījumiem nav mainījušies (jebkuras izmaiņas šādos parametros padara nederīgus vecos izpildes plānus). Parasti problēma var rasties, ja šie parametri ir iestatīti individuālā pieprasījuma līmenī vai saglabātā procedūras kodā.

Ņemiet vērā, ka jebkurā gadījumā vaicājumu izpildes plānu manuāla izveide un mājienu izmantošana ir pēdējais līdzeklis, un, ja iespējams, no tā jāizvairās.

SQL vaicājuma izpildes plāns, vai vaicājuma plāns ir darbību secība vai DBVS instrukcijas, kas nepieciešamas SQL vaicājuma izpildei. Katrā solī darbība, kas uzsāka šo SQL vaicājuma izpildes darbību, izgūst datu rindas, kas var veidot gala rezultātu vai tikt izmantotas turpmākai apstrādei. SQL vaicājuma izpildes plāna instrukcijas tiek attēlotas kā darbību secība, ko VEIC DBMS FOR SQL priekšraksti SELECT, INSERT, dzēst un Atjaunināt. Vaicājumu plāna saturs parasti tiek attēlots koka struktūrā un ietver šādu informāciju:

• datu avotu (tabulu, skatu utt.) savienošanas secība;
• piekļuves metode katram datu avotam;
• datu avotu savienošanas metodes;
• datu atlases, šķirošanas un apkopošanas ierobežošanas darbības;
• katras operācijas izmaksas un smagums;
• iespējama sadalīšanas un paralēlisma izmantošana. SQL vaicājuma izpildes plāna sniegtā informācija ļauj izstrādātājam redzēt, kuras pieejas un metodes optimizētājs izvēlas SQL darbību veikšanai.

SQL vaicājuma izpildes plāna interpretācija

SQL vaicājuma izpildes plāna vizualizācija ir atkarīga no rīkiem un izstrādes rīkiem, kas var būt daļa no DBVS, kuras vaicājums ir interesants analīzei, vai arī atsevišķi komerciāli vai brīvi izplatīti programmatūras produkti, kas nav tieši saistīti ar konkrētu DBVS. ražotājs. Viena vai cita vaicājumu plāna vizualizācijas rīka izmantošana parasti būtiski neietekmē uztveri par to, ko apraksta uzrādītais vaicājumu plāns. Noteicošais faktors analīzes procesā, kuru ceļu optimizētājs izvēlēsies, izpildot konkrētu vaicājumu, ir spēja pareizi interpretēt vaicājumu plānā sniegto informāciju.

Kā jau minēts, SQL vaicājumu plānam ir koka struktūra, kas apraksta ne tikai SQL darbību izpildes secību, bet arī attiecības starp šīm operācijām. Katrs mezgls vaicājumu plāna kokā ir darbība, piemēram, kārtošana vai tabulas piekļuves metode. Starp mezgliem pastāv vecāku un bērnu attiecības. Vecāku un bērnu attiecības regulē šādi noteikumi:

• vecākam var būt viens vai vairāki bērni;
• bērnam ir tikai viens vecāks;
• operācija, kurai nav pamatoperācijas, ir koka augšdaļa;
• Atkarībā no SQL vaicājumu plāna vizualizācijas metodes, bērns tiek novietots ar zināmu atkāpi attiecībā pret vecāku. Viena vecāka pēcnācēji atrodas vienādā attālumā no sava vecāka.

Sīkāk apskatīsim informāciju, ko sniedz SQL vaicājumu izpildes plāns. Dotie piemēri tika veikti Oracle DBMS vidē. Oracle SQL Developer tika izmantots kā rīks vaicājumu izpildei un SQL vaicājumu plāna vizualizācijai. SQL vaicājumu plāna fragments ir parādīts attēlā. 10.11.

I Id I Operācija

• 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK PRODUKTA INFORMĀCIJA

IZVĒLĒTIES PAZIŅOJUMA KĀRTOŠANAS SECINĀJUMS PĒC LIGZTOTO CILPU LĪDZOTĀS CILPAS TABULA PIEKĻUVE PILNS INDEKSS UNIKĀLA SKENĒŠANA TABULA PIEKĻUVE PĒC INDEKSA RIDAS

Rīsi. 10.11. SQL vaicājuma izpildes plāna fragments Oracle DBMS vidē

Izmantojot vaicājumu plāna darbību relāciju noteikumus, mēs varam definēt šādu formālu to aprakstu.

Operācija 0 ir vaicājumu plāna koka sakne. Saknei ir viens bērns: operācija 1.

1. darbība — operācijai ir viens bērns: 2. darbība.

2. operācija — operācijai ir divi bērni: 3. darbība un 6. darbība.

3. operācija — operācijai ir divi bērni: 4. darbība un 5. darbība.

4. operācija – operācijai nav bērnu.

5. operācija – operācijai nav bērnu.

6. operācija – operācijai nav bērnu.

Vecāku un bērnu mijiedarbība starp vaicājumu plāna darbībām ir parādīta attēlā. 10.12.

Vaicājumu plānā veiktās darbības var iedalīt trīs veidos: savrupās, nesaistītās savienošanas operācijas un saistītās pievienošanās darbības (10.13. attēls).

Autonoms		Operācijas, kas nav saistītas		Saistītās darbības
operācijas		asociācijas		asociācijas

Rīsi. 10.12.

Rīsi. 10.13.

Autonomās operācijas -Šīs ir darbības, kurās ir ne vairāk kā viena pakārtota operācija.

Šādus noteikumus, saskaņā ar kuriem tiek veiktas autonomas darbības, var formulēt šādi.

• 2. Katra bērna operācija tiek izpildīta tikai vienu reizi.
• 3. Katra pakārtotā darbība atgriež savu rezultātu vecākoperācijai.

Attēlā 10.14. attēlā parādīts šāda vaicājuma plāns:

SELECT o.order_id ,o.order_status NO pasūtījumiem o ORDER BY o.order_status

Šis vaicājums satur tikai atsevišķas darbības.

Ņemot vērā autonomo operāciju sekošanas noteikumus, to izpildes secība būs šāda.

• 1. Saskaņā ar autonomo operāciju Nr. 1 noteikumu, vispirms tiks izpildīta operācija ar ID = 2 Visas pasūtījumu tabulas rindas tiek nolasītas secīgi.
• 2. Tālāk tiek veikta operācija ar ID = 1 rindas, kuras atgriež operācija ar ID = 2, tiek sakārtotas atbilstoši ORDER BY šķirošanas klauzulas nosacījumiem.
• 3. Tiek veikta operācija ar ID = 0. Iegūtā datu kopa tiek atgriezta.

Bezsaistību savienības operācijas

Bezsaistību savienības operācijas ir darbības, kurām ir vairāk nekā viena neatkarīgi izpildoša pakārtota operācija. Piemērs: HASH JOIN, WASH JOIN, INTERSECTION, MINUS, UNION ALL.

Tālāk norādītos noteikumus, saskaņā ar kuriem darbojas nesaistītās savienošanas darbības, var formulēt šādi.

• 1. Bērna darbība tiek izpildīta pirms vecākoperācijas.
• 2. Pakārtotās darbības tiek izpildītas secīgi, sākot ar mazāko operācijas ID vērtību šo vērtību augošā secībā.
• 3. Pirms katras nākamās bērna darbības sākšanas pašreizējā darbība ir pilnībā jāpabeidz.
• 4. Katra bērnoperācija tiek izpildīta tikai vienu reizi, neatkarīgi no citām bērnoperācijām.
• 5. Katra pakārtotā darbība atgriež savu rezultātu vecākoperācijai.

Attēlā 10.15. attēlā parādīts šāda vaicājuma plāns:

IZVĒLĒTIES o.order_id no pasūtījumiem o UNION ALL

ATLASĪT oi.order_id no order_items oi

Šis vaicājums satur nesaistītu savienošanas darbību SAVIENĪBA visu. Pārējās divas darbības ir autonomas.

Rīsi. 10.15. Nesaistītas pievienošanās darbības, vaicājumu plāns

	1 IZVĒLĒTIES PAZIŅOJUMU I

Ņemot vērā noteikumus par nesaistītu pievienošanās darbību ievērošanu, to izpildes secība būs šāda.

• 1. Saskaņā ar sekojošo nesaistīto savienošanas darbību 1. un 2. noteikumu, vispirms tiks veikta darbība ar ID = 2. Visas pasūtījumu tabulas rindas tiek nolasītas secīgi.
• 2. Saskaņā ar 5. noteikumu operācija ar ID = 2 atgriež 1. darbībā nolasītās vecākoperācijas rindas ar ID = 1.
• 3. Operāciju ar ID = 3 sāks izpildīt tikai tad, kad beigsies darbība ar ID = 2.
• 4. Pēc operācijas ar ID = 2 pabeigšanas sākas darbība ar ID = 3. Visas order_items tabulas rindas tiek nolasītas secīgi.
• 5. Saskaņā ar 5. noteikumu operācija ar ID = 3 atgriež 4. darbībā nolasītās vecākoperācijas rindas ar ID = 1.
• 6. Operācija ar ID = 1 ģenerē rezultātu datu kopu, pamatojoties uz datiem, kas saņemti no visām tās pakārtotajām operācijām (ar ID = 2 un ID = 3).
• 7. Tiek veikta operācija ar ID = 0 Iegūtā datu kopa tiek atgriezta.

Tādējādi var atzīmēt, ka neatkarīgā savienojuma operācija savas atvasinātās operācijas izpilda secīgi.

Saistītās pievienošanās operācijas

Saistītās pievienošanās darbības —Šīs ir darbības, kurām ir vairāk nekā viena pakārtota operācija, un viena no operācijām kontrolē pārējo izpildi. Piemērs: ligzdotas cilpas, atjaunināšana.

Tālāk norādītos noteikumus, saskaņā ar kuriem darbojas ķēdes savienošanas darbības, var formulēt šādi.

• 1. Bērna darbība tiek izpildīta pirms vecākoperācijas.
• 2. Pakārtotā operācija ar zemāko operāciju numuru (ID) kontrolē atlikušo pakārtoto darbību izpildi.
• 3. Pakārtotās operācijas, kurām ir kopīga vecākoperācija, tiek izpildītas, sākot ar operācijas ID zemāko vērtību šo vērtību augošā secībā. Atlikušās pakārtotās operācijas NAV izpildītas secīgi.
• 4. Vienu reizi tiek izpildīta tikai pirmā bērna operācija. Visas pārējās bērnu darbības tiek izpildītas vairākas reizes vai netiek izpildītas vispār.

Attēlā 10.16. attēlā parādīts šāda vaicājuma plāns:

NO order_items oi, pasūtījumi par

WHERE o.order_id= oi.order_id

UN oi.product_id>100

UN o.customer_id no 100 līdz 1000

Šis vaicājums satur saistītu savienošanas darbību NESTED LOOPS.

I Id I Operācija

		ATLASĪT PAZIŅOJUMU |

Rīsi. 10.16. Saistītās pievienošanās darbības, vaicājumu plāns

Ņemot vērā noteikumus, kas jāievēro, sekojot ķēdes pievienošanas operācijām, to izpildes secība būs šāda.

• 1. Saskaņā ar 1. un 2. noteikumu sekojošām ķēdes savienošanas darbībām vispirms ir jāveic darbība ar ID = 2. Tomēr darbības ar 1D = 2 un 1D = 3 ir autonomas, un saskaņā ar sekojošo autonomo darbību 1. noteikumu vispirms tiks veikta darbība ar ID = 2. ORDCUSTOMERIX indeksa diapazons tiek skatīts, pamatojoties uz nosacījumu: o. klienta ID no 100 līdz 1000.
• 2. Operācija ar ID=3 atgriež vecākoperācijā (ar Ш=2) 1. darbībā iegūto Roulda rindu identifikatoru sarakstu.
• 3. Darbība ar ID = 2 nolasa rindas pasūtījumu tabulā, kurā Roulda vērtība atbilst 2. darbībā iegūtajam Roulda vērtību sarakstam.
• 4. Operācija ar ID = 2 atgriež vecākoperācijas (ar ID = 1) nolasītās rindas.
• 5. Katrai rindai, ko atgriež operācija ar ID = 2, tiek izpildīta operācijas otrā pakārtotā darbība (ar ID = 4). ligzdotas cilpas. Tas nozīmē, ka katrai rindai, ko atgriež darbība ar ID = 2, tiek veikta pilna secīga tabulas order_items skenēšana, lai atrastu savienojuma atribūta atbilstību.
• 6. 5. darbība tiek atkārtota tik reižu, cik rindu atgriež darbība ar ID = 2.
• 7. Operācija ar ID = 1 atgriež vecāku operācijas rezultātus (ar ID = 0).
• 8. Tiek veikta operācija ar ID = 0. Iegūtā datu kopa tiek atgriezta.

Atkarībā no analizējamā vaicājuma sarežģītības, tā izpildes plānam var būt diezgan sarežģīta struktūra, kas no pirmā acu uzmetiena šķiet grūti interpretējama. Iepriekš aprakstīto noteikumu metodiskā ieviešana un operāciju sadalīšana ļaus efektīvi analizēt jebkuras sarežģītības SQL vaicājuma izpildes plānu. Apskatīsim vaicājuma piemēru, kas ģenerē klientu sarakstu, viņu iegādāto preču skaitu un to kopējās izmaksas:

SELECT s. cust_first_name klienta_nosaukums,

COUNT(DISTINCT oi.product_id) kā product_qty,

SUM(oi.daudzums* oi.vienības_cena) kā kopējās_izmaksas NO oe.pasūtījumiem o IEKŠĒJIEM JOIN klientiem c IESL.

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

Šī vaicājuma plāna darbību secība ir parādīta attēlā. 10.17.

IZVĒLĒTIES PAZIŅOJUMU I

KĀRTOT GRUPU PĒC YG

	PIEKĻUVE TABULAam
	INDEX RANGE SCAN
PIEKĻUVE TABULAI PĒC INDEKSA ROWIDd
PIEKĻUVE TABULAam

Rīsi. 10.17. Vaicājuma plāns, darbību secība

Aprakstīsim iespējamo pieeju 80b vaicājuma izpildes plāna interpretācijai, kas parādīta attēlā. 10.17. Šī pieeja ietver divus galvenos posmus: darbību sadalīšanu blokos un darbību secības noteikšanu.

Pirmajā posmā veicamās darbības ir jāsadala blokos. Lai to izdarītu, mēs atrodam visas arodbiedrības operācijas, t.i. operācijas, kurām ir vairāk nekā viena bērnoperācija (10.17. att. tās ir 2., 3. un 4. operācijas), un atdala šīs bērnoperācijas blokos. Rezultātā, izmantojot piemēru attēlā. 10.17, iegūstam trīs arodbiedrību operācijas un septiņus operāciju blokus.

Otrajā posmā tiek noteikta darbību bloku izpildes secība. Lai to izdarītu, jums ir jāpiemēro noteikumi par šādām iepriekš aprakstītajām darbībām. Apsvērsim virkni apsvērumu par katras darbības izpildi attiecībā uz tās identifikācijas numuru (III).

Operācija Ш = 0 ir autonoma un ir operācijas сШ = 1 vecākais.

Operācija Yu = 1 arī ir autonoma; ir operācijas Ш = 2 vecākais un tiek izpildīts pirms operācijas Ш = 0.

Operācija GO = 2 ir nesaistīta savienošanas darbība un ir galvenā operācija operācijām Yu = 3, Yu = 8. Operācija GO = 2 tiek veikta pirms operācijas GO = 1.

Operācija GO = 3 ir saistīta savienošanas darbība, tā ir operāciju GO = 4, GO = 7 vecāku operācija. Operācija GO = 3 tiek veikta pirms darbības GO = 2.

Operācija GO = 4 ir saistīta savienošanas darbība, tā ir operāciju GO = 5, GO = 6 vecāku operācija. Operācija GO = 4 tiek veikta pirms darbības GO = 3.

Operācija GO = 5 ir autonoma darbība, kas tiek veikta pirms darbības GO = 4.

Operācija GO = 6 ir autonoma darbība, kas tiek veikta pirms operācijas GO = 5.

Operācija GO = 7 ir autonoma darbība, kas tiek veikta pēc operāciju bloka “C” izpildes.

Operācija GO = 8 ir autonoma darbība, kas tiek veikta pēc operāciju bloka “E”.

Pamatojoties uz iepriekš minēto argumentāciju un šādiem noteikumiem, mēs formulējam veikto darbību secību:

• 1. Vispirms tiek veikta autonoma darbība GO = 5, skatiet saistīto savienošanas darbību secības noteikumus. Visa tabula tiek lasīta secīgi.
• 2. Operācijas GO = 5 rezultāts - nolasītās tabulas rindas - tiek pārnests uz operāciju GO = 4.
• 3. Tiek veikta operācija GO = 4: katrai rindai, kas atgriezta ar operāciju GO = 5, tiek veikta darbība GO = 6. Tas ir, indeksa diapazons tiek skenēts pret savienojuma atribūtu. Rindas identifikatoru saraksta iegūšana Yaou1s1.
• 4. Operācijas GO = 4 rezultāts tiek pārsūtīts uz operāciju GO = 3. Tas ir, tiek pārsūtīts rindu identifikatoru saraksts Kosh1s1.
• 5. Tiek veikta operācija GO = 3: katrai operāciju bloka “C” darbības rezultātā atgrieztai vērtībai 11оу1с1 tiek veikta operācija GO = 7, t.i. tabulas rindas tiek nolasītas no dotā rindu identifikatoru saraksta ITMI, kas iegūts pēc darbības Ш = 4 veikšanas.
• 6. Tiek veikta autonoma darbība GO = 8 - visas tabulas secīga nolasīšana.
• 7. Tiek veikta nesaistīta savienojuma operācija GO = 2: savienošana tiek veikta, sajaucot operāciju bloku “E” un “E” rezultātus.
• 8. Operācijas GO = 2 rezultāts tiek pārnests uz operāciju GO = 1.
• 9. Tiek veikta nesaistītā sapludināšanas operācija GO = 1: tiek apkopoti un sakārtoti operācijas GO = 2 rezultātā iegūtie dati.
• 10. Tiek veikta operācija GO = 0. Iegūtā datu kopa tiek atgriezta.

Šie noteikumi, kas formulēti galvenajiem darbību veidiem, ir piemērojami lielākajai daļai BSGO vaicājuma izpildes plānu. Tomēr BSGO vaicājumos tiek izmantotas konstrukcijas, kas norāda uz turpmākajos noteikumos aprakstītās darbību secības pārkāpumu. Šādas situācijas var rasties, izmantojot, piemēram, apakšvaicājumus vai anti-join predikātus. Jebkurā gadījumā BSGO vaicājuma izpildes plāna interpretācijas process nenozīmē tikai vairāku noteikumu izmantošanu, kas nodrošinās visprecīzāko analīzi par to, ko optimizētājs gatavojas darīt, izpildot BSGO vaicājumu. Cits BSGO pieprasījums vienmēr ir individuāls gadījums; un tas, kā tas tiks izpildīts DBVS, ir atkarīgs no daudziem faktoriem, tostarp no DBVS versijas, operētājsistēmas versijas un veida, kurā DBVS gadījums ir izvietots, izmantotās aparatūras, 80b vaicājuma autora kvalifikācijas, utt.

1 msdevcon.ru #msdevcon

3 Olontsevs Sergejs SQL Server MCM, Kaspersky Lab MVP

4 Strukturētā vaicājuma valoda

5 Piemērs vaicājumam atlasiet pers.firstname, pers.uzvards, emp.darba nosaukums, emp.nationalidnumber no HumanResources. Darbinieks kā emp iekšējā pievienošanās Persona. Persona kā pers uz pers.businessentityid = emp.businessentityid kur pers.firstname un N"Jānis" emp.hiredate >= " "

6 Loģisko vaicājumu koks Projekta pers.firstname, pers.uzvards, emp.darba nosaukums, emp.nationalidnumber D A T A Filtrs Pievienoties pers.firstname = N"Jānis" un emp.hiredate >= " " pers.businessentityid = emp.pers.businessentityid Person.Pers. pers Saņemt datus Iegūt datus HumanResources.Employee as emp

7 Vaicājuma plāns Parāda, kā T-SQL vaicājums tiek izpildīts fiziskajā līmenī.

8 Vairāki veidi

9 DEMO Vienkāršs plāns Visu datu atlase no tabulas, kā iegūt vaicājuma plānu

11 Init() operatora metodes Init() metode liek fiziskajam operatoram inicializēt sevi un sagatavot visas nepieciešamās datu struktūras. Fiziskais operators var saņemt daudzus Init() zvanus, lai gan parasti tas saņem tikai vienu. GetNext() GetNext() metode liek fiziskajam operatoram iegūt pirmo vai nākamo datu rindu. Fiziskais operators var saņemt daudz GetNext() zvanu vai nesaņemt nevienu. Metode GetNext() atgriež vienu datu rindu, un to izsaukšanas reižu skaits ir norādīts ar ActualRows vērtību Showplan priekšraksta izvadē. Close() Kad tiek izsaukta metode Close(), fiziskais operators veic tīrīšanu un aizver. Fiziskais operators saņem tikai vienu zvanu uz Close ().

12 Operatoru mijiedarbība 1. operators 2. operators 3

13 Mijiedarbība starp operatoriem 1. Pieprasīt rindas operators 1 operators 2 operators 3

14 Mijiedarbība starp operatoriem 1. Pieprasījuma rinda 2. Pieprasījuma rindas operators 1 operators 2 operators 3

15 Mijiedarbība starp operatoriem 1. Pieprasīt rindu 2. Pieprasīt rindas operators 1 operators 2 operators 3 3. Sūtīt rindu

16 Mijiedarbība starp operatoriem 1. Pieprasīt rindu 2. Pieprasīt rindas operators 1 operators 2 operators 3 4. Sūtīt rindu 3. Sūtīt rindu

17 Mijiedarbība starp operatoriem 1. Pieprasījuma rinda 2. Pieprasījuma rindas operators 1 operators 2 operators 3 4. Sūtīt rindu 3. Sūtīt rindu

18 DEMO Operator TOP Vai kāpēc labāk operatoru saukt par iteratoru

19 Tabulas neeksistē!

20 HoBT Page 1 Page 2 Page 3 Page 4 1. rinda 3. rinda 5. rinda 7. rinda 2. rinda 4. rinda 6. rinda 8. rinda

21 HoBT lapa Lapa Lapa Lapa Lapa Lapa Lapa

22 DEMO Datu piekļuves operatori Scan, Seek, Lookup

23 Kam datu bāzē ir tikai viena tabula?

24 ligzdotas cilpas, jauktā savienošana un sapludināšanas pievienošanās

25 savienojuma operatori Nested Loops iekšējais savienojums, kreisais ārējais savienojums, kreisais pussavienojums, kreisais pussavienojums Sapludināšana Savienojums iekšējais savienojums, kreisais ārējais savienojums, kreisais pussavienojums, kreisais anti pussavienojums, labais ārējais savienojums, labais pussavienojums, labais anti pussavienojums , savienība Hash Pievienojieties visa veida loģiskām operācijām

26 DEMO Pievienošanās, kārtošana un pirmais operators Ligzdotas cilpas, sapludināšanas pievienošana, jauktā savienošana, kārtošana, pirmais operators

27 Brīdinājumi

28 DEMO Kļūdas un brīdinājumi vaicājumu plānos

29 Es zinu, ka es neko nezinu. Sokrats

30 DEMO Neliels piemērs kaut kam neskaidram

31 Vaicājumu plānu diagnostika — 10 populārākie vaicājumi, kas patērē visvairāk CPU, un to plāni atlasiet top(10) apakšvirkni (t.text, qs.statement_start_offset / 2, gadījums, kad qs.statement_end_offset = -1, tad len(t.text) else (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, cast(qs.total_worker_time / decimal(18, 2)) kā total_worker_time_ms, cast(qs.total_worker_time * 1. / count qs kā decimal execution (18, 2)) kā avg_worker_time_ms, cast(p.query_plan kā xml) kā query_plan no sys.dm_exec_query_stats kā qs cross apply sys.dm_exec_sql_text(qs.sql_handle) kā t cross_piemērot sys.dm_sque_plan. sākuma_nobīde , qs.statement_end_offset) kā p secība pēc qs.total_worker_time desc; aiziet

32 Lielu vaicājumu plānu lasīšanas paņēmieni Mēģiniet sadalīt tos loģiskos blokos un pakāpeniski analizēt. SSMS, kad plāns ir grafiski parādīts, apakšējā labajā stūrī tiek parādīta poga, kas atvieglo navigāciju vaicājumu plānā. Varat izmantot XQuery\XPath.

33 DEMO Liels vaicājumu plāns

35 DEMO SQL Sentry Plan Explorer

36 Apkoposim Pirmais operators Optimizācijas līmenis Kompilēšanas laiks Lielums kešatmiņā Parametri, kompilācijas vērtības;Iteratoru priekšlaicīgas pārtraukšanas iemesls Iteratoru izmaksas Vispirms apskatiet operatorus ar visaugstākajām izmaksām. Ņemiet vērā, ka tās ir tikai aptuvenās vērtības (pat faktiskajos izpildes plānos).

37 Apkoposim Bookmark\Key Lookup Ja to ir maz, tad visticamāk problēmu nav. Ja to ir daudz, pārklājuma indeksa izveide palīdzēs no tiem atbrīvoties. Brīdinājumi Jums jāpārbauda, ​​kāpēc tas notiek, un, ja nepieciešams, jārīkojas.

38 Apkoposim Savienojumi starp operatoriem (datu plūsmas) Jo biezāks savienojums, jo vairāk datu tiek nodots starp šiem operatoriem. Īpaši vērts pievērst uzmanību, ja kādā posmā datu plūsma strauji palielinās. Tabulu savienošanas secība Jo mazākas ir datu plūsmas, jo vieglāk tās savienot. Tāpēc, pirmkārt, ir jāsavieno tās tabulas, kuru rezultātā datu plūsma būs mazāka.

39 Kopsavilkums Skenēšana Skenēšana nenozīmē, ka pastāv problēma. Iespējams, ka tabulā nav pietiekami daudz indeksa, lai veiktu efektīvāku meklēšanu. Savukārt, ja nepieciešams atlasīt visu tabulu vai lielu tās daļu, skenēšana būs efektīvāka. Meklēšana nenozīmē, ka viss ir kārtībā. Problēmas var radīt liels skaits meklējumu negrupētos indeksos. Jebkas, ko nezināt par plānu, var radīt problēmas.

40 jautājumi

41 Kontakti Olontsevs Sergejs Kasperskis Lab

42 2013 Microsoft Corporation. Visas tiesības aizsargātas. Microsoft, Windows, Windows Vista un citu produktu nosaukumi ir vai var būt reģistrētas preču zīmes un/vai preču zīmes ASV. un/vai citās valstīs. Šeit sniegtā informācija ir paredzēta tikai informatīviem nolūkiem un atspoguļo pašreizējo Microsoft Corporation skatījumu uz šīs prezentācijas datumu. Tā kā korporācijai Microsoft ir jāreaģē uz mainīgajiem tirgus apstākļiem, to nevajadzētu interpretēt kā korporācijas Microsoft saistības, un Microsoft nevar garantēt pēc šīs prezentācijas datuma sniegtās informācijas precizitāti. MICROSOFT NESNIEDZ NEKĀDAS GARANTIJAS, TIEŠAS, NETIEŠAS VAI LIKUMĀ NOTEIKTAS ATTIECĪBĀ UZ ŠAJĀ Prezentācijā sniegto informāciju.