SQL 2008 reservierter Speicher für Tabelle 10 mal größer als Tabelleninhalt

Moin Rene1976,

Doch, und an gewissen PraktikantInnen, die beim SQLServer 2008 (ff) drei Vorgaben als "Architektur" oder "Konzept" bezeichnen.

Architektur-Vorgabe 1) Die Pagesize, also die kleinste physische Einheit, die von der DB-Engine angefordert werden kann ist auch die einzig mögliche: immer 8KByte. Niemals 4 KByte ( für die vielen kleinen Fixdatentabellen, die jedes Datenmodell hat) , niemals 32 MByte oder 2 GByte, nur weil vielleicht mal ein Datensatz so lang werden könnte..

Architektur-Vorgabe 2) Ein Datensatz, eine data row, wie der Franzose sagt, kann niemals auf zwei oder mehr Pages verteilt werden - eine data row MUSS immer in eine Page passen. Aber: mehrere Datensätze können in eine Page gespeichert werden.

Die Datentypen ntext, text und eben image passen aber nun mal NICHT (immer) in eine Page von 8 Kbyte Größe - ein image darf ja bis zu 2 GByte groß sein.

Merkt ja keiner - dass regeln die Jungs und Mädels eben intern - in einer data row wird eben nicht das "image" gespeichert, sondern nur ein 16-Bit-Pointer, der auf einen ausserhalb der Tabelle liegenden Speicherklumpen verweist. Der kann dann auch bis zu 2 GByte gross sein. Allerdings gilt hier auch: für jeden 16-Bit-Pointer muss minimal eine Page in der kleinsten Einheit, die das System verwalten kann, mitgeschleppt werden. Wenn du in deinem image-Datenfeld ein kleines *.gif-Bildschen von 345 Byte speicherst und die kleinste Pagesize 8KByte sind, dann hast da ein bisschen Verschnitt dabei.
[Edit]
Und da ein 16-Bit-Pointer nicht etwa auf einen Dateinamen o.ä. verweist, sondern nur auf die "Root-Page" des Speicherklumpens, geht es von da aus dann als Pointer-zu-Page-mit-Pointer-zu-Page-mit-Pointer-zu-Page...etc äusserst effizient weiter, bis die bis zu 2 GByte image irgendwo irgendwie untergebracht sind. Dh. 1MB-Image-Daten werden auf 128 Pages verwaltet, 10 MByte auf 1280 Seiten usw.
Kann man/frau so implementieren.... andere Datenbanken speichern dann doch lieber ""Pfad+Dateiname" in den DB-Tabellen und "Dateien" im Filesystem des OS ab (zum Beispiel Oracle mit->OCS)
[/Edit]

Architektur-Vorgabe 3)
Was das richtig Ungeschickte dabei ist: standardmäßig werden alle image (bzw text, ntext)-Felder unabhängig von ihrer Größe IMMER AUSSERHALB der Tabelle gespeichert, also auch die eben erwähnten 345 Byte eines .gif-Bildchens. Auf deutsch: für jedes benutzte image-Feld deiner Tabelle kannst du noch eine Betriebssystem-Page dazurechnen bei deiner Speicherbelegung.

Die Vorgabe 3 ist richtig richtig Panne - aber gatesseidank ist diese dritte wenigstens änderbar.
Suchmaschine mal nach der stored procedure "sp_tableoption".
Damit kannst du zB mit dem Ausführen von
sp_tableoption N'DeinLustigerTabellenName, 'text in row', '2000';
... einstellen, dass erst ab einer Grösse von >= 2000 Byte ein Image-Feldinhalt "extern" gespeichert wird - alle kleineren bleiben "innerhalb" der oben erwähnten 8-KByte Datenbank-data row, die ja ohnehin schon belegt ist durch den Datensatz.

Musst du halt ein bisschen "Verschnittminimierung" betreiben:
Wenn dein Datensatz OHNE das Image-Feld 500 Byte lang ist und
-> du den dritten Parameter der stored procedure auf 3000 setzt --> dann ist eine Page mit 2 Datensätzen zu je 500 Byte+Image-Feld=3500 Byte voll und du hast Verschnitt in der Größe 8 KByte minus 7500 Byte. Und jedes Image > 3000 Byte wird "extern" gespeichert.
-> du den dritten Parameter der stored procedure auf 7500 setzt --> dann ist eine Page mit 1 Datensatz zu je 8000 voll und du hast Verschnitt in der Größe 8 KByte minus 8000 Byte. Und nur jedes Image > 7500 Byte wird extern gespeichert.
--> du den dritten Parameter der stored procedure auf 1000 setzt --> dann ist eine Page mit 5 Datensatzen zu je 1500 voll und du hast Verschnitt in der Größe 8 KByte minus 7500 Byte. Aber jedes mistige Image > 1000 Byte wird extern gespeichert, auf einer (Betriebssystem-) Page, die definitiv unsinnig größer ist als 1000 Byte.


Schlussbemerkung:
- geh wech von diesen image-Datentypen. Jetzt.
- und lies auch einmal quer zum Thema "Datenkompression"/"compressed data". (obwohl SQLServer da nicht der Brüller ist)

Grüße
Biber

Hallo Rene,

möglicherweise hilft Dir der Befehl dbcc cleantable.

Ansonsten mach mal folgendes:
Leg eine Kopie von Deiner Tabelle an: select * into TabelleKopie from Tabelle
Wenn image unschuldig ist und irgendein Müll in der Tabelle schlummert, dann sollte die Kopie jetzt kleiner sein.
Ist die Kopie nicht kleiner, dann änder den Datentyp auf varbinary (max) und prüf dann nochmal.

Wenn die Kopie der Tabelle schon kleiner ist, dann könntest Du auch auf diese Art Dein Problem lösen:
1. Fremdschlüssel auf die Originaltabelle entfernen
2. Originaltabelle löschen
3. Kopie umbenennen, das ist jetzt Deine neue Tabelle
4. alle Eigenschaften, Indexe, Constraints, ... wieder auf der Tabelle anlegen

Und trotzdem solltest Du dann bald mal den alten Datentyp loswerden.

Gruß, Mad Max

Hallo Rene,

was verstehst Du unter "SQL Server Statistik"? Wenn Du irgendwelche Berichte meinst, die Daten stammen wahrscheinlich aus irgendwelchen Systemtabellen, die kannst Du also nicht einfach mal platt machen und neu aufbauen.

Für die Fragen zum Filestream solltest Du einen neuen Thread erstellen, weil Du diesen hier bereits abgeschlossen hast und deswegen wahrscheinlich nicht mehr allzuviele reinschauen.

Gruß, Mad Max

Hallo Rene,

die angezeigten Daten stammen aus der Systemtabelle sys.dm_db_partition_stats. Systemtabelle ist eigentlich falsch, das schimpt sich dynamische Verwaltungssicht. Aktualisieren kannst Du die nicht. Und wenn sie nicht stimmt, dann wäre es ein Fehler im SQL Server, hast Du das aktuelle SP drauf?

Außer einem neueren SP oder Verwendung von varbinary könnte also nur noch MS Euer Problem lösen.

Gruß, Mad Max