SQL MSSQL2014 HW zu schwach oder schlecht programmiert?

[sikarr]

Hi,

ich arbeite in einem Archiv, dort nutzen wir ein Lagersystem das wir programmieren haben lassen mit einer MSSQL Datenbank. Darüber läuft die Lagerverwaltung, Buchhaltung und Vernichtung unser Firma, wir sind damals von Access auf MSSQL gewechselt weil die Performance unterirdisch geworden war, die Datenbank war zu letzt 800 - 900Mb Groß (Access + und nur Lagerverwaltung).

Das ist jetzt 3-4 Jahre her, aber mittlerweile wird sich auch über die Performance des Neuen Systems beschwert. Jetzt meinte der Programmierer das unsere Hardware (Stand 2017) zu schwach sei. Aktuell ist dem MSSQL-Server, 8 Kerne (e5 2660 v4) und 32Gb Ram zugewiesen (größe DB ist 1GB).

Aber die Machine langweilt sich eigentlich nur, 5-20% CPU Auslastung und 5-6Gb Ram, und der Programmierer will mehr.

Aktuelle Forderung: i7 (mit min. 4Ghz), >= 64Gb Ram und min. 1Tb SSD.

Auch meinte er unsere Clients wären zu langsam (i5 3,2Ghz, 16Gb, 256Gb SSD), kann das wirklich sein?
Ich bin der Meinung das kann nicht sein, aber leider ist mein Wissen im Bezug mit MSSQL begrenzt von daher kann ich nicht viel dazu sagen.
Daher die Frage an die Pros, hat er Recht ???

Der Witz ist auf unserer Drängen hat er unserer Buchhaltung ein Notebook gestellt auf dem die DB lokal lief und alles schick war.

 

[Tada100]

Die Aktuelle hardware sollte doch wohl ausreichen für so eine kleine Datenbank!
Desweiteren denke ich das der Hund woanders begraben liegt, evtl. in der vebindung zwischen Server und Client.


Hinzukommt das die Buchhaltung ja selbst auf einem Notebook lief was aller warscheinlichkeit gleichschnell oder aber etwas langsamer ist als eure clients. Was mich wieder zur verbindung zwischen Client und Server bringt.

Würde nicht auf die Forderung des Programmieres eingehen.

Wie sieht der rest der Serverhardware aus?

 

[snaxilian]

Ach Entwickler wollen immer mehr Hardware weil tatsächliche Fehlersuche und -analyse anderes Fachwissen erfordert und zeitaufwendig sein kann.
Aber ein paar Ansätze gibt es schon:
- Caching des MSSQL Servers optimiert? Vermutlich eher weniger nehme ich mal an.
- Wurden die Datensätze angepasst und vorbildlich normalisiert oder nur stumpf von access nach MSSQL migriert/kopiert?
- Wenn ich raten müsste: Vermutlich größter Flaschenhals wird der Storage des Servers sein. DBs verursachen idR viele random IOPS. Verwendet euer Server einen Raid-Controller und wie ist dieser konfiguriert? Wie viele und wie schnelle HDDs? Der Grund warum der Laptop vermeintlich schneller ist als der Server wird die SSD im Laptop sein. Die Zugriffszeiten, Latenzen und IOPS einer einzelnen SSD sind nun einmal idR höher als die von durchschnittlichen Server-HDDs.

Zu den Clients: Auch das kommt drauf an... Wie sprechen Clients mit der Anwendung? Läuft die Anwendung lokal auf den Clients und diese machen diese bei Programmstart eine Verbindung zum SQL-Server auf? Oder wird bei jeder Dateneingabe/-abfrage/etc Verbindung aufgebaut, Query gemacht und Verbindung getrennt? Wie lange dauert es zwischen Absenden des Query und der Antwort? Wenn es keine debug Optionen in der Anwendung gibt tut es auch Wireshark und selbst ein bisschen rechnen. Oder ist die Anwendung eine Web-Anwendung, die gar nicht auf den Clients läuft?

 

[Hancock]

Kann alles sein, du musst rausfinden, warum es hakt => Profiling. Bedenke: 12 % Last ist ein Kern bei 8 Kernen. Ich denke, es ist wahrscheinlich irgendwo ein Bock drin, al la zu viele SQL-Querys, die auch in einem Query zu machen wäre.

 

[sikarr]

Hmm, das Bestätigt eigentlich meine Einschätzung.

