SQL-Server-Abfrageoptimierung für Dynamics ERP

SQL-Server-Abfrageoptimierung für Dynamics ERP

SQL-Server-Abfrageoptimierung für Dynamics ERP


1. Warum sollte man eine SQL-Server-Abfrageoptimierung für Dynamics ERP Systeme wie Business Central oder NAV durchführen?

In den letzten Jahren hat man unglaubliche Verbesserungen in vielen Bereichen der Datenbank Server gesehen:

  • Datenbankoptimierer verbessern sich ständig und finden Wege, um Abfragen anpassungsfähiger zu machen und Bereiche mit schlechter Optimierung zu überwinden.
  • Die Speichergeschwindigkeit hat sich aufgrund der Fortschritte sowohl bei der Speichertechnologie als auch bei der Netzwerkbandbreite massiv erhöht, CPUs sind viel schneller geworden und die Preise für Speicher sind dramatisch gesunken.

Dennoch gibt es immer noch Fachleute (wie uns), die ihren Lebensunterhalt mit der Abstimmung von Abfragen verdienen und andere darin schulen, Abfragen zu optimieren. Dieser Prozess beinhaltet das Auffinden bestimmter langsamer Abfragen (Long Durations), die für die Leistung von Dynamics Business Central oder NAV entscheidend sind. Diese „langsamen SQL-Abfragen“ können durch das Vornehmen strategischer Änderungen, sei es im Code, der Datenbankstruktur, der NAV Server Instanz Konfiguration oder etwas anderem entstehen. Optimierungen sind oft notwendig, um sicherzustellen, dass diese spezifischen SQL-Abfragen konsistent mit einer bestimmten Geschwindigkeit ausgeführt werden oder einem geforderten Leistungsstandard entsprechen.
Aber warum also SQL-Server-Abfrageoptimierung trotz der ganzen Verbesserungen?

Ganz einfach:

  • Auch die Datenmengen nehmen dramatisch zu
  • Auch die Erwartungen der Kunden/Benutzer an Leistung/Geschwindigkeit von Anwendungen wie Dynamics Business Central sind gestiegen
  • Kunden/Benutzer erwarten, dass sie jetzt nicht auf Ergebnisse warten müssen – sie möchten sofort den aktuellen Status sehen

2. Wer sollte die SQL-Abfrageoptimierung durchführen?

Die SQL-Abfrageoptimierung erfolgt durch:

  • Datenbankadministratoren
  • C/AL oder AL Entwickler, die sich auf Performance oder speziell auf Datenbanken spezialisiert haben

Full-Stack-Entwickler für AL und C/AL führen im Allgemeinen keine Abfrageoptimierung durch, es sei denn, sie haben ein bestimmtes Interesse oder Berufserfahrung. Dies ist eher eine Spezialisierung als eine „schnelle Lernaufgabe“, daher haben die meisten Full-Stack-Entwickler einfach keine Zeit und müssen eine spezialisiertere Person beauftragen, die ihnen hilft. Teams, die keinen leicht verfügbaren Spezialisten haben, können in regelmäßigen Abständen Berater wie uns das DynamicsProject Team hinzuziehen, die dabei helfen.

Es gibt auch viele Datenbankadministratoren, die SQL-Datenbanken verwalten, bei denen nur „grundlegende“ Verfügbarkeit und Leistung erforderlich sind. Diese SQL-Datenbanken werden von kostenbewussten Unternehmen verwendet, die nicht jede Datenbank wie ein „Rennauto“ tunen müssen: Die meisten ihrer SQL-Datenbanken werden von internen Benutzern verwendet, die es gewohnt sind, die Dynamics ERP Leistung und Geschwindigkeit mäßig ist.

3. Welche Fähigkeiten sind bei der SQL-Abfrageoptimierung erforderlich?

Die meisten von uns sind kein Zauberer bei TSQL, aber viele DBAs sind ziemlich gut im Abfragetuning geworden (z.B. durch unsere V8 Performance Workshops).

Eine sehr unwissenschaftliche Schätzung, welche Fähigkeiten jemanden bei der Abfrageoptimierung ausmachen:

40% – die Zeit, das Interesse und die Ressourcen (einschließlich eines Netzwerks von Personen, die gefragt werden müssen), um ein umfassendes Profil von „Leistungsinformationen“ zu erstellen – dies sind Muster, die sich nicht gut optimieren lassen, Randbedingungen, bei denen die Leistung schlecht wird, Verständnis von Trace Flags und Konfiguration auf TSQL.

