La storia è vecchia quanto il mondo. Due tavoli:

• Città - 100 città uniche.
• Persone - 10 milioni di persone. Per alcune persone, la città potrebbe non essere specificata.

Si desidera selezionare i primi 100 record Persone, ordinati per Cites.

Rimboccandoci le maniche, scriviamo allegramente:

Seleziona i primi 100 p.Nome, c.Nome come Città da Persone p
ordina per c.Nome

Questo ci darà qualcosa come:

Per... 6 secondi. (MS SQL 2008 R2, i5 / 4 Gb)

Ma com'è! Perché 6 secondi?! Sappiamo che nelle prime 100 voci ci sarà solo Almaty! Dopotutto ci sono 10 milioni di record, il che significa che ce ne sono 100mila per città, anche se non è così, possiamo scegliere la prima città della lista e verificare se conta almeno 100 abitanti.

Perché SQL Server, avendo statistiche, non fa questo:

Seleziona * da Persone p
a sinistra unisciti a Cities c su c.Id=p.CityId
dove p.CityId
in (seleziona il primo 1 ID da Città ordina per nome)
ordine di c.

Questa richiesta restituisce circa 100.000 record in meno di un secondo! Ci siamo assicurati di avere i 100 record richiesti e li abbiamo distribuiti molto, molto rapidamente.

Tuttavia, MSSQL fa tutto secondo i piani. E ha un progetto, “fusione pura” (c).

Domanda per gli esperti:
come è necessario correggere la query SQL o eseguire alcune azioni sul server per ottenere il risultato 10 volte più velocemente sulla prima query?