Zur Serverhardware, es ist ein HA-Cluster aus 2 Servern + Backupserver, mit jeweils separatem Storage auf dem die VMs liegen. Also der HA teilt sich hier ein Storage mit ich glaube 8 Platten im Raid6 Verbund. Der Backupserver hat noch mal ein eigenes Storage, das ist aber nur falls der HA mal streiken sollten.

Vom Netzwerk her sollte es auch klappen, wir haben noch mehrere PGSQL Datenbanken mit Stammdaten unserer Kunden laufen, mit deutlich schwächeren Einstellungen und die laufen wie Butter.

PS: es können höchstens 8 Clients auf den MSSQL gleichzeitig zugreifen und das ist eher die Ausnahme weil wir kaum mehr Leute haben.

 

[blackbirdone]

Also ganz ehrlich ^^ der Server reicht dicke aus und die Clients sind ja wohl mehr als gut. Da sollte man vllt mal die Fehler woanders suchen.

Ihr könnte diese mini Datenbank (also mal ehrlich das ist ein Witz) komplett im Arbeitsspeicher halten lassen, muss man halt nur mal aktivieren.. bzw konfigurieren.

SQL Server Produkte sind so hoch optimiert, bis man die in die Knie bekommt muss man entweder komplexe schlechte Qeuries schreiben oder sie schlecht einstellen... die Hardware ist so überdimensioniert für das bisschen was ihr verwendet.

 

[Nase]

Die Forderung nach einer Desktop-CPU und mehr als 64 GB Ram ist eh albern. Ein i7(-8700) unterstützt maximal 64 GB non-ECC-Ram. Da seid ihr mit eurem Xeon sehr viel besser aufgestellt.

Ich tippe hier mal eher auf schlechtes Datenbankdesign. Sucht euch lieber einen anderen Programmierer oder lasst zumindest die Datenbank mal von jemand anderen begutachten.

 

[sikarr]

Die SQL VM wird vom Programmierer verwaltet, der kann damit machen was er will damits läuft. Deswegen kann ich zur Konfig ja auch nix sagen weil ich mich da raushalte. Dafür bezahlen wir Ihn ja schliesslich (Freelancer).

Das mit der Forderung nach Desktophardware ist nochmal ein anderes Thema, deswegen bin ich jetzt drann mir mehrere Meinungen einzuholen, ist ja auch ne finanzielle Sache, mal von den tech. Unzulänglichkeiten abgesehen.

Heute haben wir Ihm einen unserer Clients hingestellt und lassen die Datenbank jetzt ohne VM direkt auf der Hardware laufen.

 

[new Account()]

Ich tippe hier mal eher auf schlechtes Datenbankdesign.

Also ohne irgendwelche Infos, kann man echt nur raten. Das kann alles sein, und möglicherweise garnichts mit der Datenbank zu tun haben.

 

[sikarr]

Klar das man ohne das genaue Design zu kenn nix weiter sagen, aber hätte ja sein können das einer sofort sagt: "ist ja logisch das es wie ein Sack Nüsse läuft mit der Hardware" . Aber sowas dachte ich mir schon, da wirds am Mo dann ein Donnerwetter geben xD.

 

[Nase]

Allein ein paar fehlende Indizes können bei einer immer größer werdenden Datenbank die Ausführungszeit von Datenbankabfragen ins fast Unermessliche erhöhen. Sowas würde ich einfach mal durchtesten, bevor hier versucht wird, das Problem mit noch mehr Hardware zu erschlagen.

 

[sikarr]

Kann alles sein, du musst rausfinden, warum es hakt => Profiling. Bedenke: 12 % Last ist ein Kern bei 8 Kernen. Ich denke, es ist wahrscheinlich irgendwo ein Bock drin, al la zu viele SQL-Querys, die auch in einem Query zu machen wäre.

5-20% Last / Kern.

 

[BrollyLSSJ]

Sofern (ich es richtig verstanden habe und der SQL Server virtuell ist) eure virtuelle Umgebung auf VMware basiert, wo der SQL Server drauf liegt: Du könntest mal prüfen, ob Ihr auch VMXNET3 als Netzwerkkarte konfiguriert habt oder doch was älteres wie E1000. Hier solltet Ihr dann auf VMXNET3 umstellen. Der ist performanter (und eventuell auch Pflicht, damit Ihr überhaupt Support bekommt, sofern Ihr dann ein Ticket bei Microsoft öffnen wolltet). Beim SCSI Controller das gleiche. Da ist der Paravirtuelle SCSI Controller der mit der besten Performance (und vermutlich auch Pflicht).

Zudem will SQL die Platte nicht in Standard NTFS (4096 Allocation Unit Size) formatiert haben, sondern mit 64K (Allocation Unit Size). Das könntest du auch mal prüfen (z.B. fsutil).