30% – Interesse und Fähigkeit zu erfahren, wie die Datenbank-Engine Abfragen mithilfe von Indizes und anderen Ressourcen optimiert und verarbeitet und was sich auf die Parallelität auswirkt, wenn mehrere Abfragen gleichzeitig gegen eine Live-Datenbank ausgeführt werden

10% – Verständnis der TSQL-Sprache und verschiedene Möglichkeiten zum Umschreiben einer Abfrage in Verbindung mit der AL oder C/AL Programmierung von Dynamics, um eine bestimmte Ergebnismenge zu erzeugen. Diese Funktioniert aller Dings oft nur mit Datenbank-DevOps.

Was ist Datenbank-DevOps?
Beginnen wir mit der Definition von DevOps. DevOps ist eine Reihe von Praktiken, die Softwareentwicklung (dev!) und IT-Betrieb (ops!) mit dem Ziel kombinieren, mehr Funktionen, Fixes und Updates schneller bereitzustellen, in Übereinstimmung mit den Geschäftszielen. Database DevOps wendet dieselben Prinzipien an und stellt sicher, dass der AL oder C/AL Datenbankcode im selben Prozess wie der Entwicklungscode enthalten ist.

Database DevOps hilft Teams, den Anwendungsentwicklungs- und -freigabeprozess weiter zu identifizieren und zu rationalisieren, indem ein bekannter Engpass behoben wird: Änderungen des AL oder C/AL Dynamics Source Codes.

4. SQL-Abfrageoptimierungstools und ihre Zusammenarbeit
4.1 Ausführungspläne

Wie die SQL-Abfrage aus Dynamics Business Central hinter den Kulissen ausgeführt wird.

  • „Geschätzte“ Ausführungspläne zeigen die Entscheidungen, die der Optimierer zum Ausführen der Abfrage getroffen hat, einschließlich der geschätzten Anzahl von Zeilen, die durch die verschiedenen Teile des Plans fließen werden
  • „Tatsächliche“ Ausführungspläne sind geschätzte Pläne, die mit Laufzeitstatistiken aktualisiert werden, z. B. wie viele Zeilen den Plan durchlaufen haben. Wenn der Plan „adaptiv“ ist, enthält er einige Informationen darüber, welche Optionen gewählt wurden
4.2 Abfragespeicher

SQL-Server 2016+, alle Editionen

  • Diese Funktion verfolgt Ausführungspläne und aggregierte Laufzeit Metriken (Dauer, CPU-Auslastung) zusammen mit aggregierten Wartestatistiken
  • Dies hat auch die Möglichkeit, Pläne „einzufrieren“
  • Abfragespeicherinformationen werden mit der Datenbank selbst wiederhergestellt, sodass sie bei Bedarf zwischen Umgebungen gemeinsam genutzt werden können.
