none
Batch Requests / s sehr unterschiedliche Messungen RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo liebe SQL Gemeinde,

    da denkt man, man weiß schon sehr viel über den SQL Server und dann kommt so eine verrückte Geschichte.

    Eine Abteilung möchte ein Tool distributieren, daß als Datenbasis den SQL Server verwendet.

    Wir haben also die Datenbank auf unsere Clusterknoten kopiert und anschließend hat die Abteilung die Tests gemacht. Dabei hat man sich furchtbar darüber echauffiert, daß doch die Abfragen viel zu lange dauerten. Das würde ja auf einem Laptop schneller gehen.

    Ich habe also das Tool auf meinen Rechner kopiert und selbst die Abfrage gegen den SQL Cluster laufen lassen. Über den Activity Monitor habe ich maximale "Batch Requests / s" von ~2.000 erhalten.

    Lt. Aussage der Fachabteilung hat man auf einem eigenen Laptop mit deutlich weniger CPU / RAM bis zu 12.000 Batch Requests / s.

    Unser Cluster hat zwei Intel XEON E7520 Quadcore Prozessoren, 64 GB RAM und der Plattenspeicher ist ein SAN-System.

    Ich konnte die Aussage - bis heute morgen - nicht glauben, wurde dann aber eines besseren belehrt. Insbesondere ist es so, daß der Entwickler das System auf seinem Laptop sogar noch begrenzt hat. 3 Cores / 4 GB werden nur verwendet.

    Ich habe anschliessend die Tests noch einmal selbst wie folgt durchgeführt:

    Tool auf lokalem Rechner / Datenbank auf Cluster:   ~2.000 Batch Requests /s
    Tool auf lokalem Rechner / Datenbank auf lokalem Rechner: ~2.000 Batch Requests /s
    (1 CPU / 2 Cores, 4 GB RAM)
    Tool auf lokalem Rechner / Datenbank auf VM ~2.000 Batch Requests /s
    (VM: 1 CPU / 4 GB RAM)
    Tool auf Cluster / Datenbank auf Cluster: ~100 Batch Requests

    Die Frage, die sich mir stellt, WARUM verarbeitet die Datenbank so wenig Requests.

    Das Tool benötigt auf dem Laptop des Entwicklers ~30 Sekunden für die Berechnung.
    Meine Tests haben immer ~15 - 20 Minuten gebracht.

    Die Konfiguration auf dem Laptop habe ich überprüft. Es geht alles über TCP/IP.
    Shared Memory ist deaktiviert!

    Habt Ihr noch eine Möglichkeit, was ich überprüfen oder einstellen soll?

    Sofern Ihr Informationen bezüglich dm_os_waitstats oder ähnliches benötigt, lasst es mich wissen.

     

    Danke für Eure Hilfe - bin hier momentan am Ende meines Lateins.

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Donnerstag, 24. November 2011 13:16
    |

