SQL 2008 reservierter Speicher für Tabelle 10 mal größer als Tabelleninhalt
Hallo,
wir haben eine Bilddatenbank auf SQL Server 2008.
Es sind ca. 40.000 Bilder als image Wertetyp in der Tabelle stBilder.
Jetzt ist mir aufgefallen das die reservierte Bereich für die Tabelle bei ca. 120 GB liegt, während der tatsächlich verbrauchte Platz laut SQL Statistik nur bei ca. 17 GB liegt.
Frage:
Wie kann man eine einzelne Tabelle verkleinern?
Warum ist da so ein großer Unterschied?
Bei keiner anderen Tabelle liegt die Differenz zwischen reservierten und tatsächlichen Speicherverbrauch so hoch.
Ich habe schon versucht die komplette DB zu verkleinern, leider ohne Erfolg.
Das Backup ist leider durch diese Tabelle auch bei ca. 160 GB (mit Kompression).
Kann man überhaupt eine einzelne Tabelle verkleinern?
Warum verkleinert der SQL Server nicht die Tabelle wenn ich die ganze DB verkleinern will?
Liegt das an dem Wertetyp Image?
Zur Info:
In der Datenbank liegen Bilder von der Größe von 100 KB bis ca. 50 MB pro Bild.
Besten Dank.
Rene
wir haben eine Bilddatenbank auf SQL Server 2008.
Es sind ca. 40.000 Bilder als image Wertetyp in der Tabelle stBilder.
Jetzt ist mir aufgefallen das die reservierte Bereich für die Tabelle bei ca. 120 GB liegt, während der tatsächlich verbrauchte Platz laut SQL Statistik nur bei ca. 17 GB liegt.
Frage:
Wie kann man eine einzelne Tabelle verkleinern?
Warum ist da so ein großer Unterschied?
Bei keiner anderen Tabelle liegt die Differenz zwischen reservierten und tatsächlichen Speicherverbrauch so hoch.
Ich habe schon versucht die komplette DB zu verkleinern, leider ohne Erfolg.
Das Backup ist leider durch diese Tabelle auch bei ca. 160 GB (mit Kompression).
Kann man überhaupt eine einzelne Tabelle verkleinern?
Warum verkleinert der SQL Server nicht die Tabelle wenn ich die ganze DB verkleinern will?
Liegt das an dem Wertetyp Image?
Zur Info:
In der Datenbank liegen Bilder von der Größe von 100 KB bis ca. 50 MB pro Bild.
Besten Dank.
Rene
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 241305
Url: https://administrator.de/forum/sql-2008-reservierter-speicher-fuer-tabelle-10-mal-groesser-als-tabelleninhalt-241305.html
Ausgedruckt am: 04.04.2025 um 20:04 Uhr
9 Kommentare
Neuester Kommentar
Hallo Rene,
ob Dein Problem am Typ image liegt kann ich Dir zwar nicht sicher sagen, aber es wäre denkbar. Der Datentyp image ist auf jeden Fall veraltet und Du solltest ihn zu varbinary (max) ändern. Der kann dann auch mit normalen SQL-Befehlen bearbeitet werden und braucht keine Pointer mehr.
Gruß, Mad Max
ob Dein Problem am Typ image liegt kann ich Dir zwar nicht sicher sagen, aber es wäre denkbar. Der Datentyp image ist auf jeden Fall veraltet und Du solltest ihn zu varbinary (max) ändern. Der kann dann auch mit normalen SQL-Befehlen bearbeitet werden und braucht keine Pointer mehr.
Gruß, Mad Max
Moin Rene1976,
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
... 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
Zitat von @Rene1976:
Hi Max,
danke für dein Feedback.
Das Image ein alter Datentyp ist wußte ich schon.
Für neue Datentypen verwenden wir auch nur noch ausschließlich varbinary (max).
Ich kann mir aber nicht vorstellen, dass das am Datentyp Image liegt, oder?
Doch, und an gewissen PraktikantInnen, die beim SQLServer 2008 (ff) drei Vorgaben als "Architektur" oder "Konzept" bezeichnen.Hi Max,
danke für dein Feedback.
Das Image ein alter Datentyp ist wußte ich schon.
Für neue Datentypen verwenden wir auch nur noch ausschließlich varbinary (max).
Ich kann mir aber nicht vorstellen, dass das am Datentyp Image liegt, oder?
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
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
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
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