Navision 4 + SQL Server 2005 SP2 Optimierung von Grundauf

Guten Morgen,

vielleicht eine ungewöhnliche Frage und falls dazu niemand bereit wäre kann ich das auch verstehen.
Ich bin seit kurzem Administrator in einem Versorgungsunternehmen welches Navision 4.0 SP3 primär mit der Build 25732 einsetzt.
Wir haben schon seit längerem mit Performance Problemen mit unter bei Standartabfragen zu kämpfen. Ich vermute sehr stark das dies mit einer nicht optimal konfigurierten DB zu tun hat.
Mein leidiges Problem ist, dass ich eher geringe Kenntnisse im Bereich SQL Server besitze, da ich im Haus von Server bis zum Handy alles abdecken soll.

Unsere Haupt- Datenbank umfasst derzeit 91GB, auf dieser Arbeiten in der Regel parallel 30 Mitarbeiter.
Diese Datenbank enthält etwa 30.000 Tabellen.
Es werden bereits diverse Maintenance Plans durchgeführt die zur Sicherung und Optimierung dienen.
Sämtliche Datenbanken liegen auf einem SAN Laufwerk welches über Glasfaser angebunden ist. Insgesamt liegen die Datenbanken auf 16 Festplatten 15K U/min.

Beim Zugriff selbst einfacher Anfragen steigt die Festplattenlast immer enorm an, sodass sich Warteschlangen bilden. Ich tippe daher darauf, dass die DB falsch optimiert ist und bitte um Hilfe.

Wenn möglich würde ich aufgrund meiner geringen Kenntnisse im Bereich SQL Server mit eher grundlegenderen Fragen anfangen.

Vielen Dank im Voraus für die Antworten.

Hallo Paul,

herzlich Willkommen bei MSDynamics.de

Nun, "NAV/SQL Performance " ist soz. mein "Steckenpferd" - also wenn Du Fragen hast: NUR HER DAMIT!
Ich empfehle Dir auch mal das Forum nach "SQL Performance" zu durchsuchen, da wirst Du "tonnenweise" Tipps & Infos finden!

Hier auch ein paar BLOGS zum Thema:
http://dynamicsuser.net/blogs/stryk/
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/

Und ein wenig Eigenwerbung (der Moderator möge mir verzeihen und dies ggf. löschen):
http://www.amazon.de/NAV-SQL-Performance-Field-Guide/dp/3837014428/ref=sr_1_1?ie=UTF8&s=books-intl-de&qid=1232711413&sr=8-1

Hallo und danke erst einmal für deine Antwort,

eine erste Frage würde ich gerne mal als Problem schildern, vielleicht wissen Sie darauf ja eine Antwort.

Wir hatten heute folgenden Fall: Eine Mitarbeiterin ging auf PC1 in Navision in die Übersicht der Buchungssätze, bis diese erschienen vergingen ca. 10 Minuten, bei jedem Buchen eines Buchungssatzen vergingen auch noch einma ca. 3 - 5 Minuten. Die Kollegin wechselte dann an PC2 an diesem erschienen alle Buchungssätze sofort und auch das Buchen war kein Problem mehr.

Auf beiden PCs ist die selbe Build von Navision installiert, sie war bei beiden PCs mit dem selben Login angemeldet. PC1 war ein P4 3Ghz mit 512 MB Ram und PC2 war ein C2D mit 2x3,1Ghz und 2GB Ram.

Jetzt wäre die Frage ob es am PC liegen kann das die Abfragen so lange dauern. Denn laut Task Manager etc. ist so gut wie keine Last zu bemerken.
Allerdings gehen in beiden Fällen die Festplattenzugriffe auf dem SQL Server SAN enorm nach oben.

Gibt es eine optimale Anforderung für Navision? (Quad Core für alle die größere Buchungen / Zugriffe) vornehmen? Oder kann es sein das auf 2 unterschiedlichen PCs mit gleicher Version die SQL Anfragen unterschiedlich generiert werden? Dadurch das, wie ich denke, die Systemleistung des SQL Server ausreichen sollte, die Festplatten lange Warteschlangenzeiten aufbauen. Befürchte ich das die SQL Abfragen falsch oder suboptimal gestellt werden. Ähnlich einem anderen Beitrag in dem erst ein "Pointer/cursor" erst vollgeladen wurde und dann die Suche statt fand.

Gibt es eine Möglichkeit sich die an den SQL Server gestellten Abfragen anzeigen zu lassen? So dass ich diese gegebenfalls hier Posten könnte um Sie einmal nach falschen oder unnötigen Einträgen zur Schau zu stellen?

Vielen Dank.

OK, erst mal was allgemeines:

