Update-Befehl. Dauer mit 2 identische Tabellen ist unterschiedlich. Warum?

[Viacheslav 1]

Hallo Leute,

 

ich habe eine Situation, die ich selber nicht erklären kann.

Es gibt eine Tabelle (Zieltabelle) mit ca. 5,6 Millionen Datensätze, der tägliche Datenzuwachs beträgt durchschnittlich 1,3 Tausend Datensätze.

Daten in dieser Tabelle werden aktualisiert, dafür werden gruppierte Daten aus einer anderen Tabelle (Quelltabelle, 38 Tausend gruppierte DS, 42 Tausend, wenn die Daten nicht gruppiert sind) genommen  (INNER JOIN-Befehl)

 

Bei der Aktualisierung sind ca. 17 Tausend Datensätze in Zieltabelle betroffen.

 

Wenn die Aktualisierungsabfrage ausgelöst ist, dauert es ca. 1200 Sekunden!

Es ist unglaublich viel. Als ich die Ursache gesucht habe, habe ich eine identische Kopie der Zieltabelle erstellt, und derselbe Abfrage ausgelöst.

Mit einer Kopie der Zieltabelle dauerte es erst 1 Sekunde!

Ich habe alles geprüft. Alle Eigenschaften der Zieltabelle und der Kopie sind identisch.

Die Daten sind auch identisch. Ein Unterschied war in Indizes-Fragmentierung, der Wert in beiden Fällen war aber unter 1%.

Die Indizes habe ich trotzdem neugebildet (REBUILD), das hat aber nicht geholfen.

 

Laut Ausführungsplan für Aktualisierung der Zieltabelle 100% Kosten sind für "Clustered Index Scan" gebraucht, 0 % für "Clustered Index Update" (Dauer - 1200 Sekunden)

Laut Ausführungsplan für Aktualisierung der Kopie von Zieltabelle 43% Kosten sind für "Clustered Index Scan" gebraucht, 54 % für "Clustered Index Update" (Dauer - 1 Sekunde)

Die Ausführungspläne enthalten auch andere Teile noch, die Prozentteil ist aber sehr gering. Die Ausführungspläne sehen unterschiedlich aus.

Version von SQL Server ist 10.50.4000.

Hat jemand eine Idee, wo kann die Ursache liegen?

[NilsK 3.017]

Moin,

 

Gibt es gleichzeitige Zugriffe auf die Originaltabelle, die Sperren auslösen?

 

Gruß, Nils

 

 

[winmadness 79]

Sind Trigger auf die Original-Tabelle konfiguriert - oder Fremdschlüssel (Referentielle Integrität)?

[MDD 13]

Guten Morgen

 

zuerst hänge mich gleich bei den vorherigen Kollegen an. Zusätzlich noch folgende Gedanken

 

A) Befindet sich die idente Kopie in der selben Datenbank?

 

B) Frage zum Update-Statement. Kannst du das vereinfacht darstellen? Greift das Statement beim Update der Kopie auch nicht auf die Daten der Orginalzieltabelle zu? Oder wäre es möglich das Bild vom Abfrageplan zu zeigen? 

 

C) Was noch einen Unterschied machen könnte ist die physische Defragmentierung auf der Platte - abhängig von der Umgebung. Aber das ist schon recht weit hergeholt. Würde auf suboptimale Einstellungen beim automatischen Vergrößern der Datenbank bzw. der Wartung hindeuten. 

 

D) Unterschiedliche Ausführungspläne lassen mich unterschiedliche Statistiken vermuten. (SQL - selbst erstellte bzw. veraltete) 

 

E) Kannst du prüfen ob bei beiden Index-Scans gleich viele Pages gelesen wurden (set statistics io on). Das müsste theoretisch der Fall sein bei identen Tabellen. 

 

Welche Edition des SQL 2008R2 verwendest du? Standard?

 

Gruß MDD

[Dukel 465]

SQL 2008? Da ist selbst der Extended Support seit zwei Jahren abgelaufen. Außerdem gibt es in jeder SQL Server Version Performance Verbesserungen (wobei das die Ursache vermutlich nicht beheben würde).

