Navision SQL Server Optimierung seit dem Release NAV 5.0 SP1 bis NAV 2009 SP1

Navision SQL Server Optimierung seit dem Release NAV 5.0 SP1 bis NAV 2009 SP1

  • Comments 3

das Thema SQL Performance ist schon immer eines unserer “heißen” Themen gewesen. Im Folgenden möchte ich ein paar wesentliche Punkte darlegen, was sich eigentlich seit der Version NAV 5.0 SP1 signifikantes in der technischen Umgebung vom NAV Client in der Kommunikation mit dem SQL Server geändert hat und welche Fortschritte in der Performance erreicht wurden.

  • Der SQL Index wird seit der Version NAV 5.0 SP1 nicht mehr genutzt

Erfahrung haben gezeigt, dass der SQL Index mit der Index Definition von NAV nicht harmonisiert bezüglich der Indexabfrage des TSQL Statements  “UPDLOCK”. Bei diesem Statement werden die Records beim Lesen gelockt, um nicht verändert zu werden.  So wurden in der Version NAV 4.0 SP3 bis zum Update6 (KB 940718) und dem Hotfix  KB950920 ausschließlich die sogenannten “Fast Forward Cursor” (FFo)  verwendet, welche bei der Indexabfrage die “WHERE” Klausel optimiert abfragt und überprüft welcher Datensatz  mit der NAV Indexdefinition korreliert.

Ab der Version NAV 5.0 wurde die finsql.exe Runtimeumgebung so programmiert , dass die Ergebnismenge dynamisch sein muss, d.h. diese Ergebnismenge enthält nur Zeilen in denen ein “Insert” oder “Update” erfolgt ist und enthält keine Zeilen, die gelöscht worden sind. Darum wird seit NAV 5.0 der Dynamic Cursor für die Abfragen des NAV Clients auf dem SQL Server verwendet, um eine dynamische Ergebnismenge in den Abfragen zu generieren. Hierbei versucht der SQL Server immer versucht einen Zugriffsplan erstellt, der die verschiedenen Indizes mit dem “ORDER BY” Statement sortiert und die geleichen Ergebnismengen mit dem WHERE Statement abgleicht. Dadurch wird ein eine SQL Server interne statistische Ereignismenge angelegt, welche nur die veränderten Datensätze der momentanen Transaktion zwischen NAV Client und SQL Server enthält. Der FFo Cursor beliebt aber weiterhin in der finsql.exe integriert, und wird für die Anfragen von Form verwendet, der Dynamic Cursor für die Abfragen der Tabellen.

Diese dynamische Generierung von Ereignismengen durch die Dynamic Cursor hat zu folge, dass SQL Indizes nicht an Tabelle verwendet werden sollten, welche während dem Schreibzugriff einen Lese- oder Änderungszugriff erfahren.

  • Parameter Sniffing gibt es nur im SQL Server 2005 und bis Dynamics NAV 5.0 RTM

Es gibt jedoch eine eine Unterschied im Verhalten der finsql.exe beim Zugriff auf einen SQL Server 2005 und SQL Server 2008. Im SQL Server 2005 wird eine Zugriffsplan auf Grund von Histogrammen erstellt, in dem eine Liste generiert wird, in der Parameter (wie ein WHERE Statement zu einem dedizierten Wert) zu einer Spalte einer Tabelle bestimme Werte zugewiesen werden. Hier wird ein Zugriffsplan erstellt, welcher auf errechneten Werten der ersten in der Tabelle existierenden SQL Indexe oder Cluster Indexes. Dabei wird aber immer nur die erste Spalte des Indexes beachtete. Das hat des Öfteren zu Performanceproblemen geführt, da in dem so genannten Parameter Sniffing diese evaluierten Parameter nicht für untergeordneten  Anfragen auf diese Tabelle anwendbar sind. "Parameter Sniffing" bezieht sich auf hier einen Prozess, bei dem Umgebung für die Ausführung von SQL Server die aktuellen Werte der Parameter während der Kompilierung oder Neukompilierung "sniffs" übergibt und dem Abfrageoptimierer so verwendet werden können, dass darus möglicherweise schnellere Abfrageausführungspläne generieren werden. Das Wort "aktuellen" bezieht sich auf die Werte der Parameter im Aufruf-Anweisung, welche durch eine Kompilierung oder eine Neukompilierung verursacht vorhanden.wurden.

In späteren Schnittstellen Versionen (API’s) von NAV zu SQL Server (in NAV ab dem SP1 für NAV 5.0) wurde aber auch das verändert. Dies Veränderung ist mit in die Veröffentlichung des SQL Servers 2008 eingeflossen und bezieht sich auf die Abfragealgorithmen welche hier “Optimize for unknown” heißt.

