Da: Ahmad Yaseen | Aggiornamento: 2018-03-30 | Commenti (3) | Correlati: Più > Ottimizzazione di Query
Gratuito MSSQLTips Webinar: Indicizzazione per le Prestazioni: Query Strategie per Massimizzare il Vostro Indici di SQL Server
Paio di cose influenzano la velocità e le prestazioni di istruzioni SQL in SQL Server come indici. Questa sessione consente di comprendere le basi delle prestazioni delle query in relazione agli indici del database di produzione. Ti insegneremo come determinare se stai utilizzando buone strategie di indice e l’impatto diretto della tua strategia di indicizzazione sulle query.
Problema
So che SQL Server può eseguire query in parallelo, ma come posso dire che una query utilizza un piano di esecuzione parallelo e come posso ottenere informazioni sul numero di CPU utilizzate quando una query viene eseguita in parallelo? Dai un’occhiata a questo suggerimento per imparare come.
Soluzione
Il concetto di parallelismo deriva dalla divisione di una grande attività in attività più piccole, doveogni piccola attività viene assegnata a una persona specifica, o processore nel caso di una query SqlServer, per eseguire parte dell’attività principale. Infine, i risultati parziali presi da ogni piccola attività saranno combinati insieme in un unico risultato finale. Youcan immaginare quanto più veloce questo può essere fatto e il guadagno di prestazioni che si canget da eseguire più attività seriali allo stesso tempo in parallelo, rispetto torunning un grande compito in serie!
Prima di passare attraverso il suggerimento, è utile comprendere una serie di termini tecnici relativi al concetto di parallelismo in SQL Server:
- Lo Scheduler è il processore fisico o logicoè responsabile della pianificazione dell’esecuzione dei thread di SQL Server.
- Il Worker è il thread associato a uno scheduler per eseguire un’attività specifica.
- Il grado di parallelismo è il numero di lavoratori, oil numero di processori, che vengono assegnati per il piano parallelo per realizzare il compito del lavoratore.
- Il grado massimo di parallelismo (MAXDOP) è un’opzione a livello di server,database o query utilizzata per limitare il numero di processoriche il piano parallelo può utilizzare. Il valore predefinito di MAXDOP è 0, in cui ilsql Server Engine può utilizzare tutti i processori disponibili, fino a 64, nella query parallelexecution. L’impostazione dell’opzione MAXDOP su 1 impedirà l’utilizzo di più di un processorin esecuzione della query, il che significa che il motore di SQL Server utilizzerà un serialplan per eseguire la query. L’opzione MAXDOP può assumere un valore fino a 32767, wherethe SQL Server Engine utilizzerà tutti i processori server disponibili nell’esecuzione del piano parallelo se il valore MAXDOP supera il numero di processorsavailable nel server. Se SQL Server è installato su un singolo processorserver, il valore di MAXDOP verrà ignorato.
- L’attività è un piccolo pezzo di lavoro che viene assegnato a un lavoratore specifico.
- Il contesto di esecuzione è il limite in cui ogni singletask viene eseguito all’interno.
- Il fornitore di pagine parallele è una parte del motore SQL ServerStorage che distribuisce i set di dati richiesti dalla query all’interno dei lavoratori partecipanti.
- Lo scambio è il componente che collegherà i diversi contesti di esecuzione coinvolti nel piano parallelo della query insieme, per ottenere il risultato finale.
La decisione di utilizzare un piano parallelo per eseguire la query o meno dipende damolti fattori. Ad esempio, SQL Server deve essere installato su un server multi-processorserver, il numero richiesto di thread deve essere disponibile per essere soddisfatto, l’opzione Massimo grado di parallelismo non è impostata su 1 e il costo della query supera la soglia di costo configurata in precedenza per Parallelismvalue.
Lo scopo di questo suggerimento è quello di recuperare le informazioni dei thread di query paralleli dal piano di esecuzione effettivo della query. Per forzare il Motore di SQL Server toexecute la query inviata utilizzando un piano parallelo, possiamo impostare il Costo Thresholdfor Parallelismo valore a 0, per assicurarsi che in tutti i casi il costo della query sarà exceedthe Costo di Soglia per il Parallelismo valore e saremo mantenere il Massimo Grado ofParallelism opzione con i suoi default il valore 0, per consentire il Motore di SQL Server useall processori disponibili durante l’esecuzione di query, che sono 4 processori nel computer utilizzato per questa demo.
La soglia di costo per il valore di parallelismo può essere impostata utilizzando SQL Server ManagementStudio, collegandosi all’istanza di SQL Server, facendo clic con il pulsante destro del mouse su instancename e scegliendo l’opzione Proprietà. Dalla pagina Avanzate del Server Propertieswindow, scorri verso il basso fino alla sezione Parallelismo, da cui puoi ignorare il valore predefinito dell’opzione Soglia di costo per il parallelismo, che è 5, o il MaxDegree di Parallelismo se necessario, come mostrato nello screenshot qui sotto:
È anche possibile ignorare il valore predefinito della soglia di costo per il parallelismo utilizzando sp_configure. Per poter modificare la soglia di costo per il valore di parallelismo,è necessario utilizzare sp_configure per abilitare prima l’opzione Mostra avanzate, come mostrato nello script di seguito:
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;GOEXEC sp_configure 'cost threshold for parallelism', 0;RECONFIGURE;GOEXEC sp_configure 'show advanced options', 0;RECONFIGURE;
Demo del piano di esecuzione parallelo
Il server è ora configurato per la demo. Creiamo una nuova tabella utilizzando l’istruzione theCREATE TABLE T-SQL come mostrato di seguito:
USE MSSQLTipsDemoGOCREATE TABLE ParallelDemo( ID INT IDENTITY (1,1) PRIMARY KEY, FirstName NVARCHAR (200), LastName NVARCHAR (200), PhoneNumber VARCHAR(50), BirthDate DATETIME, Address NVARCHAR(MAX) )
una Volta creata la tabella, possiamo riempire la tabella con 100K record utilizzando theINSERT IN dichiarazioni di seguito:
INSERT INTO ParallelDemo VALUES ('John','Horold','+96255889955','1987-01-08','Jordan - Amman - Mecca Street - Building 77')GO 50000 INSERT INTO ParallelDemo VALUES ('Michel','Anglo','+96255776655','1985-06-02','Jordan - Amman - Mecca Street - Building 74')GO 50000
Ora vogliamo attivare la “Includono il Piano di Esecuzione Effettivo” e thenrun il seguente query di selezione:
SELECT , , , , , FROM .. WHERE Address LIKE '%AMM%' ORDER BY BirthDate desc
una volta completata l’esecuzione, siamo in grado di controllare il piano di esecuzione generato fromexecuting la query, vedrete che il Motore di SQL Server decide di utilizzare un parallelplan per eseguire la query, a causa del fatto che il costo di tale query supera theCost Soglia per il Parallelismo valore.
Possiamo vedere sotto che il piano di esecuzione è un piano parallelo, a causa del Parallelismoperator e anche i cerchi gialli con due frecce sotto ogni operatore che ha eseguitocon parallelismo.
Il piano di esecuzione non è solo un grafico, contiene informazioni preziose chedescrive il processo di esecuzione della query inviata. Per visualizzare queste informazioni,fare clic con il pulsante destro del mouse sul nodo SELEZIONA nel piano di esecuzione e scegliere l’opzione Proprietà. Dalla finestra delle Proprietà, è possibile vedere il numero di processori utilizzati per elaborare la query inviata da theDegree di Parallelismo attributo, sotto Varie sectionas mostrato di seguito:
Da questa stessa finestra, informazioni sulle query parallela thread sono alsodisplayed sotto il ThreadStat sezione. In tale sezione, è possibile controllare l’attributo parallel query Branches, che mostra il numero di percorsi di esecuzione simultanei all’interno del piano di esecuzione della query, l’ID del nodo NUMA, il numero di thread paralleli riservati per il NUMAnode specificato nell’attributo ReservedThreads e il numero di threadsused durante l’esecuzione della query, nell’attributo UsedThreads.
Dalla finestra delle proprietà, è possibile derivare che il motore di SQL Server ha utilizzato 4processori per eseguire la query. Inoltre, il piano di esecuzione della query ha un executionpath, in cui vengono riservati 4 thread e utilizzati per eseguire la query inviata utilizzando un piano parallelo.
Le stesse informazioni possono anche essere derivate dal piano di esecuzione XML, consultandosotto la sezione ThreadStat, come mostrato di seguito:
Puoi anche immergerti più a fondo per controllare l’I/O e le risorse della CPU consumate da eachthread. Fare clic con il pulsante destro del mouse sul nodo di scansione dell’indice cluster nel piano di esecuzione e scegliere Proprietà. Dalla finestra delle proprietà visualizzate, è possibile espandereil nodo Statistiche I/O effettivo per controllare il numero di operazioni di lettura logiche e fisiche eseguite da ciascun thread durante l’esecuzione della query. Inoltre, youcan anche controllare il numero di righe che vengono recuperati da ogni thread, il CPU timeconsumed da ogni thread e, infine, il tempo di esecuzione che è trascorso da ogni threadto compiere il suo compito, come mostrato di seguito:
I risultati corrispondono alla finestra SELEZIONA proprietà nodo, in cui sono presenti 4 thread di lavoro che hanno partecipato all’esecuzione delle attività di query e un thread di coordinamento che coordina tra i thread di lavoro.
Tenere in considerazione che le informazioni precedenti possono essere recuperate solo dal piano di esecuzione effettivo generato dopo l’esecuzione della query. Se si tenta di cercare tali informazioni dal piano di esecuzione stimato, senza eseguire la query, nessuna informazione verrà visualizzata quando la query deve essere eseguita per specificare il numero di processoriche verranno assegnati alla query dai processori disponibili durante l’esecuzione della query come mostrato di seguito per il piano di esecuzione stimato.
di Ottenere Più di SQL Server Parallelismo Informazioni
Durante l’esecuzione di query, si può semplicemente eseguire il sp_who2 comando con il querysession ID per ottenere informazioni circa i thread che vengono utilizzati per eseguire il submittedquery durante l’esecuzione della query, come mostrato di seguito:
il piano di esecuzione, ogni operatore che piano aveva un numero assegnato di itand di uno strumento di pianificazione che viene utilizzato per eseguire esso. Queste informazioni possono essere recuperate daquerizzando il sys.dm_os_tasks vista del catalogo di sistema e di entrare con sys.dm_os_workersand sys.dm_exec_query_profiles viste del catalogo di sistema, come in T-SQL, script riportato di seguito:
SELECT OSTSK.scheduler_id, qp.node_id, qp.physical_operator_nameFROM sys.dm_os_tasks OSTSKLEFT JOIN sys.dm_os_workers OSWRK on OSTSK.worker_address=OSWRK.worker_addressLEFT JOIN sys.dm_exec_query_profiles qp on OSWRK.task_address=qp.task_addressWHERE OSTSK.session_id=58ORDER BY scheduler_id, node_id;
l’Esecuzione di script precedente durante l’esecuzione dell’istruzione SELECT con la ActualExecution Piano abilitato, il risultato ci mostra che i tre operatori del piano: Ordinare,Filtrare e Clustered Index Scan con l’ID di ciascun operatore indicato a fianco di esso. Questo viene eseguito quattro volte, utilizzando i quattro scheduler. Il Parallelismo operatorwith ID uguale a 0 sarà eseguita una volta dalla quarta di pianificazione per collegare allexecution contesti insieme, nell’ultimo passaggio, come mostrato di seguito:
qui di Seguito ho aggiunto il Nodo valori che corrispondono con la query di cui sopra, resultswith il piano di esecuzione per aiutare a illustrare ulteriormente.
Passi successivi
- Controllare le seguenti risorse:
- Specificare il grado massimo di parallelismo in SQL Server per una query.
- Quale impostazione MAXDOP deve essere utilizzata per SQL Server.
- Come forzare un piano di esecuzione parallelo in SQL Server 2016.
- SQL Server 2016 DBCC CHECKDB con MAXDOP
Ultimo Aggiornamento: 2018-03-30
Circa l’autore
Ahmad Yaseen è un amministratore di database SQL Server con una laurea in ingegneria informatica, come pure .NET esperienza di sviluppo.Visualizza tutti i miei suggerimenti
Risorse correlate
- Altri suggerimenti per gli sviluppatori di database…
