Performance Verlust trotz stärkere HW

Question

hallo,
ich bin mit einem rätzel konfrontiert. Eine bestimmte Abfrage läuft ca 10 minuten auf einem 2005 standard edition sp3 win2003 beide 64bit. die 10 minuten sind akzeptabel.
die datebank muss auf einem anderen hardware(mehr und schnellere CPUs, mehr cash, schnellere HD RAID10, mehr RAM).

wenn ein backup von der datenbank auf zweite server wiederhergestellt wird und die abfrage ausgeführt wird, braucht sie doppelt so lange. 20 minuten. die abfrage kann beliebig oft auf dem starken server laufen, keine effekte bzgl. caching immernoch 20minuten.

abfrage pläne auf beiden servern sind gleich.
server settings und optionen sind gleich.
datenbank optionen gleich.

habt ihr eine idee was sonst noch geprüft werden könnte? es ist nicht logisch oder?  

Answer 1

hallo,
ich bin mit einem rätzel konfrontiert. Eine bestimmte Abfrage läuft ca 10 minuten auf einem 2005 standard edition sp3 win2003 beide 64bit. die 10 minuten sind akzeptabel.
die datebank muss auf einem anderen hardware(mehr und schnellere CPUs, mehr cash, schnellere HD RAID10, mehr RAM).

wenn ein backup von der datenbank auf zweite server wiederhergestellt wird und die abfrage ausgeführt wird, braucht sie doppelt so lange. 20 minuten. die abfrage kann beliebig oft auf dem starken server laufen, keine effekte bzgl. caching immernoch 20minuten.

abfrage pläne auf beiden servern sind gleich.
server settings und optionen sind gleich.
datenbank optionen gleich.

habt ihr eine idee was sonst noch geprüft werden könnte? es ist nicht logisch oder?  

Du gibst nicht gerade viele Informationen preis. ;-)

Habt Ihr nach dem Restore die Statistiken aktualisiert? Sind beide Server gleich ausgelastet? Sind die Ausführungspläne tatsächlich 100% identisch?

---

-- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org

Answer 2

Hallo Frank,

Danke für die Rückmeldung.

Statistiks:
Ja ich habe die Statistiken auch aktualisiert.

Server Auslastung:
Ursprünglicher alte server trotz Tagesgeschäft schneller.
Auf dem neuen Server nur diese eine Abfrage.

Ausführungspläne:
beide als xml exportiert und vergliechen, kein Unterschied.

RAM:
alter Server 8G
neuer Server 16G

Sortierreihenfolge:
alt und neu server und DB gleich.

Auffälligkeit -Sorry habe vergessen vorher zu schreiben-ist bei dieser Abfrage, dass sie nur über 1 CPU läuft, egal wieviele installiert und lizenziert sind. 

Answer 3

Hallo Frank,

Danke für die Rückmeldung.

Statistiks:
Ja ich habe die Statistiken auch aktualisiert.

Server Auslastung:
Ursprünglicher alte server trotz Tagesgeschäft schneller.
Auf dem neuen Server nur diese eine Abfrage.

Ausführungspläne:
beide als xml exportiert und vergliechen, kein Unterschied.

RAM:
alter Server 8G
neuer Server 16G

Sortierreihenfolge:
alt und neu server und DB gleich.

Auffälligkeit -Sorry habe vergessen vorher zu schreiben-ist bei dieser Abfrage, dass sie nur über 1 CPU läuft, egal wieviele installiert und lizenziert sind. 

Statistiken: Mit Fullscan?

Da die xml Pläne ja identisch sind, läuft die Abfrage auf beiden Servern auf einer CPU!?!

Viele Grüße
Christoph

Answer 4

Hallo Frank,