bearbeitet 2. April 2021 von Dukel

[NilsK 3.017]

Moin,

 

Weder die Version noch die Fragmentierung kommen für solche Unterschiede in Frage. Das wird was Logisches sein.

 

Gruß, Nils

[Viacheslav 1]

Am 1.4.2021 um 18:55 schrieb NilsK:

Gibt es gleichzeitige Zugriffe auf die Originaltabelle, die Sperren auslösen?

Nope. Ich habe sogar nach Feierabend das gemacht, wenn keiner DB benutzt hat.

Am 1.4.2021 um 19:02 schrieb winmadness:

Sind Trigger auf die Original-Tabelle konfiguriert - oder Fremdschlüssel (Referentielle Integrität)?

Auch nicht.

Am 2.4.2021 um 08:25 schrieb MDD:

A) Befindet sich die idente Kopie in der selben Datenbank?

 

B) Frage zum Update-Statement. Kannst du das vereinfacht darstellen? Greift das Statement beim Update der Kopie auch nicht auf die Daten der Orginalzieltabelle zu? Oder wäre es möglich das Bild vom Abfrageplan zu zeigen? 

 

C) Was noch einen Unterschied machen könnte ist die physische Defragmentierung auf der Platte - abhängig von der Umgebung. Aber das ist schon recht weit hergeholt. Würde auf suboptimale Einstellungen beim automatischen Vergrößern der Datenbank bzw. der Wartung hindeuten. 

 

D) Unterschiedliche Ausführungspläne lassen mich unterschiedliche Statistiken vermuten. (SQL - selbst erstellte bzw. veraltete) 

 

E) Kannst du prüfen ob bei beiden Index-Scans gleich viele Pages gelesen wurden (set statistics io on). Das müsste theoretisch der Fall sein bei identen Tabellen. 

 

Welche Edition des SQL 2008R2 verwendest du? Standard?

A) Ja

B) Ne, beim Tests ist ausschließlich Testtabelle betroffen. Ausführungsplan kann ich zeige, wenn es noch benötigt wird.

C) Das habe ich auch vermutet. sieht aber nicht so aus...

D) Es ist tatsächlich so :)

E) Pages weiß ich nicht, Anzahl von Zeilen ist aber unterschiedlich. 758028 gegen 15537.

Ich verwende SQL 2008R2 SE 64 bit

Am 2.4.2021 um 12:37 schrieb Dukel:

SQL 2008? Da ist selbst der Extended Support seit zwei Jahren abgelaufen. Außerdem gibt es in jeder SQL Server Version Performance Verbesserungen (wobei das die Ursache vermutlich nicht beheben würde).

Ja, schon ziemlich alt :\ Das Update ist aber noch nicht geplant :(

Das Problem aber wurde gelöst. Nach Statistik Update in der Zieltabelle ist Abfragedauer genauso schnell wie für die Kopie.
Danke an alle für die Hilfe!

[Sunny61 829]

vor 39 Minuten schrieb Viacheslav:

Das Problem aber wurde gelöst. Nach Statistik Update in der Zieltabelle ist Abfragedauer genauso schnell wie für die Kopie.

Danke für die Nennung der Lösung. Wird nicht die Statistik per Default jede Nacht auf einem SQL Server aktualisiert? Bei der Express Variante muss man das manuell machen, aber beim 'normalen' SQL wird das doch per Task gemacht. Warum dann nicht hier?

[Viacheslav 1]

vor 5 Minuten schrieb Sunny61:

Warum dann nicht hier?

Das hat mir auch gewundert.

Werden jetzt alle Datenbanken zu prüfen, ob Statistik Update in Wartungspläne eingerichtet ist.

[Dukel 465]

https://www.sqlshack.com/sql-server-statistics-and-how-to-perform-update-statistics-in-sql/

[Viacheslav 1]

vor 22 Minuten schrieb Dukel:

https://www.sqlshack.com/sql-server-statistics-and-how-to-perform-update-statistics-in-sql/

Danke! Sehr detailliert und hilfreich!