Mit NAV 4.0 SP3 sollte man mindestens Update 6.9 (Build 26565) oder höher einsetzen. SQL Server 2005 solle mindestens Update 4 sein (Build 3200) oder höher.
In NAV ist unbedingt darauf zu achten, dass das "Default Index Hinting" deaktiviert ist.
Wie ist der SQL Server ausgestattet, CPU/RAM? Bei 91GB mit 30 Usern sollte es 1 x QuadCore oder 2 x DualCore CPU sein mit mindestens 8GB RAM (besser 16GB und mehr)!

Wir hatten heute folgenden Fall: Eine Mitarbeiterin ging auf PC1 in Navision in die Übersicht der Buchungssätze, bis diese erschienen vergingen ca. 10 Minuten, bei jedem Buchen eines Buchungssatzen vergingen auch noch einma ca. 3 - 5 Minuten. Die Kollegin wechselte dann an PC2 an diesem erschienen alle Buchungssätze sofort und auch das Buchen war kein Problem mehr.
Auf beiden PCs ist die selbe Build von Navision installiert, sie war bei beiden PCs mit dem selben Login angemeldet. PC1 war ein P4 3Ghz mit 512 MB Ram und PC2 war ein C2D mit 2x3,1Ghz und 2GB Ram.
Jetzt wäre die Frage ob es am PC liegen kann das die Abfragen so lange dauern.

Hier kann u.U. ein Caching-Problem vorliegen; d.h. als die Abrage das erste mal gestellt wurde, musten die Daten erst in den Cache gelesen werden (= Druck auf SAN). Beim zweiten mal waren die Daten im Cache, dann ging's flott ... d.h. danach müsste es auch am PC1 schneller gehen. Wenn nicht, dann kann es sein, dass der PC nicht genügend Speicher hat um die Daten aufzunehmen, was z.B. auch daran liegen kann, dass der RAM des PC1 mit irgendwelchem Mist zugemüllt ist ... u.U. kann es helfen, das entsprechende Benutzer-Profil aufzuräumen oder neu zu erstellen ...
Und: es muss sicher gestellt sein, dass der NAV Object-Cache auf 32.000KB konfiguriert ist.

Denn laut Task Manager etc. ist so gut wie keine Last zu bemerken.

Auch keine Swapping-Aktivitätetn? Netzwerk?

Allerdings gehen in beiden Fällen die Festplattenzugriffe auf dem SQL Server SAN enorm nach oben.

Lesen, nehme ich an. Entweder werden hier eine Menge an Datensätzen "gepumpt" oder es werden Indexe/Tabellen abgescannt - in beiden Fällen wird eine hohe Zahl a "Logical Reads" erzeugt, die widerum zu "Physical Reads" führen ...
Alleine die Tatsache, dass es sich um ein SAN handelt heisst noch lange nicht, dass die Perfromance damit gut ist: Ein SAN muss KORREKT für den SQL Server Betrieb konfiguriert sein!

Gibt es eine optimale Anforderung für Navision? (Quad Core für alle die größere Buchungen / Zugriffe) vornehmen? Oder kann es sein das auf 2 unterschiedlichen PCs mit gleicher Version die SQL Anfragen unterschiedlich generiert werden? Dadurch das, wie ich denke, die Systemleistung des SQL Server ausreichen sollte, die Festplatten lange Warteschlangenzeiten aufbauen. Befürchte ich das die SQL Abfragen falsch oder suboptimal gestellt werden. Ähnlich einem anderen Beitrag in dem erst ein "Pointer/cursor" erst vollgeladen wurde und dann die Suche statt fand.

Vorrangig sollte man sich erst einmal um den Server kümmern. Bei gleicher NAV Version wird der selbe C/AL Code immer gleich "übersetzt"; d.h. die Abfragen sind immer gleich. Allerdings kann der SQL Server unterschiedliche "Ausführungspläne" erzeugen; hier spielt ebenfalls der (Prozedur-)Cache eine Rolle.
Stellt man fest, dass der SQL Server die Abfragen gut bearbeitet, und kommt diese Performance beim User am PC nicht an, dann liegt das Problem wohl tatsächlich beim PC (oder dem Weg dahin (Netzwerk, CITRIX, Terminal Server, etc.)).
Benutzer, die mit hohem Daten-/Transaktionsvolumen umgehen müssen benötigen auch leistungsfähige PC: alle Daten werden ja vom Server an den Client übermittelt; hat der PC zu wenig Speicher geht die Perfromance in den Keller (Swapping, etc.). 1 bis 2 GB RAM sowie einen DualCore sollte man heutzutage schon haben; echte Power-User 1 Quad Core mit 3GB (PC-OS Maximum), oder darüber hinaus kann's auch schon mal ein kleiner Server sein. Auch die Netzwerkanbindung spielt eine Rolle, Gigabit darf es schon sein.
(P.S.: es gibt auch einen offiziellen "NAV Hardware Sizing Guide" der aber IMHO viel zu tief stapelt)

Gibt es eine Möglichkeit sich die an den SQL Server gestellten Abfragen anzeigen zu lassen? So dass ich diese gegebenfalls hier Posten könnte um Sie einmal nach falschen oder unnötigen Einträgen zur Schau zu stellen?

