stefanlausl
Goto Top

MSSQL 2008. Trigger Performanceproblem und Alternativen

Hallo,

folgendes Problem.


In einer Datenbank befinden sich X-Millionen Datensätze.
Auf der Tabelle befindet sich ein Insert/Update/Delete Trigger der die Daten in 5 andere Datenbanken verteilt.

In jeder der 5 Datenbanken läuft ein relativ komplexer Prozess der Daten aus der eigenen Datenbank selektiert und wenn notwendig Daten in der übergeordneten Datenbank
ändert oder ergänzt, so das der Trigger auslöst.

Der Prozess der 5 Datenbanken läuft parallel mit jeweils unterschiedlichen Datensätzen und wird mehre Tausendmal am Tag aufgerufen.
Zuvor lief dieser fast 9 Jahre zu vollsten Zufriedenheit.
Nun "schaukelt" sich der Prozess je nach Verarbeitungsmenge in die Höhe.
Die Performance ist praktisch am Vormittag im "Keller".

Was für Alternative gibt es um den Trigger zu ersetzen ?

Ich dachte zu erst an Synonyme.
Aber da müsste die Tabelle wohl erst gedropped werden (Fremdschlüsselverletzungen etc wären vorprogrammiert).

Es muss bei jeder Lösungsmöglichkeit gewährleistet sein, da der Stand der 5 Datenbanken identisch der "Hauptdatenbank" ist.

Evtl. hat ja jemand eine Idee von Euch.
Ich habe langsam keine Ideen mehr.
Es wurden keine Änderungen an dem Prozess vorgenommen.
Wo (NoLock) möglich ist, wurde dieser auch eingesetzt.

Für Vorschläge wäre ich sehr dankbar.

Content-ID: 585435

Url: https://administrator.de/forum/mssql-2008-trigger-performanceproblem-und-alternativen-585435.html

Ausgedruckt am: 23.12.2024 um 14:12 Uhr

it-frosch
it-frosch 07.07.2020 um 16:47:32 Uhr
Goto Top
Hallo Stefan,

Nun "schaukelt" sich der Prozess je nach Verarbeitungsmenge in die Höhe.
Die Performance ist praktisch am Vormittag im "Keller".
Was meinst du damit? CPU Last steigt bis 100% / RAM steigt bis max?

Könnte es einfach sein, dass die Last für deine Maschine zu groß geworden ist?
Auch wenn sich "an dem Prozess" nichts geändert hat, könnte es sein, dass Änderungen ausgeführt werden, die sich quasi wieder selbst ändern.
(Zirkelbezug in Excel - Katze jagt ihren eingen Schwanz hinterher)
Treten Locks auf?

So würde ich da rangehen. Bin hier aber nicht der Profi. face-wink

grüße vom it-frosch

PS: Der 2008 sollte ein Update bekommen. face-wink
wiesi200
wiesi200 07.07.2020 aktualisiert um 17:08:38 Uhr
Goto Top
Hallo,

Mssql auf ne Aktuelle Version hochziehen.
Sehr viel Arbeitsspeicher einbauen und dann die 5 Zusatz Datenbanken auf InMemory umbauen und die Hauptdatenbank auf ne Intel Optane SSD packen.

Edit:
https://www.informatik-aktuell.de/betrieb/datenbanken/sql-server-2014-in ...
nachgefragt
nachgefragt 07.07.2020 um 17:55:01 Uhr
Goto Top
Zitat von @StefanLausL:
Für Vorschläge wäre ich sehr dankbar.
Wie sehen deine SQL Wartungspläne aus?
MadMax
MadMax 07.07.2020 um 19:41:20 Uhr
Goto Top
Hallo Stefan,

ich rätsel hier gerade, was bei Deinen Datenbanken da genau läuft. Es geht wohl nur um eine Tabelle richtig?
Möglichkeiten:
1. Das ist eine ganz normale Replikation und die Tabelle ist auf allen sechs Datenbanken gleich.
2. DB1 schreibt was in die zentrale DB und der Trigger dort schreibt irgendwelche anderen Daten in die vier anderen Datenbanken.
3. In der zentralen DB wird was geändert und es wird daraufhin irgendwas in die anderen Datenbanken geschrieben.