PS
CREA TABELLA . (


identificativo unico
SU
ANDARE

CREA TABELLA . (
identificatore univoco NON NULL,
nvarchar(50) NOT NULL,
SU
ANDARE

P.P.S.
Da dove crescono le gambe?
Il compito è molto reale. C'è una tabella con l'entità principale, da essa partono molte dimensioni secondo il principio della "stella". L'utente deve visualizzarlo nella griglia, fornendo l'ordinamento per campi.
Partendo da una certa dimensione della tabella principale, l'ordinamento si riduce a selezionare una finestra con gli stessi (estremi) valori (tipo "Almaty"), ma il sistema inizia a rallentare terribilmente.
Vorrei avere UNA query parametrizzata che funzioni in modo efficace sia con una piccola tabella People che con una grande.

P.P.P.S
È interessante notare che se City fosse NotNull e InnerJoin è stato utilizzato, la richiesta viene eseguita immediatamente.
È interessante notare che ANCHE SE il campo City fosse NotNull ma fosse stato utilizzato LeftJoin, la richiesta rallenta.

Idea nei commenti: seleziona prima tutti i valori InnerJoin e poi Union by Null. Domani controllerò questa e altre idee folli)

P.P.P.S ci ho provato. Ha funzionato!

CON Aiuto AS
seleziona i primi 100 p.Nome, c.Nome come Città da Persone p
INNER unisciti a Cities c su c.Id=p.CityId
ordine per c.Nome ASC
UNIONE
seleziona i primi 100 p.Nome, NULL come Città da Persone p
DOVE p.CityId È NULL
SELEZIONA TOP 100 * DA aiuto

Dà 150 millisecondi nelle stesse condizioni! Grazie

6 risposte

Esistono diversi modi per ottenere un piano di esecuzione, quale utilizzare dipenderà dalle circostanze. In genere è possibile utilizzare SQL Server Management Studio per ottenere il piano, tuttavia, se per qualche motivo non è possibile eseguire la query in SQL Server Management Studio, potrebbe essere utile ottenere il piano tramite SQL Server Profiler o controllando il piano cache.

Metodo 1: utilizzo di SQL Server Management Studio

Ci sono alcune caratteristiche interessanti in SQL Server che semplificano la raccolta di un piano di esecuzione, assicurati solo che la voce di menu "Includi piano di esecuzione effettivo" (che si trova nel menu "Query") sia selezionata ed eseguirà il tuo normalmente.

Se stai cercando di ottenere il piano di esecuzione per le istruzioni in una procedura memorizzata, devi eseguire la procedura memorizzata, in questo modo:

Esegui p_Esempio 42

Al termine della query, nel riquadro dei risultati verrà visualizzata una scheda aggiuntiva "Piano di esecuzione". Se hai eseguito molte approvazioni, potresti vedere molti piani visualizzati in questa scheda.

Qui puoi controllare il piano di esecuzione in SQL Server Management Studio o fare clic con il pulsante destro del mouse sul piano e selezionare "Salva piano di esecuzione con nome..." per salvare il piano in un file XML.

Metodo 2: utilizzo delle opzioni SHOWPLAN

Questo metodo è molto simile al metodo 1 (in realtà è ciò che SQL Server Management Studio fa internamente), tuttavia l'ho incluso per completezza o se non si dispone di SQL Server Management Studio.

Prima di eseguire la query, eseguire uno seguenti operatori. L'istruzione deve essere l'unica istruzione nel batch, ad es. Non puoi eseguire un'altra istruzione contemporaneamente:

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILO ON SET STATISTICS XML ON -- Questa è l'opzione consigliata da usare

Queste sono opzioni di connessione, quindi devi eseguirle solo una volta per ogni connessione. D'ora in poi, tutte le istruzioni in esecuzione saranno seguite da insieme aggiuntivo di risultati contenente il tuo piano di esecuzione nel formato desiderato: esegui la query come al solito per vedere il piano.

Una volta terminato, puoi disabilitare questa impostazione con la seguente istruzione:

IMPOSTARE<> SPENTO

Confronto dei formati del piano di esecuzione

Se hai una forte preferenza, ti consiglio di usare l'opzione STATISTICS XML. Questa opzione equivale all'opzione "Includi piano di esecuzione effettivo" in SQL Server Management Studio e fornisce la maggior parte delle informazioni nel formato più conveniente.

• SHOWPLAN_TEXT - Visualizza un piano di esecuzione stimato di base basato su testo senza eseguire la query
• SHOWPLAN_ALL - Visualizza un piano di esecuzione stimato basato su testo con un costo stimato senza eseguire la query
• SHOWPLAN_XML - Visualizza un piano di esecuzione stimato basato su XML con un costo stimato senza eseguire la query. Equivale all'opzione "Visualizza piano di esecuzione stimato..." in SQL Server Management Studio.
• PROFILO STATISTICHE - Esegue una query e visualizza il piano di esecuzione effettivo in base al testo.
• STATISTICS XML - Esegue una query e visualizza il piano di esecuzione effettivo basato su XML. Ciò equivale all'opzione "Includi piano di esecuzione effettivo" in SQL Server Management Studio.

Metodo 3: utilizzo di SQL Server Profiler

Se non riesci a eseguire la query direttamente (o la query non viene eseguita lentamente quando la esegui direttamente, ricorda che desideriamo che il piano di query funzioni male), puoi correggere il piano utilizzando la traccia di SQL Server Profiler. L'idea è di eseguire la query mentre è in esecuzione una traccia che acquisisce uno degli eventi "Showplan".

Si prega di notare che a seconda del carico si Puoi usa questo metodo in un ambiente di produzione, tuttavia dovresti ovviamente stare attento. I meccanismi di profilatura di SQL Server sono progettati per ridurre al minimo l'impatto sul database, ma ciò non significa che non ci sarà un impatto sulle prestazioni. Potresti anche avere problemi a filtrare e determinare il piano corretto nella tua traccia se il tuo database è molto utilizzato. Ovviamente devi verificare con il tuo DBA per assicurarti che siano felici che lo stai facendo sul tuo prezioso database!

• Aprire SQL Server Profiler e creare una nuova traccia che si connetta al database da cui si desidera acquisire la traccia.
• Nella scheda "Selezione evento", seleziona la casella di controllo "Mostra tutti gli eventi", seleziona la riga "Prestazioni" → "Showplan XML" ed esegui la traccia.
• Durante l'esecuzione della traccia, fai tutto il necessario per eseguire la query lenta.
• Attendere il completamento della richiesta e l'interruzione della traccia.
• Per salvare la traccia, fare clic con il pulsante destro del mouse sul piano xml nel profilo di SQL Server e selezionare "Recupera dati evento..." per salvare il piano in un file XML.

Il piano che ottieni equivale all'opzione "Includi piano di esecuzione effettivo" in SQL Server Management Studio.

Metodo 4: controllo della cache delle query

Se non puoi eseguire direttamente la tua query e non puoi nemmeno acquisire una traccia del profiler, puoi comunque ottenere un piano stimato controllando il piano della cache della query SQL.

Verifichiamo la cache del piano eseguendo query sui DMV di SQL Server. Di seguito è riportata una query di base che elencherà tutti i piani di query memorizzati nella cache (come xml) insieme al relativo testo SQL. Nella maggior parte dei database, dovrai anche aggiungere ulteriori condizioni di filtro per filtrare i risultati fino ai piani che ti interessano.

SELEZIONA UseCounts, Cacheobjtype, Objtype, TEXT, query_plan DA sys.dm_exec_cached_plans APPLICAZIONE CROSS sys.dm_exec_sql_text(plan_handle) APPLICAZIONE CROSS sys.dm_exec_query_plan(plan_handle)

Esegui questa query e fai clic sul piano XML per aprire il piano in una nuova finestra: fai clic con il pulsante destro del mouse e seleziona "Salva piano di esecuzione con nome..." per salvare il piano in un file XML.

Appunti:

Poiché ci sono così tanti fattori (che vanno dalla tabella e dallo schema dell'indice ai dati archiviati e alle statistiche della tabella), dovresti sempre prova a ottenere il piano di esecuzione dal database che ti interessa (di solito quello che sta riscontrando un problema di prestazioni).

Non è possibile correggere il piano di esecuzione per le stored procedure crittografate.

piani di esecuzione "reali" e "stimati".

Il piano di esecuzione effettivo è il punto in cui SQL Server esegue effettivamente la query, mentre il piano di esecuzione stimato di SQL Server funziona su ciò che potrebbe fare senza eseguire la query. Sebbene logicamente equivalente, il piano di esecuzione effettivo è molto più utile perché contiene dati e statistiche aggiuntivi su ciò che è effettivamente accaduto quando è stata eseguita la query. Questo è importante quando si diagnosticano problemi quando le valutazioni del server SQL sono disabilitate (ad esempio, quando le statistiche non sono aggiornate).

Come interpretare il piano di esecuzione delle query?

È un tema abbastanza degno per un libro gratuito.

Oltre alla risposta esauriente già pubblicata a volte, è utile poter accedere al piano di esecuzione a livello di codice per estrarre informazioni. Il codice di esempio per questo è di seguito.

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

Il mio strumento preferito per ottenere e analizzare in modo approfondito i piani di esecuzione delle query è Esplora il piano di sentinella SQL. È molto più conveniente, conveniente e completo per l'analisi dettagliata e la visualizzazione dei piani di esecuzione rispetto a SSMS.

Ecco una schermata di esempio per farti un'idea di quali funzionalità offre lo strumento:

Questa è solo una delle visualizzazioni disponibili nello strumento. Si noti l'insieme di schede nella parte inferiore della finestra dell'applicazione, che consente di ottenere diversi tipi di visualizzazioni del piano di esecuzione e utili informazioni aggiuntive.

Inoltre, non ho notato alcuna restrizione nella sua versione gratuita che ne impedisca l'uso quotidiano o ti costringa ad acquistare la versione Pro alla fine. Quindi, se preferisci restare con la versione gratuita, non ti è precluso nulla.

Oltre ai metodi descritti nelle risposte precedenti, puoi anche utilizzare il visualizzatore del piano di esecuzione gratuito e lo strumento di ottimizzazione delle query ApexSQL Plan (che mi sono imbattuto di recente).

È possibile installare e integrare il piano ApexSQL in SQL Server Management Studio in modo che i piani di esecuzione possano essere visualizzati direttamente da SSMS.

Visualizzazione dei piani di esecuzione predittivi in ​​ApexSQL Plan

• Fare clic sul pulsante Nuova richiesta in SSMS e incollare il testo della query nella casella di testo della query. Fare clic con il pulsante destro del mouse e selezionare "Mostra piano di esecuzione stimato" dal menu contestuale.

1. Il grafico del piano di esecuzione mostrerà la scheda Pianificazione dell'esecuzione nella sezione dei risultati. Quindi fare clic con il pulsante destro del mouse sul piano di esecuzione e selezionare "Apri in ApexSQL Plan" dal menu di scelta rapida.

1. Il piano di esecuzione stimato verrà aperto in ApexSQL Plan e può essere analizzato per l'ottimizzazione delle query.

Visualizzazione dei piani di esecuzione effettivi nel piano ApexSQL

Per visualizzare il piano di esecuzione della query effettivo, vai al secondo passaggio menzionato in precedenza, ma ora, una volta visualizzato il piano stimato, fai clic sul pulsante "Attuale" sulla barra multifunzione principale nel piano ApexSQL.

Facendo clic sul pulsante Effettivo verrà visualizzato il piano di esecuzione effettivo con un'anteprima dettagliata dei parametri di costo insieme ad altri dettagli del piano di esecuzione.

Ulteriori informazioni sulla visualizzazione dei piani di esecuzione sono disponibili seguendo questo collegamento.

I piani di query possono essere recuperati da una sessione di eventi estesi tramite l'evento query_post_execution_showplan. Ecco un esempio di una sessione XEvent:

/* Generato tramite il modello "Tracciamento dei dettagli della query". */ CREA SESSIONE EVENTO SUL SERVER AGGIUNGI EVENTO 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_frame,sqlserver.tsqlserver.tsqlserver.tsql)), / * Rimuovere uno qualsiasi dei seguenti eventi (o includere eventi aggiuntivi) come desiderato. */ AGGIUNGI EVENTO 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,sqlserver.tsql_frame,.) DOVE (.(.,(4)) E .(.,(0)))), AGGIUNGI EVENTO 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) DOVE (.(.,(4)) E .(.,(0)))), AGGIUNGI EVENTO DOVE (.( .,(4)) E .(.,(0)))), AGGIUNGI EVENTO sqlserver.sp_statement_completed(SET collect_object_name=(1) AZIONE (pacchetto0.sequenza_evento,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) DOVE (.(.,) ) E .(.,(0)))), AGGIUNGI EVENTO 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_stack) DOVE (.(.,(4)) E .(.,(0)))), AGGIUNGI EVENTO 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.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) DOVE (.(.,(4)) E .(.,(0)) )) AGGIUNGI pacchetto TARGET0.ring_buffer CON (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECON DS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NESSUNO,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) VAI