Yo, dafür ist der "SQL Server Profiler" bestens geeignet! Alles was mehr als 1000 "Reads" erzeugt und/oder länger als 50 msec "Duration" hat kann als Problem betrachtet werden ...

Aber auch von Applikationsseite gäbe es noch einiges zu tun ... in höchster Priorität sollte das System soz. ent-SIFT-et werden; d.h. die Anzahl der Buckets muss reduziert werden (siehe mein BLOG). Auch periodische SIFT Wartung ist Pflicht (ACHTUNG: erfordert NAV Buils 26565+). Und dann wäre noch die DB Konfiguration, tempdb, etc. ...

Ich hoffe das hilft Dir weiter!

Schöne Grüße,
Jörg

Super, das war doch schonmal ne sehr hilfreiche Antwort :)

Ich fang mal an Teile deiner Gegenfragen zu beantworten, den Rest versuche ich Montag.

Also

Mit NAV 4.0 SP3 sollte man mindestens Update 6.9 (Build 26565) oder höher einsetzen. SQL Server 2005 solle mindestens Update 4 sein (Build 3200) oder höher.

Microsoft SQL Server 2005 - 9.00.3203.00 (Intel X86) Oct 23 2007 23:12:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Ich habe eine aktuellere Version von Navision vorliegen (Build 27256) jedoch wurde mir gesagt, dass ich diese Nicht installieren kann da diese Version andere Abfragekriterien nutzt und dadurch das System noch langsamer wird. Bzw. das dann erst am SQL Server eine andere optimiertere Build eingespielt werden müsste.

In NAV ist unbedingt darauf zu achten, dass das "Default Index Hinting" deaktiviert ist.

Wo kann man das nachschauen?

Wie ist der SQL Server ausgestattet, CPU/RAM? Bei 91GB mit 30 Usern sollte es 1 x QuadCore oder 2 x DualCore CPU sein mit mindestens 8GB RAM (besser 16GB und mehr)!

Aktuell:
System Manufacturer: MAXDATA
System Model: PLATINUM 5220 I
BIOS: SE7520AF20 v10.00, P
Processor: Intel(R) Xeon(TM) CPU 3.20GHz (4 CPUs), ~3.2GHz
Memory: 3584MB RAM
Page File: 3253MB used, 4284MB available

Der neue steht schon bereit und wird anfang März ins Produktivsystem gehen:
System Manufacturer: HP
System Model: ProLiant DL380 G5
BIOS: Default System BIOS
Processor: Intel(R) Pentium(R) III Xeon-Prozessor (4 CPUs), ~2.5GHz
Memory: 4094MB RAM
Page File: 554MB used, 5324MB available

Also ziehe ich da schonmal ein Fazit - Wir brauchen mehr Arbeitsspeicher. Allerdings läuft der jetzige noch auf einem 32 Bit Betriebssystem der neue ist ein Windows 2003 Server 64Bit.

... u.U. kann es helfen, das entsprechende Benutzer-Profil aufzuräumen oder neu zu erstellen ...

Das lokale / serverseitig- gespeicherte Benutzerprofil ? oder kann sich ein Navisionprofil auch zumüllen?

Und: es muss sicher gestellt sein, dass der NAV Object-Cache auf 32.000KB konfiguriert ist.

Der wird bei uns über das Icon auf 64.000 eingestellt. Schlimm wenn es mehr ist?

Auch keine Swapping-Aktivitätetn? Netzwerk?

Das werde ich Montag bei der Kollegin noch einmal testen.

Ein SAN muss KORREKT für den SQL Server Betrieb konfiguriert sein!

Was genau gibt es da für optimal Vorgaben? Blockgröße? Anzahl an Platten? Etc. Wir haben immoment 3 Raids, jeweils für Log, Backup und Daten. 10 der Festplatten sind für die Daten, also die Datenbanken.

Yo, dafür ist der "SQL Server Profiler" bestens geeignet! Alles was mehr als 1000 "Reads" erzeugt und/oder länger als 50 msec "Duration" hat kann als Problem betrachtet werden ...

Könnten Sie mir mitteilen wie ich den SQL Server Profiler einstelle um einmal zu betrachten / mitzuloggen wo Probleme auftreten?

Aber auch von Applikationsseite gäbe es noch einiges zu tun ... in höchster Priorität sollte das System soz. ent-SIFT-et werden; d.h. die Anzahl der Buckets muss reduziert werden (siehe mein BLOG). Auch periodische SIFT Wartung ist Pflicht (ACHTUNG: erfordert NAV Buils 26565+). Und dann wäre noch die DB Konfiguration, tempdb, etc. ...

Das werde ich mir am Montag samt BLOG mal zu gemüte führen. Sofern mich mein Telefon in Ruhe läßt.