Antworten

  • Question
    Anmelden
    1
    Anmelden

    Hallo Uwe,

    genauso kann man sagen: nur weil Kunde sich die passende Zahl rausgesucht hat, so hat er nicht automatisch Recht ;-)

    Die Zahl Batch Req / s ist zwar als eine pauschales Kriterium für CPU Auslastung "beliebt",
    womit sie aber auch als Kritik angewendet werden kann, nämlich dann, wenn eine einzelne
    Anwendung so viele Anfragen stellt, dass sie den Server auslastet.
    Womit sich der Ersteller der Anwendung fragen lassen muss, ob das Ganze nicht auch mit weniger Abfragen funktioniert.

    So kann ich hier eine lokale 2008 R2 Express Instanz (also dort nur eine CPU) locker
    auf 10-15++ TSD Batch Req/s (und 80+ % CPU) bringen, wenn ich meine Quad Core CPU  (Intel Q9300 3 Jahre alt)
    mit mehreren Threads darauf loslasse, allerdings mit eben jenen Trivialabfragen
    (hier war es "SELECT CompanyName FROM dbo.Customers WHERE CustomerID=N'ALFKI'").

    Wobei der Wechsel von lpc auf tcp etwa 10 % Leistung kostet und mehr 5 % CPU benötigt (grob visuell geschätzt).

    Mache ich was Batchorientiert, kriege ich nie so hohe Werte hin (aber ein Vielfaches an Daten verarbeitet!).

    Soweit ich aus der Beschreibung fabulieren kann:

    Wenn auf eine große Abfrage sehr viele TOP 1 folgen könnte, das ein typisches Verhalten
    von Lazy Loading bei ORMs (und eine der größeren Sünden) sein, siehe z. B.:
    http://ayende.com/blog/1328/combating-the-select-n-1-problem-in-nhibernate
    (Oren Eini aka Ayende gehört zu den Mitentwicklern von NHibernate und einigem mehr - u. a. RavenDB -,
    und weiß wovon redet. Und ähnliche Szenarien gibt es für Linq To SQL, Entity Framework und wie sie alle heißen,
    und von Hand programmieren kann man solchen "Unfug" natürlich auch).

    Wobei es nicht so sein  muss, wenn man das aber ablesen kann, so sollte so etwas geändert werden,
    so programmiert man nur mit Access und ISAM ;-)

    Auf der anderen (Deiner) Seite solltest Du Dir aber auch anschauen, wo die relativ geringe Zahl von Abfragen herkommt.
    Und zunächst prüfen, welche anderen Indikatoren (CPU, I/O, Netzwerk) parallel an den Anschlag gehen.

    Besonders irritiert hat mich Tool auf Cluster / Datenbank auf Cluster: ~100 Batch Requests.
    (falls es sich nicht um einen Tippfehler handelt).
    Die Netzwerkkomponenten sollten dabei außen vor sein - wenn nicht durch das "Tool"
    anderweitig frequentiert oder durch eine VM etc. implizit verursacht.

    Gruß Elmar

    • Als Antwort markiert Uwe RickenMVP Donnerstag, 15. Dezember 2011 16:53
    Donnerstag, 24. November 2011 17:06
    |
  • Question
    Anmelden
    1
    Anmelden

    Hallo Uwe,

    ich denke mal mit dem Parameter "Max degree of parallelism = 1" hast Du schon die richtige Ursache für das unterschiedliche Verhalten gefunden. Je nach Art der SELECT Abfrage und anderen Einstellungen des SQL Servers, kann es zu so einem Verhalten kommen. Eine wichtige Einstellung hierzu ist "Kostenschwelle für Paralallität". Diese ist standardmäßig auf 5 gesetzt, was bedeutet das es zu keiner paralellen Abfrage kommt, wenn der Abfrageoptimierer feststellt das eine serielle Abfrage in 5 Sekunden erledigt ist. Der Wert ist meist etwas zu gering und sollte herauf gesetzt werden, da paralelle Abfragen einen gewissen Overhead erzeugen z.B. durch das entsprechende Abgleichen der Ergebnisse der einzelnen Threads. Desweiteren kann es bei paralellen Abfragen passieren, das diese sich gegenseitig blockieren und so ein Thread auf die Abarbeitung eines anderen warten muß. Unter ungünstigen Umständen ist es sogar möglich das es hierbei zu einer kreuzweisen Blockierung kommt und somit zu einer Deadlock.

    Den Vorschlag von Olaf alle SELECT's einmal mit der Option MAXDOP 1 laufen zu lassen, sollte Dich auf alle Fälle weiter bringen.

    Gruß Falk
    Blog Falk Krahl
    • Als Antwort markiert Uwe RickenMVP Donnerstag, 15. Dezember 2011 16:54
    Donnerstag, 24. November 2011 21:57
    |
  • Question
    Anmelden
    1
    Anmelden

    Hallo Uwe,

    gehöre ich zu den "Regulars"? :-!)

    Wenn ich Dir eine Hardware Konfiguration nenne, könntest Du mir sagen, wieviele Batches/sec der Server verarbeiten könnte? Ich nicht  und um ganz ehrlich zu sein, bis vorhin hätte ich Dir nicht mal sagen können, wieviele Batches/sec meine Server bzw. wieviele Batches/sec Clients gegen die Server maximal ausführen können ... das ist nämlich noch mal ein kleiner Unterschied.

    Ich habe mal bei mir etwas experimentiert und mir dazu ein PowerShell Skript erstellt; ist unten zu finden. Es macht nichts anderes als eine Connection zu öffnen und dann 100T mal ein und das selbe SQL Statement auszuführen; alle 5T Steps wird "Batches/sec" ausgegeben und wenn ich das mit dem Activity Monitor SSMS vergleiche, passen die Werte sehr gut.

    Wie Du unten sehen kannst, habe ich mir "hoch-komplizierte" SQL Statements ausgedacht, die gegen wirklich jede DB ausgeführt werden können; also mal ein Test unabhängig von Deiner Kundendatenbank.

    Die besten Werte erhalte ich immer lokal auf dem Server ausgeführt mit LPC und dem SELECT 'Test' Statement. An Werten bekomme ich:
    Server Xeon Dual Core 3,2GHz, Win 2003 x86, 4 GB, SQL 2005 Std => 2.446 Batches/sec
    Workstation Intel Q6600 Quad Core 2,4 GHZ, Win7 x64, 8 GB, SQL 2008 Exp => 9.440 Batches/sec
    VM Xeon 3040 1,86 GHz, Win 2003 x86, 2 GB, SQL 2005 Exp => 6.823 Batches/sec
    Notebook Intel Core2 Duo T5450, 1,66 GHz, 3 GB, Vista x86, SQL 2008R2 Dev => 8.781 Batches/sec

    Und wie gesagt, das mit einem simplen SQL Statement, das keine IO und so gut wie keine CPU Last verursacht; da frage ich mich, wie 12.000 Batches/sec mit "richtigen" SQL Statements gegen größere Datenmengen zustande kommen sollen?

    Wobei ... um wieder auf den Anfang zurück zu kommen ... das sind die Batches, die der Client gegen den Server schafft. Die CPU / IO des Servers ist dabei ja nicht am Anschlag und kann somit noch mehr verkraften.
    Man kann PowerShell ja mehrfach starten und das Skript dann mehrfach parallel ausführen und wenn ich das auf meinem Notebook mache (3x gleichzeitig), dann schaffe ich laut Activity Monitor bis zu 16.000 Batches/sec.

    Aber ich nehme mal an, das Tool führt die Statements auch nur sequentiell & synchron durch.

    Auf wieviele Batches schaffst Du es bei Deinem Server?

    <#