Una volta creata la sessione (in SSMS), vai su Esplora oggetti e vai su Gestisci | Eventi estesi | Sessioni. Fare clic con il pulsante destro del mouse sulla sessione "GetExecutionPlan" ed eseguirla. Fare clic con il tasto destro e selezionare "Guarda dati in tempo reale".

Quindi apri una nuova finestra di query ed esegui una o più query. Eccone uno per AdventureWorks:

USA AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Sconti = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY Nome Prodotto DESC; ANDARE

Dopo un minuto o due, dovresti vedere alcuni risultati nella scheda "GetExecutionPlan: Live Data". Seleziona uno degli eventi query_post_execution_showplan nella griglia, quindi fai clic sulla scheda Query Plan sotto la griglia. Dovrebbe assomigliare a qualcosa di simile a questo:

MODIFICARE: Il codice XEvent e lo screenshot sono stati generati da SQL/SSMS 2012 con SP2. Se stai usando SQL 2008/R2 puoi impostare uno script per eseguirlo. Ma questa versione non ha una GUI, quindi dovrai estrarre il file XML di showplan, salvarlo come file *.sqlplan e aprirlo in SSMS. È ingombrante. XEvents non esisteva in SQL 2005 o versioni precedenti. Quindi, se non sei su SQL 2012 o versioni successive, suggerirei vivamente una delle altre risposte pubblicate qui.