Ich weiß leider nicht in wiefern Ihnen das etwas sagt doch ich befürchte aufgrund der Tatsache das wir auch mit 2 NAS Anwendung arbeiten und einem EDAP wird unsere jetzige Serverkapazität wohl Performanceseitig am Limit sein.

Wünsche ein schönes Wochenende.

Grüße Paul

Ich habe eine aktuellere Version von Navision vorliegen (Build 27256) jedoch wurde mir gesagt, dass ich diese Nicht installieren kann da diese Version andere Abfragekriterien nutzt und dadurch das System noch langsamer wird. Bzw. das dann erst am SQL Server eine andere optimiertere Build eingespielt werden müsste.

Hmmm ... den kenn ich nicht. Ich habe Erfahrungen nur is Update 6.12 , Build 27010, der funzt.

In NAV ist unbedingt darauf zu achten, dass das "Default Index Hinting" deaktiviert ist.

Wo kann man das nachschauen?

In der NAV Datenbank sollte eine Tabelle namens "$ndo$dbconfig" vorhanden sein, darin der Wert "IndexHint=No". Wenn dem nicht so ist, dann Index Hinting wie folgt abschalten:

Code:

USE [Navision]  --- change DB name here
GO
CREATE TABLE [$ndo$dbconfig] (config VARCHAR(1024))
GRANT SELECT ON [$ndo$dbconfig] TO [public]
GO
INSERT INTO [$ndo$dbconfig] VALUES('IndexHint=No')

System Manufacturer: MAXDATA
System Model: PLATINUM 5220 I
BIOS: SE7520AF20 v10.00, P
Processor: Intel(R) Xeon(TM) CPU 3.20GHz (4 CPUs), ~3.2GHz
Memory: 3584MB RAM
Page File: 3253MB used, 4284MB available

Der neue steht schon bereit und wird anfang März ins Produktivsystem gehen:
System Manufacturer: HP
System Model: ProLiant DL380 G5
BIOS: Default System BIOS
Processor: Intel(R) Pentium(R) III Xeon-Prozessor (4 CPUs), ~2.5GHz
Memory: 4094MB RAM
Page File: 554MB used, 5324MB available

Also ziehe ich da schonmal ein Fazit - Wir brauchen mehr Arbeitsspeicher. Allerdings läuft der jetzige noch auf einem 32 Bit Betriebssystem der neue ist ein Windows 2003 Server 64Bit.

Auch mit einem 64bit System würde ich auf 16GB gehen - beim SQL Server gilt: "RAM ist durch nix zu ersetzen, ausser durch noch mehr RAM"
Je mehr Daten gecacht werden können, desto eher werden Fehler im Disk-Subsystem verziehen ...

Das lokale / serverseitig- gespeicherte Benutzerprofil ? oder kann sich ein Navisionprofil auch zumüllen?

Nun, ich hatte mal nen ähnlichen Fall, und da haben wir das Benutzerprofil - lokal und serverseitig - soz. "from the scratch" neu erstellt (nach Sicherung wichtiger Dateien selbstverständlich).

Der [Object Cache] wird bei uns über das Icon auf 64.000 eingestellt. Schlimm wenn es mehr ist?

Bei "Fat Clients" ist das nicht so schlimm, bei CITRIX/Terminal Servern kann das schon was ausmachen. Normalerweise ist ein so großer Object Cache aber unnötig, da die meisten User niemals mit so vielen Objekten arbeiten. Wenn Du z.B. ALLE Objekte in ein FOB exportierst dann ist die Dateigröße ein guter Richtwert für die maximale Object Cache Größe, also wenn ALLE Objekte genutzt würden ... normalerweise liegt man beim Standard von 32.000KB schon richig ...

[SAN:] Was genau gibt es da für optimal Vorgaben? Blockgröße? Anzahl an Platten? Etc. Wir haben immoment 3 Raids, jeweils für Log, Backup und Daten. 10 der Festplatten sind für die Daten, also die Datenbanken.

Ich fürchte DAS ist ein sehr individuelles Thema und hängt von den technischen Möglichkeiten des SAN ab. Wenn ich dich richtig verstehe, dann sind die einzelnen RAID tatsächlich physikalische Laufwerke und keine LUN? Wichtig ist dabei immer, dass bestimmte Komponenten einen dedizierten I/O brauchen; hier mal ein Beispiel:

C:\ RAID1 OS, Swap, Programme, SQL Server [master, model, msdb]
D:\ RAID1 master, model, msdb [, tempdb]
[E:\ RAID1 tempdb]
F:\ RAID1[0] NAV Datenbank (mdf/ndf)
G:\ RAID1[0] NAV Datenbank (ldf)
H:\ RAID1 Backups, Verschiedenes

Wie gesagt, das ist ein Beispiel - sowas muss IMMER im Einzelfall erörtert werden ....

Könnten Sie mir mitteilen wie ich den SQL Server Profiler einstelle um einmal zu betrachten / mitzuloggen wo Probleme auftreten?