4.3 Dynamische Verwaltungsansichten und Leistungsindikatoren (DMV)
  • Diese helfen, die Engpässe der gesamten Instanz während der langsamen Leistung zu verstehen
  • Beispiel: Gesamtwartestatistik für die Instanz und Metriken zur Speicherlatenz während der Zeit, in der die Abfragen schlecht ausgeführt wurden, können helfen zu erklären, ob die Abfrage wirklich optimiert werden muss
  • Bei der Abfrageoptimierung sind häufig Rückrufe für die „Workload-Optimierung“ erforderlich.
  • SQL-Ablaufverfolgung und erweiterte Ereignisablaufverfolgungen (bei erweiterten Ereignissen handelt es sich um ein Lightweight-System zur Leistungsüberwachung, um Daten zu sammeln und bilden die Grundlage von V8 Search XE)
  • Der „Alte“ SQL Profiler ist für die Abfrageoptimierung im „live“ Betrieb schwierig zu verwenden, da sie Ihre Arbeitslast leicht verlangsamen und Leistungsprobleme beim Tracing verursachen.
  • Ausführungspläne (Filtern hilft in diesem Fall nicht, die Pläne werden alle untersucht / gesammelt und der Filter wird zu spät angewendet)
  • Wartestatistiken (Filtern kann hier helfen, aber die gesammelten Daten sind so massiv, dass man sehr vorsichtig sein muss – und auch das Sortieren und Abfragen der gesammelten Daten ist recht umständlich
  • „Business Central Server Trace Events“ Es gibt zwei Ereignis-Trace-Anbieter, die verschiedene Trace-Ereignisse im Ereignisprotokoll veröffentlichen: Microsoft-DynamicsNAV-Server und Microsoft-DynamicsNAV-Common. Der Microsoft-DynamicsNAV-Common-Anbieter ist ausschließlich für Telemetrie-Trace-Ereignisse vorgesehen. Alle anderen Ereignisse verwenden Microsoft-Dynamics NAV-Server. In der Regel müssen Sie den Ereignis-Trace-Anbieter in dem von Ihnen verwendeten Überwachungstool (z.B. V8 Search XE) angeben.
  • „SQL-Trace-Ereignisse“ verfolgen einen bestimmten Satz von SQL-Anweisungen, die von der Business Central Server-Instanz gegen die Business Central-Datenbank auf SQL-Server ausgeführt werden.

5. Schwierige Probleme – Streit um Ressourcen

Es ist schwer vorherzusagen, wie SQL-Abfragen in einem Live-Workload miteinander interagieren

Gemeinsam genutzte Ressourcen:
  • Arbeitsspeicher für Abfragen – eine bestimmte Menge an Arbeitsspeicher muss für Sortierungen/Verknüpfungen/Verschieben von Daten in einer Abfrage zugewiesen werden. Viele gleichzeitig ausgeführte Abfragen, die viel Arbeitsspeicher benötigen, können dabei zu Problemen führen. (Manchmal gehen die Abfragen davon aus, dass sie viel mehr von diesem Speicher benötigen, als sie benötigen, und er muss optimiert werden – dies wird den Benutzern außerhalb eines Live-Workloads wahrscheinlich nicht bewusst sein)
  • Die Anzahl der Abfragen, die Änderungen vornehmen, und der Ansatz zum Sperren sind außerhalb einer Live-Workload schwer vorherzusagen (Änderungen in Abfrageplänen können zu Blockierungen führen, wenn sie vorher nicht vorhanden waren)
Änderungen der Serverressourcen – sogar Verbesserungen – können zu Blockierungen führen, wenn sie vorher nicht vorhanden waren
  • Beispiel: Der Wechsel zu einem neuen Server mit mehr Arbeitsspeicher und schnelleren CPUs führte zu einer Zunahme der Wartezeiten für Sperren, da die Wartezeiten im Speicher verringert und die Ausführung von Abfragen beschleunigt wurde.

6. Ist eine Prüfung in der „Live“ SQL-Datenbank erforderlich?

Ja. Ein Beispiel dafür ist Parallelität.

Das Optimieren des Parallelitätsgrads für eine Workload und für bestimmte Abfragen in dieser Workload ist in der Regel ziemlich hardwarespezifisch, und Sie benötigen eine Live-Umgebung.

Workload- „Replays“ sind im Toolkit von SQL-Servern verfügbar, aber sie sind:

  • Nur Wiederholungen – Sie können die Aktivität nicht sinnvoll „verstärken“ (das 10-malige Löschen derselben Zeilen ist nicht dasselbe wie das 10-malige Löschen verschiedener Zeilen)
  • Zeitaufwändig einzurichten

7. Automatisierte SQL-Server-Abfrageoptimierung: Verlauf und Entwicklung
7.1 Automatisierte Plankorrektur

SQL-Server 2017+, Enterprise Edition

  • Aufgebaut auf dem Abfragespeicher
  • Erkennt SQL Abfragen, die manchmal schnell und manchmal langsam sind
  • Kann Änderungen nur empfehlen, wenn gewünscht und eingerichtet
  • Kann Pläne einfrieren, testen, ob es hilft und entsprechend reagieren (Das Einfrieren ist als vorübergehende Lösung gedacht – es wird empfohlen, dass ein Benutzer die Abfrage zur Optimierung als längerfristige Lösung auswertet)
  • Sehr gute Funktion zur Identifizierung von Parameter-Sniffing
7.2 Intelligente Abfrageverarbeitung
  • „Intelligente Abfrageverarbeitung“ (Intelligent Query Processing, IQP) wurden in den letzten Versionen von SQL-Server veröffentlicht
  • Mehrere dieser Features beheben häufige Probleme bei der Abfrageoptimierung in SQL-Server
  • Mehr Informationen: Intelligente Abfrageverarbeitung in SQL-Datenbanken

8. Häufige Fehler und Fallstricke bei der SQL-Server-Abfrageoptimierung
  • Fehlende Verbindung zwischen DBAs und Dynamics Entwicklungsteams
  • Mangelnde Kenntnis der Ausführungspläne im Team
  • Mangelndes Verständnis von SQL-Server-Isolationsstufen und „optimistischen“ Optionen
  • Mangelnde Kenntnis wie von Dynamics AL oder C/AL Programmierung auf dem SQL Server umgesetzt wird

Gerne beantworten wir Ihnen persönlich weitergehende Fragen zu diesem Thema.
Ihr dynamicsproject.com Team

SQL Server Buffer Cache


SQL Server Buffer Cache

Was ist der SQL Server Buffer Cache und wie wirkt er sich auf die Leistung z.B. von Microsoft Dynamics ERP Systemen aus.

In SQL Servern ist der „Buffer Cache“ der Speicher, mit dem Anwendungen wie Dynamics 365 Business Central häufig aufgerufene Daten schnell abfragen kann. Wenn Daten in eine SQL Server-Datenbank geschrieben oder aus dieser gelesen werden, kopiert der Buffer Manager sie in den „Buffer Cache“ (auch als Pufferpool – Pufferspeicher eines Datenbankmanagementsystems bezeichnet). Wenn Pufferpool voll ist, werden ältere oder weniger häufig verwendete Datenseiten („Data Pages“) auf die Festplatte verschoben.

Warum sollten Sie den Buffer Cache überwachen?

Die Speichernutzung kann einen erheblichen Einfluss auf die Leistung haben. Wenn nicht genügend Speicher vorhanden ist, werden Datenseiten häufig aus dem Puffercache gelöscht. Dies verlangsamt Abfragen, da SQL Server auf die Festplatte gehen muss, um die Datenseite zu finden. Danach muss der Server die Datenseite im Puffercache wiederherstellen und dann die Seite lesen, bevor das Abfrageergebniss zurückgegeben werden kann.

Es gibt viele Gründe, warum Abfragen langsam ausgeführt werden. Wenn Sie jedoch Speicherprobleme ausschließen möchten, schauen Sie sich an, was im Puffercache vor sich geht. Ein Blick hinein zeigt, welche Datenbank, Tabelle oder welcher Index den Speicher belastet und Druck auf den Puffer ausübt.

Verwenden Sie die folgende SQL Abfrage, um festzustellen, welche Datenbank den meisten Speicher belegt (bei einem Dynamics 365 Business Central System sollte dies immer die „Live“ Datenbank sein):

SELECT CASE database_id
		WHEN 32767
			THEN 'ResourceDb'
		ELSE db_name(database_id)
		END AS database_name
	,COUNT(1) / 128 AS megabytes_in_cache
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id)
	,database_id