Batch Request / s
#>

# Servername: Mögliche Presets: 
# tcp: für TCP/IP 
# lpc: Shared Memory
[string] $server   = "lpc:.\SQLEXPRESS";      # SQL Server Instance
[string] $database = "master";      # Database containing the BLOB data.

#$sql = "SELECT COUNT(*) as result FROM sys.tables;"
$sql = "SELECT 'Test batches per sec' AS result;"


Clear-Host;
# Open ADO.NET Connection with Windows authentification.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;";
$con.open();

$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$cmd.CommandType = [Data.CommandType]::Text;
            
[long] $loops = 0;
[datetime] $start = [datetime]::Now;
while ($loops -lt 100000)
{
    $nil = $cmd.ExecuteScalar();
    $loops++;
    
    if (($loops % 5000) -eq 0)
    {
        [long] $duration = [datetime]::Now.Ticks - $start.Ticks;
        [TimeSpan] $ts = New-Object TimeSpan $duration;
        $duration = $ts.TotalMilliSeconds;
            
        Write-Host ("Batch Request/s: " + [Math]::Round($loops / ($duration / 1000.0), 1));
    }       
}
$cmd.Dispose();
$con.Close();
$con.Dispose();
Write-Host "Done";

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Als Antwort markiert Uwe RickenMVP Donnerstag, 15. Dezember 2011 16:51
    Donnerstag, 1. Dezember 2011 17:34
    |

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo Uwe,

    bevor man sich über Wartezeiten und Co. unterhalten kann, wäre zumindest eine rohe Funktionsbeschreibung des "Tools" hilfreich.

    Welche Datenmengen und in welcher Form - kleine, große - wenige, viele Häppchen ;-) werden dabei verarbeitet.

    Sind die Ausgangsdaten mit denen auf dem (so schnellen) Laptop in Verteilung  / Umfang überhaupt vergleichbar?

    Zum Messen auf Deiner Seite würde ich mir neben den Waitstats auch die dm_exec_query_stats anschauen,
    wenn noch nicht klar ist, wo das problematische Ende liegen könnte.

    Nebenbei:
    Auch wenn man via Loopback TCP/IP aktiviert, so ist das immer noch eine Speicher-zu-Speicher-Operation
    (nur mit ein, zwei Umwegen),  die mit einem echten Netzwerkzugriff nicht vergleichbar ist.

    Gruß Elmar

    Donnerstag, 24. November 2011 14:46
    |
  • Question
    Anmelden
    0
    Anmelden

    Wir haben also die Datenbank auf unsere Clusterknoten kopiert und anschließend hat die Abteilung die Tests gemacht.

    Hallo Uwe,

    hat man vielleicht vergessen Indizes auf dem Server neu zu erstellen, oder war es eine leere Datenbank?

    Donnerstag, 24. November 2011 14:56
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo Elmar,

    erst einmal danke für die Informationen. Das Tool liest ausschließlich aus der Datenbank. Ich habe mal den Profiler mitlaufen lassen und zu 99% bestehen die Commands aus trivialen SELECT TOP 1 * FROM Tabelle WHERE Condition.

    Zusammenfassend macht das System (ich kenne es selbst nicht!) folgendes:

    1. Hole ~16.000 Datensätze

    2. Gehe durch die gefundenen Datensätze und suche entsprechend andere Informationen (mit separaten SELECT TOP 1 ...)

    Wir müssen nicht über die Sinnhaftigkeit sprechen - ich habe auch keine Möglichkeiten, da Änderungen durchzuführen (Abfrageoptimierungen o. ä.)

    Wir haben hier nur das Problem, daß - leider momentan zu Recht - der Kunde sagt....:

    "Auf meinem Laptop schaffe ich ~10.000 Req / sec; warum nicht auf dem Server?"

    Wenn ich dann mit meinen < ~ 2.000 Req. / sec komme, kann ich das schon nicht mehr erklären.

    Der Clusterknoten wird zu Testzwecken nur und exklusiv für diese Datenbank genutzt, um keine Seiteneffekte zu haben.

    Und selbst da schaffe ich nicht mehr als 2.000 Req / sec.

    Gleiches Programm, gleiche Datenbank, unterschiedliches Ergebnis.

     

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Donnerstag, 24. November 2011 15:01
    |
  • Question
    Anmelden
    0
    Anmelden

    Wir haben also die Datenbank auf unsere Clusterknoten kopiert und anschließend hat die Abteilung die Tests gemacht.

    Hallo Uwe,

    hat man vielleicht vergessen Indizes auf dem Server neu zu erstellen, oder war es eine leere Datenbank?


    Hallo Yury,

    nee - die Indizes sind schon da. Die Datenbank kommt von der Fachabteilung; das Tool auch!

    Der Entwickler hat BEIDES auf seinem Laptop laufen und schafft die von mir genannten Werte.
    Nehme ich die DB auf den Cluster und starte das Tool lokal ... (siehe oben)

    Wenn ich den Database I/O sehe, kann man sehr gut erkennen, daß nur gelesen wird.
    Der IO geht gegen NULL,

    Der Prozessor langweilt sich.
    Wartende Tasks = 0

    Bei einigen Abfragen habe ich gesehen, daß der Cache-Plan sehr hoch ist > 3.500.
    Das bedeutet, daß hier wohl mit dynamischem SQL gearbeitet wird!

    Wäre ja alles noch erklärbar nur...

    Dann müßten ja auf meinem System wie auch auf dem System des Entwicklers in etwa gleiche Zeiten herauskommen.

    Hier genau setzt ja meine Frage an - was mag an seiner "tollen" Kiste so anders sein, daß er es schafft, ~12.000 Req / sec zu produzieren...

    Scheint eine harte Nuß - ich bin vor lauter Verzweifelung schon so weit, die Abfragen selbst zu analysieren und ggfls. Optimierungshinweise für den Entwickler bereit zu stellen.

    Nur wird dieses Argument nicht ziehen, da ja die Aussage zutrifft, daß wir beide mit gleichen Waffen kämpfen. :D

    Ach ja, der Cluster hat eine Netzanbindung von 4 GBit (4 Netzwerkkarten im Team)

    DAnke für Eure Hilfe...

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Donnerstag, 24. November 2011 15:10
    |
  • Question
    Anmelden
    0
    Anmelden

    nee - die Indizes sind schon da. Die Datenbank kommt von der Fachabteilung; das Tool auch!

    Hallo Uwe,

    mit "neu erstellen" meine ich:

    ALTER INDEX REBUILD

    Donnerstag, 24. November 2011 15:33
    |
  • Question
    Anmelden
    1
    Anmelden

    Hallo Uwe,

    genauso kann man sagen: nur weil Kunde sich die passende Zahl rausgesucht hat, so hat er nicht automatisch Recht ;-)

    Die Zahl Batch Req / s ist zwar als eine pauschales Kriterium für CPU Auslastung "beliebt",
    womit sie aber auch als Kritik angewendet werden kann, nämlich dann, wenn eine einzelne
    Anwendung so viele Anfragen stellt, dass sie den Server auslastet.
    Womit sich der Ersteller der Anwendung fragen lassen muss, ob das Ganze nicht auch mit weniger Abfragen funktioniert.

    So kann ich hier eine lokale 2008 R2 Express Instanz (also dort nur eine CPU) locker
    auf 10-15++ TSD Batch Req/s (und 80+ % CPU) bringen, wenn ich meine Quad Core CPU  (Intel Q9300 3 Jahre alt)
    mit mehreren Threads darauf loslasse, allerdings mit eben jenen Trivialabfragen
    (hier war es "SELECT CompanyName FROM dbo.Customers WHERE CustomerID=N'ALFKI'").

    Wobei der Wechsel von lpc auf tcp etwa 10 % Leistung kostet und mehr 5 % CPU benötigt (grob visuell geschätzt).

    Mache ich was Batchorientiert, kriege ich nie so hohe Werte hin (aber ein Vielfaches an Daten verarbeitet!).

    Soweit ich aus der Beschreibung fabulieren kann:

    Wenn auf eine große Abfrage sehr viele TOP 1 folgen könnte, das ein typisches Verhalten
    von Lazy Loading bei ORMs (und eine der größeren Sünden) sein, siehe z. B.:
    http://ayende.com/blog/1328/combating-the-select-n-1-problem-in-nhibernate
    (Oren Eini aka Ayende gehört zu den Mitentwicklern von NHibernate und einigem mehr - u. a. RavenDB -,
    und weiß wovon redet. Und ähnliche Szenarien gibt es für Linq To SQL, Entity Framework und wie sie alle heißen,
    und von Hand programmieren kann man solchen "Unfug" natürlich auch).

    Wobei es nicht so sein  muss, wenn man das aber ablesen kann, so sollte so etwas geändert werden,
    so programmiert man nur mit Access und ISAM ;-)

    Auf der anderen (Deiner) Seite solltest Du Dir aber auch anschauen, wo die relativ geringe Zahl von Abfragen herkommt.
    Und zunächst prüfen, welche anderen Indikatoren (CPU, I/O, Netzwerk) parallel an den Anschlag gehen.

    Besonders irritiert hat mich Tool auf Cluster / Datenbank auf Cluster: ~100 Batch Requests.
    (falls es sich nicht um einen Tippfehler handelt).
    Die Netzwerkkomponenten sollten dabei außen vor sein - wenn nicht durch das "Tool"
    anderweitig frequentiert oder durch eine VM etc. implizit verursacht.

    Gruß Elmar

    • Als Antwort markiert Uwe RickenMVP Donnerstag, 15. Dezember 2011 16:53
    Donnerstag, 24. November 2011 17:06
    |
  • Question
    Anmelden
    0
    Anmelden

    Lieber Elmar,

    danke, daß Du dir so viele Gedanken über diese Problem machst (ich mache das schon seit zwei Tagen :) ).
    Die 100 Req. / sec sind tatsächlich so vorhanden - das hat mich total sprachlos gemacht.

    CPU und Netwerk I/O geht gegen 0 und ist vernachlässigbar.
    Jetzt werde ich erst einmal die Indexes neu organisieren.

    An den Tipp von Yury könnte was dran sein, weil die DB, die der Entwickler auf dem eigenen Rechner hat, schon ein paar Tage älter ist als die DB, die wir in die LIVE-Umgebung portiert haben.

    Ich werde morgen mal das Ergebnis zusammenfassen.

    Ich dachte halt, dass es ev. eine Einstellung am SQL Server ist - aber auch die bin ich an seinem Laptop dediziert durchgegangen und habe keine "Besonderheiten" gefunden... - ausser:

    - Er nutzt von drei Cores + 1 I/O Affinity (also manuelle Konfiguration)
    - Max degree of parallelism = 1
    (wobei sich mir hier der Sinn und Zweck nicht erschließt!)

    Nochmals herzlichen Dank - ich bin selbst gespannt, was hier die Ursache ist :)

    Allen einen schönen Abend und bis morgen.
    Habe noch einen langen Weg aus dem Schwarzwald zurück nach Frankfurt :(

     

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Donnerstag, 24. November 2011 17:43
    |
  • Question
    Anmelden
    0
    Anmelden

    - Max degree of parallelism = 1
    (wobei sich mir hier der Sinn und Zweck nicht erschließt!)


    Hallo Uwe,

    aus wie vielen Datendateien besteht die Datenbank; nur aus einer MDF? Ich habe schon ein paar mal gehört/gelesen, das es durch die parallel Ausführung von Abfragen es zu ineffizienten Ausführungsplänen kommen kann, wenn nur ein MDF vorhanden ist; dann stören sich die CPU (Cores) sozusagen gegenseitig.

    Du solltest Dir mal die Ausführungspläne ansehen, ob wirklich auf dem Laptop die Abfragen sequentiell und evtl. auf dem Cluster parallel ausgeführt werden. Wenn dem so sein sollte, dann bitte mal die Entwickler zum Test in den SELECT Statements einen Abfrage-Hinweis OPTION (MAXDOP 1) einzubauen, dadurch braucht der Server nicht umkonfiguriert zu werden; ist also auf Abfrage-Level die gleiche Wirkung wie Max Degree Of Parallelism = 1.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Donnerstag, 24. November 2011 17:57
    |
  • Question
    Anmelden
    1
    Anmelden

    Hallo Uwe,

    ich denke mal mit dem Parameter "Max degree of parallelism = 1" hast Du schon die richtige Ursache für das unterschiedliche Verhalten gefunden. Je nach Art der SELECT Abfrage und anderen Einstellungen des SQL Servers, kann es zu so einem Verhalten kommen. Eine wichtige Einstellung hierzu ist "Kostenschwelle für Paralallität". Diese ist standardmäßig auf 5 gesetzt, was bedeutet das es zu keiner paralellen Abfrage kommt, wenn der Abfrageoptimierer feststellt das eine serielle Abfrage in 5 Sekunden erledigt ist. Der Wert ist meist etwas zu gering und sollte herauf gesetzt werden, da paralelle Abfragen einen gewissen Overhead erzeugen z.B. durch das entsprechende Abgleichen der Ergebnisse der einzelnen Threads. Desweiteren kann es bei paralellen Abfragen passieren, das diese sich gegenseitig blockieren und so ein Thread auf die Abarbeitung eines anderen warten muß. Unter ungünstigen Umständen ist es sogar möglich das es hierbei zu einer kreuzweisen Blockierung kommt und somit zu einer Deadlock.

    Den Vorschlag von Olaf alle SELECT's einmal mit der Option MAXDOP 1 laufen zu lassen, sollte Dich auf alle Fälle weiter bringen.

    Gruß Falk
    Blog Falk Krahl
    • Als Antwort markiert Uwe RickenMVP Donnerstag, 15. Dezember 2011 16:54
    Donnerstag, 24. November 2011 21:57
    |
  • Question
    Anmelden
    0
    Anmelden

    Guten Morgen Olaf,
    guten Morgen Falk,

    erst mal herzlichen Dank für die Info. Was "Max Degree of Parallelism" bedeutet, war mir klar - aber den Zusammenhang mit EINER mdf habe ich total vernachlässigt :(

    Heute werde ich auf meinem Laptop zunächst einmal die Indexes neu organisieren - das hat gestern abend leider auf dem Cluster nicht mehr funktioniert, da man dort recht sparsam mit den Plattenkapazitäten umgeht. Soll mich aber nicht stören.

    Da ich die Abfragen nicht ändern kann (sind "hardcodiert" in der Anwendung!), werde ich auf meinem lokalen Server einfach mal die MAXDOP auf 1 stellen. Sobald ich ein Ergebnis habe, melde ich mich wieder zurück.

    Nochmals wirklich herzlichen Dank für Eure Hinweise - manchmal sieht man einfach den Wald vor lauter Bäumen nicht :)

    Euch allen einen angenehmen Start in den Tag...

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Freitag, 25. November 2011 05:03
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo!

    Wie ist der Stand Deiner Sache? Das interessiert uns doch wohl alle wo da das Problem lag/liegt.

    Grüße
    Jörg Schneider

    Jörg Schneider
    Mittwoch, 30. November 2011 10:31
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    so - leider etwas gedauert (man muß ja auch noch andere Dinge machen :) ).

    Die Sache wird immer verzwickter und ich muß auch eingestehen, daß ich jetzt doch mit meinem Latein am Ende bin.

    Ich habe die IDENTISCHE Datenbank auf meinen Rechner kopiert und das "Tool" auf die DB losgelassen.
    Ergebnis: 2.000 Requests / min

    Mein Rechner hat die folgende Ausstattung:
    2 * Intel Q 740 , 7.33 GHz
    8 GB RAM

    Ich komme also auch auf meinem Rechner nicht wirklich weiter...

    Anschließend habe ich die Indexreorganisation durchgeführt und das Tool erneut gestartet.

    gleiches Ergebnis.

    Aktuell werden wir wohl bei Microsoft einen Call aufmachen.
    Ist ein wenig frustrierend, weil wir einfach nicht eingrenzen können, woran es liegt.

    Ich habe im dritten Anlauf dann den SQL Profiler mitlaufen lassen, um mal genau zu sehen, was das "Tool" überhaupt auf der DB macht.
    Erschreckend - aber trivial. Es werden 100.000 de "identische" Statements gegen die DB gefeurt.

    Ich muß noch einmal betonen, daß die Funktionalität - leider - nicht zur Debatte steht. Es ist also müßig, über Sinn und Unsinn zu spekulieren. Ich habe halt gesehen, daß die Requests auf dem lokalen Rechner des Entwicklers tatsächlich bis zu 7 mal schneller waren :(

    Sofern jemand Interesse an eigenen Tests hat, einfach eine EMail an mich. Ich würde dann ein Share zur Verfügung stellen, in dem sich die DB sowie das "Tool" befindet. Die Daten wären dann selbstverständlich unkenntlich gemacht ...

    EMail: uwe.ricken@db-berater.de

    (Dieses Angebot geht aber nur an die Regulars hier, die ich "kenne"!)

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Donnerstag, 1. Dezember 2011 13:21
    |
  • Question
    Anmelden
    1
    Anmelden

    Hallo Uwe,

    gehöre ich zu den "Regulars"? :-!)

    Wenn ich Dir eine Hardware Konfiguration nenne, könntest Du mir sagen, wieviele Batches/sec der Server verarbeiten könnte? Ich nicht  und um ganz ehrlich zu sein, bis vorhin hätte ich Dir nicht mal sagen können, wieviele Batches/sec meine Server bzw. wieviele Batches/sec Clients gegen die Server maximal ausführen können ... das ist nämlich noch mal ein kleiner Unterschied.

    Ich habe mal bei mir etwas experimentiert und mir dazu ein PowerShell Skript erstellt; ist unten zu finden. Es macht nichts anderes als eine Connection zu öffnen und dann 100T mal ein und das selbe SQL Statement auszuführen; alle 5T Steps wird "Batches/sec" ausgegeben und wenn ich das mit dem Activity Monitor SSMS vergleiche, passen die Werte sehr gut.

    Wie Du unten sehen kannst, habe ich mir "hoch-komplizierte" SQL Statements ausgedacht, die gegen wirklich jede DB ausgeführt werden können; also mal ein Test unabhängig von Deiner Kundendatenbank.

    Die besten Werte erhalte ich immer lokal auf dem Server ausgeführt mit LPC und dem SELECT 'Test' Statement. An Werten bekomme ich:
    Server Xeon Dual Core 3,2GHz, Win 2003 x86, 4 GB, SQL 2005 Std => 2.446 Batches/sec
    Workstation Intel Q6600 Quad Core 2,4 GHZ, Win7 x64, 8 GB, SQL 2008 Exp => 9.440 Batches/sec
    VM Xeon 3040 1,86 GHz, Win 2003 x86, 2 GB, SQL 2005 Exp => 6.823 Batches/sec
    Notebook Intel Core2 Duo T5450, 1,66 GHz, 3 GB, Vista x86, SQL 2008R2 Dev => 8.781 Batches/sec

    Und wie gesagt, das mit einem simplen SQL Statement, das keine IO und so gut wie keine CPU Last verursacht; da frage ich mich, wie 12.000 Batches/sec mit "richtigen" SQL Statements gegen größere Datenmengen zustande kommen sollen?

    Wobei ... um wieder auf den Anfang zurück zu kommen ... das sind die Batches, die der Client gegen den Server schafft. Die CPU / IO des Servers ist dabei ja nicht am Anschlag und kann somit noch mehr verkraften.
    Man kann PowerShell ja mehrfach starten und das Skript dann mehrfach parallel ausführen und wenn ich das auf meinem Notebook mache (3x gleichzeitig), dann schaffe ich laut Activity Monitor bis zu 16.000 Batches/sec.

    Aber ich nehme mal an, das Tool führt die Statements auch nur sequentiell & synchron durch.

    Auf wieviele Batches schaffst Du es bei Deinem Server?

    <#