ich würde mal versuchen die Parallelisierung der Ausführung zu minimieren. Wir hatten einmal ein ähnliches Phänomen, die Ursache war hier, dass der Task von zwei CPUs gleichzeitig abgearbeitet wurde, wodurch sie sich gegenseitig behindert haben. Du schreibts zwar das die Abfrage auf nur eine CPU beschränkt ist, aber ich weiss nicht wie du dass sicherstellst. Denn es ist auch möglich, dass eine Abfrage bei einer CPU mit mehreren Kernen parallelisiert wird. 

In unserem Fall konnte es im Aktivitätsmonitor beobachtet werden.

Diese Option muss nicht Serverweit angepasst werden sondern kann durch den HINT MAXDOP für einzelne SQLs aktiviert werden.

http://msdn.microsoft.com/de-de/library/ms181714.aspx

Hoffe das hilft.

Grüsse

Pichel

Answer 5

Hallo zusammen,
Danke für alle Vorschläge.

@frank
- Ja Ich habe mit Fullscan die Statistiken aktualisiert, habe sogar davor ein Index Rebuild gemacht. leider kein erfolg.
- Auf dem alten Server server läuft definitv auf 1 CPU. Auf neuem Server zuckten hin und wieder die andern CPU's aber haben sich wieder hingelegt und lief alles unter dem 1ster CPU.

Daher Fand ich den Vorschlag von Kollege Pichel interesant und habe auch probiert. Leider Keine Änderung.

Da Projekt in Gefahr ist, werde ich die Datenbank nach SQL 2008 migrieren und ausprobieren. Die Lösung hat uns schon Mal geholfen.
Ich werde mich melden.

Vielen Dank
Majid
 

Answer 6

Hallo Majid,

bitte führe mal die Abfrage auf beiden Servern mit der OPTION ( MAXDOP = 1 ) aus. (QueryHint)

Speichere dabei die Querypläne ab. ( Rechte Maustaste -> in der Queryplan Anzeige..als XML )

Nur machst du das gleiche ohne die Option (MAXDOP..)  auf beiden Servern.
Vergleiche nun die beiden Querypläne mit den beiden alten.

Es wird ein Unterschied vorhanden sein!

Der SQL Server entscheidet je nach HW und verfügbaren CPUs, Einstellungen der MAXDOP( Servereinstellungen), Query Cost ( Servereinstellungen)
wie eine Abfrage abgearbeitet wird.

Mir sind schon eine Menge Server "untergekommen" die mit einer anderen/besseren HW erst einmal schlechter liefen als zuvor.
Hier spielt meist der CPU Context Switch eine große Rolle. ( Perfmon:Context Switch pro Sec)
Je mehr der SQ Server zwischen den CPUs an Arbeit unnützt verschiebt, desto schlechter ist das Abfrageergebnis.

Beste Grüße
Lars

---

lp

Answer 7

Hallo Lars,

Auch 2008 hat hier nicht geholfen.

Ich werde deinen Vorschlag nächste Woche aus probieren.

Jedoch ich muss sagen, dass die Abfrage auf dem alten Server ohne Hint 10 braucht und auf dem neuen Server mit oder ohne Hin ca 20 Minuten. 

Ich habe noch die Abfragepläne mit Hint noch nicht geprüft.

Vielen Dank & Gruß

Majid

Answer 8

Hallo zusammen,

das alles führte leider zur keiner Lösung.

Nach 2 oder 3 mal Wiederholung der Abfrage auf dem neuen Server findet keine Plattenzugiffe mehr statt. Alles ist im Cash und di Ausgabe der Ergebnisse begint direkt nach 4-5 Sekunden an. Aber bis alle Daten ausgegeben dauert es einfach ca. doppelt so lange wie auf dem alten Server.

Damit Einfluss vom Frontend -Managment Studio- isoliert wird, habe ich die ergebnisse  -ca. 250000 rows-mit bcp in eine Datei umgelenkt. Trotzdem kein Unterschied.

Nun die Frage ist: was beeinflusst die Geschwindigkeit der Ausgabe der Daten, die eigentlich fertig im Cash bereit liegen?