ORDER BY megabytes_in_cache DESC;

Führen Sie diese Abfrage in der Datenbank aus, die Sie untersuchen möchten, um die Tabelle oder den Index zu identifizieren, die bzw. der den meisten Speicher belegt.

USE [your database]

SELECT COUNT(1) / 128 AS megabytes_in_cache
	,name
	,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN (
	SELECT object_name(object_id) AS name
		,index_id
		,allocation_unit_id
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
		AND (
			au.type = 1
			OR au.type = 3
			)
	
	UNION ALL
	
	SELECT object_name(object_id) AS name
		,index_id
		,allocation_unit_id
	FROM sys.allocation_units AS au
	INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id
		AND au.type = 2
	) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name
	,index_id
ORDER BY megabytes_in_cache DESC;

Verwalten Sie den Speicher mit Metriken

Obwohl es hilfreich ist, Datenbanken und Indizes auf Überbeanspruchung des Speichers zu überprüfen, ist die Verfolgung von Puffer-Cache-Metriken der beste Weg, um Leistungsprobleme zu identifizieren und zu beheben.

Hier sind die fünf wichtigsten zu überwachenden Metriken, um speicherbezogene Leistungsprobleme (SQL Server Buffer Cache) zu verbessern:

1. Buffer Cache Hit Ratio (Puffer-Cache-Trefferquote)


  • Diese Metrik zeigt, wie SQL Server den Puffercache verwendet
  • Die Trefferquote gibt den Prozentsatz der Seitenanforderungen an, die von Datenseiten aus dem Puffercache ausgeführt wurden, im Vergleich zu allen Datenseitenanforderungen
  • Seiten, die nicht im Puffercache gefunden werden, werden von der Festplatte gelesen, was viel langsamer ist
  • Das ideale Puffer-Cache-Verhältnis beträgt 100 (d.h. der SQL Server liest alle Seiten aus dem Puffer-Cache und keine von der Festplatte)
  • Der empfohlene Puffer-Cache-Wert ist größer als 90