Batch Request / s
#>

# Servername: Mögliche Presets: 
# tcp: für TCP/IP 
# lpc: Shared Memory
[string] $server   = "lpc:.\SQLEXPRESS";      # SQL Server Instance
[string] $database = "master";      # Database containing the BLOB data.

#$sql = "SELECT COUNT(*) as result FROM sys.tables;"
$sql = "SELECT 'Test batches per sec' AS result;"


Clear-Host;
# Open ADO.NET Connection with Windows authentification.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;";
$con.open();

$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$cmd.CommandType = [Data.CommandType]::Text;
            
[long] $loops = 0;
[datetime] $start = [datetime]::Now;
while ($loops -lt 100000)
{
    $nil = $cmd.ExecuteScalar();
    $loops++;
    
    if (($loops % 5000) -eq 0)
    {
        [long] $duration = [datetime]::Now.Ticks - $start.Ticks;
        [TimeSpan] $ts = New-Object TimeSpan $duration;
        $duration = $ts.TotalMilliSeconds;
            
        Write-Host ("Batch Request/s: " + [Math]::Round($loops / ($duration / 1000.0), 1));
    }       
}
$cmd.Dispose();
$con.Close();
$con.Dispose();
Write-Host "Done";

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Als Antwort markiert Uwe RickenMVP Donnerstag, 15. Dezember 2011 16:51
    Donnerstag, 1. Dezember 2011 17:34
    |
  • Question
    Anmelden
    0
    Anmelden
    Hallo Olaf, selbstverständlich gehörst Du auch dazu :) Ich bereite gerade für Elmar die Datenbank vor. - Datenbank - Tracefile - Tool Sofern Du interessiert bist, würde ich Dir dann den Link ebenfalls zusenden. Dein Script werde ich noch heute ausprobieren und die Ergebnisse hier posten!
    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Freitag, 2. Dezember 2011 08:24
    |
  • Question
    Anmelden
    0
    Anmelden

    Update in eigener Sache...

    so - nun hat sich Elmar Boye (dafür auch hier ein großes Dankeschön!) der Sache auch mal angenommen. Er konnte die gleichen Erkenntnisse ziehen, wie auch ich. Auch bei ihm waren max. ~3.000 Req / sec erreichbar.

    Der Ansatz von Elmar war der, dass das Problem wohl eher nicht am SQL Server liegt sondern an der Applikation.
    Wir werden nun mit dem Kunden noch einmal ein Gespräch führen müssen um ihn davon zu überzeugen, die vielen Tausend Einzelaktionen in mengenorientierte Aktionen umzuprogrammieren.

    Gleichzeitig investigieren wir noch in Richtung TCP/IP Stack. Ev. gibt sich auch hier noch ein Engpass, den wir übersehen haben.

    Allen hier ein großes Danke für Eure Hilfe.
    Wenn auch noch kein Ergebnis vorhanden ist, so wurden mir doch schon einige Ansätze gezeigt, die ich schlicht und einfach übersehen habe.

    Sofern sich Neuigkeiten ergeben, werde ich auf jeden Fall weitere Informationen bereitstellen.

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Dienstag, 6. Dezember 2011 09:37
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    ich möchte diesen Thread nun abschließen und mich noch einmal ganz herzlich bei Elmar, Olaf und Falk für ihre Hinweise bedanken. Wir haben fü dieses Problem den Premium Support von Microsoft mit ins Boot geholt und können nun bestätigen, daß nicht der SQL Server das Problem ist.

    Elmar hatte bereits in einem privaten Mailverkehr darauf hingewiesen, daß wohl eher das Problem in der Applikation liegt.

    Damit aber nun alle wissen, warum genau die Requests nicht höher gingen, hier noch mal die Erläuterung.

    Wir konnten beobachten, daß die Requests (lokal) in direktem Zusammenhang mit der CPU standen.
    Schnellere CPU - mehr Requests

    Das "Tool" hat die Requests seriell gesendet, somit wurde über eine Connection auch nur ein Kern belastet.
    Eine Parallelisierung fand nicht statt.

    Die lokalen Tests waren immer ausreichend, da hier - selbst bei Verwendung von TCP - kein Netzwerkverkehr stattfindet sondern nur bis zur TCP-Schicht eine Kommunikation lokal statt findet.

    Wird der Test dann über das Netztwerk realisiert, werden Datagramme und Pakete generiert!

    Letztendlich wurde der Netzwerkverkehr überwacht und es wurde festgestellt, daß für EINE Aktion teilweise 6 Pakete gesendet wurden.
    Nachdem ich mir dann die Anwendung angeschaut habe, habe ich festgestellt, daß drei Commands vom Tool an den Server versendet wurden.

    BEGIN TRANSACTION
    SELECT einen Record
    COMMIT TRANSACTION

    Nachdem TRANSACTIONS rausgenommen wurden, wurden die Requests nicht erhöht - aber die Tests waren ca. 40% schneller :)

    Auf Grund der gemessenen Latenzzeiten zwischen den einzelnen Paketen von ca. 0.1 ms war klar, daß rein rechnerisch nicht mehr Requests bearbeitet werden können.

    Letztendlich hat das auch ein weiterer Test bewiesen, in dem wir das "Tool" noch ein zweites und drittes Mal gestartet haben. Die Anzahl der Requests sind linear gestiegen.

    Fazit: Microsoft hat mehr oder weniger bestätigt, daß das Problem nicht in der Infrastruktur sondern im Programm liegt.
    Dazu hatte mir Elmar auch schon bereits ein paar Hinweise gegeben.

    Es beruhigt mich, zu wissen, daß der SQL Server noch lange nicht an seine Grenzen gestoßen ist.

    Dies als Update an alle, die mir geholfen haben.
    Zu guter Letzt sei noch erwähnt, daß sich Microsoft - zu Beginnn des Incidents - nicht gerade sehr professionell verhalten hat.
    Nach erheblichen Beschwerden war dann aber innerhalb von zwei Tagen ein verwertbares Ergebnis da.
    Sofern Microsoft Premium Support hier mitliest - ebenfalls ein Danke an das Team von Microsoft!

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Donnerstag, 15. Dezember 2011 16:51
    |
  • Question
    Anmelden
    0
    Anmelden

    Es beruhigt mich, zu wissen, daß der SQL Server noch lange nicht an seine Grenzen gestoßen ist.


    Hallo Uwe,

    freut mich, das es plausible geklärt werden konnte, auch wenn es wohl sehr mühselig war.
    Wobei mir einfällt, das ich vergessen habe von meinen "Negativ-Rekord" für Req/sec zu berichten; das waren 38 Req/sec. Da gingen die Request über ein WAN Leitung (also Internet) mit einer Latenzzeit von ~26ms, auch hier könnten rechnerisch nie mehr ausgeführt werden.

    ... und mich beruhigt es, das es immer eine logische Erklärung gibt und selten bis nie der SQL Server schuld ist :-) 

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Donnerstag, 15. Dezember 2011 18:18
    |
  • Question
    Anmelden
    0
    Anmelden

    Guten Morgen Olaf,

    genau so ist es. Bisher konnten wir IMMER belegen, daß nicht der SQL Server die Ursache ist sondern andere Parameter. In diesem Fall haben wir uns leider verunsichern lassen, da immer wieder darauf hingewiesen wurde, daß in "eigenen" Tests mindestens 6.000 / sec über das Netzwerk geschafft wurden.

    Elmar hatte mich in einer privaten Mail darauf hingewiesen, daß Ansätze von "Multithreading" vorhanden gewesen sind. Ich vermute mittlerweile ganz stark, daß die Tests mit aktiviertem Multithreading gemacht wurden.

    Nochmals Danke an Alle und allen hier ein schönes Weihnachtsfest verbunden mit den besten Wünschen für das neue Jahr.

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Freitag, 16. Dezember 2011 08:12
    |