Oder irgendwas ganz anderes?

Ohne zu wissen, was da passiert, wird es schwer, Dir zu helfen.

Aber:
Wenn Euer Konstrukt seit neun Jahren problemlos und zur "vollsten Zufriedenheit" läuft, dann ändert sich das normalerweise nicht von jetzt auf gleich.

Hast Du mal geschaut, woran es jetzt genau hängt? Ist die zentrale Datenbank die Bremse? Oder vielleicht hat eine der anderen Datenbanken eine Macke und bremst alles aus. Habt Ihr einen Datensatz verarbeitet, der so noch nie auftrat und jetzt quer hängt? Ist bei einer Datenbank der Speicher knapp geworden? Sind in einer von den Datenbanken nicht so viele Daten drin gewesen und deshalb ist nie aufgefallen, daß dort ein Index fehlt?

Du siehst, es gibt einige Möglichkeiten, woran es hängen könnte. Du solltest mal mit dem Profiler schauen, was die genaue Ursache ist.

Gruß, Mad Max
StefanLausL
StefanLausL 08.07.2020 aktualisiert um 08:41:24 Uhr
Goto Top
Guten Morgen,

erst mal danke für die vielen Ratschläge.

Einige Fragen kann ich leider nicht beantworten, da ich als Entwickler keine Berechtigungen habe Locks, Wartungspläne etc. anzuschauen und auszuwerten.

Der Prozess läuft parallel noch auf 20 anderen Datenbanken.
Mit dem Unterschied das es hier keine zentrale Tabelle in einer übergeordneten Datenbank gibt.

zu den Fragen von MadMax:

Die untergeordnete Datenbank selektiert Daten aus ihren eigenen Tabellen.
Fehlt etwas bzw. muss ein Datensatz aus der einen Tabelle (welche aus der zentralen Datenbank per Trigger verteilt wird)
dann muss ein Insert/Update auf die zentrale Datenbank erfolgen.
Die Änderungen werden nun durchgetriggert und der Prozess läuft weiter.

Wenn der Prozess nur in einer der 5 DB's läuft, also keine Parallelverarbeitung aller 5 Datenbanken) dann dauert die Ausführung ca. 1,5 Sekunden pro Datensatz.
Ob der Prozess dabei in den 20 anderen läuft spielt keine Rolle.
Die Abarbeitung hier dauert ca 0,5 Sekunden.
Die 1 Sekunde die der Prozess länger dauert schiebe ich mal auf den Trigger.
Ist zwar auch nicht schön aber die 1 Sekunden lässt sich verkraften.

Problem ist wenn der Prozess in 5 DB's gleichzeitig läuft.
Es wird die Tabelle selektiert die gleichzeitig von dem Prozess in ein einer anderen DB geändert wurde.
Aus den 1,5 Sekunden werden mal schnell 30 Sekunden.
Und das ist definitiv zu lange.

Gibt es die Möglichkeit den Trigger performanter zu machen ?

Die Möglichkeiten von Synonymen schliesse ich aus.
Ob es schneller werden könnte die Tabelle in eine View zu ändern ?
Wie schaut es hier aus mit der Performance ?

PS: Thema Hardware.
Da ist nichts zu machen.
Ich habe die Details nicht im Kopf, aber an der Hardware wurde nicht gespart. face-smile
Abgesehen davon, wäre die Zusatzkosten nicht argumentierbar.


Der Trigger sieht im Moment so aus:

DECLARE @action NCHAR(1)

SELECT @action = CASE
WHEN EXISTS (SELECT TOP 1 '' FROM INSERTED)
AND EXISTS (SELECT TOP 1 '' FROM DELETED) THEN 'U'
WHEN EXISTS (SELECT TOP 1 '' FROM INSERTED) THEN 'I'
WHEN EXISTS (SELECT TOP 1 '' FROM DELETED) THEN 'D'
ELSE ''
END

