SQL Server Performance für Dynamics Business Central

SQL Server Performance für Dynamics Business Central. Dynamics NAV ist jetzt Business Central und dann kam irgendwann der Frust

Vor einiger Zeit hatten wir eine Anfrage zu den bekannten Problemen in Business Central / NAV. Die SQL Server Performance für Dynamics Business Central / NAV wäre auch nicht besser als die aktualisierte NAV Version 2013. Täglich viele Tabellensperren, frustrierte Benutzer und ein IT-Partner der etwas ratlos ist.

Unser Angebot, mit  V8 Care den “IST” Zustand des SQL Server und der Dynamics Business Central Lösung zu analysieren und die Engpässe der Datenbank zu sammeln. Getreu dem neuen Motto „Daten sind das Gold des 21. Jahrhunderts“.
Zu unserer Überraschung bekamen wir zur Antwort: “Daten zur Analyse der Dynamics Business Central Probleme seien genügend gesammelt worden.” Da der Interessent nicht über eigene IT-Experten im Unternehmen verfügte, nutzt er spezialisierte IT Dienstleister, die den SQL Server und Dynamics Business Central betreuen.
Zwar leben wir in einer Welt, in der Algorithmen in vielen Bereichen die Entscheidungen darüber treffen, was beim Eintreten eines bestimmten Ergebnisses oder dem Überschreiten einer Datengrenze zu passieren hat. Aber in ganz vielen Anwendungsszenarien ist und bleibt der Mensch die letzte Instanz.

Unsere Frage, was für Daten wurden gesammelt? Mit welchem SQL Server Monitoring Tool? Es gibt phantasische Real-Time SQL Server Monitoring Tools. Leider liefern die SQL Monitoring Tools keine Informationen zum Dynamics AL Code.

So ist bei der Überwachung von IT-Sicherheitssystemen letztendlich der IT-Admin oder Chief Security Officer derjenige, der die Entscheidungen trifft.

Aus diesem Grund möchten wir kurz auf die Möglichkeiten eingehen, die unser Analyse-Tool  V8 Search XE  bietet.

Das Problem:

Der SQL Server ist in der Lage, Anfragen von einer großen Anzahl gleichzeitiger Benutzer zu bearbeiten. Wenn SQL Server Anfragen von vielen Clients bearbeitet, besteht eine hohe Wahrscheinlichkeit, dass Konflikte auftreten, weil verschiedene Prozesse gleichzeitig Zugriff auf die selben Ressourcen anfordern. Ein Konflikt, bei dem ein Prozess darauf wartet, dass ein anderer Prozess eine Ressource freigibt, wird als Block bezeichnet. Obwohl sich in SQL Server ein blockierter Prozess normalerweise selbst auflöst, wenn der erste Prozess die Ressource freigibt, kann ein Prozess eine Transaktionssperre halten und sie nicht freigeben.

Unsere Lösung:

Um einen blockierten Prozess von Dynamics NAV / Business Central freizugeben, müssen wir zunächst feststellen, welcher Prozess der blockierende Prozess ist. Und dann, wenn möglich, den Sperrprozess von Dynamics NAV/BC analysieren und optimieren.
In V8 Search XE gibt es viele verschiedene Möglichkeiten, einen Blockierungs- und Entsperrungsprozess zu identifizieren, die unten aufgeführt sind:

1. SQL Server: Extended Events
2. SQL Server: Dynamic Management Views (DMV)
3. Windows Trace Events: SQL Trace Events
4. Business Central/NAV Server: C/AL tracing

 SQL Server: Extended Events

Das Ziel der durch V8 installierten Extended Events ist es, alle Informationen die von den Extended Events-Sitzungen gesammelt werden, in einer lesbarein Form darzustellen.
Die SQL-Abfragen, die den angegebenen Schwellenwert überschreiten (wir empfehlen mit 10 sekunden zu starten), werden in verschiedenen Extended Events-Sitzungen aufgezeichnet und gesammelt. Die SQL-Skripte werden als Grundlage für die Codeanalyse in V8 Search XE verwendet. Im V8 XE Profiler werden SQL-Abfragen bei ihrer Erstellung durch das Dynamics Business Central / NAV-Objekt aufgenommen.

V8 Extended Events sessions:
1. blocked_process -> Tabellensperren und Deadlocks
2. long_duration -> Lang laufende SQL-Abfragen in Business Central/NAV
3. V8_User_NAV_Trace -> Diese Sitzung ermöglicht die Anzeige von SQL-Abfragen für alle vom C/AL-Code ausgegebenen Anweisungen (Name des Windows-Benutzers mit der SPID des SQL Servers). Diese werden im V8 XE Profiler gespeichert und als Kommentare angezeigt.
/*
Get connection from the pool.
User: ComputerName\WindowsUsername
*/
4. V8_FullSQL_NAV_Trace -> Die Sitzung ermöglicht die Anzeige von SQL-Abfragen für alle vom C/AL-Code ausgegebenen Anweisungen. Diese werden als Kommentare für eine komplette Transaktion im V8 XE Profiler des SQL-Servers gesammelt und angezeigt.

 SQL Server: Dynamic Management Views (DMV)

“DMVs” sind in SQL Server eingebaute Abfragestrukturen, die Details über den Zustand und die Leistung von Servern und Datenbanken liefern. DMVs bieten einen gemeinsamen Mechanismus zum Extrahieren von “all things SQL” sowie von Leistungsdaten des Windows-Betriebssystems. Es gibt mehrere DMV-Kategorien, die Konfigurationsinformationen und Leistungsdaten zurückgeben.

 Windows Trace Events: SQL Trace Events