Diese Abfrage verändert das Verhalten der finsql.exe in der Art, dass nun keine Histogramme mehr verwendet werden und jeder Index einer Tabelle wird nun gleichwertig betrachtet und nicht mehr nur der erste Index. Das hat nun den Vorteil, das die Anzahl der Abfragen reduziert werden und somit die Performance des NAV SQL Clients sich steigert.

Für eine technische Erklärung der SQL Clients von NAV möchte ich Sie auf folgenden Blogeintrag meines Kollegen Lars Lohdorf Larsen hinweisen

Abschließend kann gesagt werden, das die Optimierung der Performance vom NAV Client immer weiter verbessert wird in neune Releases. Es gilt abzuwarten was in neuen Produktversionen diesbezüglich noch zu erwarten ist.

 

Herzliche Grüsse,

Peter Schimon Mosessohn

Specialist Support Engineer Microsoft Dynamics NAV

EMEA Customer Support & Services - SMS&P

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • Hallo Peter,

    Super! Ein hervorragender Beitrag der das Zusammenspiel von Indexen und Cursor und der ggf. daraus resultierenden Probleme sehr gut darstellt.

    Vielleicht noch folgendes als Ergänzung:

    "Parameter Sniffing" Probleme können auch (vor SQL 2008 und "OPTIMIZE FOR UNKNOWN") mittels RECOMPILE Hinting (via "$ndo$dbconfig") vermindert werden (oder "Plan Guides" im SQL Server).

    Manchmal trägt auch die "Cursor Preparation" zu solchen Problemen bei, die kann in NAV 5.0 SP1+ via "diagnostics" Eintrag (in "$ndo$dbproperty") bei Bedarf deaktiviert werden.

    Dennoch bleibt, dass "Dynamic Cursor" aufgrund ihrer "Affinität" zum ORDER BY oft problematisch bleiben, es ist of nicht so einfach einen optimalen Index für "Problemabfragen" zu bauen, da es in NAV naturgemäß oft zu großen Unterschiedene in "Filter-Feldern" und "Sortierungs-Key" kommt(insbesondere in "Forms", deshalb ist es schön dass hier noch FFC genutzt werden).  Optimal wäre es, wenn man finsql.exe so parameterisieren könnte, dass man den Cursor Typ wählen kann (z.B. via "diagnostics"?).

    Cheers,

    Jörg

  • Und nach einer weiteren Tass Kaff und einem Tabakspfeifchen in der Frühlingssonne fällt mir nochwas ein ;c)

    Das mit den UPDLOCK ist je ein zweistufiges Problem: Erst serialisiert sich der Prozess (isolation SERIALIZABLE) und darf somit nur noch "committed" lesen, dann versucht er selbst die UPDLOCKs zu setzten. Das birgt so natürlich sehr hohes Konfliktpotential in sich, es kommt häufig zu Blockaden. Wäre es hier nicht wünschenswert - in künftigen NAV Versionen - mit "SNAPSHOT Isolation" und ohne UPDLOCK zu arbeiten?

    Cheers,

    Jörg

  • Und schon wieder ich ;c)

    Ähm, ich habe heute ein paar Nachfragen zu dieser Aussage erhalten:

    >>Der SQL Index wird seit der Version NAV 5.0 SP1 nicht mehr genutzt

    Ich fürchte das ist ein wenig miss(t)verständlich formuliert; einige denken, dass es das Feature "SQLIndex" nicht mehr gibt - was so ja nicht richtig ist.

    GEMEINT ist (oder?), dass im NAV Standard die Eigenschaft "SQLIndex" wieder leer ist, NUR in NAV 5.0 hat man versucht, hier schon "ab Werk" optimierte Indexe einzuführen - was aber nicht funktiniert hat.

    Grundsätzlich funtz "SQLIndex" schon: was man hier einträgt, überschreibt die Standard NAV "Key - Index" Übersetzung. Allerdings muss man hier aufpassen und wissen was man tut, da man sonst eher Probleme heraufbeschwört als zu lösen ...

    IMHO ist die einzige sinnvolle und gefahrlose Nutzung des "SQLIndex" in dem man den Inhalt von "Key" 1:1 nach "SQLIndex" kopiert - so werden nur die angehängten PK Anteile und die UNIQUEness entfernt; ich nenne das "Basic Streamlining" (ergo: es kann zu keinen Konflikten zwischen Index-Sortierung und Key-Sortierung kommen! - kein Stress mit DC & FFC).

    Diese Minimal-Optimierung lohnt sich aber nur bei großen Tabellen, die viele Indexe haben ...

    Auf keinen Fall sollte man im "SQLIndex" "Key"- Felder entfernen oder umsortieren, das wird früher oder später weh tun ...

Page 1 of 1 (3 items)