-- **
-- Insert

IF @action = 'I'
BEGIN

INSERT INTO etc.......................
FROM INSERTED
GrueneSosseMitSpeck
GrueneSosseMitSpeck 08.07.2020 aktualisiert um 10:32:44 Uhr
Goto Top
Zitat von @StefanLausL:

Ich dachte zu erst an Synonyme.
deine Datenbanken sind einfach natürlich gewachsen... und datenbankübergreifende Trigger sind immer eine gaaaanz böse Sache.

Ich hab mal vor 20 Jahren sowas geamcht und das war seinerzeit wegen den eher bescheidenen Hardwareressourcen mit einer 2 GB großen Datenbank im ERP System instabil, meist weil Trigger on Insert im Zielsystem in ein Timeout gelaufen sind und am Quellsystem der Insert dann abgebrochen wurde.

Wie bei allen Performanceproblemen MUSST du aber erstmal die Flaschenhälse finden. Komplexe Statements sind CPU-lastig... oder IO lastig.... wenn inner joints drin sind, kann man auch mit mehr Arbeitsspeicher den Server dazu bringen, Joins effektiver aufzulösen. Aber ich kenne deine Datenbank nicht, und datenbankübergreifende Trigger bzw. SQL Zugriffe sind WORST PRACTICE.

In meinem Fall damals vor 20 Jahren haben wir die Deltas (neue / geänderte Artikel, neue / geänderte Kundendatensätze im ERP) in eine Delta-Tabelle geschrieben (so blieb die Abfrage innerhalb einer Datenbank) und haben die dann periodisch mit einem Wartungsplan mit dem Zielsystem (ein Webshop) abgeglichen, incl einer Rüchsynchronisierung - die wir aber am Ende per Mail hatten, denn das waren überpfüfungswürdige DAten die nicht in das ERP gesynct werden durften, z.B. Bonitätsprüfung bei Neukunden ohne die ein Kunde nicht angegt werden durfte, und Aufträge von Bestandskunden wollte der paranoide Chef nicht life im ERP haben... was allerdings auch ein wenig begründet war, da das ERP fast monatlich Updates gekriegt hat und einen harten Sync hätte man dann jedesmal mit viel Aufwand nachziehen müssen.


Die Alternative....
a) die allermeisten komplexen Selects laufen irgendwann mal gegen die Wand wenn die dazugehörigen Indizes nicht gepflegt wurden. Mal DBCC SHOWCONTIG ('tabelle') WITH FAST machen, sollte unter 20% beim Grad der Indexfragmentierung herauskommen. Wenn nicht, Indizes mal neu machen, und falls das geholfen hat eine Automatisierung per Wartungsplan

b) wenn die Indizes ok sind dann könnte es ein IO Problem sein... Table scans im Multiuserbetrieb sind manchmal instabil von der Laufzeit, man kann das aber mit der "WITH NOLOCK" Option im Select Statement tunen. Macht nichts kaputt, vermeidet aber Table locks. Bei neueren SQL Servern würde man noch was mit der Transaction isolation auf Tabellenniveau machen, aber der 2008er kann das glaub ich noch nicht.

c) wenns das auch nicht bringt... Daten mal auf NVMEs migrieren

und zuguterletzt

d) neuere Hardware, mehr Speicher

und ganz am Ende

e) mal alles neu machen. Ich hab sowas schon ein paarmal analysiert und graue Haare bekommen. Den Sync würde ich heutzutage mit einer Always On Gruppe machen, ist kinderleicht eingerichtet, kann in beide Richtungen synchronisieren, und 5 Server sind noch kein Problem. Man muß nur mit den Lizenzen etwas aufpassen denn ältere SQL Server sind lizenztechnisch tlw viel viel billiger auf moderen CPUs weil z.B. die CPU-Kerne nicht gedeckelt waren...
GrueneSosseMitSpeck
GrueneSosseMitSpeck 08.07.2020 aktualisiert um 11:55:32 Uhr
Goto Top
Zitat von @wiesi200:

Hallo,