Zum RAID6: Wenn damit sehr viele Platten eingerichtet sind, kann es unter Umständen schon Performance kosten im Vergleich z.B. zum RAID10. Bei Exchange wurde mal angeraten beim RAID Controller ab 6 oder 8 Platten auf RAID10 zu wechseln (wir hatten mal Exchange Probleme mit 22 oder 24 Platten im RAID5. Nach der Umstellung auf RAID10 lief es wesentlich besser.). SSDs wären natürlich noch besser gewesen aber in der Menge nicht bezahlbar gewesen.

Es war auch mal so, dass SQL mehr von Ghz anstelle von Kernen profitiert. Ob das bei der 2014 auch der Fall ist, weiß ich nicht. Wenn ich aber einen Server für Datenbanken bestellen darf, dann ist das meistens ein 6 Kerner mit viel GHz anstelle von einem 12 Kerner mit wenigen GHz). Dann könntest du ja auch mal gucken, ob z.B. das Notebook höher taktet, weil dort nur 1 Kern benutzt wird und dadurch z.B. der Turbotakt dauerhaft anliegt und euer Server Xeon dies nicht kann (keinen Turbo hat).

Mehr kann ich aber zu SQL nicht beitragen. Das machen bei uns andere im Unternehmen. Dies sind auch nur so allgemeine Dinge, die ich aufgeschnappt habe. Wäre aber zumindest eine Kontrolle Wert.

 

[sikarr]

Ja ist eine VM, Citrix. Aber die Allocation Unit Size werd ich mal prüfen. die Server haben 56 logische Kerne pro Physischen Server, da ist der Maximaltakt halt deutlich niedriger.

Aber sollte die Last auf einem der Kerne (sind der VM exklusiv zugewiesen) nicht auch mal auf 100% schnippsen wenn der takt zu gering ist?

 

[PHuV]

Ich bin u.a. DB Admin für verschiedene Kunden und DB-Systeme (Oracle, MS-SQL-Server, MySQL, Mongo-DB, DB2, PostGres, ... bald kommt noch Exasol dazu). Mein erster Tipp, schmeißt diesen Freelancer raus, und holt Euch einen echten Profi oder entsprechendes Systemhaus, was die Sache bei Euch mal grundlegend analysiert.
Bei uns sind wir schon öfters auf Softwareentwickler bzw. Firmen gestoßen, die eine selbstgeschriebene Anwendung anbieten, und oftmals von Datenbanken überhaupt keine Ahnung haben. Entsprechend sehen die Anwendungen auch aus. Da wird oftmal die Datenbank eigentlich nur als simpler Data-Store gebraucht, mit anspruchslosen Tabellen und entsprechend simplen Tabellenstrukturen. Keine Normalsierung, keine Struktur, von Indexen mal ganz zu schweigen.

Nur mal so, Profis und Experten lassen normalerweise ein entsprechendes Monitoring laufen, so daß sehr wohl schnell rausgefunden werden kann, wo die Performanceprobleme liegen. Wir überwachen täglich zig Datenbanken für Kunden, und bekommen viele Probleme dadurch eher mit als er selbst.

1 GB sind im DB-Bereich gar nix, da lacht ein MQ-SQL-Server nur. Das fackelt man auch locker selbst in einer VM auf einem Notebook ab. Das kann man heute mit diveren DBs bei Eurer Konstellation IN-MEMORY abfackeln (bei MS-SQL-Server 2016 heißt das In-Memory OLTP). Und das was er über Eure Clients sagt, ist absoluter Quatsch, hier ist Netzwerkperformance gefragt.

Bei der Größe sollte es auch keine große Rolle spielen, ob virtualisiert oder nicht, aber laut Erfahrungen brauch eine übliche DB immer viel File-I/O. Verwendet Ihr Transaktionslogs? Kann viel Performance kosten, wenn beispielsweise für jede Transaktion ein Commit gemacht wird. Besser, erst nach 100 oder 1000 Transaktionen mit Commit abschließen. Das kann man per SQL entsprechend formulieren. Ebenso kann man bei SQL-Befehlen viel falsch machen, wenn man falsch verkünpft per JOIN, oder dann doch durch ungeschickte Abfragen einen Full-Table-Scan durchführt. Weiterhin sind falsch oder nicht verwendete Indexe eine Performancebremse.

All das führt zu viel File-I/O und zum Knackpunkt bei VMs. Du mußt Dir vorstellen