Ja kann ich, aber nur wenn DU mich auch DUzt (hier im Forum gilt die informelle Anrede )
Im Anhang findest Du eine Profiler Vorlage (muss in TDF umbenannt werden), die kann dann in den Profiler importiert werden (Datei - Vorlage - Import). Hier können nun Filter ergänzt werden, z.B. >>"Reads" größer oder gleich 1000<< und/oder >>"Duration" größer oder gleich 50<<. Der Output kann in eine Datei gespeichert werden (max. Größe 50-100MB, KEIN "Rollover").

Ich weiß leider nicht in wiefern Ihnen das etwas sagt doch ich befürchte aufgrund der Tatsache das wir auch mit 2 NAS Anwendung arbeiten und einem EDAP wird unsere jetzige Serverkapazität wohl Performanceseitig am Limit sein.

Jaaaa, schön wäre es schon, wenn man den NAS einen eigenen Server spendieren würde (EDAP ???) ... die NAS verbrauchen u.U. einiges an CPU und RAM ...

Schönes Wochenende!
Jörg

Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.

Guten Morgen erstmal,
wünsche ein schönes Wochenende gehabt zu haben.

Also ich habe gerade mal in die "$ndo$dbconfig" hineingeschaut und folgendes stand in den ersten Zeilen:

Code:


0   NTS   config   1   IndexHint=No
2   100 Servicemandant SWL   config   1   IndexHint=Yes;Company="100 Servicemandant SWL";Table="Gen. Journal Line";Key="Journal Template Name","Journal Batch Name","Account Type","Account No.","Issued Invoice No.","Line No.";Search Method=;Index=7
3   100 Servicemandant SWL   config   1   IndexHint=Yes;Company="100 Servicemandant SWL";Table="Payment Line";Key="Payment No.","Account No.","VU Banking Account ID","Umlage geb. Abrechnung Nr.","Umlage geb. Abrechnung Lfd-Nr.";Search Method=;Index=1
4   100 Servicemandant SWL   config   1   IndexHint=Yes;Company="100 Servicemandant SWL";Table="Payment Line";="Payment No.","Line No.";Search Method=;Index=2
10   100 Servicemandant SWL   config   1   IndexHint=Yes;Company="100 Servicemandant SWL";Table="Reg. VU Abrechnungskopf";Key="Status","Buchungsdatum","Bis-Datum";Search Method=;Index=5
11   100 Servicemandant SWL   config   1   IndexHint=Yes;Company="100 Servicemandant SWL";Table="Reg. VU Abrechnungszeile";Key="Rechnungsnr.","Betragsberechnungsart";Search Method=;Index=3



Also primär scheint IndexHint ausgeschaltet zu sein, aber für manche Tabellen im nachhinein wieder eingeschaltet worden zu sein.

Dann läuft gerade auf dem Server der Trace mit, ich habe jetzt die UND einrichtung genommen, also Reads >= 1000 && Duration >= 50, und was soll man sagen es kommen massig Einträge.