Eine zusätzliche unterschied zwieschen schnellen und langsamen aber besser ausgestatteten Servern ist, PROCESSOR_LEVEL aus Umgebungsvariablen. Die alten aber performanten habe 6 und die neuen 15.

Ich denke langsam es liegt an Subraumverzerrung und der Spalt in Raum-Zeit-Kontinuum.

Naja jetzt werden wir nach externe Experten Suchen, die Das Problem für uns lösen können.

VG
Majid

Answer 9

Hallo Majid,

da Du nicht gerade viele Informationen preisgegeben hast, ist eine Analyse von unserer Seite aus nicht möglich.

Da bleibt einzig der Tipp, sich mal die sys.dm_os_wait_stats genauer anzuschauen.
Darüber sollten sich Ansatzpunkt ergeben, wo das neu System seine Zeit verschläft.

Gruß Elmar

Answer 10

Hallo Majid,

Hat Dir die Antwort geholfen?

Grüße,
Robert

Answer 11

Hallo zusammen,
Ich habe Elmars Tipp noch nicht nachgeprüft. Das kommt morgen. Ich möchte mich an dieser Stelle auch sehr bei den Kollegen in diesem Forum für die Tipps bedanken.

Unten ist nochmal eine zusammenfassung von dem was wir bisher gemacht haben incl. HW Beschreibung.

VG
Majid

--*********************************************************

-- Hardware Beschreibung

--*********************************************************

-- Alte Server mit ca 10 Miunten Laufzeit

 

Server: Alt-Prod (physik)

2x Dualcore 2,5Ghz Xeon

8 GB RAM

6 SAS-Platten Raid 5

Windows Server 2003 64 Bit

SQL Server 2005 64Bit SP3

 

 

Server: Alt-QA (physik)

2x Dualcore 2Ghz Xeon

6 GB RAM

6 SAS-Platten im Raid 5

Windows Server 2003 64 Bit

SQL Server 2005 64Bit SP3

 

--******************************

--******************************

 

-- Neue Server mit ca. 18 Minuten Laufzeit

Server: NEU-PRD (VM)

1x Quadcore 2,3Ghz AMD

12 GB RAM

SAN-LUN

Windows Server 2003 64 Bit

SQL Server 2005 64Bit SP3

 

-- Neue Server mit ca. 15 Minuten Laufzeit

Server: NEU-TEST-PROD (Physik)

4x Dualcore 3Ghz Xeon 16 GB RAM

16 SAS-Platten, 2x8 im Raid 10

Windows Server 2003 64 Bit

SQL Server 2005 64Bit SP3

--***************************************************

 

Wie haben wir getestet:

- Die Laufzeiten sind mit Ausnahme von Alt-Prod alle ohne zusätzliche Last und Aktivitäten von anderen Useren gemessen worden.

- Die Testdatenbanken waren Restores vom Produktion. Anschließend mit und ohne "Index Rebuild" und "update Statistics" die Laufzeiten und Abfragepläne gemessen und geprüft.

- Abfragehint MAXDOP =1 eingesetzt um CPU-Wechsels zu verhindern.

- SQL Server Settings sind gleich. - Datenbank Settings wurden ebenfalls verglichen.

- Sortierreihenfolge geprüft (Server und Datenbank). Paradoxerweise auf dem  Alt-Prod entgegen Bestpractice hat der Server Latin1_General_CI_AS und die DB SQL_Latin1_General_CP1_CI_AS. Wir haben alle möglichen Kombinationen getestet.

- Sprache von SQL Server und Betriebssystem geprüft.

- Abfragepläne geprüft.

- Der Abfrageausführung Abfrageplan von der Produktion erzwungen. (siehe USE PLAN)

- Abfrageergebnis in Managementstudio und als bcp query out in Datei ausgegeben. Keine unterschiedlichen Zeiten.

