Werden die Ergebnisse von Tabellen-Scans im Speicher gehalten und heben dadurch den Nutzen von Indizes auf?

Question

Theoretische SQL Server 2008 Frage:

Wenn auf dem SQL Server ein Tabellenscan mit einer erheblichen Menge an "freiem" Speicher durchgeführt wird, werden die Ergebnisse dieses Tabellenscans im Speicher gehalten, wodurch die Effizienz, die durch einen Index auf der Tabelle eingeführt werden kann, zunichte gemacht wird?

Update 1: Die betreffenden Tabellen enthalten Referenzdaten mit ca. 100 - 200 Datensätzen pro Tabelle (Ich kenne die durchschnittliche Größe jedes Datensatzes nicht), also sprechen wir hier nicht von riesigen Tabellen.

Ich habe mit dem Kunden über die Einführung einer Memcached / AppFabric Cache-Lösung für diese Referenzdaten gesprochen, jedoch ist das derzeit nicht relevant und sie suchen nach einem "schnellen Erfolg", der mit minimalem Risiko verbunden ist.

Answer 1

Jede Seite, die im Scan gelesen wird, wird in den Pufferpool geladen und nur unter Speicherdruck gemäß der Cache-Verdrängungsrichtlinie freigegeben.

Bin mir nicht sicher, warum du denkst, dass dies die Effizienz beeinträchtigen würde, die durch einen Index auf der Tabelle eingeführt werden könnte.

Ein Index bedeutet wahrscheinlich, dass viele weniger Seiten gelesen werden müssen und selbst wenn alle Seiten bereits im Cache sind, so dass keine physischen Lesevorgänge erforderlich sind, ist es dennoch gut, die Anzahl der logischen Lesevorgänge zu reduzieren. Logische Lesevorgänge sind nicht kostenlos. Sie haben immer noch Overhead für das Sperren und Lesen der Seiten.

Answer 2

Neben dem Leistungsproblem (selbst wenn alle Seiten im Speicher vorhanden sind, wird ein Scan immer noch viele Male langsamer sein als ein Index-Suchen in einer Tabelle von signifikanter Größe) gibt es ein weiteres Problem: Überschneidung.

Das Problem bei Scans ist, dass jede Operation jede Zeile besuchen muss. Das bedeutet, dass ein select hinter einem insert/update/delete blockiert wird (da es garantiert ist, die von diesen Operationen gesperrte Zeile zu besuchen). Der Effekt ist im Grunde die Serialisierung von Operationen und führt zu enormen Verzögerungen, da SELECT jetzt auf DML warten muss, um jedes Mal zu commit. Selbst bei geringer Konkurrenz führt der Effekt zu einer insgesamt trägen und langsam reagierenden Tabelle. Mit Indizes vorhanden schauen Operationen nur auf Zeilen in den relevanten Bereichen und das reduziert, allein durch einfache Wahrscheinlichkeiten, die Konflikte. Das Ergebnis ist ein wesentlich lebhafteres, reaktionsschnelles, niedrig verzögertes System.

Answer 3

Vollständige Tabellenscans sind auch nicht skalierbar, wenn die Daten wachsen. Es ist ganz einfach. Mit zunehmenden Daten in einer Tabelle müssen vollständige Tabellenscans mehr Daten verarbeiten, um abgeschlossen zu werden, und daher dauern sie länger. Außerdem erzeugen sie mehr Festplatten- und Speicheranforderungen, was die Belastung Ihrer Ausrüstung weiter erhöht. Betrachten Sie eine Tabelle mit 1.000.000 Zeilen, auf der ein vollständiger Tabellenscan durchgeführt wird. SQL Server liest Daten in Form einer 8K-Datenseite. Obwohl die Menge der Daten, die in jeder Seite gespeichert sind, variieren kann, nehmen wir für unser Beispiel an, dass durchschnittlich 50 Zeilen Daten in jede dieser 8K-Seiten passen. Um einen vollständigen Scan der Daten durchzuführen und jede Zeile zu lesen, sind 20.000 Festplattenzugriffe erforderlich (1.000.000 Zeilen / 50 Zeilen pro Seite). Das entspräche 156MB Daten, die nur für diese eine Abfrage verarbeitet werden müssen. Es könnte eine Weile dauern, um alle diese Daten abzurufen und zu verarbeiten, es sei denn, Sie haben ein wirklich super schnelles Festplattensubsystem. Angenommen, diese Tabelle verdoppelt sich jedes Jahr. Im nächsten Jahr muss dieselbe Abfrage 312MB Daten nur für den Abschluss lesen.

Bitte beachten Sie diesen Link - http://www.datasprings.com/resources/articles-information/key-sql-performance-situations-full-table-scan