1 msdevcon.ru #msdevcon

3 Sergey Olontsev SQL Server MCM, MVP Kaspersky Lab

4 Linguaggio di query strutturato

5 Esempio di query select pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber da HumanResources.Employee as emp inner join Person.Person as pers on pers.businessentityid = emp.businessentityid dove pers.firstname = N"John" e emp.noleggio >= " "

6 Logical Query Tree Project pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber Filtro DATI Unisci pers.firstname = N"John" e emp.hiredate >= " " pers.businessentityid = emp.businessentityid Person.Person as pers Ottieni dati Ottieni dati HumanResources.Employee as emp

7 Query Plan Mostra come una query T-SQL viene eseguita a livello fisico.

8 modi multipli

9 DEMO Piano semplice Seleziona tutti i dati dalla tabella come ottenere il piano di query

11 Metodi dell'operatore Init() Il metodo Init() fa in modo che l'operatore fisico si inizializzi e prepari tutte le strutture dati necessarie. Un operatore fisico può ricevere molte chiamate Init(), sebbene di solito ne riceva solo una. GetNext() Il metodo GetNext() fa in modo che l'operatore fisico ottenga la prima o la successiva riga di dati. Un operatore fisico può ricevere molte chiamate GetNext() o nessuna. Il metodo GetNext() restituisce una riga di dati e il numero di chiamate ad essa viene mostrato dal valore ActualRows nell'output dell'istruzione Showplan. Close() Quando viene chiamato il metodo Close(), l'operatore fisico esegue alcune operazioni di pulizia e chiude. Un operatore fisico riceve solo una chiamata Close().

12 Interazione tra operatori Operatore 1 Operatore 2 Operatore 3

13 Interazione tra operatori 1. Riga di richiesta Operatore 1 Operatore 2 Operatore 3

14 Interazione tra operatori 1. Riga di richiesta 2. Riga di richiesta Operatore 1 Operatore 2 Operatore 3

15 Interazione tra operatori 1. Riga di richiesta 2. Riga di richiesta Operatore 1 Operatore 2 Operatore 3 3. Riga di invio

16 Interazione tra operatori 1. Riga di richiesta 2. Riga di richiesta Operatore 1 Operatore 2 Operatore 3 4. Riga di invio 3. Riga di invio

17 Interazione tra operatori 1. Riga di richiesta 2. Riga di richiesta Operatore 1 Operatore 2 Operatore 3 4. Riga di invio 3. Riga di invio

18 DEMO L'operatore TOP Ovvero perché è meglio chiamare l'operatore un iteratore

19 Le tabelle non esistono!

20 HoBT Pagina 1 Pagina 2 Pagina 3 Pagina 4 Riga 1 Riga 3 Riga 5 Riga 7 Riga 2 Riga 4 Riga 6 Riga 8

21 HoBT Pagina Pagina Pagina Pagina Pagina Pagina Pagina

22 DEMO Operatori di accesso ai dati Scansione, ricerca, ricerca

23 Chi ha una sola tabella in un database?

24 loop nidificati, unisci hash e unisci unisci

25 Loop nidificati join interno, join esterno sinistro, semi join sinistro, anti semi join sinistro Unisci join interno, join esterno sinistro, semi join sinistro, anti semi join sinistro, join esterno destro, semi join destro, semi join destro, union Hash Unisciti a tutti i tipi di operazioni logiche

26 DEMO Loop nidificati, unisci unisci, unisci hash, ordina, primo operatore

27 Avvertenze

28 DEMO Errori e avvisi nei piani di query

29 So di non sapere nulla. Socrate

30 DEMO Un piccolo esempio di oscuro

31 Diagnostica del piano di query -- Le prime 10 query che consumano più CPU e i relativi piani selezionano top(10) substring(t.text, qs.statement_start_offset / 2, case when qs.statement_end_offset = -1 then len(t.text) else (qs.statement_end_offset - qs.statement_start_offset) / 2 fine), 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)) come avg_worker_time_ms, cast(p.query_plan come xml) come query_plan da sys.dm_exec_query_stats come 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_offset , qs.statement_end_offset) come p ordine per qs.total_worker_time desc; andare

32 Tecniche per la lettura di piani di query di grandi dimensioni Cercare di suddividere in blocchi logici e analizzare gradualmente. In SSMS, quando il piano viene visualizzato graficamente, nell'angolo inferiore destro viene visualizzato un pulsante per facilitare la navigazione nel piano di query. Puoi usare XQuery\XPath.

33 DEMO Ampio piano di query

35 DEMO Esplorazione del piano di sentinella SQL