Ich habe hier stellenweise Einträge mit 25.000 Reads und einer Duration von 100 oder aber Einträge mit 1.500 Reads und einer Duration von 15.000.
Ist das Normal? Heute kamen wieder erste Leute an und beschwerten sich, diesmal nichtmal über Performance sondern über einen fast totalstillstand :(

EDAP = Energiedaten Austausch Prozesse,
nutzen wir um mit anderen Händlern in Kontakt zu treten/bleiben und stützt sich auch über den NAS auf die NTS Datenbank.

Wir haben gerade mal den PerMon mitloggen lassen was der besagt PC so tut in derzeit wie sich Navision weghängt. Laut Navisionüberwachung ist der Server mit elendig vielen Lesezugriffen beschäftigt und der Arbeitsplatz hängt im leerlauf. Siehe Anhang.

Bitte die Datei in .htm umbennen, dass ist der Mitschnitt aus dem PerfMon.

Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.

Guten Morgen erstmal,
wünsche ein schönes Wochenende gehabt zu haben.

ditto

Also ich habe gerade mal in die "$ndo$dbconfig" hineingeschaut und folgendes stand in den ersten Zeilen:
Also primär scheint IndexHint ausgeschaltet zu sein, aber für manche Tabellen im nachhinein wieder eingeschaltet worden zu sein.

Da hat sich also schon jemand ausgetobt - der hoffentlich weiss, was er da tut. Ich selber bin kein Fan von "Index Hints", da sich die Hints nie auf die WHERE Klausel bezihen, sondern auf den KEy-Kontext, also das ORDER BY - deshalb können "Index Hints" auch ganz fies "nach Hinten" losgehen ... ich würde RECOMPILE Hints vorziehen ...

Ich habe hier stellenweise Einträge mit 25.000 Reads und einer Duration von 100 oder aber Einträge mit 1.500 Reads und einer Duration von 15.000.
Ist das Normal? Heute kamen wieder erste Leute an und beschwerten sich, diesmal nichtmal über Performance sondern über einen fast totalstillstand :(

Nö, normal ist das auf keinen Fall. Grundsätzlich hängt die Anzahl der "Reads" von der ausgeführen SQL-Operation ab (also z.B. INdex Seek oder Index Scan) und der größe des Result-Sets. Ein Beispiel: bei 25.000 Reads werden 25.000 x 8 KB (Seitengröße) gelesen = 200.000 KB (entspricht etwa 200 MB). Um 200 MB an Daten zu bekommen - mit NAV - müssen schon zig Tausend (Millionen?) Datensätze gelesen werden; und sowas macht man in NAV eigentlich nicht immer. Normale "Reads" - auch bei sehr großen Resultsets - bewegen sich im Bereich kleiner 100, meist sogar kleiner 10. Derart hohe "Reads" sind i.d.R. auf "Index Scans" zurückzuführen, also das vollständige Lesen der Leaf Nodes eines Indexes. Das dauert nicht nur lange, sondern verbraucht viel RAM - z.b. bei 4GB RAM hat SQL Server max. 2,7 GB verfügbar, d.h. bei 200MB Reads werden für diese eine Abfrage ca. 8% verbraucht. Dies wiederum führt zu "Physical Reads", also dem Lesen der Daten von HDD - und das ist noch langsamer ...
Eine "Duratuion" von mehr als 50 msec pro Operation bedarf der Überprüfung, denn i.d.R. kann SQL Server sowas deutlich schneller! Bei einer hohen "Duration" muss man auch noch auf die "CPU" Zeit achten: sind "CPU" und "Duration" etwa gleich, dann wird die Zeit beim Scan verbraucht. Ist "Duration" deutlich höher als "CPU", dann weist dies entweder auf ein Blockade- oder I/O- Problem hin, d.h. der Prozess musste warten ...

Bitte die Datei in .htm umbennen, dass ist der Mitschnitt aus dem PerfMon.

Es sieht so aus als hättest Du die perfmon Definition geposted, nicht das Ergebnis! Du müsstest zuert ein "Counter Log" einrichten und dann die Daten in eine BLG oder CSV etc. Datei schreiben lassen ...

Gruß,
Jörg

Ich wollte nur eben vorab mal 2 Bilder aus dem trace posten.

Ich schau gleich mal nach dem PerfMon ob ich da was falsches gespeichert habe.

Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.

Hmmm ... das sieht ja nicht so prickeld aus ...

Diese SELECT TOP 1 Abfrage auf "Work Order Management Setup" liefert ja nur einen Datensatz (mehr ist wohl auch nicht drin in der Tabelle) und verbraucht 37.000 Reads; ich denke aber nicht dass der DS 290 MB groß ist ...
... hier sind die "Bilder" das Problem! BLOB Felder (also "image" in SQL) werden anders als normale Daten gespeichert und können soz. recht verstreut im der DB liegen - daher die vielen "Reads". Solche Tabellen/Indexe sollte man gelegentlich mit einem ALTER INDEX REORGANIZE ... WITH LOB_COMPACTION warten, um den Fragemntierungsgrad einzudämmen (hohe Fragmentierung = viele Reads).
Grundsätzlich sollte man BLOBs nicht in Tabellen verwenden die ständig in Business Prozessen benötigt werden (z.B. Item oder Customer und Vendor); Bilder etc. sollte man in eigenen Tabellen vorhalten, auf die nur im Bedarfsfall zugergiffen wird (z.B. zur Anzeige in Reports etc.).

Tja, dann sind da noch ein paar SELECT SUM, die ggf. auf fehlende SIFT/VSIFT hinweisen (könnten aber auch durch entsprechende Indexe kompensiert werden), oder SELECT TOP 1 NULL, die über ISEMPTY oder CalcFormula "Exist" kommen und ebenfalls ein Index Problem haben ...

Ja, ja ... es besteht Optimierungspotential ...

Mahlzeit,

also auf die Fehler mit der Tabelle und den Bildern hin haben wir diese jetzt einmal in eine extra Tabelle eingepflegt.
Es scheint so als sei wenigstens dieses Problem schoneinmal behoben :)

Wir bleiben am Ball und weiter gehts ...

Besten Dank.

nabend,

hab hier schon bissle mitgelesen und hab folgendes problem.

Hab eigentlich (bevor ich hier gelesen und gesucht habe) auch mittels profiler / Clientmonitor und perfmon alles analysiert. Auch ist die dbconfig tabelle bei mir gepflegt.
Nun zu meinem Problem. Unsere artikelnummer hat eine länge von 15 und ist leider "sprechend".
Für die "bereiche" der sprechenden Artikelnummer gibt es auch eigene Felder zum filtern (z.B. Herrsteller, Artikelkategorie, usw.). Leider filtern ein Großteil meiner Mitarbeiter nicht auf diese Felder (Hersteller / Kategorie) sondern filtern weiter auf die Artikelnr. So waren sie es vom nativen Navision gewöhnt und machen es so weiter.

Nun hab ich ca. 300Tausend Artikel in der Tabelle 27. Meine dbconfig sieht wie folgt aus:

IndexHint=No
IndexHint=Yes;Company="MandantName";Table="Item";Key="No.";Search Method=;Index=0;
UseRecompileForTable="Item";Company="MandantName";RecompileMode=1;

So, filtern die jungs direkt nach einer Artikelnummer (alle 15 Stellen) geht das fix. Filtern diese Deppen aber z.B. nach den letzen 4 Stellen (Hersteller) braucht das System ewig bis es mal die Daten zurückliefert (weißer Bildschirm). Natürlich wird nun rumgemotzt und den Vorschlag doch mal die anderen, dafür vorgesehen Felder zu nutzen wird nicht gemacht (da kann man dann ja nicht motzen, weil dort die Daten sofort gefunden werden).

Schauch ich mir im Profiler den SQL query an, sieht der wie folgt aus:

SELECT *,DATALENGTH("Picture") FROM "Datenbank"."dbo"."Mandant$Item" WITH (INDEX("Mandant$Item$0")) WHERE (("No_" LIKE @P1)) AND "No_">=@P2 ORDER BY "No_" (und noch das recompile)

Die Reads sind 256780!!!!! Also quasie die ganze Tabelle einmal durch (was ich theoretisch auch verstehe, da der Index ja 15Stellen aufnimmt und nicht teilbereiche)
DEr Ausführungplan zeigt mir

Clusterd Index Insert (tempdb.dbo.CWT_PrimaryKey] 6% <- Compute scalar 0% <- Compute scalar 0% <- Clustert index seek (was ja eigentlich super ist) 94%

Die SQL Version ist 2005 build 3200
NAV ist 4.03 build 26565

Der ganze Quellcode ist auch für SQL optimiert, meine SIFTs sind spitze und die Indizes sind auhc nur die, die ich brauche. Steh kurz davor entweder die Leute hier zu killen wenn die nicht endlich mal die anderen Felder zum filtern nehem oder teste evtl noch Indexe direkt aufm Server selber.
Der Witz ist ja, führe ich diesen SQL Query direkt im Quary analyzer aus, sind die DAten sofort da.

Also was nun ??? Hat jemand ne Idee? Hab ich was vergessen?

Ach ja Jörg, super Buch und super BLOG

Nun hab ich ca. 300Tausend Artikel in der Tabelle 27. Meine dbconfig sieht wie folgt aus:

IndexHint=No
IndexHint=Yes;Company="MandantName";Table="Item";Key="No.";Search Method=;Index=0;
UseRecompileForTable="Item";Company="MandantName";RecompileMode=1;

Wozu? Das Feld "No." is PK und CI, NAV setzt immer einen SELECT * ab, also wird dieser Index mit Sicherheit sowieso gezogen ... Der "Index Hint" kann sogar nach hinten losgehen, wenn z.B. die User tatsächlich mal über andere - indizierte - Felder suchen, mit dem Hint wird die Verwendung des CI erzwungen, was dann zu einem "CI Scan" führen kann ...

So, filtern die jungs direkt nach einer Artikelnummer (alle 15 Stellen) geht das fix. Filtern diese Deppen aber z.B. nach den letzen 4 Stellen (Hersteller) braucht das System ewig bis es mal die Daten zurückliefert (weißer Bildschirm).Schauch ich mir im Profiler den SQL query an, sieht der wie folgt aus:

SELECT *,DATALENGTH("Picture") FROM "Datenbank"."dbo"."Mandant$Item" WITH (INDEX("Mandant$Item$0")) WHERE (("No_" LIKE @P1)) AND "No_">=@P2 ORDER BY "No_" (und noch das recompile)

Ja, ja, ja ... also nochmal: der "Index Hint" ist unnötig - "Item$0" wird sowieso gezogen; der "Recompile Hint" bringt ebenfalls nix ...
Bei der Abfrage währen nun die übergebene Parameter interessant (SQL Profile Event "RPC_Completed"), hier wird für "No." (also @P1) mit Sicherheit ein "Regulärer Ausdruck" übergeben, etwas in der Art '%[Aa][Bb]12345[Yy][Zz]%'. Das Problem dabei sind die Wildcards (* NAV = % SQL):
Eine Wildcard am Anfang des Ausdrucks macht es dem SQL Server beinahe unmöglich einen Einsprungspunkt in die Root Node des Indexes zu finden; d.h. er wird den Index von Beginn an scannen.
Wildcard am Ende des Ausdrucks bedeutet fast immer, dass der Index bis zum Ende hin gelesen wird.
Ergo: Der Clustered Index - in dem Fall "Item$0" - wird IMMER vollständig gescannt! Sollte man an den "Reads" sehen, die sollten immer gleich hoch sein ...

Die Reads sind 256780!!!!! Also quasie die ganze Tabelle einmal durch (was ich theoretisch auch verstehe, da der Index ja 15Stellen aufnimmt und nicht teilbereiche)
Der Ausführungplan zeigt mir
Clusterd Index Insert (tempdb.dbo.CWT_PrimaryKey] 6% <- Compute scalar 0% <- Compute scalar 0% <- Clustert index seek (was ja eigentlich super ist) 94%

Siehe oben. Das der CI in dem Fall "geseekt" wird heisst, dass der INdex nicht von links nach rechts gescannt wird (eigentlich erwartet), sondern dass der B-Tree MEHRMALS (!!!) von oben nach unten gelesen wird (hängt wohl davon ab wie tief und wie breit der B-Tree in dem Fall ist)
Kurz gesagt: werden Wildacards verwendet - hauptsächlich am Anfang des Suchausdrucks - dann ist SQL Server fast immer gezwungen "was blödes" anzustellen ... da helfen keine Query Hints ...
... hier muss man entweder die Programmierung entsprechend optimieren, oder den Benutzern zeigen, wie Sie besser mit Such- und Filter-Features in NAV umgehen.

Der Witz ist ja, führe ich diesen SQL Query direkt im Quary analyzer aus, sind die Daten sofort da.

Mit welchen Reads? Welcher Execution Plan? Sollte eigentlich immer gleich sein ...
Der Unterschied zwischen NAV un QA ist ja, dass NAV die Datensätze in einen "Cursor" pumpt - im QA nicht. Ausserdem - wenn Du direkt am Server arbeitest - müssen die Daten nicht über's Netzwerk gehen. D.h. obwohl die die Daten schneller siehst, sollte die SL Server Operation immer die gleich sein ... oder etwa nicht?

Also was nun ??? Hat jemand ne Idee?

Na ja, wie gesagt, man könnte vielleicht versuchen "erzieherisch" zu wirken und die User im Umgang mit Suchen/Filtern schulen. Oder man könnte sich vielleicht eine eigene Suchmaske bauen, die ggf. den Suchausdruck "zerlegt" und dann einzelnen Felder wie "Hersteller" etc. filtert.
Last but not least kann man das Ganze "Fehlverhalten" auch mit reichlich RAM kompensieren:
Bei 256.780 Reads werden im schlimmsten Fall 256.780 x 8 KB in den Cache gepumpt, = 2.054.240 KB = ca. 2 GB. Wie groß ist der verfügbare RAM? ICh denke mindestens 16 GB (64bit wäre schick) könnte euer Server schon vertragen ...

Ach ja Jörg, super Buch und super BLOG

Dankschön - freut mich

Ach ja ... un P.S.: wenn "Pictures" in Tabelle "Item" verwendet werden, dann wird das Problem noch verschlimmert!

ich kann mich Jörg nur anschließen.
Hoffe du hast keine bilder im BLOB (die speichere lieber extern auf der Platte) und schmeiß den IndexHind für den CI (ClusteredIndex) und das Recompile für den CI raus.
Erzieh die Endanwender. Du sagst, dass du schon Felder wie den Herrsteller oder die Kategorie hast. Ich schätze mal, dass du auch Indizes dafür angelegt hast. Weise die Leute darauf hin, diese Felder beim Filtern zu nutzen und ggf. auch die Sortierung vorher auf einen passenden Schlüssel zu setzen (OK, das verstehen die meisten nicht).
Somit brauch der Server nicht umzusortieren wenn die ein Query wie: Select *, Datalenght(Picture) from TableItem where "Item Category" = 'AUTO'' order by "NO_" losgeschickt wird.
Denn der gewählte Schlüssel in NAV gibt die order by Clausel an.
sicher werden deine Abfragen etwa so aussehen (was Jörg wissen wollte):

Select *, Datalenght(Picture) from TableItem where "No_" like '*1234 and "No_" <'123456789012345' order by "No_"

Du wirst dich sicher auch gewundert haben, geh ich mal davon aus, dass du im Profiler siehst, dass er ggf. das gleiche query 2x oder mehr hintereinander ausgeführt hat. Oder?
So, wenn die Anwender nun direkt über die Artikelübersicht filtern ohne ein anderes Feld als die Artikelnr. zu nutzen und ohne ein wechsel des PK kann der SQL server nicht anderes als den Baum (siehe Wikipedia für B-Tree oder ich glaub Jörg hatte das auch mal beschrieben in seinem Buch <- Jörg nen kostenfreies Update der geänderten Seiten könntest du mir nicht geen oder? ) von oben nach unten kopmlett zu scannen und die zwischenergebnisse in der tempdb zwischenzupuffern.

Also, notfalls bei lernresistenten usern, bau eine Filtermaske und unterbinde im C/AL die direkte filterung auf die Artikelnr. wenn der Filter mit einem Wildcard beginnt.

Gruß