Wie viel Speicher wird benötigt, um eine Tabelle im Cache zu halten?(SQL Server 2008)


5

Ich führe lange und komplexe Abfragen aus, die meist eine große Tabelle verwenden - 8 GB, 40 MB Zeilen.AFAIK, alle/die meisten Zeilen werden in jeder Abfrage verwendet.Im Aktivitätsmonitor werden viele E/A-Vorgänge angezeigt - für die erste und jede weitere Abfrage.Der Server verwendet derzeit 6,5 GB Arbeitsspeicher und ich möchte ein Upgrade durchführen.Die Frage ist, wie viel Speicher benötigt wird, um all diese Festplattenlesevorgänge zu vermeiden.Ist es im Baseballstadion der Größe des Tisches oder mehr?

Dies ist der SET STATISTICS IO-Ausgang.BigTable ist derjenige, nach dem ich frage, SmallTable hat eine 1 zu viele Beziehung zu BigTable.#entrance enthält die Ausgabe der Abfrage (mehrere hundert Ausgabezeilen).

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+2

Führt dieser Server/diese Datenbank nichts anderes aus, als Ihre Abfragen über 1 Tabelle mit 8 GB Daten auszuführen?Ich kratzte mich auch etwas über der Tabelle 1, aber ein komplexer Aspekt der Abfrage dieser Frage.Haben Sie eine Chance auf DDL oder einen kleinen Einblick in die Natur dieser Komplexität? 14 sep. 112011-09-14 22:20:40

  0

Nur eine grundlegende Überprüfung, welche Version von SQL Server verwenden Sie?Ist dies nicht der Fall, können Sie nicht den gesamten aktuellen Speicher für SQL Server verwenden. 16 sep. 112011-09-16 15:30:00

  0

Schließlich habe ich den RAM des Servers aktualisiert und einen Test durchgeführt.Ich habe die Ergebnisse unten als Antwort gepostet. 09 feb. 132013-02-09 08:50:11

3

Was ist die Natur Ihrer Fragen?Nur SELECTs?Oder eine Mischung aus DUI (DELETE, UPDATE, INSERTs)?und was ist dein Wiederherstellungsmodell?Verwenden Sie die Standardisolationsstufe für Transaktionen?

Wenn Sie alle diese Seiten im Speicher ablegen möchten, benötigen Sie mehr Speicher (aber das wussten Sie bereits).Sie möchten wissen, wie viel es kostet, aber je nach Art Ihrer Abfragen können dennoch einige E/A-Probleme auftreten, auch wenn genügend Speicher für die gesamte Tabelle vorhanden ist.

Betrachtet man all das oben Genannte ... ja, parkt die Größe der Tabelle auf der Festplatte als die Größe des Arbeitsspeichers.Seien Sie nur nicht überrascht, wenn Sie feststellen, dass Sie danach noch Festplatten-E/A haben.

  0

Ich habe den Arbeitsspeicher des Servers aktualisiert und "die Größe der Tabelle auf der Festplatte als die Größe des Arbeitsspeichers" ist absolut korrekt.Siehe meine Antwort unten. 08 feb. 132013-02-08 11:59:48


8

Dies ist eine zweiteilige Antwort.

Der erste Teil ist, so viel RAM zu kaufen, wie Sie sich leisten können (solange es in den Server passt).

