Gewinnen von Leistungserkenntnissen dann, wenn sie benötigt werden, und Steigern der Leistung ohne Codeänderungen
- 10 Minuten
SQL Server 2022 bietet integrierte Funktionen, um die Zeit für die Abfrageoptimierung zu reduzieren, einschließlich der Funktionen des Abfragespeichers und der nächsten Generation der intelligenten Abfrageverarbeitung (Intelligent Query Processing, IQP), die Ihnen helfen, eine schnellere und konsistentere Leistung ohne Codeänderungen zu erzielen.
Herausforderungen bei der Abfrageoptimierung
Entwickler und SQL-Experten stimmen zu, dass während einige Abfragen nur funktionieren, einige Leistungssituationen für Abfragen auftreten können, die Leistungsbehandlungs- und Abfrageoptimierungsübungen erfordern. Die Optimierung der Abfrageleistung kann ein kostspieliger und oft langwieriger Prozess sein.
Lösungen für Abfrageoptimierung mithilfe des Abfragespeichers
Der Abfragespeicher ist eine integrierte Gruppe von Abfrageleistungsstatistiken, die in einer Benutzerdatenbank gespeichert sind. Der Abfragespeicher erfasst automatisch einen Verlauf von Abfragen, Plänen und Laufzeitstatistiken und speichert diese Informationen für Ihre Überprüfung. Sie trennt Daten nach Zeitfenstern, sodass Sie Datenbanknutzungsmuster anzeigen und verstehen können, wann Änderungen des Abfrageplans auf dem Server vorgenommen wurden. Weitere Informationen finden Sie unter Überwachen der Leistung mithilfe des Abfragespeichers.
SQL Server 2022 enthält wichtige Verbesserungen am Abfragespeicher, um die Zeit für Abfrageoptimierung zu verkürzen.
Abfragespeicher standardmäßig aktiviert
Vor SQL Server 2022 muss der Abfragespeicher mithilfe der T-SQL ALTER DATABASE-Anweisung aktiviert werden. Für SQL Server 2022 wird für jede neue Datenbank, die erstellt wurde, standardmäßig der Abfragespeicher aktiviert. Datenbanken, die aus früheren Versionen von SQL Server wiederhergestellt wurden, behalten die Beim Sichern der Datenbank erfassten Abfragespeichereinstellungen bei. Der Abfragespeicher verfügt seit seiner Einführung in SQL Server 2016 über mehrere Verbesserungen, mit denen Benutzer den Abfragespeicher aktivieren können, ohne die Anwendungsleistung erheblich zu beeinträchtigen. Darüber hinaus sind mehrere neue Einstellungen im Abfragespeicher möglich, damit Benutzer leichter steuern können, wie Abfrageleistungsinformationen erfasst und bereinigt werden. Benutzer können den Abfragespeicher jederzeit mithilfe der T-SQL ALTER DATABASE-Anweisung deaktivieren.
Hinweise zu Abfragespeicher
Mit Abfragespeicherhinweisen können Sie Abfragepläne auf einfache Weise strukturieren, ohne den Anwendungscode ändern zu müssen. Sie können jede Abfrage, die im Abfragespeicher gespeichert ist, entnehmen und Systemprozeduren nutzen, um einen Abfragehinweis anzuwenden. Der Abfragehinweis wirkt sich auf den Abfrageplan aus, mit der Absicht, die Abfrageleistung zu verbessern, ohne den Anwendungscode zu ändern. Sie können z. B. einen Abfragespeicherhinweis anwenden, damit eine Abfrage einen bestimmten MAXDOP Wert verwenden kann, ohne den Abfragetext zu ändern.
Abfragespeicherhinweise sollen nicht als normaler Schritt zum Optimieren der Abfrageleistung verwendet werden, können aber ein nützliches Tool für die Abfrageoptimierung sein, insbesondere, wenn Sie den Abfragetext in einer Anwendung nicht ändern können. Darüber hinaus verwenden einige neue Intelligente Abfrageverarbeitungsfeatures einen Abfragespeicherhinweis. Sie können alle permanenten Abfragespeicherhinweise in der sys.query_store_query_hints Katalogansicht anzeigen. Weitere Informationen finden Sie unter Query Store-Hinweise.
Abfragespeicher für Lesereplikate
Der Abfragespeicher ist zwar vorteilhaft, um den Zeitaufwand für die Optimierung von Abfragen zu reduzieren oder Probleme mit der Abfrageleistung einfach zu identifizieren, Leistungsinformationen sind jedoch nur für Abfragen verfügbar, die für das primäre Replikat in einer Always On-Verfügbarkeitsgruppe ausgeführt werden. In SQL Server 2022 steht mithilfe der T-SQL ALTER DATABASE-Anweisung eine neue Option zur Verfügung, damit der Abfragespeicher Leistungsinformationen für schreibgeschützte Abfragen sammeln kann, die auf sekundären Replikaten ausgeführt werden. Alle Leistungsinformationen für alle Replikate werden im primären Replikat beibehalten. Neue Informationen werden im Abfragespeicher erfasst, um anzugeben, welches Replikat einer Abfrage oder einem Abfrageplan zugeordnet ist.
Hinweis
Um den Abfragespeicher für sekundäre Replikate zu aktivieren, wird das Traceflag 12606 benötigt.
Abfragespeicher für intelligente Abfrageverarbeitung
Während der Abfragespeicher Wichtige Leistungsinformationen für Abfragen sammelt, verwendet der Abfrageprozessor in SQL Server 2022 auch den Abfragespeicher, um Informationen beizubehalten, um die Abfrageleistung zu beschleunigen. Zu diesen Features gehören optimierte Planerzwingung, Feedback zur Speicherzuweisung, Feedback zum Kardinalitätsschätzungsmodell (CE) sowie Feedback zum Grad an Parallelität (DOP).
Lösungen für eine schnellere Leistung mit der nächsten Generation der intelligenten Abfrageverarbeitung
Intelligent Query Processing (IQP) ist eine Reihe von Funktionen, die in den Abfrageprozessor im Datenbankmodul integriert sind, um die Leistung ohne Codeänderungen zu beschleunigen. Die nächste Generation der intelligenten Abfrageverarbeitung basiert auf einer Grundlage von Funktionen in SQL Server 2017 und 2019, wie im folgenden Diagramm dargestellt:
Wie Sie sehen können, gab es mehrere IQP-Features, die Teil von SQL Server 2017 und SQL Server 2019 waren. SQL Server 2022 bietet mehrere neue Funktionen für IQP. Sie können mit den neuesten IQP-Funktionen bei der intelligenten Abfrageverarbeitung in SQL-Datenbanken auf dem neuesten Stand bleiben. Sehen wir uns die einzelnen neuen Funktionen an.
Das Datenbankmodul verwendet zwei Prinzipien, um Entscheidungen für die intelligente Abfrageverarbeitung zu treffen:
- Vermeiden Sie, dass Abfrageleistungsregressionen mithilfe einer neuen Methode oder Automatisierung verursacht werden.
- Stellen Sie eine Methode auf Datenbank- oder Abfrageebene bereit, um eine bestimmte IQP-Funktion zu deaktivieren. Sie können wählen, welches IQP-Feature auf Datenbank- oder Abfrageebene aktiviert werden soll, während Sie je nach Datenbankkompatibilitätsstufe andere IQP-Features verwenden.
Funktionen nach dem Upgrade auf SQL Server 2022
Wenn Sie ein Upgrade auf SQL Server 2022 durchführen, gibt es neue Funktionen, um die Leistung unabhängig von der Datenbankkompatibilitätsstufe für Ihre Datenbank zu beschleunigen. Mit der Kompatibilitätsstufe können Sie neue Features nutzen, auch wenn Sie eine Datenbankkompatibilitätsstufe aus einer früheren Version von SQL Server verwenden müssen. Weitere Informationen finden Sie unter Kompatibilitätszertifizierung.
Quantil-Näherungsfunktionen
SQL Server enthält zwei Transact-SQL (T-SQL)-Funktionen, um die analytische Arbeitslast bei der Berechnung eines Perzentils eines Wertebereichs zu unterstützen.
- PERCENTILE_CONT
- PERCENTILE_DISC
SQL Server 2022 bietet ungefähre Entsprechung zu diesen beiden Funktionen:
- APPROX_PERCENTILE_CONT
- APPROX_PERCENTILE_DISC
Die Quantil-Näherungsfunktionen können für Analyseworkloads mit außergewöhnlich großen Datensätzen nützlich sein. Diese Funktionen werden schneller ausgeführt, und die Implementierung garantiert eine Fehlerrate von bis zu 1,33 % innerhalb einer Wahrscheinlichkeit von 99 %.
Erzwingen des optimierten Plans
Optimierte Planerzwingung ist eine neue Funktion in SQL Server 2022, die dazu dient, die Zeit zum Kompilieren bestimmter Abfragen zu verkürzen, wenn der Abfrageplan im Abfragespeicher erzwungen wird.
Einige Abfragen können eine erhebliche Zeit in Anspruch nehmen, um die Kompilierung zu erledigen. Optimierte Planzwingung ermöglicht es, die Zeit zu reduzieren, die zum Kompilieren einer Abfrage erforderlich ist, indem die Kompilierungsschritte für berechtigte Abfragen im Query Store gespeichert werden, die Abfragepläne enthalten, die im Query Store erzwungen werden. Mit dem Erzwingen von Abfrageplänen können Sie einen Abfrageplan für eine bestimmte Abfrage sperren. Wenn eine Abfrage das nächste Mal kompiliert werden muss, für die optimierte Planerzwingung aktiviert ist, werden Kompilierungsschritte verwendet, um die Kompilierungsphase für die Ausführung einer Abfrage erheblich zu beschleunigen.
Weitere Informationen finden Sie unter Optimierte Planerzwingung mit dem Abfragespeicher.
SQL Server 2022 IQP-Funktionen mit Datenbankkompatibilitätsebene 140 oder höher
Sie können mehr intelligente Abfrageverarbeitungsfunktionen erhalten, um das Feedback zur Speichererteilung in SQL Server 2022 zu verbessern, wenn Sie eine Datenbankkompatibilitätsstufe 140 oder höher verwenden. Das Feedback zur Speichererteilung wurde in SQL Server 2017 (Batchmodus) und SQL Server 2019 (Zeilenmodus) eingeführt. Das Feedback zur Speichererteilung ist ein Mechanismus, bei dem der Abfrageprozessor von Ausführungsfeedback lernen wird, um eine Speichererteilung für weitere Ausführungen anzupassen, wodurch tempdb-Überlaufs und RESOURCE_SEMAPHORE Wartezeiten vermieden oder reduziert werden.
Perzentile des Feedbacks zur Speicherzuweisung
Vor SQL Server 2022 basiert das Feedback zur Speichererteilung auf der letzten Ausführung für eine bestimmte Abfrage. Dies kann in einigen Fällen zu unterschiedlichen Feedbackanpassungen führen, was dazu führen könnte, dass der Abfrageprozessor das Feedback zur Speicherzuteilung bei einer bestimmten Abfrage deaktiviert. In SQL Server 2022 verwendet das Feedback zur Speicherzuweisung eine Perzentilmethode, um Speicherzuweisungen über mehrere Ausführungen hinweg zu untersuchen, bevor ein Feedback zur Speicherzuweisung verwendet wird.
Persistenz des Feedbacks zur Speicherzuweisung
Vor SQL Server 2022 wurde das Feedback zur Speicherzuweisung nur in einem zwischengespeicherten Plan im Arbeitsspeicher gespeichert. Wenn der Cacheplan entfernt wurde, müsste das Feedback zur Speichererteilung bei neuen Abfrageausführungen neu berechnet werden. In SQL Server 2022, bei aktiviertem Query Store, wird das Speicherzuteilungs-Feedback im Query Store beibehalten. Sie können die Persistenz des Feedbacks zur Speicherzuweisung in der sys.query_store_plan_feedback-Katalogsicht anzeigen.
Weitere Informationen finden Sie unter Feedback zur Speicherzuweisung.
SQL Server 2022 IQP-Funktionen mit Datenbankkompatibilitätsebene 160 oder höher
Wenn Sie eine Datenbankkompatibilitätsstufe 160 oder höher verwenden, können Sie intelligentere Funktionen für die Verarbeitung von Abfragen abrufen, z. B. Optimierung des parametersensitiven Plans, Verbesserungen der Kardinalitätsschätzung und des Grads der Parallelität.
Optimierung parametersensitiver Pläne
Wenn eine Abfrage kompiliert wird, berücksichtigt der integrierte Ausführungsplan Werte für alle Parameter, die in Abfragen in einer gespeicherten Prozedur oder parametrisierten Abfrage verwendet werden. Dieses Konzept wird als Parametersniffing bezeichnet. Für Anweisungen in einer gespeicherten Prozedur oder parametrisierten Abfrage kann nur ein Abfrageplan im Cache vorhanden sein. In den meisten Fällen führt dies nicht zu Leistungsproblemen für Anwendungen. Es gibt jedoch Situationen, in denen die Daten, die für Abfragen basierend auf Parametern abgerufen werden, verzerrt oder nicht gleichmäßig verteilt sein können. In diesen Fällen ist der einzelne Cacheplan für unterschiedliche Parameterwerte möglicherweise nicht optimal. Dieses Problem wird als parametersensitiver Plan bezeichnet.
In SQL Server 2022 kann der Optimierer Parametersensitive Planszenarien erkennen und mehrere Pläne für dieselbe gespeicherte Prozedur oder parametrisierte Abfrage zwischenspeichern. Der Optimierer verwendet ein Konzept namens Abfragevarianten , um Parameterwerte zu aggregieren, um einem Abfrageplan zu entsprechen, der für diese Parameterwerte am besten geeignet ist.
Weitere Informationen finden Sie unter Optimierung des Parameterempfindlichkeitsplans.
Feedback zur Kardinalitätsschätzung (CE)
In SQL Server 2014 mit Datenbankkompatibilitätsebene 120 begann Microsoft mit der Verwendung eines neuen Modells innerhalb des Abfrageprozessors, um bestimmte Annahmen zur Kardinalitätsschätzung für bestimmte Abfragemuster zu treffen. In einigen Fällen hat das neue Modell einen korrekteren Abfrageplan generiert, führt aber möglicherweise zu einer langsameren Leistung als mit dem älteren CE-Modell. Zu den CE-Modellszenarien gehören Korrelation, Verknüpfungseindämmung und Zeilenziel. Seit SQL Server 2014 sind mehrere Optionen enthalten, um das CE-Legacymodell zu verwenden oder das CE-Verhalten auf Datenbank- oder Abfrageebene mit Ablaufverfolgungsflags oder Abfragehinweisen zu steuern.
In SQL Server 2022, wobei der Abfragespeicher aktiviert ist, wertet der Optimierer sehr sich wiederholende Abfragen aus, die Muster für CE-Modellszenarien abgleichen, bei denen das Modell möglicherweise eine falsche Annahme macht. Der Optimierer versucht dann, zu testen und zu überprüfen, ob ein Abfragehinweis verwendet werden kann, um die Abfrage schneller auszuführen. Nach der Überprüfung der schnelleren Leistung wird ein Abfragehinweis im Abfragespeicher beibehalten, der für zukünftige Abfrageausführungen verwendet wird. Sie können alle angewendeten Abfragehinweise für CE-Feedback in der sys.query_store_query_hints Katalogansicht und CE-Feedbackdetails in der sys.query_store_plan_feedback Katalogansicht anzeigen. CE-Feedback wird nicht verwendet, wenn das ältere CE-Modell aktiviert wurde, wenn ein Abfrageplan im Abfragespeicher erzwungen wird oder eine Abfrage bereits Hinweise zum Abfragespeicher enthält.
Weitere Informationen finden Sie unter Kardinalitätsschätzung (SQL Server).
Feedback zum Grad der Parallelität (DOP)
Der Optimierer in SQL Server führt in einigen Fällen Teile des Abfrageplans (als Operatoren bezeichnet) mithilfe von Parallelität mit mehreren gleichzeitigen Threads aus. Die Anzahl der Threads, die für einen Abfrageplanoperator verwendet werden, wird als Grad der Parallelität (DOP) bezeichnet. SQL Server kann die maximale Anzahl von Threads pro Operator mithilfe von Server, Datenbank, Ressourcengruppe oder Abfrageeinstellungen steuern, die als max. Grad der Parallelität (MAXDOP) bezeichnet werden. Das Festlegen des richtigen MAXDOP für eine SQL Server-Bereitstellung kann eine komplexe und manchmal schwierige Übung sein.
In SQL Server 2022 kann der Optimierer eine Technik namens DOP-Feedback verwenden, um die parallele Effizienz für eine Abfrage zu finden. Parallele Effizienz ist der minimale Parallelitätsgrad für eine Abfrage, der zu derselben gesamten Abfragedauer führen kann (unter Berücksichtigung der allgemeinen Wartezeiten). Das Reduzieren des DOP für eine Abfrage kann weitere Threads und CPU-Ressourcen für andere Abfragen oder Anwendungen bereitstellen.
DOP-Feedback erfordert, dass der Abfragespeicher aktiviert ist, die Datenbank auf Kompatibilitätsebene 160 eingestellt ist und eine Datenbankeinstellung namens DOP_FEEDBACK eingeschaltet wird. Mit diesen Einstellungen arbeitet der Optimierer in Abstimmung mit Hintergrundtasks des Abfragespeichers, um nach repetitiven und zeitintensiven Abfragen zu suchen, die von einem niedrigeren Parallelitätsgrad profitieren könnten. Ein Feedbackzyklus wird verwendet, um zu überprüfen, ob eine angepasste Abfragedauer (unter Berücksichtigung von Wartezeiten) nicht zu einem niedrigeren Wert für den Parallelitätsgrad führt und ob eine niedrigere CPU-Gesamtauslastung für die Abfrage zu beobachten ist. Nach einem Zeitraum der Überprüfung gilt ein niedrigerer DOP als stabilisiert und wird im Abfragespeicher beibehalten. Der Optimierer überprüft weiterhin niedrigere DOP-Werte schrittweise nach unten, um die beste parallele Effizienz oder einen minimalen DOP zu finden, was 2 ist. Feedback zum Parallelitätsgrad erhöht den Parallelitätsgrad niemals und berücksichtigt die MAXDOP-Einstellung für eine Abfrage abhängig vom Server der Datenbank, dem Resource Governor oder dem Abfragehinweis, der angewendet wurde.
DOP-Feedback erfordert keine Erneute Kompilierung, aber die Überprüfung wird bei jeder neuen Abfragekompilierung untersucht. Sie können persistent gespeicherte Werte für Feedback zum Parallelitätsgrad in der sys.query_store_plan_feedback-Katalogansicht anzeigen. Mit der Spalte last_dop aus der dynamischen Verwaltungsansicht sys.dm_exec_query_stats und der Katalogansicht sys.query_store_runtime_stats können Sie sehen, was der neueste DOP ist, der für eine Abfrage verwendet wird.
Weitere Informationen finden Sie unter DoP-Feedback (Grad of Parallelism).