36 Riepilogo Prima istruzione Livello di ottimizzazione Tempo di compilazione Dimensioni nella cache Parametri, valori di compilazione Motivo dell'interruzione anticipata Costo degli iteratori Osservare prima le dichiarazioni di costo più elevate. Tieni presente che queste sono solo ipotesi (anche nei piani di esecuzione effettivi).

37 Riepilogo Segnalibro\Ricerca chiave Se ce ne sono pochi, molto probabilmente non ci sono problemi. Se ce ne sono molti, creare un indice di copertura ti aiuterà a sbarazzartene. Avvisi Verificare il motivo per cui si verifica e intervenire se necessario.

38 Riepilogo Connessioni tra istruzioni (flussi di dati) Più spessa è la connessione, più dati passano tra queste istruzioni. Vale soprattutto la pena prestare attenzione se a un certo punto il flusso di dati aumenta notevolmente. Ordine di unione delle tabelle Più piccoli sono i flussi di dati, più facile è unirli. Pertanto, prima di tutto, è necessario unire quelle tabelle il cui flusso di dati risultante sarà inferiore.

39 Riepilogo delle scansioni Le scansioni non indicano che c'è un problema. È possibile che non ci sia abbastanza indice sul tavolo per fare di più ricerca efficiente. Se invece devi selezionare tutta o la maggior parte della tabella, la scansione sarà più efficiente. La ricerca non significa che tutto va bene. Un gran numero di ricerche su indici non cluster può rappresentare un problema. Tutto ciò che non sai sul piano potrebbe potenzialmente essere un problema.

40 domande

41 Contatti Sergey Olontsev Kaspersky Lab

42 2013 Microsoft Corporation. Tutti i diritti riservati. Microsoft, Windows, Windows Vista e altri nomi di prodotti sono o possono essere marchi e/o marchi registrati negli Stati Uniti. e/o altri paesi. Le informazioni qui contenute sono solo a scopo informativo e rappresentano la visione corrente di Microsoft Corporation alla data di questa presentazione. Poiché Microsoft deve rispondere alle mutevoli condizioni del mercato, non deve essere interpretato come un impegno da parte di Microsoft e Microsoft non può garantire l'accuratezza delle informazioni fornite dopo la data di questa presentazione. MICROSOFT NON FORNISCE ALCUNA GARANZIA, ESPRESSA, IMPLICITA O LEGALE, RELATIVAMENTE ALLE INFORMAZIONI CONTENUTE IN QUESTA PRESENTAZIONE.

Alexander Kuklin ha scritto un eccellente articolo “Plan Cache and Query Parameterization. Parte 1. Pianifica l'analisi della cache. Consiglio a tutti di fare conoscenza.

Eccone un piccolo estratto:

Il Query Processor, che è responsabile dell'esecuzione delle query SQL ricevute dal server SQL e della restituzione dei risultati al client, è costituito da due componenti principali:

1. Ottimizzatore di query.
2. Esecutore della richiesta (motore relazionale).

Poiché l'istruzione SELECT non definisce i passaggi esatti che SQL Server deve eseguire per fornire al client i dati richiesti, SQL ServerSQL Server deve analizzare l'istruzione stessa e determinare il modo più efficiente per estrarre i dati richiesti. Innanzitutto, l'istruzione viene elaborata da Query Optimizer, in cui i seguenti passaggi vengono eseguiti utilizzando i componenti dell'ottimizzatore:

