Durch: Ahmad Yaseen / Aktualisiert: 2018-03-30 / Bemerkungen (3) | Verwandt: Mehr > Abfrageoptimierung
Kostenloses MSSQLTips-Webinar: Indizierung für Leistung: Abfragestrategien zur Maximierung Ihrer SQL Server-Indizes
Nur wenige Dinge beeinflussen die Geschwindigkeit und Leistung von SQL-Anweisungen in SQL Server so stark wie Indizes. Diese Sitzung hilft Ihnen, die Grundlagen der Abfrageleistung in Bezug auf die Indizes in Ihrer Produktionsdatenbank zu verstehen. Wir zeigen Ihnen, wie Sie feststellen können, ob Sie gute Indexstrategien verwenden und welche direkten Auswirkungen Ihre Indexierungsstrategie auf Abfragen hat.
Problem
Ich weiß, dass SQL Server Abfragen parallel ausführen kann, aber wie kann ich feststellen, dass eine Abfrage einen parallelen Ausführungsplan verwendet, und wie kann ich Informationen über die Anzahl der verwendeten CPUs erhalten, wenn eine Abfrage parallel ausgeführt wird? Schauen Sie sich diesen Tipp an, um zu erfahren, wie.
Lösung
Das Parallelitätskonzept ergibt sich aus der Aufteilung einer großen Aufgabe in kleinere Aufgaben, wobei jede kleine Aufgabe einer bestimmten Person oder im Falle einer SQLServer-Abfrage einem Prozessor zugewiesen wird, um einen Teil der Hauptaufgabe zu erfüllen. Schließlich werden die Teilergebnisse jeder kleinen Aufgabe zu einem Endergebnis zusammengefasst. Sie können sich vorstellen, wie viel schneller dies erledigt werden kann und welchen Leistungsgewinn Sie erzielen können, wenn Sie mehrere serielle Aufgaben gleichzeitig parallel ausführen, verglichen mit dem seriellen Ausführen einer großen Aufgabe!
Bevor Sie den Tipp durchgehen, ist es von Vorteil, eine Reihe von technischen zu Verstehenbegriffe im Zusammenhang mit dem Parallelitätskonzept in SQL Server:
- Der Scheduler ist der physische oder logische Prozessor, der für die Planung der Ausführung der SQL Server-Threads verantwortlich ist.
- Der Worker ist der Thread, der an einen Scheduler gebunden ist, um eine bestimmte Aufgabe auszuführen.
- Der Grad der Parallelität ist die Anzahl der Arbeiter, oder die Anzahl der Prozessoren, die für den parallelen Plan zugewiesen werden, um die Arbeiteraufgabe zu erfüllen.
- Der maximale Parallelitätsgrad (MAXDOP) ist eine Option auf Server-, Datenbank- oder Abfrageebene, mit der die Anzahl der Prozessoren begrenzt wird, die der parallele Plan verwenden kann. Der Standardwert von MAXDOP ist 0, in dem theSQL Server-Engine kann alle verfügbaren Prozessoren verwenden, bis zu 64, in der Abfrage parallelexecution. Wenn Sie die Option MAXDOP auf 1 setzen, wird verhindert, dass mehr als ein Processorin die Abfrage ausführt, was bedeutet, dass das SQL Server-Modul einen serialplan zum Ausführen der Abfrage verwendet. Die Option MAXDOP kann einen Wert von bis zu 32767 annehmen, wobei die SQL Server-Engine alle verfügbaren Serverprozessoren in der Ausführung des parallelen Plans verwendet, wenn der MAXDOP-Wert die Anzahl der auf dem Server verfügbaren Prozessoren überschreitet. Wenn der SQL Server auf einem einzelnen Processorserver installiert ist, wird der Wert von MAXDOP ignoriert.
- Die Aufgabe ist eine kleine Arbeit, die einem bestimmten Mitarbeiter zugewiesen wird.
- Der Ausführungskontext ist die Grenze, in der jede Singletask ausgeführt wird.
- Der parallele Seitenlieferant ist ein Teil der SQL ServerStorage-Engine, der die von der Abfrage angeforderten Datensätze innerhalb der beteiligten Worker verteilt.
- Der Austausch ist die Komponente, die die verschiedenen Ausführungskontexte verbindet, die am parallelen Abfrageplan beteiligt sind, um das endgültige Ergebnis zu erhalten.
Die Entscheidung, einen parallelen Plan zur Ausführung der Abfrage zu verwenden oder nicht, hängt davon abmehrere Faktoren. Beispielsweise sollte SQL Server auf einem Multiprozessorserver installiert sein, die angeforderte Anzahl von Threads sollte verfügbar sein, um erfüllt zu werden, die Option Maximaler Parallelitätsgrad ist nicht auf 1 festgelegt und die Kosten der Abfrage überschreiten den zuvor konfigurierten Kostenschwellenwert für Parallelismvalue.
Ziel dieses Tipps ist es, die Informationen der parallelen Abfragethreads aus dem tatsächlichen Ausführungsplan der Abfrage abzurufen. Um die SQL Server-Engine zu zwingen, die übermittelte Abfrage unter Verwendung eines parallelen Plans auszuführen, setzen wir den Wert Cost Thresholdfor Parallelism auf 0, um sicherzustellen, dass die Abfragekosten in allen Fällen den Wert Cost Thresholdfor Parallelism überschreiten, und wir behalten die Option Maximum Degree ofParallelism mit dem Standardwert 0 bei, damit die SQL Server-Engine während der Ausführung unserer Abfrage alle verfügbaren Prozessoren verwenden kann, bei denen es sich um 4 Prozessoren in mymachine handelt, die für diese Demo verwendet werden.
Der Kostenschwellenwert für Parallelität kann mit SQL Server ManagementStudio festgelegt werden, indem Sie eine Verbindung zur SQL Server-Instanz herstellen, mit der rechten Maustaste auf den Instanznamen klicken und die Option Eigenschaften auswählen. Scrollen Sie auf der Seite Erweitert des Fensters Server Propertieswindow nach unten zum Abschnitt Parallelität, in dem Sie den Standardwert der Option Kostenschwelle für Parallelität (5) oder den maximalen Parallelitätsgrad (falls erforderlich) überschreiben können, wie in der Abbildung unten gezeigt:
Sie können den Standardwert des Kostenschwellwerts für Parallelität auch mit sp_configure überschreiben. Um den Kostenschwellenwert für den Parallelitätswert ändern zu können, sollten Sie mit sp_configure zuerst die Option Show Advanced aktivieren, wie im folgenden Skript gezeigt:
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 des parallelen Ausführungsplans
Der Server ist jetzt für die Demo konfiguriert. Lassen Sie uns eine neue Tabelle mit der Anweisung Create TABLE T-SQL wie unten gezeigt erstellen:
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) )
Sobald die Tabelle erstellt ist, füllen wir die Tabelle mit 100K-Datensätzen mithilfe der folgenden Insert INTO-Anweisungen:
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
Jetzt möchten wir den „Include Actual Execution Plan“ aktivieren und dann die folgende SELECT-Abfrage ausführen:
SELECT , , , , , FROM .. WHERE Address LIKE '%AMM%' ORDER BY BirthDate desc
Nachdem dies abgeschlossen ist, können wir den Ausführungsplan überprüfen, der vonausführung der Abfrage, Sie werden sehen, dass die SQL Server-Engine beschließt, einen Parallelplan zum Ausführen der Abfrage zu verwenden, aufgrund der Tatsache, dass die Kosten dieser Abfrage Diekostenschwelle für Parallelitätswert.
Wir können unten sehen, dass der Ausführungsplan ein paralleler Plan ist, wegen der Parallelismoperator und auch die gelben Kreise mit zwei Pfeilen unter jedem Operator, der ausgeführtmit Parallelität.
Der Ausführungsplan ist nicht nur ein Diagramm, sondern enthält wertvolle Informationenbeschreibt den Ausführungsprozess der übermittelten Abfrage. Um diese Informationen anzuzeigen,klicken Sie mit der rechten Maustaste auf den Knoten AUSWÄHLEN im Ausführungsplan und wählen Sieoption Eigenschaften. Im Eigenschaftenfenster können Sie die Anzahl der Prozessoren sehen, die zum Verarbeiten der übermittelten Abfrage verwendet werdenGrad des Parallelitätsattributs, unter dem Abschnitt Verschiedenes wie unten gezeigt:
Aus Demselben Fenster werden auch Informationen zu den parallelen Abfragethreads angezeigt Abschnitt ThreadStat. In diesem Abschnitt können Sie das Attribut parallele Abfragezweige überprüfen, das die Anzahl der gleichzeitigen Ausführungspfade innerhalb des Abfrageausführungsplans, die NUMA-Knoten-ID, die Anzahl der parallelen Threads, die für den angegebenen NUMAnode reserviert sind, unter dem Attribut ReservedThreads und die Anzahl der Threads anzeigt, die während der Ausführung der Abfrage verwendet wurden, unter dem Attribut UsedThreads.
Aus dem Eigenschaftenfenster können Sie ableiten, dass das SQL Server-Modul 4prozessoren zum Ausführen der Abfrage verwendet hat. Darüber hinaus verfügt der Abfrageausführungsplan über einen Ausführungspfad, in dem 4 Threads reserviert und zum Ausführen der übermittelten Abfrage mit einem parallelen Plan verwendet werden.
Die gleichen Informationen können auch aus dem XML-Ausführungsplan abgeleitet werden, indem Sie unter dem Abschnitt ThreadStat nachsehen, wie unten gezeigt:
Sie können auch tiefer eintauchen, um die von jedem Thread verbrauchten E / A- und CPU-Ressourcen zu überprüfen. Klicken Sie mit der rechten Maustaste auf den Knoten Clustered Index Scan im Ausführungsplan und wählen Sie Eigenschaften. Im angezeigten Eigenschaftenfenster können Sie den tatsächlichen E / A-Statistikknoten erweitern, um die Anzahl der logischen und physischen Lesevorgänge zu überprüfen, die von jedem Thread während der Ausführung der Abfrage ausgeführt werden. Darüber hinaus können Sie auch die Anzahl der Zeilen überprüfen, die von jedem Thread abgerufen werden, die CPU-Zeit, die von jedem Thread verbraucht wird, und schließlich die Ausführungszeit, die von jedem Thread verstrichen ist, um seine Aufgabe zu erfüllen, wie unten gezeigt:
Die Ergebnisse stimmen mit dem Fenster SELECT node Properties überein, in dem 4 Arbeitsthreads vorhanden sind, die an der Ausführung der Abfrageaufgaben teilgenommen haben, und ein Koordinatorthread, der zwischen den Arbeitsthreads koordiniert.
Berücksichtigen Sie, dass die vorherigen Informationen nur aus dem tatsächlichen Ausführungsplan abgerufen werden können, der nach der Ausführung der Abfrage generiert wird. Wenn Sie versuchen, nach diesen Informationen aus dem geschätzten Ausführungsplan zu suchen, ohne die Abfrage auszuführen, werden keine Informationen angezeigt, da die Abfrage ausgeführt werden muss, um die Anzahl der Prozessoren anzugeben, die der Abfrage von den verfügbaren Prozessoren während der queryexecution zugewiesen werden, wie unten für den geschätzten Ausführungsplan gezeigt.
Weitere SQL Server-Parallelitätsinformationen abrufen
Während die Abfrage ausgeführt wird, können Sie einfach den Befehl sp_who2 mit der querysession-ID ausführen, um Informationen zu den Threads abzurufen, die zum Ausführen der submittedquery während der Abfrageausführung verwendet werden, wie unten gezeigt:
Im Ausführungsplan wurde jedem Operator in diesem Plan eine Nummer zugewiesen und ein Scheduler, mit dem er ausgeführt wird. Diese Informationen können durch Abfragen des sys abgerufen werden.dm_os_tasks Systemkatalogansicht und verbindet sie mit sys.dm_os_workersand sys.dm_exec_query_profiles Systemkatalogansichten, wie im T-SQL-Skript unten:
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;
Wenn Sie das vorherige Skript ausführen, während Sie die SELECT-Anweisung mit aktiviertem ActualExecution-Plan ausführen, zeigt uns das Ergebnis, dass die drei Planoperatoren: Sortieren, Filtern und Clustered Index mit der ID jedes Operators daneben scannen. Dies wird viermal mit den vier Schedulern ausgeführt. Der Parallelitätsoperatormit ID gleich 0 wird einmal vom vierten Scheduler ausgeführt, um im letzten Schritt alle Ausführungskontexte miteinander zu verbinden, wie unten gezeigt:
Unten habe ich die Knotenwerte hinzugefügt, die den obigen Abfrageergebnissen entsprechen, mit dem Ausführungsplan, um die weitere Veranschaulichung zu erleichtern.
Nächste Schritte
- Überprüfen Sie die folgenden Ressourcen:
- Angeben des maximalen Parallelitätsgrads in SQL Server für eine Abfrage.
- Welche MAXDOP-Einstellung sollte für SQL Server verwendet werden.
- Erzwingen eines parallelen Ausführungsplans in SQL Server 2016.
- SQL Server 2016 DBCC CHECKDB mit MAXDOP
Zuletzt aktualisiert: 2018-03-30
Über den Autor
Ahmad Yaseen ist ein SQL Server DBA mit einem Bachelor-Abschluss in Computer Engineering sowie .NET-Entwicklungserfahrung.Alle meine Tipps anzeigen
Verwandte Ressourcen
- Weitere Tipps für Datenbankentwickler…