Mssql auf ne Aktuelle Version hochziehen.
Sehr viel Arbeitsspeicher einbauen und dann die 5 Zusatz Datenbanken auf InMemory umbauen und die Hauptdatenbank auf ne Intel Optane SSD packen.

Edit:
https://www.informatik-aktuell.de/betrieb/datenbanken/sql-server-2014-in ...

selbst im SQL SErver 2019 sind die Chancen, eine große und komplexe Datenbank ohne Änderungen an den Tabellenstrukturen auf InMemory umzubauen, eher gering. Bekannte Stoplperstellen:
a) keine clustered Indizes vs Applikation die clustered Indizes vorraussetzt und nicht läuft wenn nicht vorhandnen
b) inkompatible bzw. legacy Datentypen die man erst migrieren muß (text, image)
c) Sachen die In Memory nicht einfach so tut... z.B. stored procedures und Trigger muß man mit Sicherheit umschreiben
Grinskeks
Grinskeks 08.07.2020 um 10:58:43 Uhr
Goto Top
Hallo,

es ist schwierig die genaue Ursache zu ermitteln wenn man keine Monitoring-Möglichkeiten hat. Es kann sein, dass verschiedene Transaktionen aufeinander warten müssen und sich so blocken, dass es nur noch sehr langsam voran geht.Alle Folgetransaktionen reihen sich munter ein.

Wenn du als Entwickler keine Rechte hast, das Verhalten zu analysieren, muss es jemand anderes tun.
Alternativ könnte dir das Recht VIEW SERVER STATE und evtl. VIEW ANY DEFINITION schon ausreichen, um den Activity-Monitor benutzen und Blocking erkennen zu können. Alternativ muss ein Monitoring-Tool her, das dir zur Verfügung gestellt werden kann, ohne daß du dessen Authentifizierung für andere Zwecke nutzen kannst.

WITH NOLOCK ist Im Prinzip nichts anderes als der Versuch, die Abfrage über den IsolationLevel Read Uncommitted laufen zu lassen. Wenn du dir dessen bewusst bist und Dirty Reads als solches akzeptieren kannst, kann nicht evtl. auch der Isolation Level der DB's angepasst werden? Das würde das Locking und Blocking insgesamt wesentlich reduzieren.

Verbesserungspotential gibt es eigentlich immer.

Eventuell kann man auch darüber nachdenken, aus einem Trigger mehrere zu machen, was Komplexität reduzieren und Wartbarkeit erhöhen könnte.
Zudem wäre es sinnvoll darüber zu schauen, ob immer getriggert werden muss oder ob es Fälle gibt, die am Trigger vorbei (bypass) geschickt werden können.
sind die Millionen Datensätze in der Tabelle direkt erforderlich oder können diese auch segmentiert 7 ausgelagert werden? Können die Abfragen auf diese Zeilen auch inperformant und nicht deterministisch sein? Werden Index Scans oder Seeks gemacht, gibt es viele Indizes etc. Indizes können die performance bei Selects drastisch steigern, aber auch zu blocking führen. Zudem kosten Indizes immer beiDML-Statements, da sie dann neu beschrieben und fragmentiert werden.

Gruss
Grinskeks
StefanLausL
StefanLausL 08.07.2020 um 16:29:23 Uhr
Goto Top
Also lt. unserem Admin liegt das definitiv am Schreiben in die "Hauptdatenbank".
Und an der zu langen Verteilung der Daten durch den Trigger.

NoLocks sind wo weit es geht vorhanden.
Indizies fehlen auch keine.

Ich hab die Laufzeit nun etwas verbessern können, in dem ich im Prozess steuere, wann und wo der Prozess in welcher Menge parallel laufen darf.

Die Parallelverarbeitung ist den 5 Datenbank nun mehr oder wenig abgeschaltet.

Nun sind wir bei 2 Sekunden Verarbeitungszeit.
Damit kann man erst mal leben.

Der Trigger bleibt erst mal so, weil wir im Moment genügend andere Baustellen haben.


Vielen Dank für Eure Hilfe !

Gruss Stefan