- die Abfrage in 4 parallelen Sessions aus geführt. Alle brauchten bis auf einigen Sekunden gleich (zu)lang.

- Das ganze unter WIN 2008 mit SQL 2008 ausprobiert. Kein Erfolg.

- Jede Abfrage wurde mehrmals auf den Testservern ausgeführt. Ab zweiten Mal waren kaum HD Zugriffe mehr zu sehen. Alles war im Cash.

 

Noch ein Nachsatz: die Benchmark-Abfrage wird nächtlich für ein Gesamtexport von Stammdaten benötigt. Ergebnis umfasst ca. 250000 Datensätze.

Answer 12

OK Ich habe es bis morgen warten nicht aus gehalten.

Als erstes habe ich mit dbcc den os_wait_state zurück gesetzt. Die Abfrage gestartet. Und zwischendurch die untere Abfrage ausgeführt. Auffällig war das steigen von PAGELATCH_SH bei waiting_tasks_count.

Zum Schluß habe ich die Abfrage nochmal gefeuert. Ergebnis ist unten zu sehen. Ehrlich gesagt ich kann es nicht interpretieren und muß mich etwas einlesen was dabei normal ist und was sollte wie verbessert werden.


select * from sys.dm_os_wait_stats where 0!=waiting_tasks_count + wait_time_ms+ max_wait_time_ms+ signal_wait_time_ms

/*
WÄHREND AUSFÜHRUNG

wait_type                                                               waiting_tasks_count   wait_time_ms      max_wait_time_ms   signal_wait_time_ms
------------------------------------------------------------ --------------------       -------------------- --------------------      --------------------
PAGELATCH_SH                                                     1920831                     20468                  15                          9593
PAGELATCH_EX                                                            136                           0                     0                               0
LAZYWRITER_SLEEP                                                      545                   545000                 1000                             0
ASYNC_NETWORK_IO                                                    640                   281                      125                              15
SLEEP_TASK                                                                3397                   0                         0                                 0
SOS_SCHEDULER_YIELD                                               3281                   0                         0                                 0
SQLTRACE_BUFFER_FLUSH                                            136                     544000                4000                            0




NACHHER


wait_type                                                              waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
------------------------------------------------------------ --------------------     --------------------    --------------------        --------------------
PAGELATCH_SH                                                     3349349                   35312                    15                             16265
PAGELATCH_EX                                                     247                                 0                      0                                  0
LAZYWRITER_SLEEP                                              1394                          1394000              1000                                0
ASYNC_NETWORK_IO                                            643                           281                      125                                15
SLEEP_TASK                                                         8539                          0                          0                                    0
SOS_SCHEDULER_YIELD                                        3623                          0                         0                                     0
SQLTRACE_BUFFER_FLUSH                                     348                          1392000              4000                                  0


*/

Viele Grüße
Majid

Answer 13

Hallo Majid,

PAGELATCH sind kurzfristige Sperren, die verwendet werden um interne Synchronisierungen
durchzuführen, und werden dort eingesetzt wo keine "richtigen" Sperren benötigt werden.

Die Ursache für das Ansteigen läßt sich ohne weitere Informationen schwer eingrenzen.
Grundsätzlich betreffen sie alle Arten von internen Speicheroperationen.
Die Ursache kann indirekt auch durch eine Fragmentierung der Daten verursacht werden,
wenn mehr Puffer benötigt werden.

Evtl. einkreisen (ich habe dazu immer noch zu wenig Infos) kannst Du anhand weiterer Sichten.
Unter sys.dm_exec_query_memory_grants findest Du eine (allerdings knappe) Anleitung.
Weitere Erläuterungen dazu findest DU in http://support.microsoft.com/kb/907877

Falls Du darüber größere Abweichungen feststellst, kannst Du sie hier gerne posten.