• VM Host hat ein Filesystem auf Platte, oder eine Art "Simulation" eines Filesystems (LVM)
• VM Host simuliert eine Festplatte über ein virtuelles Filesystem
• VM Client verwendet virtuelles Filesystem mit NTFS
• Datenbank verwendet wiederum eine Art eigenes Filesystem in Form von Datendateien (eben die Datenbankdateien).
• viele VMs teilen sich den Plattenbereich des VM Hosts

Bei Datenbanken mit großen Dateien und vielem Suchen auf diesen Datendateien kann man sich vorstellen, da bleibt von der Performance einer Festplatte nicht viel übrig. Gerade Raid5 und Co. sind für DB Anwendungen aus diversen Gründen nicht so optimal. Auch ein ausgelagerter Storage über iSCSI, SAN etc. hängt an einem Netzwerkadapter, und 1 GBit liefert hier gerade mal im optimalen Fall 120 MB/s. Das hört sich erst mal viel an, kann aber durch viel Schreiben in viele kleine Dateien schnell stark verlangsamt werden. Bei einem virtualisertem Server muß man daran denken, daß er sich die maximale Performance mit allen anderen VMs auf dem Host teilt. Ist da nur eine VM, die viel File-I/O benötigt, ist schnell die Grenze des phyisch machbaren erreicht. Genau das gleiche Schicksal ist Netzwerk-I/O. Alle VMs auf dem Host teilen normalerweise 1-2 Netzwerkadapter mit 1 Gbit/s. Ist hier viel los, geht die Performance auch schnell runter.

Wenn Ihr Citrix verwendet, einfach folgendes machen: Eine SSD kaufen, im Server reinbauen, und exkusiv dieser VM zuweisen. Wenn Euer Admin fit ist, ist das in 1-2 Stunden locker erledigt. Damit sollte dann eure VM Server mit der Datenbank viel flotter laufen. Ebenso, wenn möglich, ihm einen eigenen Netzwerkadapter exklusiv zuteilen.
Generell ist für größere DB-Systeme eine Virtualisierung aus meiner Erfahrung nicht so dolle. Darüber könnte man sich hier jetzt stundenlang auseinandersetzen.
Besser ist native Hardware. Ein Dell T130 mit einem Xeon E3-1240 v6 3.7GHz, 16 GB RAM liegt bei ca. 1000 €, dazu noch ein Raid0 mit 2x256 GB SSDs (Samsung Pro 860 mit 2x ca. 105 €) für die Datenbank, eine HDD für Sicherungen und noch eine weitere im SAN/Netzwerk oder per USB als Sicherung. Das reicht nach grober Abschätzung für Euch erst mal dicke aus. OS kann ruhig auf Platte sein, optimal wäre kleine SSD für OS Windows Server mit 128 GB, und eigene SSD 128-256 GB für Datenbank. Wenns kritisch ist, hier an ein Standby oder weiteres Testsytem denken.

Aus Performancegründen und weil sie heute nicht mehr so viel kosten (außer die Enterprise-SSDs), würde ich immer SSDs vor HDDs bevorzugen. Gerade im Datenbankbereich, mit vielen gleichzeitigen Zugriffen auf das Filesystem mit parallelisierten Abfragen auf mehrere Dateien macht sich das schnell bemerkbar.

 

[Piktogramm]

Welche Version / Lizenz vom MS SQL Server habt ihr eigentlich? Falls ihr mit der Express Version herumgurgt schlagen div. Limitierungen zu:
https://en.wikipedia.org/wiki/SQL_Server_Express

Die niedrige CPU-Auslastung deutet darauf hin*, denn Express limitiert auf einen Sockel bzw. 4 Kerne (egal ob physisch oder virtuell).

Anonsten bevor ich wild mit mit Vorschlägen um mich werfe, gibt es Unterschiede in der Performance bei verschiedenen Vorgängen? Also eine Suche, das Aufrufen eines Datensatzes, Speichern neuer Daten, Erstellen des Quartalberichts, ...


* Der Ms SQL Server ist sehr gut darin selbst vergleichsweise schlechte Abfragen auf mehrere Threads aufzuteilen und zu optimieren. Zumindest solang die Abfragen sich nicht abstrakter Kunst annähern.

 

[Holt]

1 GB sind im DB-Bereich gar nix

Eben, 1GB ist nichts und jedes moderne OS nutzt sonst unbelegtes RAM als Diskcache, vor allem als Lesecache und daher dürfte das Storage allenfalls bei Schreibzugriffen limierten. Nicht aber bei:

durch ungeschickte Abfragen einen Full-Table-Scan durchführt.

Ein Table Scan ist bei einer 1GB DB weniger als bei viele anderen der Index umfasst.

Die Anzahl der Commits bestimmt das Problem, diese kann man daher nicht unbedingt verringern nur um die Performance zu verbessern.

Eine wirklich Diagnose der Performanceprobleme ist eine komplexer Vorgang und kann nicht einfach so in einem Forum erfolgen. Es kann hier nur ein Stochern im Nebel bleiben, vielleicht findet man dabei woran es liegt, aber dies ist eher Glückssache als eine echte Diagnose, denn dafür muss man ans System um dies zu analysieren. Die HW scheint mir jedenfalls soweit sie genannt wurde, doch eher großzügig als zu knapp ausgelegt zu sein, aber da ja mehrere VMs auf den Servern laufen, müsste man auch hier die Auslastung vor Ort analysieren.

 

[ayngush]

Was ist denn das Frontend? Weiterhin Access? Per ODBC angebunden?
Falls ja: Wurden dann die Sichten in Access belassen (Access-Abfragen) oder wurden die auf dem SQL-Server erzeugt? Access-Abfragen sind in so einem Szenario nämlich richtige Bremsanker.

Ich vermute, dass euer Freelancer leider nicht kompetent genug ist um die Performance-Probleme fachgerecht zu analysieren und dann die passenden Maßnahmen auszuwählen und umzusetzen. Die reinen Eckdaten eurer Server und Clients jedenfalls sind ausreichend, um eine sehr gute Performance bei den wenigen Usern hin zu bekommen.

Grüße

PS: Wir haben ein ähnliches Umfeld, mit weitaus größerem MS SQL-Server und mehr Usern und alten Access- und neuen Web-Frontends und die Datenbank flutscht. Alles, was hier genannt wurde ist richtig und wichtig: Normalisierung, Abfragegestaltung im Client, die Architektur der Anwendungen und der Datenbank, Idizes, usw. Jemand, der sich damit auskennt, kann das aber wie gesagt in 0,nichts analysieren. Einfach mal den Ausführungsplan und den Performance-Monitor anschauen, usw. Probleme mit "mehr Hardware" zu erschlagen ist bei eurer Ausstattung jedenfalls Humbug.

 

[Piktogramm]

Eben, 1GB ist nichts und jedes moderne OS nutzt sonst unbelegtes RAM als Diskcache, vor allem als Lesecache und daher dürfte das Storage allenfalls bei Schreibzugriffen limierten. Nicht aber bei:
Ein Table Scan ist bei einer 1GB DB weniger als bei viele anderen der Index umfasst.

Die Anzahl der Commits bestimmt das Problem, diese kann man daher nicht unbedingt verringern nur um die Performance zu verbessern.

Indizes durchläuft man aber mit annähernd O(log n) und ein Table Scan im besten Fall mit O(n). Insofern ist es schon ein deutlicher Unterschied ob man einen Index oder eine Tabelle gleicher Größe durchläuft.
Auch ist 1GB nicht unbedingt Nichts, ein paar Joins, Altlasten im Design der Datenbank und schon muss man da doch allerhand Rechenzeit auf an sich simple Abfragen werfen.

Die Anzahl an Commits kann es fast nicht sein, so wie es der TE umschreibt werden der Datenbank im Jahr ~100MB Daten hinzugefügt. Das ist derart wenig, dass selbst SQLite auf einem RaspberryPi damit überhaupt keine Probleme bekommen dürfte.

 

[ayngush]

Die Commits hätte ich auch nicht in Verdacht. Auch nicht die I/O. Für mich klingt das nach nicht optimierter Softwarearchitektur + nicht optimalem Datenbankdesign

PS: Unsere CRM Datenbank ist (nach der Komprimierung vom Wartungsplan allerdings) als Backup-File gerade einmal knapp 500 MB Groß. Beinhaltet aber Millionen von Datensätzen und, wenn der SQL Server so läuft, belegt das Ganze fast 70GB RAM (von 128GB). Die reinen Größen sagen aber nichts über die Architektur oder gar über die Performance aus. Auch eine In Memory-DB kann langsam sein, wenn die Indizes nicht richtig genutzt werden können, weil eventuell keine passenden vorhanden sind.

PPS: Das Ding steht bei uns auch auf Autocommit nach jeder Operation. Lediglich in den neuen Webfrontends wird, aufgrund einer wohlüberlegteren Fehlerbehandlung, mit gesteuerten Transaktionen und Rollbacks / Commits gearbeitet.