1. Il parser esamina l'istruzione SELECT e la scompone in unità logiche come parole chiave, espressioni, operatori e identificatori e normalizza la query.
2. Dal parser, i dati entrano nell'input del componente Algebrizer, che esegue l'analisi semantica del testo. Algebrizer verifica l'esistenza degli oggetti del database e dei relativi campi specificati nella richiesta, il corretto utilizzo degli operatori e delle espressioni di query ed estrae i letterali dal codice della richiesta per consentire l'utilizzo della parametrizzazione automatica.
   Ad esempio, ecco perché una query che ha una sezione SELEZIONA campi, che non sono contenuti né nelle funzioni aggregate né nella sezione GROUP BY, passeranno il controllo di SQL Server Management Studio (SSMS) tramite Ctrl + F5 (analisi), ma non riusciranno con un errore quando si tenta di eseguire con F5 (non passare l'analisi semantica).
3. Successivamente, Algebrizer crea un albero di analisi della query che descrive i passaggi logici necessari per trasformare i dati di input nel risultato desiderato. Per l'albero delle query, vengono estratti i metadati dell'oggetto query (tipi di dati, statistiche dell'indice e così via), vengono eseguite conversioni di tipo implicito (se necessario), vengono rimosse le operazioni ridondanti (ad esempio, join di tabelle non necessarie o ridondanti).
4. Query Optimizer analizza quindi i vari modi in cui è possibile accedere alle tabelle di origine. E sceglie una serie di passaggi che secondo l'ottimizzatore restituiranno i risultati più velocemente e utilizzeranno la minor quantità di risorse. La sequenza di questi passaggi derivati ​​viene registrata nell'albero della query e un piano di esecuzione della query viene generato dalla versione finale ottimizzata dell'albero.

Successivamente, il piano di esecuzione della query risultante viene archiviato nella cache del piano. E l'esecutore della query, in base alla sequenza di istruzioni (passaggi) specificata nel piano di esecuzione, richiede i dati richiesti dal sottosistema di archiviazione, li converte nel formato specificato per il set di dati risultante e li restituisce al client.

Probabilmente, ogni soprannome 1C poneva la domanda "che è più veloce, connessione o condizione in DOVE?" o, ad esempio, "crea una query nidificata o inserisci l'operatore B()"?

Dopodiché, il nick 1C va al forum e lì gli dicono: devi guardare il piano di query. Guarda, e senza capire nulla, lancia per sempre l'idea di ottimizzare le query attraverso i piani, continuando a confrontare le opzioni con una semplice misurazione delle prestazioni.

Di conseguenza, sulla macchina dello sviluppatore, la richiesta inizia a volare, e quindi nella base di combattimento, quando il numero di record aumenta, tutto muore e iniziano i reclami nello stile di "1C rallenta". Un'immagine familiare, non è vero?

In questo articolo, non ti fornirò istruzioni esaurienti per leggere i piani di query. Ma cercherò di spiegare in modo intelligibile - che cos'è e da quale parte avvicinarli.

Inoltre, non mi considero un buon ottimizzatore di query, quindi nell'articolo sono molto probabili stipiti fattuali. Bene, allora lascia che i guru mi correggano nei commenti. Ecco perché siamo qui come comunità per aiutarci a vicenda, giusto?

Se sai già come leggere i piani di query, probabilmente dovresti saltare l'articolo. Qui sarà il più semplice e dall'inizio. L'articolo è rivolto a quegli sviluppatori che non hanno ancora capito che tipo di bestia sia: un piano di query.

Come funziona un computer

E comincio da lontano. Il fatto è che i computer a cui siamo abituati non sono così intelligenti. Ricordi probabilmente le prime lezioni di informatica, o i corsi junior dell'università? Ricordi di ordinare gli array con una bolla lì o di leggere un file riga per riga? Quindi, non è stato inventato nulla di fondamentalmente nuovo nei moderni DBMS relazionali.

Se nei laboratori leggi le righe da un file e poi le scrivi in ​​un altro posto, allora hai già un'idea approssimativa di come funziona un moderno DBMS. Sì, certo, lì è tutto molto (molto) più complicato, ma - sono cicli in Africa, cicli, la lettura del disco non è ancora diventata più veloce della lettura della RAM e gli algoritmi O (N) sono ancora più lenti di O (1 ) algoritmi al crescere di N.

Immaginiamo che una persona sia venuta da te, un semplice soprannome 1C, e abbia detto: "Guarda, amico, devi scrivere un database. Ecco un file, scrivici alcune righe. E poi leggi da lì". Facciamo finta che non puoi rifiutare. Come risolveresti questo problema?

E lo risolveresti nello stesso modo in cui lo risolvono i ragazzi di Microsoft, Oracle, Postgres e 1C. Dovresti aprire il file nel tuo linguaggio di programmazione, leggere le righe da lì e stamparle sullo schermo. Il mondo non ha escogitato algoritmi fondamentalmente diversi da quelli che ho già descritto.

Immagina di avere 2 file. Uno contiene le controparti e l'altro contiene gli accordi di controparte. Come implementeresti un'operazione INNER JOIN? Proprio sulla fronte, senza ottimizzazioni?

Controparti

Trattati

Saltiamo ora le sfumature dell'apertura di file e della lettura in memoria per semplicità. Concentriamoci sull'operazione di unione. Come lo faresti? io farei così:

Per ogni StringContractor dal ciclo dell'appaltatore Per ogni StringAgreement dai contratti Ciclo Se StringContract.IDContractor = StringContractor.ID Quindi OutputConnectionResult(StringContractor, StringContract); Finisci se; ciclo finale; ciclo finale;

IN f-io esempio PrintJoinResult visualizzerà semplicemente tutte le colonne delle righe indicate. Il suo codice non è essenziale qui.

Quindi vediamo due loop nidificati. Esterno su una tabella, quindi in interno: ricerca di una chiave dall'esterno mediante una semplice ricerca. E ora, all'improvviso, se apri un piano di query con un JOIN in uno qualsiasi dei DBMS 1C, con una probabilità abbastanza alta vedrai il costrutto "Nested Loops" lì. Se traduciamo questo dalla lingua di un potenziale avversario nella nostra, otteniamo "Nested loops". Cioè nel "piano delle query" il DBMS ti spiega che qui, per la "connessione", ha applicato l'algoritmo sopra descritto. Questo algoritmo può essere scritto da qualsiasi scolaretto di circa la 7a elementare. E potenti DBMS da combattimento di livello mondiale usano questo algoritmo con calma. Perché in alcune situazioni - è il migliore che ci sia in assoluto.

E in generale, perché ho iniziato subito con la "connessione". Supponiamo che tu debba solo trovare una controparte per nome. Come risolveresti questo problema? Qui hai un file con gli appaltatori. Scrivi un algoritmo. Lo scrivo così:

Per ogni StringContractor dal ciclo dell'appaltatore Se StringContractor.Name = "Ivanov" Allora DisplayResult(StringContractor); Finisci se; ciclo finale;

No, sul serio, in quale altro modo puoi scriverlo? Ma non in sostanza. Se non è noto in quale ordine giacciono i record nella tabella, dovrai rivedere tutto, qualunque cosa si possa dire. Nel linguaggio del piano di query, questo è chiamato Scan. Scansione. Visualizzazione completa dei dati e nient'altro.

Indici

Ma come possiamo velocizzare la ricerca dei dati nella tabella? Bene, la verità è che rivedere tutto continuamente è una specie di male.

Richiamare l'archivio della clinica o della biblioteca. Come viene eseguita la ricerca per nome del cliente? Negli armadietti di legno ci sono carte pulite con lettere dalla A alla Z. E il paziente "Pupkin" è nell'armadietto con la carta "P". Non è necessario guardare tutte le altre lettere di seguito. Se ordiniamo i dati nella nostra tabella e sappiamo dove (sotto quali numeri di riga) abbiamo voci che iniziano con la lettera "P", ci avvicineremo in modo significativo alle prestazioni della zia dal registro. Ed è meglio della forza bruta, vero?

Quindi, la parola "Indice" in questo contesto significa (di nuovo, tradotta dalla lingua di un potenziale nemico) "Sommario". Per trovare rapidamente un capitolo in un libro, vai al sommario, trova lì il titolo del capitolo, quindi guarda il numero di pagina e vai direttamente a quella pagina.

Quando il database ha bisogno di trovare un record in una tabella, va al sommario, guarda il nome dell'account, guarda il numero del record sotto il quale si trova e va all'area desiderata del file di dati immediatamente dopo questo record.

Nel codice, potrebbe assomigliare a questo:

Indice = Nuova corrispondenza; // bla blaNumeroRecord = Indice["Ivanov"] OutputResult(TableAccounts[NumeroRecord]);

È noto che i miracoli non accadono, quindi la memoria sotto l'"Indice" della partita, così come la ricerca nella partita stessa, non sono operazioni gratuite. Ma sono molto più economici di un'enumerazione diretta di tutti i dati. Oh sì, questa conformità dovrà essere costantemente aggiornata quando si aggiungono o si modificano i dati di base.

Ora pensiamo, come implementeresti questo indice stesso? È possibile memorizzare i record nel file di dati immediatamente in forma ordinata. E tutto andrebbe bene, ma, in primo luogo, è necessario cercare ogni volta in campi diversi e, in secondo luogo, se l'utente desidera inserire un record con la lettera M nella tabella già compilata dalla A alla Z? Ma lo farà, te lo assicuro.

Ricordiamo come viene generalmente eseguita la scrittura su un file.

fsee(file, posizione); // salta all'indirizzo desiderato write(file, dataArray, dataLength); // scrive byte dataLength da dataArray

Se l'indirizzo di posizione punta da qualche parte nel mezzo del file e ci sono dati in questo posto, vengono sovrascritti con quelli nuovi. Se è necessario inserire qualcosa nel mezzo di un file (incluso un array in memoria), è necessario "spostare" esplicitamente tutto ciò che si trova dopo la posizione, liberando spazio e solo successivamente scrivere nuovi dati. Come capisci, il "movimento" dei dati è di nuovo cicli e operazioni di input / output. Voglio dire, non così in fretta. Non succede nulla sul computer stesso. Tutto a comando.

Torniamo all'indice. L'utente vuole inserire qualcosa nel mezzo. Che ti piaccia o no, dovrai spostare i dati o escogitare di archiviare i dati in "pagine" collegate tra loro in un elenco. Scrivi fisicamente fino alla fine, o in uno spazio vuoto, ma come al centro del tavolo. E quindi aggiorna i numeri di record nel sommario. Ora si sono spostati e l'indice mostra il posto sbagliato. Probabilmente hai sentito che gli indici del database accelerano le ricerche, ma rallentano gli inserimenti e le eliminazioni. Ora, sai perché è così.

Bene, non abbiamo ancora risolto il problema della ricerca in diversi campi. Non possiamo memorizzare i dati in un file in un ordine diverso. Un utente per nome e un altro, diciamo, per data. E allo stesso tempo. Come risolveresti questo problema? A mio parere, la soluzione è ovvia: è necessario archiviare i dati separatamente e il sommario separatamente, ordinati in base ai campi obbligatori. Quelli. i dati vengono archiviati nel database come dovrebbe essere, ma fianco a fianco creeremo un file in cui i record sono ordinati per nome. Questo sarà un indice nel campo "Nome". E accanto ad esso ci sarà un altro file simile, ma ordinato per il campo "Data". Per risparmiare spazio, memorizzeremo negli indici non tutte le colonne della tabella principale, ma solo quelle tramite le quali viene eseguito l'ordinamento (per cercare velocemente qui, trovare il numero del record e passare immediatamente ad esso per leggere il resto dei dati) .

Anche i ragazzi che scrivono DBMS per adulti non hanno trovato niente di meglio. Gli indici in un DB sono disposti così. Tutti i dati della tabella vengono ordinati fianco a fianco in un'entità separata. Fondamentalmente, un indice è solo un'altra tabella. E occupa spazio in proporzione alle dimensioni del tavolo principale, il che è logico. Sì, ci sono ancora vari trucchi, come alberi bilanciati e tutto il resto, ma il significato non cambia molto.

A proposito, se scrivi i dati nella tabella principale immediatamente ordinata, non puoi creare un indice memorizzato separatamente e considerare la tabella dei dati stessa come un indice. È fantastico, vero? Tale indice è chiamato "cluster". È logico che il campo in base al quale vengono ordinati i record nella tabella cerchi di crescere in modo monotono. Ricordi l'inserto al centro, vero?

Pianificazione dell'esecuzione delle query

Immagina di avere una tabella con cinque milioni di record. E lei ha un indice. Dobbiamo trovare rapidamente un record con la parola "Ciao". Immagina anche di avere la stessa tabella, ma con tre record. E devi anche trovare "Ciao". Quale metodo di ricerca scegliere? Apri il file indice, eseguilo con una ricerca binaria, trova il numero di record desiderato, apri il file della tabella principale, salta al record in base al suo numero, leggilo? O eseguire un ciclo da uno a tre, controllando ogni voce rispetto a una condizione? Computer moderno i cicli da uno a tre si comportano in modo orribile, quanto velocemente.

Per prendere una decisione, il pianificatore di query deve capire di cosa si tratta. Opera con una cosa come la statistica. Le statistiche includono il numero di record nelle tabelle, la distribuzione dei dati nelle colonne, la selettività e così via. Questi sono tutti suggerimenti per il pianificatore su come raccogliere i dati sarà più veloce. Non il più veloce possibile, ma almeno abbastanza veloce con una certa probabilità. E il pianificatore ha poco tempo per prendere una decisione. vogliamo ottenere rapidamente i dati e non aspettare che si pianifichi lì.

Qui, non intraprenderei il lavoro di scrivere un pianificatore senza prima difendere una tesi. Come lavori lì e come riesca a farlo abbastanza tollerabilmente - non lo so. Pertanto, ci limitiamo alla documentazione del DBMS. Ne consegue che, sulla base delle statistiche, lo scheduler costruisce diverse opzioni possibili per l'esecuzione di query passo dopo passo, quindi seleziona da esse quella più adatta. Ad esempio, il primo disponibile. È anche un'euristica, vero?

"Cosa devo fare prima", pensa il pianificatore: "attraversare l'intera tabella A, selezionando i record per condizione, quindi unirmi alla tabella B con cicli nidificati, oppure trovare tutti i record corrispondenti della tabella B per indice, e solo dopo eseguire attraverso la tabella A”? Ciascuno dei passaggi ha un certo peso o costo. Più alto è il costo, più è difficile da eseguire. Il piano di query scrive sempre il costo di ciascuno dei passaggi eseguiti dal motore DBMS per raccogliere i risultati della query.

Pianificare il dispositivo dell'operatore

Ogni passaggio del piano di query viene implementato come un oggetto, il cui input è un set di record e l'output è un altro set. Se lo inserisci nel codice, risulta che l'operatore del piano di query è un'implementazione di un'interfaccia astratta con un unico metodo:

Interfaccia IQueryOperator ( DataRow GetNextRow(); )

Per coloro che non capiscono cosa c'è scritto qui, spiegherò. Ogni istruzione del piano di query ha un metodo "GiveNextEntry". Il motore DBMS richiama l'operatore per questo metodo e, con ciascuno di tali pull, aggiunge il record risultante al risultato della query. Ad esempio, l'operatore di filtraggio record ha l'intera tabella come input e solo quelli che soddisfano la condizione come output. Inoltre, l'output di questo operatore viene inviato all'operatore, ad esempio FIRST 100, e quindi all'operatore di aggregazione (SUM o QUANTITY), che, allo stesso modo, incapsula tutte le elaborazioni al suo interno e genera un record con il risultato .

Schematicamente si presenta così:

AllData ->Filter(Name="Petrov")->Top(100)->Aggregation(NUMBER)

Quando apri il piano di query, vedrai i cubi collegati da frecce. I cubi sono operatori. Frecce: la direzione dei flussi di dati. I dati scorrono lungo le frecce da un operatore all'altro, fondendosi alla fine nel risultato della query.

Ogni operatore ha determinati parametri: il numero di record elaborati, il costo, il numero di operazioni di I/O, l'utilizzo delle cache e chi più ne ha più ne metta. Tutto questo ci permette di giudicare l'efficacia della richiesta. Una scansione della tabella che esegue un milione di record e ne produce due nell'output non è un ottimo piano di query. Ma il pianificatore non ha trovato niente di meglio. Non aveva un indice da esaminare. O forse le statistiche hanno mentito e hanno detto che c'erano tre voci nella tabella, ma in realtà sono riusciti a scrivere un milione di pezzi lì, ma le statistiche non sono state aggiornate. Tutto questo è oggetto di indagine da parte dell'ingegnere che studia la richiesta.

Il piano di query è un debugger di query passo-passo. Passo dopo passo guardi cosa esattamente, quale algoritmo (letteralmente) il DBMS ha applicato per dare il risultato. Hai visto esempi degli algoritmi stessi: sono estremamente complessi, perché ci sono cicli e condizioni. Anche a volte diversi cicli sono annidati, questo è orrore. È importante capire quali processi avvengono all'interno di ciascun operatore. Quale algoritmo è stato applicato all'array di record durante l'esecuzione e per quanto tempo ha funzionato.

Nel prossimo articolo intendo considerare operatori specifici trovati nei piani di query e la loro struttura interna. Grazie per aver letto fino alla fine!