Der zweite Teil ist, dass Sie immer Lesungen auf Daten sehen.Der Trick besteht darin, festzustellen, ob diese Lesevorgänge physisch oder logisch sind.Die physischen Lesevorgänge werden auf die Festplatte übertragen.Logische Lesevorgänge werden aus dem Pufferpool im Speicher gelesen.Sie können dies sehen, indem Sie "SET STATISTICS IO ON" am Anfang des SQL-Skripts setzen und es dann in SSMS ausführen.Auf der Registerkarte Nachrichten erhalten Sie spezifische E/A-Informationen für jede ausgeführte Abfrage, insbesondere die Anzahl der logischen und physischen Operationen (in Ihrem Fall Lesevorgänge), die tabellenweise ausgeführt werden.Wenn die physischen Lesezahlen ziemlich hoch sind (diese Zahlen entsprechen im Grunde der Anzahl der Blöcke, die so vielfach durch 8 gelesen wurden, dividieren Sie durch 1024, um eine Zahl in Megabyte zu erhalten), benötigen Sie mehr RAM.Wenn die Anzahl der logischen Lesevorgänge hoch ist, werden Sie möglicherweise nicht auf die Festplatte verschoben, Ihre Abfrage muss jedoch optimiert, Indizes erstellt usw. werden.

(Hinweis: Für die Beantwortung dieser Frage gehe ich nur von SELECT-Anweisungen aus. INSERT/UPDATE/DELETE-Vorgänge sind etwas komplexer, es gelten jedoch dieselben Grundlagen.)

  0

Ich versuche: SET STATISTICS IO ON und melde mich zurück.Weitere Informationen: Die Abfragen sind zu 100% ausgewählt.Jede Abfrage dauert mehrere Minuten.Auf dem Server wird auch die kleine App ausgeführt, die die Abfragen durchführt und die Ergebnisse in Excel ausgibt. Ich bezweifle jedoch, dass dies die Serverleistung beeinträchtigt. 15 sep. 112011-09-15 05:00:37

  0

Wenn Sie Hilfe beim Lesen der Ausgabe benötigen, können Sie den Ausführungsplan und die SET STATISTICS IO-Ausgabe veröffentlichen. 15 sep. 112011-09-15 05:05:59


1

Ich bin mir nicht sicher, ob hier mehr RAM helfen wird.Schnelle Berechnungen deuten darauf hin, dass Ihre Anfrage irgendwo im Bereich von 125 GB Daten verarbeitet wird, was für eine Tabelle mit 8 GB recht beeindruckend ist.

(2829948 + 13482115 + 1568) x 8/1048576 = 125 GB

Die 270 MB an physischen Lesevorgängen machen einen Teil der langsamen Ausführung aus, sind jedoch nicht so umfangreich wie die In-Memory-Overheads.

Wenn Sie die Abfrage überarbeiten, erhalten Sie bessere Ergebnisse als durch Hinzufügen von zusätzlichem Speicher.Stellen Sie einen DDL-/Abfrage-/Ausführungsplan in eine neue Frage, und jemand wird Ihnen sicherlich helfen können.


3

Ich bin immer zu spät zur Party.:) Diese Abfrage kann höchstwahrscheinlich mit großer Wirkung eingestellt werden.Aber für die Frage, wie viel RAM eine 8-GB-Tabelle zwischenspeichern soll ... ist eine vorläufige Frage, wie viele Speicherknoten SQL Server für den Server meldet.Jeder Speicherknoten (logisch äquivalent zu den physischen NUMA-Knoten) verfügt über einen unabhängig verwalteten Pufferpool-Cache.Aufgaben, die auf Prozessoren in diesem Knoten ausgeführt werden, fügen Blöcke in den Pufferpool in diesem Knoten ein.

Wenn also die Möglichkeit besteht, dass die Tasks, die die 8-GB-Tabelle vollständig lesen, auf einem einzelnen NUMA-Knoten ausgeführt werden, muss der Pufferpool dieses Knotens die 8-GB-Tabelle enthalten können.Beginnen Sie also mit 8 GB maximalem Serverspeicher pro NUMA-Knoten.Dann lassen Sie uns anfangen, es zu erhöhen.Der Abfragespeicher kann bis zu 70% des Pufferpools belegen, während für den Inhalt von Datenbankblöcken nur 30% übrig bleiben.Die 8-GB-Tabelle darf also nicht mehr als 30% des maximalen Serverspeichers auf einem einzelnen NUMA-Knoten für den Server betragen.26 2/3 GB für maximalen Serverspeicher.Pro NUMA-Knoten.Wenn es sich um SQL Server 2012 handelt, liegt das Material, für das SQL Server 2008 R2 und frühere Versionen den mehrseitigen Allokator verwendet haben (der sich somit außerhalb des maximalen Serverspeicherbereichs befand), nun im maximalen Serverspeicherbereich und muss berücksichtigt werden.Es muss in beiden Fällen in den gesamten Serverspeicher einbezogen werden, unabhängig davon, ob der Teil der maximalen Speicherkapazität des SQL Servers ist oder nicht.