2. Page Life Expectancy (PLE – Lebenserwartung der Seite)


  • Die Seitenlebenserwartung misst, wie lange (in Sekunden) eine Datenseite im Puffercache verbleibt
  • Je länger die PLE ist, desto besser ist die Chance, dass SQL Server die Seiten aus dem Puffercache liest und nicht auf die Festplatte gehen muss
  • Wenn nicht genügend Speicher vorhanden ist, werden Datenseiten häufiger aus dem Puffercache gelöscht, um Speicherplatz für neue Seiten freizugeben
  • In der Vergangenheit betrug ein „normaler“ PLE-Wert 300 Sekunden, wenn Systeme viel weniger Speicher hatten als Heutzutage
  • Für neuere SQL Server wird folgende Formel verwendet, um „gute“ PLE zu bestimmen: Seitenlebensdauer = 300 Sekunden für jeweils 4 GB RAM auf Ihrem Server
  • Die PLE sollte stabil bleiben, wenn er über die Zeit überwacht wird
  • Schnelle, häufige Abnahmen weisen auf Speicherprobleme hin
  • Ein Abfall von mehr als 50% sollte sofort untersucht werden

3. Page reads / Sec. (Serverebene)


  • Diese Metrik zeigt an, wie viele physische Lesevorgänge (d.h. Lesevorgänge von der Festplatte) in einer Sekunde in allen Datenbanken einer Instanz aufgetreten sind
  • Physische Lesevorgänge sind teuer und langsam
  • Verringern Sie physische Lesevorgänge, indem Sie einen größeren Datencache, intelligente Indizes und effizientere Abfragen verwenden oder das Datenbankdesign ändern
  • Der empfohlene Wert liegt unter 90
  • Ein Wert über 90 weist auf unzureichende Speicher- und Indizierungsprobleme hin

4. Page Writes/Sec


  • Diese Metrik gibt an, wie oft Seiten in einer Sekunde auf Serverebene auf die Festplatte geschrieben wurden
  • Der empfohlene Wert liegt unter 90

5. Pages Input/Sec and Pages Output/Sec (Memory Counters)


  • Seiteneingabe / Sek. Ist die Anzahl der Seiten, die pro Sekunde von der Festplatte eingelegt werden
  • Die Seitenausgabe / Sek. Ist die Anzahl der Seiten, die pro Sekunde auf die Festplatte geschrieben werden, um Platz im Puffercache zu schaffen
  • Seiten / Sek. Ist die Summe aus Seiteneingabe / Sek. Und Seitenausgabe / Sek
  • Wenn der Wert für Seiten / Sek. durchweg mehr als 50 beträgt, sind zusätzliche Untersuchungen erforderlich

In V8 Search XE finden die eine Abfrage zur Messung des SQL Server Buffer Cache unter:

V8 Search SQL Server Buffer Cache Navi

V8 Search SQL Server Buffer Cache

Ein fehlerfreier SQL Server Buffer Cache ist eine wichtige Komponente bei der Optimierung der SQL Server-Abfragegeschwindigkeit. Obwohl Speicherprobleme nur einer von mehreren Faktoren sind, die die Abfrageantworten verlangsamen können, sind sie relativ einfach zu identifizieren und zu beheben.
Das Verfolgen dieser fünf Schlüsselmetriken kann Ihnen dabei helfen, Datenseiten länger im Pufferpool zu halten, sodass SQL Server keine Zeit mit der Suche auf der Festplatte verschwenden muss, bevor Abfrageergebnisse zurückgegeben werden.

Gerne beantworten wir Ihnen persönlich weitergehende Fragen zu diesem Thema.
Ihr dynamicsproject.com Team