SQL Trace-Ereignisse verfolgen einen bestimmten Satz von SQL-Anweisungen, die von der Business Central Server-Instanz gegen die Business Central/NAV-Datenbank auf SQL Server ausgeführt werden.
Zu den gesammelten Ereignisdaten gehören: session ID, tenant ID, der Benutzer von Business Central/NAV und die SQL-Anweisung. Die Auflistung ist nur der SQL-Teil der Trace-Ereignisse von Business Central Server.

Wichtig!
Um diese Daten zu sammeln, benötigen Sie einen V8-Dienst pro Business Central Server-Instanz. Diese Funktion ist ab Version 6 der V8-Dienste verfügbar und erfordert das .NET Framework 4.7.2, das bei älteren Dynamics NAV nicht standardmäßig installiert ist.

Es besteht auch die Möglichkeit, einzelne Ereignis-IDs zu sammeln.

Die folgende Tabelle listet ein paar Dynamics SQL-Trace-Ereignisse auf. Zum Beispiel:

IDEvent (task/opcode)What is traced
1ExecuteScalar/StartSQL statements that query a database table and return a single field from a row in the query result.
2ExecuteScalar/StopSQL statements that query a database table and return a single field from a row in the query result.
3ExecuteNonQuery/StartSQL statements that return a number of rows from a database table
4ExecuteNonQuery/StopSQL statements that return a number of rows from a database table
5ExecuteReader/StartSQL statements that return a set of rows from a database table.

So sehen die SQL-Trace-Ereignis Daten aus, die in der SQL-Tabelle “V8 ETW Log Viewer” in der V8 Search XE-Datenbank gespeichert werden.

 Business Central/NAV Server: C/AL tracing

Seit der Version Microsoft Dynamics NAV 2013 verfügt der Server über eine Funktion, die es Ihnen ermöglicht, den AL-Aufrufstapel für SQL-Befehle anzuzeigen. Full SQL Trace aktiviert / deaktiviert das Tracing für alle neuen und bestehenden Sitzungen pro Dynamics Server-Instanz.

Damit können Sie SQL-Abfragen für alle von der AL ausgegebenen Anweisungen anzeigen. Alle SQL-Anweisungen zwischen aufeinander folgenden Kommentaren entsprechen der AL-Anweisung ab dem ersten Kommentar.
Diese Kommentare entsprechen den Ereignissen, wenn die Verbindung abgerufen und an die Microsoft DynamicsNAV Server-Verbindungsabfrage zurückgegeben wird. Diese Kommentare werden benötigt, um SQL-Abfrageprobleme von verschiedenen Clients auf derselben SQL-Verbindung zu trennen. Die SQL-Anweisung, die mit diesen Kommentaren übereinstimmt, wird von Microsoft Dynamics NAV Server ausgegeben, aber nicht von AL. Kommentare, die nur den Benutzernamen enthalten, entsprechen ebenfalls SQL-Anweisungen, die von Microsoft Dynamics NAV Server, aber nicht von Dyanmics AL Code ausgegeben werden.

Beispielsweise führt Microsoft Dynamics NAV Server Abfragen aus, um berechnete Felder zu berechnen, die in den Faktenfeldern angezeigt werden. Diese Arten von Kommentaren sind erforderlich, da Microsoft Dynamics NAV Server möglicherweise eine SQL-Abfrage ausführt, ohne dass eine erneute Verbindung zum Pool hergestellt wird, und sie nicht von Dynamics AL stammen.

Wichtig! Um diese Daten zu sammeln, benötigen Sie einen V8-Dienst pro Business Central Server-Instanz.

Daten sammeln und analysieren
V8 Search XE speichert zwei Arten von Daten:

1. Nach dem der SQL-Trace erfasst wurde, werden die Daten in der SQL-Tabelle gespeichert. Der Trace wurde in der Tabelle “V8 XEvents Full SQL Trace” in der V8 Search XE-Datenbank gespeichert.

2. Der vollständige AL-Programmiercode der jeweiligen Objekte. Die Daten sind in der Tabelle “V8 Performance Profiler” in der V8 Search XE-Datenbank gespeichert.

Mit dem Modul “V8-Quellcode-Suche” können Sie Ihre gesamte Dynamics NAV/BC-Codebasis durchsuchen, um die Stellen zu finden, an denen bestimmte Codeelemente referenziert werden.

Fazit
Diese speziellen Daten, sind mit einem Real-Time SQL Server Monitoring Tool nur sehr schwer zu erfassen. Sie müssen alle im Business Central Server-Instanz parallel überwachen.

V8 Search XE soll allen Administratoren und Entwicklern, die eine ERP-Lösung von Dynamics NAV/BC unterstützen, die Möglichkeit geben, eine Aussage über die Schwächen der von Dynamics Dynamics Business Central / NAV generierten SQL-Befehle zu treffen und diese zu dokumentieren.

Es zahlt sich auf jeden Fall aus, die Dynamics NAV Performance von einem Dritten mal checken zu lassen um herauszufinden, in welchem Zustand das ERP System und der SQL Server sich befindet.
Danach kann man überlegen, welche Optionen man für die weitere Vorgehensweise dann hat, die SQL Server Performance für Dynamics Business Central zu verbessern.

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