Oh ja ... jede Verwendung von temporären Tabellen oder anderen temporären Datenbanken sollte ebenfalls im Hauptspeicher des Servers untergebracht werden, damit die Tabelle nicht aus dem Cache entfernt wird.

Fügen Sie genügend Speicher für das Windows-Betriebssystem hinzu (mindestens 4 GB).Fügen Sie genug für SQL Server-Arbeitsthreads, den SQL Server-Kernprozess, SQL Agent-Jobs und alle anderen Anforderungen an den Anwendungsspeicher hinzu.

Auf einem einzelnen NUMA-Knotenserver würde ich ungefähr 36 GB RAM mit einem maximalen Serverspeicher von mindestens 27 GB bereitstellen, um die 8-GB-Tabelle zuverlässig zwischenzuspeichern.Wenn mehrere NUMA-Knoten vorhanden sind, wird der maximale Serverspeicher pro NUMA-Knoten um mindestens 26 2/3 GB erhöht, und die zusätzlichen Worker-Threads werden mit den zusätzlichen Kernen in den zusätzlichen NUMA-Knoten verknüpft.

Dies mag zwar übertrieben erscheinen, ist jedoch teilweise darauf zurückzuführen, wie SQL Server mit NUMA umgeht.Aber Sie könnten Glück haben!Wenn ein einzelner Lazy Writer ausreicht, können Sie das Start-Trace-Flag 8015 verwenden, um NUMA zu ignorieren und einen einzelnen Pufferpool zu verwalten.Auf diese Weise sollten etwa 36 bis 40 GB ausreichen, um die 8-GB-Tabelle speicherresident zu halten, unabhängig davon, wie viele NUMA-Knoten sich auf dem Server befinden.Diskutiere dies ein bisschen mehr im folgenden Blog-Beitrag.Wenn Sie sich für die Verwendung des Ablaufverfolgungsflags 8015 entscheiden, sollten Sie das Ablaufverfolgungsflag 8048 zusammen mit diesem auswerten, um zu verhindern, dass Spinlock-Konflikte und CMEMTHREAD-Wartezeiten im Zusammenhang mit der Speicherzuweisung eskalieren.http://sqlsasquatch.blogspot.com/2013/02/sql-server-cache-database-working-set.html


2

Ich habe den Arbeitsspeicher des Servers von 8 GB auf 64 GB aktualisiert und einen Test durchgeführt.Die Tabellengröße beträgt jetzt 16,05 GB und der Indexbereich 187 MB (1 gruppierter und 1 eindeutiger nicht gruppierter Index).Ich habe die RAM-Auslastung des Servers vor und nach dem Ausführen einer Abfrage überprüft, bei der alle 137 Felder in der Tabelle gemittelt werden.Der Gesamtspeicher stieg von 4,22 GB auf 20,4 GB = 16,18 GB, was 100,8% der Tabellengröße entspricht.SQLRockstar hat zu Recht vorausgesagt, dass die Größe des Arbeitsspeichers von der Größe der Tabelle abhängt.

Mehr Info:

  • Der Seiteninhalt des Clustered-Index beträgt 98,1%.
  • Weitere Abfragen in der Tabelle verursachten keine Platten-E/A.
  • SQL Server 2008R2 Developer
  • Der Ausführungsplan zeigt, dass der Clustered-Index verwendet wurde