Da sich die ASYNC_NETWORK_IO quasi identisch verhält, kann man Probleme bei
der Übertragung via Netzwerk ausschließen.
LAZYWRITER_SLEEP, SLEEP_TASK,  SOS_SCHEDULER_YIELD, SQLTRACE_BUFFER_FLUSH
sind unabhängig von der Abfrage zu betrachten und insofern hier nicht relevant.

Gruß Elmar

Answer 14

um eigene DB und Abfrage auszuschließen haben wir die AdventureWorks DB für's Testen genutzt. Alles beim Alten.

Nun ist ein CASE bei MS offen.

Answer 15

Hallo Majid,

wenn du jetzt die Adv2008 benutzt, dann poste bitte mal das SQl State.
Das Problem fasziniert mich... :-)
evtl. kann man das bei uns simulieren.

Beste Grüße
Lars

---

lp

Answer 16

Hallo Lars,
Wir nutzen die ADV2005, haben die SalesorderHeader auf 250000 Sätze erweitert und nur die:

select * from Sales.SalesOrderHeader

ausgeführt.

Support von MS war leider nicht sehr hilfreich. Wir haben uns selber weiter auf der Suche gemacht.
Eins war uns bis dahin klar. Alle Schritte vom Start der Abfrage bis kurz vor Ausgabe der Ergebnismenge war ausgeschlossen.
Es sollte dann geprüft werden: Was und wo passiert etwas, wenn ich die Ergibnisse im RAM zur Verfügung habe und muss sie an Client(lokal) liefern. Ab hier bleibt einzig und allein der Weg vom RAM aus. Dafür haben wir diesmal die CPU Taktfrequenz außer Acht gelassen und nach FSB und Cach Level geschaut.

und sehe da: bis auf ein AMD CPU,bei allen Intel CPUs war L1, L2 und FSB war Maß für Performance. Je größer L2 und FSB umso schneller werden die Daten aus Cash raus geschickt. Logisch oder?

Wir haben noch die Tests nicht ganz abgeschlossen. Wenn die endgültigen Ergebnisse da sind, werde ich sie posten.

Gruß
Majid

Answer 17

Hallo Majid,

bis auf ein AMD CPU,bei allen Intel CPUs war L1, L2 und FSB war Maß für Performance.
Je größer L2 und FSB umso schneller werden die Daten aus Cash raus geschickt. Logisch oder?

das ist so nicht richtig.  Und die (Über-)Betonung solcher Werte in Prospekten div. Hersteller
schlicht Leuteverdummung, nach dem Motto viel hilft viel und größere Werte sind besser.

Weder Prozessorcache (L1-L3) noch FSB/HT Geschwindigkeit sind in solchen Szenarien die
einzig relevanten Faktoren.
Denn der Weg vom RAM des Servers zum Client führt über viele Stationen.
So kann schon ein kleiner Fehler im BIOS große Auswirkungen haben -
und nach der reinen Hardwarer kommen die Treiber für Netzwerkkarte uam.

(Und PAGELATCH bezieht sich immer auf den "normalen" Speicher).

Um eine realistische Messung vorzunehmen solltest Du zunächst den SQL Server Cache leeren
(und der liegt im ganz normalen Speicher). Ein Test mit leeren Puffern sollte begonnen werden mit:
DBCC DROPCLEANBUFFERS
und
DBCC FREEPROCCACHE respektive DBCC FREESYSTEMCACHE

Um andere Faktoren auszuschliessen, verwende Tools wie SQLIOSIM
http://www.sqlskills.com/BLOGS/PAUL/category/SQLIOSim.aspx

Denn selbst wenn Du überzeugt bist, das Problem wäre nicht I/O bezogen:
Dabei müssen die Daten mehrmalig die Speichersubsysteme durchlaufen.
Und die dort erzielten Werte kannst Du als Grundlage für eine Differenzrechnung
ansetzen, wenn Du den Client <-> Server Transfer begutachtest.

Gruß Elmar